THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

How to check if cell is empty in MDX

Recently I was looking at cube from customer, which had hundrends of calculated members. I noticed, that many of them were simple ratios in the form of a/b. However the consultant who developed these calculated members wanted to avoid division by zero so he wrapped the expression inside IIF MDX function (on the side note, this is wide spread practice, but I really wonder whether it is worth it. 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 the ratio). But what made me really surprised was how the checks for division by zero were done. It seemed like, there were no two calculated members which used the same logic. I have seem the following variants:

IIF(b = 0, NULL, a/b) 
IIF(IsEmpty(b), NULL, a/b)
IIF(b IS NULL, NULL, a/b) 

and all possible combinations of above, i.e.

IIF(b IS NULL OR IsEmpty(b), NULL, a/b) 
IIF(IsEmpty(b) OR b = 0 OR b IS NULL, NULL, a/b) 

etc. Let's try to analyze what each condition really does. The first one

IIF(b = 0, NULL, a/b) 

Is actually the most correct one. The operator = compares two scalars (i.e. number or string). In the right hand side we already have number zero, so it reduces the left hand side to b.Value, i.e. value at coordinate b and compares it to zero. Since we wanted to avoid division by zero - this is all that was needed. The second expression

IIF(IsEmpty(b), NULL, a/b)

is slightly different. What it does is, it invokes MDX function IsEmpty, which, as the name suggests, checks whether cell value (in this case at coordinate b) is empty or not. Note, that if the cell value is empty, it is treated as number zero in arithmetic operations, however, it is possible, that b had value zero, which is not empty ! Therefore check for IsEmpty is appropriate when the user wants to differentiate empty or missing values from the existing values (for example in NON EMPTY like analysis), but is not appropriate for checks in division by zero. Finally the third expression


is completely absurd. If you will check my earlier post about IS operator, you will see that IS operator compares MDX objects, such as members or levels, but not scalars. I.e. b IS NULL will compare whether MDX member b and NULL member are the same object. Well, obviously they are not the same object, since b is existing member, and NULL member is not, therefore expression b IS NULL will always return false !

In conclusion, we established, that the correct way of checking whether expression evaluates to zero is by using = 0 syntax, and checking whether expression evaluates to empty or missing value is by using IsEmpty().

Published Thursday, June 30, 2005 11:12 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement