Date dimension, "unknown" entry
3 posters
Page 1 of 1
Date dimension, "unknown" entry
When dealing with creating an "Unknown" or "Missing data" entry for the date dimension, what are the best practices for the columns in the date dimension which are non-text valued (i.e. "year", "month", "is_leap_year"), etc. given that these cannot (should not?) be null.
ryno1234- Posts : 33
Join date : 2015-01-07
Re: Date dimension, "unknown" entry
I don't think there is any agreement that attributes cannot be null. Keys should not be null, but that's different.
There is no benefit in putting contrived values in columns like that (year, month, etc…). I would leave them null. Flags and descriptive fields can be set to useful values. In the case of an 'is leap year flag', false would be a good choice.
There is no benefit in putting contrived values in columns like that (year, month, etc…). I would leave them null. Flags and descriptive fields can be set to useful values. In the case of an 'is leap year flag', false would be a good choice.
Re: Date dimension, "unknown" entry
There's not much value in putting not null constraints on the date dimension. This particular dimension is typically handcrafted as opposed to an ongoing ETL process. Once it's built, it's done for several years.
If you want to keep the not null constraint then you need to provide some sort of value. I typically put a 0 in any sort of numeric column and if the text column is big enough, I put 'not applicable'. I've also been known to put a space as well. There's usually one description column big enough to store 'not applicable'.
If you want to keep the not null constraint then you need to provide some sort of value. I typically put a 0 in any sort of numeric column and if the text column is big enough, I put 'not applicable'. I've also been known to put a space as well. There's usually one description column big enough to store 'not applicable'.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Date dimension, "unknown" entry
Thank you both for your response. I was struggling with this one due to readings such as this: http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/null-dimension-attribute/
ryno1234- Posts : 33
Join date : 2015-01-07
Similar topics
» Null value for dates
» Unknown number of relationships from dimension to fact until fact loaded
» Date Dimension: Representing partial dates/Imputing date values
» Eliminate Date Dimension Surrogate Key
» Always link date fields to Date Dimension?
» Unknown number of relationships from dimension to fact until fact loaded
» Date Dimension: Representing partial dates/Imputing date values
» Eliminate Date Dimension Surrogate Key
» Always link date fields to Date Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum