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

Dealing with empty/missing dates in dimensions

4 posters

Go down

Dealing with empty/missing dates in dimensions Empty Dealing with empty/missing dates in dimensions

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

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

http://www.olap.it

Back to top Go down

Dealing with empty/missing dates in dimensions Empty Re: Dealing with empty/missing dates in dimensions

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

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

http://aginity.com

Back to top Go down

Dealing with empty/missing dates in dimensions Empty Re: Dealing with empty/missing dates in dimensions

Post  raghuk 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

Back to top Go down

Dealing with empty/missing dates in dimensions Empty Re: Dealing with empty/missing dates in dimensions

Post  BoxesAndLines Fri Aug 21, 2009 10:27 am

I leave them null also.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Dealing with empty/missing dates in dimensions Empty Leave them Null is the best choice

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

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

http://www.olap.it

Back to top Go down

Dealing with empty/missing dates in dimensions Empty Re: Dealing with empty/missing dates in dimensions

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