Should dates in dimensional tables use dimDate

data-warehousedimensional-modelingolap

Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date?

For example, in dimensional table dimCustomer, there may be birthday, join date, graduation date, …. Etc.

Best Answer

I would model this by storing both the date value and the SK back to the date dimension. Here is why storing that actual state value allows you to have ms level precision and can be useful when someone wants to see exactly when something happened. however for aggregates the SK can be a computed column if you are worried about the two values going out of sync

Customer
ID               1
Name             John Smith
BirthdateValue   2018-07-04 04:20:69.007
BirthdateSK      20180704

Repeat the pattern as needed for other dates.