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.

Solution 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.

Solution 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.

Solution 3

Each member of a hierarchy has a property "HideMemberIf". Setting this to "NoName", should hide the null values from the Dimension Browser and allow processing of the cube.

Also you could created Named Calculations in the Datamart View. The Named Calculation would use the ISNULL function, which can fill in values in place of nulls. Then of course build your Time Dimension off of these Named Calculations instead of the raw data fields.

Again, it's better not to have any nulls in your data altogether, but you can usually fix this inside the Cube.