Link to home
Start Free TrialLog in
Avatar of ukerandi
ukerandiFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Data warehouse Date Null issue

Hi Experts,
In the date fields there are no values. For example, the order date is always available, but there are no shipped dates or received dates available in some of the records. That means "NULL" values. When I read some users' advice for the "fact table," it is mentioned that you can use blank dates, like "01/01/0001.",1900-01-01 , -42659,19000101 or 99991231 ,-1  I am just wondering what the best way is to solve this problem.
Because this issue can't connect with Dimension Date table 
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You need to analyze the usage of this table. But in general:

Null-Dates which making the start of a process or step are set to a meaningful minimum.
Others which are marking the end of a process or step are set to a meaningful maximum.

The reason be´hind this is, that existing queries using these columns should return them. E.g. a search for all deliveries which are shipped after 2021-08-11. Using here a minimum value would exclude such a row.

And for your date dimension: This means that it may need to start at 1900-01-01 and ends at 2099-12-31, But whatever minimum and maximum you chose, you need once to complete the date dimension between those values. Remember, the date dimension is normally a calculated one.
Avatar of ukerandi