Date Dimension at Various Grains

Recently our data model work group has been discussing expanding/changing our Date Dimension to more of a time dimension to encompass smart keys to faciliate the Month and Quarterly fact tables, and still use the new time Dimension surrgoate key.

Why do you need to rename it as time dimension if the individual date is the grain of the dimension? I would stick to the date dimension, and norminate a date, maybe the first or the last date, in the period (ie. month or quarter) to represent that period, so that you can still use the surrogate key in your facts. For clearness, you may also create period dimension views based on the date dimension.

What I usually do is add natural keys to the date dimension for the other periods I wish to represent. The natural key would only be populated on the nominated row (per Hang's suggestion) that represents the particular period. This allows you to use the normal surrogate key lookup process without having to add logic to select a particular date. The natural key values would be approprate for the period, for example a month natural key could be the date in YYYYMM format.

ngalemmo wrote:What I usually do is add natural keys to the date dimension for the other periods I wish to represent. The natural key would only be populated on the nominated row (per Hang's suggestion) that represents the particular period.

Why do you "overload" existing date dimension records (such as the last day of the month to represent a month) rather than add additional records to the date dimension (such as a record representing a month)?

When using the last day of the month row to represent a month, isn't there a danger of the user putting a date-level attribute (date, day of week, etc.) into a report even when working with a fact table at a monthly grain? With a "dedicated" month-record in the date dimension these day-level attributes could be left blank/null/NA so the user doesn't get a misleading result. What is the down side to this approach?

When using the last day of the month row to represent a month, isn't there a danger of the user putting a date-level attribute (date, day of week, etc.) into a report even when working with a fact table at a monthly grain?

Actually, it is the need to include date level attributes in a 'monthly' report that is the downside of the extra row approach. For example, displaying the 'thru date' in the header of a month end report. Sure, it may be redundant, but people want to see it.

Bottom line is, by designating a date row, you have the date level attributes should you need them. The choice of which row to use (i.e. populating the natural key) should be driven by business rules that dictate which date should be shown when doing such a report.

ngalemmo wrote:Actually, it is the need to include date level attributes in a 'monthly' report that is the downside of the extra row approach.

That sounds like a nod towards having a month-level mini-dimension which could pick up a few additional month-specific attributes along the way (FirstDayOfMonth, LastDayOfMonth, DaysInMonth, etc.)

From a design perspective I like the mini-dimension approach, but I've found that having a separate mini-dimension sometimes complicates ad-hoc reporting across fact tables at different grains (such as monthly budget vs. daily actual). A single dimension table seems to cause fewer issues, particularly with Business Objects (especially given my meager universe design skills!)

I tend to have a single physical date dimension and other logical shrunken/aggregate dimensions through views if needed. As ngalemmo suggested, the users may need to know some attribute values specific to nominated date in the period, or you may have different nominated dates for different business process. Sometimes you may need a hot rolling date (MTD) for the current month. I think it is simpler and more flexible to leave these dynamic logics to the logical layers instead of physical aggregate dimensions.

I tend to have the single day dimension with all attributes, and then supplement it with physical mini dimensions (conformed of course) if required.

For example, in a warehouse with many fiscal period fact tables, I would probably have a physical fiscal period dimension table, rather than a logical table defined in the BI tool. I know the result will be the same, but I prefer to have the logic in the ETL rather than the BI tool.

LAndrews wrote:I would probably have a physical fiscal period dimension table, rather than a logical table defined in the BI tool.

But creating views for dimensions is not something done by BI tools, views in this case are similar to role playing dimension views recommended by Kimball, and should be part of dimensional schema.