Dealing with empty/missing dates in dimensions

View previous topic View next topic Go down

Dealing with empty/missing dates in dimensions

Post  Andrea Vincenzi on Thu Aug 20, 2009 9:22 am

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.
avatar
Andrea Vincenzi

Posts : 8
Join date : 2009-02-04
Age : 62
Location : Rome (Italy)

View user profile http://www.olap.it

Back to top Go down

Re: Dealing with empty/missing dates in dimensions

Post  ngalemmo on Thu Aug 20, 2009 11:58 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Dealing with empty/missing dates in dimensions

Post  raghuk on Thu Aug 20, 2009 3:39 pm

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.

raghuk

Posts : 8
Join date : 2009-06-16

View user profile

Back to top Go down

Re: Dealing with empty/missing dates in dimensions

Post  BoxesAndLines on Fri Aug 21, 2009 10:27 am

I leave them null also.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Leave them Null is the best choice

Post  Andrea Vincenzi on Fri Aug 21, 2009 11:19 am

"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.
avatar
Andrea Vincenzi

Posts : 8
Join date : 2009-02-04
Age : 62
Location : Rome (Italy)

View user profile http://www.olap.it

Back to top Go down

Re: Dealing with empty/missing dates in dimensions

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