Date instead of date key in fact tables

View previous topic View next topic 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

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum