Aggregated Date Dimension

[jimbo1580]

In my dimensional model, I have a Date dimension that plays many different roles in multiple fact tables. I will also have a fact table that store metrics aggregated at the Month level and one that stores metrics aggregated at the Quarter level. How should I populate the rows in my aggregated Date dimensions (Month, Quarter)? Should the keys of the rows in the aggregated tables match the rows in the detail Date dimension? If so, what are the benefits of this?

Thanks!

[VHF]

It sounds like the best practice is to use the same keys as the daily date dimension. Pick a key--tyically either the first day or the last day of the month/quarter--to use as the key for that month/quarter. Seems like there is a split of opinion on creating mini-dimenions that contain 1 row for each month/quarter or just using the daily date dimension table for everything--typically with the addition of flags to indicate that this row also represents a month or quarter.

In your case of working with aggregated data of a given grain it might not be quite as important, but when working with data at different grains--such as comparing monthly budgets or forecasts against daily facts--using the same key greatly facilitates reporting (by grouping on the date 'month' attribute, for example.)

[ngalemmo]

There is a discussion of this at http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/date-dimension-t290.htm

[Sponsored content]