How to handle NULL Dates in an “Accumulating fact table”

All we need is an easy explanation of the problem, so here it is.

One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article :
https://www.holistics.io/blog/the-three-types-of-fact-tables/.

Imagine we have an accumulating fact table which has three important date column in it

'Order_Datekey , Manufacturing_Datekey , Ship_Datekey'

When the table is being inserted for the first time ,there is no data for columns Manufacturing_Datekey and Ship_Datekey'. We only know the date in which the product has been ordered (Order_date). What I need to know is that how we can handle null values for Date columns ?
The important point I read in Kimbal book is that null should be avoided in a foreign key column.

'Referential
integrity is violated if you put a null in a fact table column declared as a foreign key
to a dimension table'

On the other hand we don’t have any value for these two date columns at first. What do you suggest?

Thanks in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You say "null should be avoided in a foreign key column," but are these dates really FKs?

Most likely, these dates are just attributes of an order, and if the dates are unknown (and the reason they are unknown may be "because it hasn’t happened yet"), then NULL is the best way to represent those attributes.

If the Manufacturing Date or Shipping Date is your Foreign Key (or part of your Foreign Key) to other tables, then it would be an indication that you might want to rethink your table structure.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply