Date Dimension at Various Grains

View previous topic View next topic Go down

Date Dimension at Various Grains

Post  omng392 on Tue Jul 19, 2011 11:45 am

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.


omng392

Posts : 1
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  hang on Fri Jul 22, 2011 9:11 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  ngalemmo on Mon Jul 25, 2011 10:27 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Date Dimension at Various Grains

Post  VHF on Tue Jul 26, 2011 3:53 pm

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?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  ngalemmo on Tue Jul 26, 2011 4:40 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Date Dimension at Various Grains

Post  VHF on Tue Jul 26, 2011 4:56 pm

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

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  hang on Tue Jul 26, 2011 6:40 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  LAndrews on Tue Jul 26, 2011 7:51 pm

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

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

Post  hang on Tue Jul 26, 2011 10:16 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Date Dimension at Various Grains

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