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

Date instead of date key in fact tables

2 posters

Go down

Date instead of date key in fact tables Empty Date instead of date key in fact tables

Post  Guest Tue Jul 22, 2014 3:49 pm

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.

Guest
Guest


Back to top Go down

Date instead of date key in fact tables Empty Re: Date instead of date key in fact tables

Post  nick_white Wed Jul 23, 2014 6:07 am

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

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Date instead of date key in fact tables Empty Re: Date instead of date key in fact tables

Post  BoxesAndLines Wed Jul 23, 2014 8:30 am

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

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

Back to top Go down

Date instead of date key in fact tables Empty Re: Date instead of date key in fact tables

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