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

Question

I have a fact table in which there is a column with dates downloaded from an MS Access source. The fact is that some of the NULL and SSAS values ​​will not allow me to refer to its DATE dimension.

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

Thank you so much for your help.

Answer 1

Best practice is the absence of a NULL key (i.e. a dimensional key) in the fact table.

Instead, create a MAX date in the Date dimension table (or, for example, "UnknownValue", -1) and enter the key.

Answer 2

It is sometimes undesirable for non-technical reasons to fix zeros in a DSV or source system. In this case, you can use unknown elements and zero-processing properties to work around this problem:

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

I did this while trying to highlight data quality issues or for rapid prototyping.

Answer 3

Each member of the hierarchy has the "HideMemberIf" property. Setting this parameter to "NoName" should hide the null values ​​in the size browser and allow cube processing.

You can also create named calculations in the Datamart view. The named calculation will use the ISNULL function, which can populate values ​​instead of zeros. Then, of course, build a time dimension from these Named calculations instead of raw data fields.

Again, it's best not to have any zeros in your data at all, but usually you can fix this inside the cube.