Dates being null

[OV]

I have several date fields in my tables that do not have a value. For example a shipment date does not have a value until something is shipped. Date fields are defines as integers in the fact tables. However, allowing them to be null, causes problems with aggregate tables (These date fields cannot be part of the key in aggregates since they have null values)
I resorted into replacing nulls with 0s but I am not sure if this is the way to go. Is there an accepted technique for missing dates?
Thanks, OV

 

[rasETL]

We have taken the approach of using 12/31/9999 for dates that either do not exist or dates that currently do not apply, such as an expiration date on an active record.

Hope that helps.

 

[johnherman]

Why not create a surrogate (or synthetic, or warehouse) key? This solves the problems of null dates. Would you really wan to use 12/31/9999 for (for instance) date of death for someone who is still alive?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[MDXer]

I agree with John. If you have a seperate Date Dimension table you can easily add a value of something less than 0 say -1 and define it as inapplicable. Key your null dates to this value then when you do the aggregation you have a value for null dates and they aren't dropped out do to the Null values.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[rasETL]

I agree in the use of a Date Dimension Table IF you have one and if you want or need to put a more sophisticated Database together.

However, the 12/31/9999 date option is a suggestion for a more basic alternative. 12/31/9999 is sometimes referred to as the "end of time" date, and as such, means the date currently holds no meaningfull information.

Sometimes it is easier to take the KISS method, especially if the development group and DBA group have relatively limited knowledge, and you need a system that can be supported and maintained by such a group. It may also be more appicable if the system being developed is relatively small and self-contained.

Hope that helps.

 

[rkishoretek1]

Data Mart Design
We are planning to build a data mart for calculating the efficiency of the Service Requests from Siebel.

The Audit table holds the history but its in a row format
We have the following columns in an audit table


ROW_ID CREATED LAST_UPD FIELD_NAME OPERATION_CD RECORD_ID NEW_VAL OLD_VAL
1-602GD 11/3/2004 7:08:15 PM 11/3/2004 7:08:15 PM Id New Record 1-602G9 1-602G9

1-602GF 11/3/2004 7:08:15 PM 11/3/2004 7:08:15 PM Severity Modify 1-602G9 1-Critical


1-602GG 11/3/2004 7:08:15 PM 11/3/2004 7:08:15 PM Priority Modify 1-602G9 1A


1-602GI 11/3/2004 7:14:39 PM 11/3/2004 7:14:39 PM Priority Modify 1-602G9 1C 1A


1-602GK 11/3/2004 7:15:33 PM 11/3/2004 7:15:33 PM Severity Modify 1-602G9 3-Medium 1-Critical


1-602GP 11/3/2004 7:16:10 PM 11/3/2004 7:16:10 PM Status Modify 1-602G9 Cancelled Open

What should be the fact table design for an SR audit trial table from Siebel.

pLEASE Do let me know the approach

I want to have one row for every change in the SR in the target fact table.