Date and Time Dimensions Template

Many times people are asking on the forums a common question: “what is the best way to make date/time dimensions?”. We will try to answer this question by sharing our experience. We do not pretend our approach to be considered “the only correct” or “the best in the world”, we simply want to share how we do it.

You may find the prepared SQL and AS databases here.

Date Dimension

In the SQL database you may find the following table:

It contains 2 sets of fields: one for the attribute keys, the other – for names and translations. Imagine that today is 08 November, 2008. For this date the values of the fields will be as following:

The rest fields will be used for names of the attributes and for translation. The algorithm of constructing the names has one feature: it distinguished names for the attributes [Month] and [MonthOfYear], [Day] and [DayOfMonth], [Quarter] and [QuarterOfYear]. Example: the [Month] will be “January 2008” while [MonthOfYear] will be just “January”. The reason is obvious: if you place [Month] on the rows of a pivot-table, and if you have data for several years, you will not get confused what each month stands for.

The dimension looks like this:

It has one natural Calendar hierarchy and a set of attributes as on the picture.

Sample how dimension looks like:

There is a stored procedure sprCalendarFromDateToDateFill that accepts 2 parameters - @FromDate and @ToDate and fills the table dimDate with records corresponding to this date interval. This procedure is called from the ETL.

Time Dimension

For the time dimension we have a separate table dimTime statically filled for each minute of the day:

The dimension looks like this:

Advantages

The main advantage is format of the keys – YYYYMMDD, YYYYMM, etc.: if date B > A, then the key for B is bigger than the key for A (both as integer and as a sting value). This feature can be used when building MDX queries. Let us show an example. If we have reporting based on SQL, and we want to build a report of the type “from date A till date B”, we do it like this:

select * from FactTable where DateColumn between '20081115' and '20081231'

To do the same thing in MDX we have to explicitly specify the members:

select …
from Cube
where ({[Date].[Calendar].[Day].&[20081115] : [Date].[Calendar].[Day].&[20081231]})

There is a big disadvantage with MDX: it is not generic. If members 20081115 or 20081231 do not exist in the dimension, the command will produce wrong result.

But there is another way of doing selections of the type “from date A till Date B”:

select …
from Cube
where (Filter([Date].[Calendar].[Day].Members,
[Date].[Calendar].CurrentMember.Properties(“Key”) >= “20081115” AND [Date].[Calendar].CurrentMember.Properties(“Key”) <= “20081231”))

This query is a little bit less optimal, but it is completely generic: you don’t have to worry about if A and B exist in the dimension, you have only to convert the dates A and B to the correct format YYYYMMDD (that is easy).

Conclusion

Feel free to use this template databases as the starting point for your Analysis Services projects. Your comments and suggestions are welcome to my e-mail This email address is being protected from spambots. You need JavaScript enabled to view it.

Ihor Bobak,
BIT Impulse (http://www.bitimpulse.com/)