Dealing with empty/missing dates in dimensions
4 posters
Page 1 of 1
Dealing with empty/missing dates in dimensions
I'd like to know other people's opinions on how to deal with missing dates in dimensions, which is a situation that happens quite frequently (for example, think of birth date in a customer dimension).
We all know that attributes in dimensions should not be left Null: Design Tip N° 43 suggests to replace them with values like "Unknown" or "Not provided", but doesn't tell us what to do in case of date fields.
What do you suggest in these cases? Leaving the field Null, with the effect that a report using birth date as a header will show a blank header, or using conventional values like 01/01/01, which are quite ugly anyway?
Replacing the date field with a band ("1-20, 21-30, ...) is sometimes the best solution, but not always applicable.
We all know that attributes in dimensions should not be left Null: Design Tip N° 43 suggests to replace them with values like "Unknown" or "Not provided", but doesn't tell us what to do in case of date fields.
What do you suggest in these cases? Leaving the field Null, with the effect that a report using birth date as a header will show a blank header, or using conventional values like 01/01/01, which are quite ugly anyway?
Replacing the date field with a band ("1-20, 21-30, ...) is sometimes the best solution, but not always applicable.
Re: Dealing with empty/missing dates in dimensions
If they are simply attrbutes, I leave them null. I can't think of any good reason to do otherwise. Populating it with an arbitrary date is misleading. Taking the birth date example, if you set it to 01/01/1800, how is a user going to know that you don't have a birthdate for the customer or there is a birthdate but somebody made a mistake entering it?
I think the design tip has more to do with attributes that describe the dimensional entity moreso than ancillary attributes. So, in the case of a date dimension you would have a row for null dates with some descriptive text (I always have a string format date column in the date dimension for display purposes) indicating an unknown value.
I think the design tip has more to do with attributes that describe the dimensional entity moreso than ancillary attributes. So, in the case of a date dimension you would have a row for null dates with some descriptive text (I always have a string format date column in the date dimension for display purposes) indicating an unknown value.
Re: Dealing with empty/missing dates in dimensions
I have a -1 added to all dimensions.
However, in DimDate/DimMonthEnd, since the date comparison should not fail, I have added -1 with a date of '1/1/1900' or a -99 date of '12/31/9999' depending on whether you need min date or max date for date compares.
However, in DimDate/DimMonthEnd, since the date comparison should not fail, I have added -1 with a date of '1/1/1900' or a -99 date of '12/31/9999' depending on whether you need min date or max date for date compares.
raghuk- Posts : 8
Join date : 2009-06-16
Re: Dealing with empty/missing dates in dimensions
I leave them null also.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Leave them Null is the best choice
"If they are simply attrbutes, I leave them null".
I agree with that... these are simply date attributes, not FKs to the date dimension, so in these cases I think that leaving a Null is the best thing to do.
I agree with that... these are simply date attributes, not FKs to the date dimension, so in these cases I think that leaving a Null is the best thing to do.
Similar topics
» Dates in many dimensions
» Are dates degenerated dimensions?
» Start and Finish dates and role-playing dimensions
» Dimensions with effective and end dates - best design and best way to join to fact tables?
» Fact with eff / exp dates referencing dimension with eff / exp dates
» Are dates degenerated dimensions?
» Start and Finish dates and role-playing dimensions
» Dimensions with effective and end dates - best design and best way to join to fact tables?
» Fact with eff / exp dates referencing dimension with eff / exp dates
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum