Month Attributes in the Date Dimension

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.