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

Date dimension, "unknown" entry

3 posters

Go down

Date dimension, "unknown" entry Empty Date dimension, "unknown" entry

Post  ryno1234 Tue Jan 13, 2015 8:26 pm

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
ryno1234

Posts : 33
Join date : 2015-01-07

Back to top Go down

Date dimension, "unknown" entry Empty Re: Date dimension, "unknown" entry

Post  ngalemmo Tue Jan 13, 2015 11:08 pm

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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Date dimension, "unknown" entry Empty Re: Date dimension, "unknown" entry

Post  BoxesAndLines Tue Jan 13, 2015 11:52 pm

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'.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Date dimension, "unknown" entry Empty Re: Date dimension, "unknown" entry

Post  ryno1234 Wed Jan 14, 2015 8:14 am

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
ryno1234

Posts : 33
Join date : 2015-01-07

Back to top Go down

Date dimension, "unknown" entry Empty Re: Date dimension, "unknown" entry

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