Aggregated Date Dimension
3 posters
Page 1 of 1
Aggregated Date Dimension
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!
Thanks!
jimbo1580- Posts : 23
Join date : 2009-04-30
Re: Aggregated Date Dimension
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.)
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.)
Last edited by VHF on Thu Dec 24, 2009 1:24 pm; edited 2 times in total (Reason for editing : clarity)
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Aggregated Date Dimension
There is a discussion of this at http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/date-dimension-t290.htm
Similar topics
» Conformance of Dimension for facts aggregated at different levels
» Date Dimension: Representing partial dates/Imputing date values
» Store Aggregated data in dimension
» Eliminate Date Dimension Surrogate Key
» Always link date fields to Date Dimension?
» Date Dimension: Representing partial dates/Imputing date values
» Store Aggregated data in dimension
» Eliminate Date Dimension Surrogate Key
» Always link date fields to Date Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|