dates -roles

[dellsters]

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?

[BoxesAndLines]

Put all of them in the fact.

[Jeff Smith]

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.

[Sponsored content]