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

Is it best practice to use intelligence key on Date_Key in the Date dimension?

3 posters

Go down

Is it best practice to use intelligence key on Date_Key in the Date dimension? Empty Is it best practice to use intelligence key on Date_Key in the Date dimension?

Post  thomaszhwang Fri Aug 19, 2011 12:34 pm

Is it best practice to use intelligence key (YYYYMMDD as integer) on Date_Key (the surrogate key) in the Date dimension?

I think it is, but I read on Kimball's book saying it is not. Just want to clarify. Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

Back to top Go down

Is it best practice to use intelligence key on Date_Key in the Date dimension? Empty Re: Is it best practice to use intelligence key on Date_Key in the Date dimension?

Post  BoxesAndLines Fri Aug 19, 2011 2:16 pm

Either way is fine. It's nice to see a column and know what the date is. OTOH, it's also nice to be able to add/subtract date sk's to get elapsed days.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Is it best practice to use intelligence key on Date_Key in the Date dimension? Empty Re: Is it best practice to use intelligence key on Date_Key in the Date dimension?

Post  ngalemmo Fri Aug 19, 2011 3:07 pm

The reasoning behind using YYYYMMDD as the value for a date key (rather than an arbitrary number) was to give the DBA an easy way do define partitions for large fact tables, which are typically partitioned by date. By knowing the values of the key in advance, the DBA could prepare the necessary partitions.

However, over the years various DBMS systems have improved the capability to define how and when partitions are created. Some allow partitioning on every n'th value, so you can provide a similar partitioning result and allow the DBMS to keep up with it automatically (sort of) without defining explicit value ranges.

So the need to use YYYYMMDD has diminished. As a rule, any key in a dimensional warehouse should always be viewed as a surrogate value. From an application/user point of view the date key is not intellegent and does not have a value that can be manipulated or evaluated in a query or report.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Is it best practice to use intelligence key on Date_Key in the Date dimension? Empty Re: Is it best practice to use intelligence key on Date_Key in the Date dimension?

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