A Better Date Dimension – Modelling in Analysis Services

Hopefully you are continuing from part one of this post.

If you have run the script to create and populate your date, calendar and bridge tables you can go ahead and add the tables to your Analysis Services project’s data source view.  The tables all have foreign keys so just add them and the DSV will sort out the relationships.

Next, create two dimensions – one based on the date dimension and one based on the calendar dimension.  Set the Type of the calendar dimension to ‘Time’ and the ErrorConfiguration to ‘Default’.  On the calendar dimension create these attributes with the associated properties:

• Calendar Key
  Name: ‘Date’
  Type: Calendar –> Date
  NameColumn: ActualDate
• Calendar Name
  DefaultMember: [Calendar].[Calendar Name].&[1]
  IsAggregatable: False
  KeyColumns: CalendarOrder
• Date Name
  Type: Calendar –> Date
• Year
  Type: Calendar –> Years
  NameColumn: YearName
• Quarter
  Type: Calendar –> Quarters
  KeyColumns –> Quarter and Year
  NameColumn: QuarterName
• Month

Type: Calendar –> Months
KeyColumns –> Month, Quarter and Year
NameColumn: MonthName

You might well want to use some of the other columns in the calendar table such as month of year.  My date dimensions are usually very rich so fill your boots and add whatever you think will help your end users.

Next, create a hierarchy of Calendar Name –> Year –> Quarter –> Month –> Date and set up the attribute relationships.

 

 

At this point you should be able to process the dimension.  You can then browse it to get something like this:

As you can see, the definition of what constitutes ‘2008’ differs based on the calendar you expand.

Bridging the Many-to-Many

Create a new measure group in your cube using the bridge table as its source.  A count measure will be added automatically.  This is required but useless so set its Visible property to False.

On the Dimension Usage tab, make sure the new measure group is connected to the Date and Calendar dimensions (the relationships in the DSV will do this properly when you add the dimensions to this tab).

Any measure groups that need to join to the calendar dimension must have a regular join to the date dimension and a many-to-many join to the calendar dimension through the bridging table measure group.

You can now process and test the cube.

Here is an example with a very simple sales measure group, showing two calendars being browsed:

The TOTAL and all the total for each months for each calendar is the same – 75.  This is because the calendar name attribute is set to non-aggregatable.  But the months belong in different quarters.  For example, April is in Q2 in the Gregorian calendar, but it is the first month of Q1 in a fiscal calendar which starts April 1.

You can also use the calendar name as a parameter in a Reporting Services report based on the cube – just make sure the parameter is set to allow single values only.

What if you also create reports from the relational database?  You now have to join to two extra tables every time you want to include a date, don’t you?  Well, no, you don’t, and I will explain why in my next post.

A Better Date Dimension

This requirement has come up a couple of times but I have only just thought of a solution.  How can your date dimension support lots of different calendars (standard January – December Gregorian, fiscal calendars starting in various months, a manufacturing calendar, etc.) without the table getting immensely wide?  And how can you make the calendar in use user-selectable at report run time?

For example, suppose you want to create a report that can be sent to many different customers containing their activity with you, but each customer wants the data based on their own fiscal calendar?  You need to have multiple calendars and choose the one you want to categorise your facts by at run time rather than design time. You want to be able to give the users a parameter like this:

The standard way of having different calendars is to add more columns to the date dimension but this quickly becomes impractical – and it doesn’t solve the problem of letting a user choose which calendar to base a report on.

Well, how about making the relationship between facts and dates a many-to-many?  So each fact exists in lots of different calendars?  It’s a model that is supported in Analysis Services and will fit in well with SQL.  Just a question of implementing it.

How to go about this?  Three tables are needed.

• A Date dimension which the fact tables can join to.  This can be extremely simple – you only really need a date column and a surrogate key column. (Well, you could actually get away without the date column but let’s keep it to stay simple when assigning keys.)
  This will have a many-to-one relationship with the fact tables.  It behaves like a normal date dimension, there’s one record for each day.
• A Calendars dimension.  This will have a record for each date – for each calendar you want to build.
  The calendars dimension contains all the attributes for each date that would normally go into the date dimension.
• A bridging table between the two.

What we will end up with is more rows in our calendar dimension, not more columns.

The best way to understand is, of course, to see an example.  So here is a script which creates all the tables, sets relationships between them and populates all three.  The script creates a detailed and fully populated date dimension model.

Next post: setting up the Analysis Services model.