SQL SERVER SSAS: How do I handle a NULL date value in my fact table, so I can process my time dimension without errors?

Question

I have a fact table that has a column with dates loaded from an MS Access source. The thing is, some of the values are NULL and the SSAS won't let me refer my DATE dimension to it.

Is it better to solve it at the SSIS stage or is there some solution at the SSAS?

Thank you very much for you help.

Answer 1

Best practice is not to have any NULL key (i.e. Dimension key) values in a Fact table.

Instead, create a MAX date in the Date dimension table (or an 'UnknownValue', -1 for instance) and key to that.

Answer 2

Sometimes it is undesirable for non-technical reasons to fix the nulls in the DSV or source system. In that case you can use the unknown member and null processing properties to work around this issue:

http://technet.microsoft.com/en-us/library/ms170707.aspx

I have done this when trying to highlight the data qualities problems or for fast prototyping purposes.