Natural date key

View previous topic View next topic Go down

Natural date key

Post  gbaren on Thu Apr 03, 2014 10:45 am

I was looking at the date dimension spreadsheet in the DWT resources, and have a question regarding it's date key column. Is there an advantage to using an integer versus using date?

In my case, I decided to use hourly granularity for dates and was considering using date/time type. Is using an integer in form YYYYMMDDHH better than a date/time type?

Thanks!

gbaren

Posts : 15
Join date : 2014-01-30
Location : 02

View user profile http://equalsql.wordpress.com

Back to top Go down

Re: Natural date key

Post  LAndrews on Thu Apr 03, 2014 11:19 am


If time is required, then usually it would have its own dimension, resulting in both DIM_Date and DIM_Time.

The surrogae dimensional key is typically an integer. There are numerous debates on whether it should be a smart/meanignful key or not.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Natural date key

Post  ngalemmo on Thu Apr 03, 2014 12:07 pm

You do not use a date data type to hold the natural key for a date dimension in order to allow for invalid or unknown dates. Using a non-date type (number or string) allows you to accomodate bad dates.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Natural date key

Post  gbaren on Thu Apr 03, 2014 1:06 pm

Thank you once again for a very clear and concise answer.

So, with bad dates in mind, it would be better to just assign an identity rather than a smart key? I would imagine that if I gave them a smart key, some developers won't be able to resist using it as a date. It would be safer to answer question about the sanity of the design than making sure they don't do that. Is that the controversy?

gbaren

Posts : 15
Join date : 2014-01-30
Location : 02

View user profile http://equalsql.wordpress.com

Back to top Go down

Re: Natural date key

Post  LAndrews on Thu Apr 03, 2014 1:14 pm

Yes - that is the debate.

I've always tended to avoid smart-keys. I want consistancy for all my dimensions - if you want an attribute, you get it from the dimension, not some derivation of the key.

Also, As ngalemmo mentioned, having a true surrogate key allows you to easily acommodate missing/invalid dates.

Additionally, i've found that always using the date dimension for attributes allows you to change things like display formats in one place, rather that in each report/query.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Natural date key

Post  gbaren on Thu Apr 03, 2014 1:34 pm

Thank you for that explanation, LAndrews.

As for what you said about the Time dimension, I've given that some additional thought and briefly consulted with our analysts. One attribute of the Date dimension is giving me pause on separating it from the Time. It's the IsWeekend flag. Our weekend starts on Friday night and if I separated date and time, I would need to put IsWeekend into the fact table as another degenerate dimension along with IsPeak. At least, that's what it looks like to me at this point. It's all lines on paper so far, so I'm willing to be convinced that I'm committing dimensional heresy.

gbaren

Posts : 15
Join date : 2014-01-30
Location : 02

View user profile http://equalsql.wordpress.com

Back to top Go down

Re: Natural date key

Post  ngalemmo on Thu Apr 03, 2014 2:55 pm

I would create a second dimension, date_hour, to cover those attributes. Facts that are time dependent would reference both dimensions. If you try to do it in a single table you would run into issues when trying to create date level aggregates or dealing with facts that are not time dependent.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Natural date key

Post  Jeff Smith on Fri Apr 04, 2014 9:14 am

Would it make sense to have a Date Time dimension as a snowflake to the Date Dimension and the Time dimension?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Natural date key

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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