SSAS NULL value default behavior

One of my direct reports asked me last week about how SSAS handles NULL measure if present in the fact table. I think he encountered this in one of his business intelligence job interview :).

I thought let’s write a small post about the topic and this way others will also be get benefited plus I don’t have to explain it to him separately.

I have a sales fact table which is linked to product, measure type, organization and time dimensions. Here is part of the fact table -
 
As you see we have got plenty of NULL value under plan and forecast measures. We will stick to Actual and Plan measures for our discussion. Let’s browse the cube and put this in perspective for you to see how SSAS treat NULL measure value. Any guess at this point?
 
I have filter products with PRODUCT_SK in (48, 55 and 56) where we have NULL under plan but has got some Actual sales value. This is bit strange with SSAS which replaces NULL with 0 by default, meaning does not preserve NULL. However, this may mislead the business and you may want to preserve NULL for all intense purposes. In this case it is just that business has not decided on the plan, forecast etc for certain products does not necessarily mean that plan amount is 0.

Lets get straight to the SSAS measure properties and there is a way to preserver NULL value. Go to the cube solution, select you measure and change NullProcessing property under Source from Automatic to Preserve. Next process the cube and reconnect to OLAP db to refresh.
 
Here is what I mean. We don’t see 0 anymore. You should carefully decide whether or not you want to keep NULL and make use of this property.

Please leave any feedback or suggestions you have!