Date instead of date key in fact tables

Go down

Date instead of date key in fact tables

Post  Guest on 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

Re: Date instead of date key in fact tables

Post  nick_white on 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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Date instead of date key in fact tables

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

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