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

View previous topic View next topic Go down

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

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

View user profile

Back to top Go down

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

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

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

View user profile

Back to top Go down

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

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

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

View user profile http://aginity.com

Back to top Go down

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

View previous topic View next topic Back to top

- Similar topics

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