How to show measure values as either ‘0’ or ‘NULL’ in Cube?
INTRODUCTION:
This article will allow you to know more about showing measure values as either 0 or NULL in SSAS OLAP Cubes. This will help you to understand what the conditions you need to consider while displaying measure values within a cube.
OVERVIEW:
While designing a cube everyone simply doesn't care about the ‘NULL’ or ‘0’ values within the cube output. This article will share with you how can we show the NULL or 0 value output within the cube.
Also, if anyone needs to show the exact client data, whether it’s NULL or as 0 within a Cube, then for them this article might be helpful too.
IN BRIEF:
One can handle/preserve the NULL values of data by following the blog by Devin Knight: http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values
Since BIDS will treat all the NULL values as ‘0’ while calculating, if you have hundreds of calculated measures and you want to show the values as either NULL or 0 you need:
And there you need to handle the 4 cases mostly NULL/NULL,NULL/0 , 0/0, 0/NULL.
If divisor is zero, then there will be no error. The cell value simply will be positive or negative infinity represented as 1.#INF - but people don't like this for some reason and prefer to replace it with NULL, which makes the user wonder why there is no data for that calculated measure.
Let’s say, we have a calculated measure as [Measure].[Cal] and you need to show like this.
[Measure].[Cal] =[Measure].[A]/ [Measure].[B]
case 1 when [Measure].[A]=0 and [Measure].[B]=0, then show 0,
case 2 when [Measure].[A]= NULL and [Measure].[B]=NULL then show NULL,
Case 3 when [Measure].[A]= 0 and [Measure].[B]=NULL then show 0,viceversa
Case4 when [Measure].[A]= value1 and [Measure].[B]=value2 then show value1/value2,
The best approach to calculating [Measures].[Cal] is the following:
IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )
If you return null, NON EMPTY will work and the calculation will be faster as there are optimizations in the engine to handle this specific pattern.
Least one used:
IIF([Measures].[B] = 0, 0, [Measures].[A] / [Measures].[B] )
You don't want to return a 0 in the second argument of the IIF as this will always return a value and will mean that you cannot use NON EMPTY in your queries (as the calculation would never return an empty value).
In the above all 4 cases can be handled within one nested IIF condition as you only want to display NULL if both A and B are NULL, otherwise it is always 0 or the number:
IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e
IIF(
ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,
IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES].[B])
)
Some more details to handle like this are further describe within Mosha Blog.
http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx
Hope this article will be helpful for all of us in the field of BI, mainly in SSAS/MDX, and anyone who is looking for such solution.
For example, some snapshots of the output:
SUMMARY:
From this article, now you are able to display the real data in measure values as either 0 or NULL as you need. Also, you got familiar with the MDX which helps to achieve the result.
This is helpful if anyone needs to show the exact client data as either NULL or as 0 within a Cube.