Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

dates -roles

3 posters

Go down

dates -roles Empty dates -roles

Post  dellsters 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?


Posts : 39
Join date : 2009-02-11

Back to top Go down

dates -roles Empty Re: dates -roles

Post  BoxesAndLines Wed Feb 16, 2011 10:58 pm

Put all of them in the fact.

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

Back to top Go down

dates -roles Empty Re: dates -roles

Post  Jeff Smith 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

Back to top Go down

dates -roles Empty Re: dates -roles

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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