Date dimension, "unknown" entry

View previous topic View next topic Go down

Date dimension, "unknown" entry

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

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Date dimension, "unknown" entry

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

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

View user profile http://aginity.com

Back to top Go down

Re: Date dimension, "unknown" entry

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

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

View user profile

Back to top Go down

Re: Date dimension, "unknown" entry

Post  ryno1234 on 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/
avatar
ryno1234

Posts : 33
Join date : 2015-01-07

View user profile

Back to top Go down

Re: Date dimension, "unknown" entry

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