Showing Measure values as either ‘0’ or ‘NULL’ in Cube ?

Hello BIDN Family,

It been long time and I really miss BIDN family group network ,after a long time gap I would love to post this blog which might be helpful to the one who works in SSAS.

While designing a cube everyone simply doesn’t care about the ‘NULL’ or  ‘0’ values within the cube output. So through this blog , I want to share with you guys that how can we show the NULL  or 0 value output within the cube.

Also , if anyone need to show the exact client data either it’s NULL or as 0 within a Cube then for them this blog might be helpful too.

One can handle/Preserve the NULL values of data by following the blog by DevinKnight which is one of nice blog I referred

http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values

Since , BIDS will treat all the NULL values as ‘0’ while calculation and if you have hundreds of calculated measure and you want to show the values as either NULL or 0 as per you need.

And there you need to handle the 4cases 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).

The above all 4 case can be handle 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 post will be helpful for all of us in the field of BI mainly in SSAS/MDX and one who is looking for such solution.

Regards,

Anil Maharjan