dates -roles

View previous topic View next topic Go down

dates -roles

Post  dellsters on Wed Feb 16, 2011 6:38 pm

If you have many dates to the order (date shipped, date ordered, 10 others), would you have a date dimension role to all the dates or just the few most important ones?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: dates -roles

Post  BoxesAndLines on Wed Feb 16, 2011 10:58 pm

Put all of them in the fact.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: dates -roles

Post  Jeff Smith on Thu Feb 17, 2011 9:56 am

I think it depends on 1) how often the date field is used, and 2) the number of unique combinations for the 12 dates.

On 1 extreme, lets say that the fact table is huge and made up primarily of the date fields and none of the 12 date fields are queried very often and all of the 12 dates are usually the exact same date. You could create a junk dimension of the 12 dates. In this case, the Date Junk dimension would contain the surrogate key for the dimension and 12 date keys from the true date dimension table. The Junk date dimension could contain the calendar date as well as the date dimension key. This design would greatly reduce the size of the fact table. Performance degradation from the additional joins would be offset to some extent by a reduction in IO.

In the other extreme, lets say that the fact table is relatively short, none of the 12 dates are ever the same and all 12 date fields are queried on a regular basis. I would put all 12 dates on the fact table.

Reality is probably somewhere in between.

For fields that are regularly queried, I'd put on the fact. For dates fields that have few combinations and not quered very often (or used for partitioning), I would in a junk dimension.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: dates -roles

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