Natural date key
4 posters
Page 1 of 1
Natural date key
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!
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!
Re: Natural date key
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
Re: Natural date key
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.
Re: Natural date key
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?
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?
Re: Natural date key
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.
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
Re: Natural date key
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.
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.
Re: Natural date key
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.
Re: Natural date key
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

» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Date Dimension: Representing partial dates/Imputing date values
» scd2 effective date, end date data type
» Initial date of effective date column for SCD 2 implementation
» Loading dimension when source already has effective to and from dates
» Date Dimension: Representing partial dates/Imputing date values
» scd2 effective date, end date data type
» Initial date of effective date column for SCD 2 implementation
» Loading dimension when source already has effective to and from dates
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|