Date Dimension with multiple keys at different levels, is that normal?

Hi,

I have come accross a date dimension table design (in MS SQL which was made by somebody else) which not only has a key at it's most granular level (e.g. at the day level like the Microsoft adventure works example '20070101') but also has keys for other levels in the date Hiearachy e.g. Month (200701). I pressume additonal keys have been implemented because there are facts stored at different levels of grain e.g. at the day level in some instances or at the month level in others and this provides a method of key'ing the facts to the dimension at the requied level. I have tried this method out in SSAS and it seems to work. Is this normal practice because I normaly have facts at the day level?

Thanks,

Simon.

You can use date dimension as role playing dimension for other level time series, eg. month, year etc. However the best practice suggests nominating an existing date, say the first or last day of the month, to represent the month. Adding additional records to cater for higher level dimension makes other low level attributes inapplicable, which is unnecessary confusion to users.

Thanks for the reply Hang. I thought the same about allocating the fact (at month level), all to a particular day, however it does run the risk of producing unusual reports if the user pick a date range during the month that excludes that day. Out of interest I tried the using the design in SSAS; having a month key in addition to the date key. When I browsed the cube and drop the date dimension on (day attribute), you end up with the monthly value being repeated for everyday, so you also run the risk of incorrect values if the user picks the wrong dimension attribute. Maybe if I turned the fact into a caculated measure I could return the right value in this scenario. In the case of the fact table and solution I have been looking at, it is a Cognos cube/report that utilises the table (not SSAS) so maybe there is some special implementation with that product to handle this better.

In Ralph Kimball's dimension modelling book he speaks of 'allocating' facts to the lowest grain. Therefore in terms of a fact table having monthly records, I take this to mean that the table should be de-aggregated down to the date level, with a portion of the monthly value spread over each day (or whatever the business rule are.

I don't think you should 'allocate' monthly measures down to date level in the daily fact table. We call this kind fact table mixed grained. Instead, you should have separate aggregate fact table for month or any other level higher than date.

'Allocation' only applies to the degenerate dimension like order number in order item/line fact table, not the metric itself. The reason for 'allocation' is that you want to relate the order number to the dimension like product which is only available at item/line level, and you are not supposed to have repeated aggregate facts at order level in item/line fact. Mixed fact grains in the same fact table is the major culprit for double counting.

The idea of having additional natural keys to locate a date row to represent the month, quarter, year or whatever is not unusual. Having additional rows isn't that common, but regardless it doesn't have anything to do with the issue you discussed about cubes.

One rule when dealing with facts and cubes is you do not mix grains. If you have on set of measures that are daily and another set of measures that are monthly, you do not put them in the same fact table or cube for that matter. If you combine them you need to store them at the same grain, inother words, the daily values are aggregated.

It is possible some cubes have added extensions that allow you to combine the two (making the monthly numbers semi-additive), so, consult your user guide.

I have those columns in my data dimension as well. I also have year, e.g. 2011, 2010, etc.