A few weeks ago I saw Rob Collie’s blog post about the DAX Divide() function, and I was a bit surprised that I hadn’t seen it before. Then, yesterday, I found that the same function has appeared in MDX in SSAS 2012 Multidimensional. Here’s the entry in BOL:
http://msdn.microsoft.com/en-us/library/jj873944.aspx
The syntax is:
Divide (<numerator>, <denominator> [,<alternateresult>])
Numerator and Denominator are self-explanatory; alternateresult is the constant value you want to return in case of division by zero, and if it is not specified a null is returned.
It turns out that it was added after RTM of SSAS 2012, and officially first appeared in SSAS 2012 SP1. This is the first new MDX function since… what, 2005? 2008? Five long years at least.
[A loud bump is heard as Chris falls off his chair in amazement]
This is important because there have been requests for a safe divide function in MDX for a long time. I posted a request on Connect, for example, and Darren Gosbell blogged about it here. It would have been nice if someone had mentioned to me that this feature had been added…
Why should you care about this? Well, anyone with any experience of MDX will know that you need to trap for division-by-zero and division-by-null when writing calculations. Consider the following query in Adventure Works:
with
member measures.[France Sales] as
([Measures].[Internet Sales Amount],
[Customer].[Country].&[France])
member measures.[US Sales] as
([Measures].[Internet Sales Amount],
[Customer].[Country].&[United States])
member measures.[France as a % of US] as
measures.[France Sales]/measures.[US Sales]
, format_string=’0.00%’
select
{measures.[France Sales],
measures.[US Sales],
measures.[France as a % of US]}
on 0,
[Date].[Date].[Date].members
on 1
from [Adventure Works]
The measure I’ve highlighted divides two other measures, and returns the value 1.#INF (infinity) when the measure [US Sales] is null:
1.#INF is not something you want to show to your end users. Furthermore, Mosha showed us all a long time ago that when dividing we should always use the pattern
iif(measures.x=0, null, measures.y/measures.x)
to get the best performance.
It now looks like this pattern has been superseded by the Divide() function. Here’s the measure in bold from the query above rewritten to use it:
member measures.[France as a % of US] as
divide(measures.[France Sales],measures.[US Sales])
, format_string=’0.00%’
From my limited testing on Adventure Works performance seems to be the same as with the iif() pattern, but I have heard that in other scenarios it may perform better. So I would recommend you try testing it on your cube, and use it in all your MDX from now on.
Finally! This was a baffling oversight by MS. The functionality was available via a global setting in the old Oracle Express BI product over 15 years ago.
Hi Chris,
My buddy Neal Waterstreet discovered the DAX Divide fucntion and even blogged about it a few weeks before Rob (not that it matters, but just trying to hype up my friend a bit). His entry is here:
http://www.nealwaterstreet.com/2013/06/25/dax-divide-by-zero-error/
I’m amazed they added it to MDX too! Since it’s in 2012 SP1, that means like, three people in the world can currently take advantage of it =)
Thanks for all you do for the SQL community.
Well this is welcome news for the mere 99.999% of MS BI implementations that still use multidimensional SSAS and MDX.
Reblogged this on analyzerhakan and commented:
New function for MDX 🙂
Hi!
In my limited testing Divide() has actually performed worse.
What does your experience of the last 2 years show?
Thanks!
Christos
I’ve never seen Divide() cause performance problems. It could be that the real problem is somewhere else in your code, and the change to using Divide() is making it more obvious. What were you using before?
The setup is simple: I take a physical measure, create a previous year (Weekday) value with lag(364), and then want the change in percent.
It’s not causing “problems” but in cases with sparse data DIVIDE(A-B,B) is slower (up to x2) than IIF(B=0,NULL,(A-B)/B).
The cube and script have much bigger problems; I was curious, if anyone else has made similar observations.
Thanks for the many awesome posts!
Christos