Date instead of date key in fact tables

[Guest]

Shall I use date field in fact tables instead of DATE dimension key (YYYYMMDD).
* Memory and performance is same as integer key as the internal representation of date field is integer.
* It avoids joining date dimension table each time if I need the actual date in the select query (and if the fact table contains multiple date fields).
* Date dimension can be joined (using date field) if any date related attributes are required in the output.

[nick_white]

As the Date Dim SK is the one SK where the value has meaning (i.e. it has the format of YYYYMMDD rather than being a random number) why not just have this in your fact table - you can then convert it to a date value in any SQL query or join it to the Date Dim if you need to?

Holding it as a Date column in your fact table and then joining that to the Date Dim breaks the fundamentals of Kimball's Dimensional Modelling methodology - where you only join facts to Dims via numeric SKs

[BoxesAndLines]

For Teradata, I use date, for all other DB's (Oracle, SQL Server, DB2) I use a smart key.

[Sponsored content]