Data Warehousing and Business Intelligence

28 May 2012

Month Attributes in the Date Dimension

Filed under: Data Warehousing — Vincent Rainardi @ 6:36 am

When building a date dimension we often have month attributes such as:

  • Month Name: January, February, March
  • Month Number: 1, 2, 3

The primary function of the month attributes is to drill down from year or quarter to date. And in this case, the above attributes are useless, because it does not have the year element. You cannot drill down from 2012 to January. You can drill down from 2012 to January 2012. So you need the year element in the month attribute, like this:

  • Month Name: January 2012, February 2012, March 2012 (*1)
    or: 2012 Jan, 2012 Feb, 2012 Mar (*2)
  • Month Number: 2012-01, 2012-02, 2012-03 (*3)

In my experience, some users preferred to have *3 (the year in the front and all numeric), so that it could be sorted automatically. In SSAS, you can have the sort key so that *1 and *2 can be sorted automatically too. But after the data has been exported to Excel, it’s not sorted any more.

This is also the case with Quarter attribute. It’s no good to have Q1, Q2, Q3 without the year. We need to have the year there to be able to drill down, i.e. 2012 Q1, 2012 Q2, 2012 Q3. This way, the quarter attribute can be made into a hierarchy, i.e.

The same with date attribute, it needs to have the year and month elements, i.e.

  • Date: 2012-05-14, 2012-05-15, 2012-05-16
  • or 2012 May 14, 2012 May 15, 2012 May 16
  • or 14 May 2012, 15 May 2012, 16 May 2012
  • or 14/5/2012, 15/5/2012, 16/5/2012

So that the date hierarchy will look like this in SSAS or BO/Cognos:
(left: all numeric with year in the front, right: month name in character)

My point here is: to make a date hierarchy (in AS/BO/Cognos), the month attribute must have more members than the quarter attribute. And the quarter attribute must have more members than the year attribute, so that we can drill down from year to quarter to month to date.

1 Comment »

  1. Its usually hard for beginners to get there head around but nevertheless worth pointing out anyway. A months attribute (one that shows each month as { Jan, Feb, Mar …}) is not the same as a month attribute (which identifies the month by reference to year) in the cube space. Both have usages for summarisation but are really different attributes in the cube. For example, you don’t show ‘months’ in a year / month hierarchy when the moths only refer to Jan, Feb … because this is poor dimension design.

    Comment by Paul te Braak — 28 May 2012 @ 7:35 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: