Date instead of date key in fact tables
2 posters
Page 1 of 1
Date instead of date key in fact tables
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.
* 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.
Guest- Guest
Re: Date instead of date key in fact tables
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
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
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Date instead of date key in fact tables
For Teradata, I use date, for all other DB's (Oracle, SQL Server, DB2) I use a smart key.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» DATE OR DATE KEYS IN FACT TABLES
» Storing Date Keys in dimension tables versus fact tables
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Actual Date vs Date Key in Fact table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Storing Date Keys in dimension tables versus fact tables
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Actual Date vs Date Key in Fact table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum