Aggregated Date Dimension

View previous topic View next topic Go down

Aggregated Date Dimension

Post  jimbo1580 on Thu Dec 24, 2009 9:54 am

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!

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Aggregated Date Dimension

Post  VHF on Thu Dec 24, 2009 12:23 pm

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.)


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

View user profile

Back to top Go down

Re: Aggregated Date Dimension

Post  ngalemmo on Thu Dec 24, 2009 1:20 pm

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Aggregated Date Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum