Should date field in DW date-map be of Date data type or of String data type, if I want to put placeholders?

Question

I have a date map (date dimension) like every other data warehouse.

The most obvious way to store date field in the date field is in date datatype. However, I want to keep some records in my date dimension which ID as negative values and the date field should give description of why this date is invalid.

For example, in my fact table, let's say I have a field called order_date_id which references the date dimension. However, for some records in the fact table, I want to say that the order_date was not recorded by the system and hence we can't use it. But I want it's entry present.

I thought that I would make an entry in the date dimension with ID=-1 and date = 'Date was not recorded'. But to use this kind of a placeholder, I will have to keep date as a string value. If I store it as a string value it will be very ineffective when I compare two dates.

Please advise a good practice.

Answer 1

I think you are confusing two things;

• Date Dimension Design
• Identification of Date not assigned by system

This is how I would design the date dimension

Date_id = number with yyyymmdd - intelligent key and this will be the primary key; this saves doing a lookup to the database to assign the id Date - the actual date Year - year Month - Month (yyyy-mmm or yyyy-mmmm) Month_Nbr - Month ( yyyy-mm ) Quarter - yyyy-Qn(1,2,3,4)

all the other attributes you need

if there are sales order which do not have a date assigned, assign them 19000101 and have them in your fact table... if you incrementally keep getting sales order without dates, then I would consult the business and put in the 1st day of the month of when they are arriving into the system; this will be a more acceptable solution.