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 :
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.
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 🙂