Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

New MDX Divide() Function

with 5 comments

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:

image

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.

Written by Chris Webb

July 26, 2013 at 11:16 am

Posted in MDX, Multidimensional

5 Responses

Subscribe to comments with RSS.

  1. 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.

    David Clem

    July 26, 2013 at 3:07 pm

  2. 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.

    Lance England

    July 26, 2013 at 3:32 pm

  3. Well this is welcome news for the mere 99.999% of MS BI implementations that still use multidimensional SSAS and MDX.

    Sam Loud

    July 26, 2013 at 11:27 pm

  4. Reblogged this on analyzerhakan and commented:
    New function for MDX :)

    analyzerhakan

    July 29, 2013 at 8:50 am


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

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: