Time Dimension Best Practices

Question

Hello,

We're considering using MDS to store our data warehouse dimensions and I'm wondering if there are any best practices or guidance on how time dimensions might be managed by MDS (or whether it even makes sense to do so).

The following videos did a nice job of explaining how simple collections and hierarchies could be managed with MDS and the Excel Add-In but didn't touch on time at all.

http://technet.microsoft.com/en-us/sqlserver/hh709039.aspx
http://technet.microsoft.com/en-us/sqlserver/hh709038

I've searched around myself without finding anything that specifically addresses implementing time dimension within MDS but if anyone knows of any good resources, please let me know.

Thanks!

Dave

Answer 1

In my opinion, you have two design options:

1. A domain entity approach which would entail the creation of the following entities: Year, Quarter, Month and a Calendar table with a row per date and Year, Quarter and Month as domain entities. In the Month entity I would include attributes such Month Number - e.g. 1 for February, Short Month Name -  e.g. Feb - Full Month Name -  e.g. February - and any other attributes you might need in your data warehouse for a month. I would do something similar for the Quarter entity -  i.e., Quarter Number (1, 2, 3 or 4), Quarter Abbreviation (Q1), and Full Quarter Name (Quarter 1, Quarter 2, Quarter 3 and so on). You might need or might not need to do something similar for Years -e.g., 2013, 13, Y-13 ...)
2. Something very similar can be achieved for collections.

Personally, I prefer the first approach. My personal preference is to use collections for simple lists - e.g. Colors or, in the context of this thread, days of the week -  e.g. Monday, Tuesday and so on. As a general rule of thumb, I use an entity (a domain entity) for anything that requires more than one attribute (other than a name). My experience is that in a data warehouse Months, Quarters and Days typically require a number of supporting attributes.

A word about codes, use smart codes -  e.g, 01 for January and Q1 (not 1), 2013 for the Year, and 20130221 for the date.

A word of caution about hierarchies, I might be wrong about this, if you want to create a derived hierarchy looking like Year > Quarter > Month > Day, you are going to need 4 entities linked as follows Year > Quarter > Month > Day. This, in my opinion, is a pain in the neck, because your Month entity becomes a Month in Year entity and your Quarter entity becomes a Quarter in Year; I am not trying to be pedantic here, February and February 2013 are not the same thing.

A word on fiscal years, if you are lucky and have a fiscal year running from 1-Jan to 31-Dec - i.e., the same as the calendar year-  then you can link the Month and Quarter entities. Otherwise, I am afraid you need a more complicated model.

Hope this helps