Handling Divide by null in Cubes for PPS, a better way than IIF()

Today i needed to get rid of the “1.#INF” error you get from SSAS when there is a divide by zero or null. The reason for this was that i was building a scorecard in PPS that had a “% change from previous month” calculated member as one metric in the KPI. This was specified as (Current-Previous)/Previous with the appropriate format string. When there was no activity in the previous month Previous would be null and this would give the error. I wanted to produce null instead so i could use the PPS scorecard option to replace null with a custom string.. in my case just a hyphen (i.e. “-“)

I initially though of using IIF() to solve my problem but i have read many times how it should be used thoughtfully as it can affect performance in certain scenarios. I have also experienced this in practice. Unfortunately ive lost the original post i read but i will reproduce the method i found here.

The SSAS formula parser tries to performs certain optimizations and we can take advantage of that! ill simplify the example by using just two values A and B.

If our calculated member originally took the form:

A/B

we replace it with:

(B * (A/B)) / B

What does this do? Well if you do the simple maths, effectively it just multiplies the calc by 1.. but the advantage is that the parser will not evaluate the rest of the expression if B is null (N.B this does not work if B = 0) . I tested this behaviour with the following query:

with member a as 55

member b as null
member c as 0

member c1 as a/b
member c2 as a/c

member c3 as (b * (a/b))/b
member c4 as (c * (a/c))/c

select  {c1, c2, c3, c4} on 0

from

[AdventureWorks]

Result:
divByNull

Its interesting that c4 returns “-1.#IND”. This (according to the IEEE 754 definition) means “Negative Indefinite”. Basically 0/0 is special case and is not the same as any other divide by zero.

Here is a related post on this topic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: