Chris Webb's BI Blog

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

Recursive Calculation Problems

with 3 comments

If you read my blog, and you’re interested in MDX, you should be reading Jeffrey Wang’s blog too – he’s the new Mosha! Anyway, I’ve been having some performance problems with recursive calculations recently (in fact, I’ve had similar problems in the past as I say in the comments here) and Jeffrey has been helping me; he’s also written up his explanation in a blog post here:
http://mdxdax.blogspot.com/2011/04/performance-considerations-for.html
It does a great job of explaining why the performance of recursive calculations can vary so much, even if there is no good solution to these problems.

While I was wrestling with these problems, I had the idea that maybe the calculation could be rewritten in a non-recursive way using the techniques that Mosha detailed in this post, where the aggregation of values as a product can be achieved by turning it into a sum of logarithms. However the calculation was fairly complex and it was beyond my limited maths skills to do the rewrite. Luckily, I got introduced to a guy called Martin Cairns at SQLBits who is a maths whiz and very kindly helped me out, coming up with a great solution – he’s promised to write up his work in a new blog soon – but I then ran into the next problem that this approach relies on being able to find the base 10 logarithm of a number, and there’s no internal MDX function to do this. You can use the Excel Log10 function, as Mosha does, but when you do this you’re calling out to an external COM assembly which has two negative implications for performance:

  • It forces the formula engine into cell-by-cell mode
  • It also forces query scope caching, which means the formula engine can’t cache the result of the calculation for longer than the lifetime of the query

Hmm. An internal MDX version of the Log10 function would solve all these problems, so if you’d like to see one please vote on this Connect item:
https://connect.microsoft.com/SQLServer/feedback/details/666000/make-log10-an-internal-mdx-function

I don’t think it’s even possible to write a recursive calculation in DAX, but there is a DAX Log10 function so it seems like the log approach is going to be the only viable option here. I’ll explore that in a future post, perhaps…

Written by Chris Webb

May 1, 2011 at 12:03 am

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. [...] mentioned that I was a math expert (his words not mine); later on Jen was talking to Chris Webb and he mentioned a problem he was with the performance of a recursive formula in Analysis Services [...]

  2. Chris, it looks like this is a built-in MDX function now, at least in 2008 R2…which I discovered after voting :) You simply say Log10([YourMeasure]). When I used it just now it worked. Cheers!

    Samantha

    September 10, 2012 at 6:59 pm

    • Hi Samantha,

      Unfortunately, Log10 is not yet a built-in MDX function – I’ve just had this confirmed by the dev team. It works, as you’ve seen, but it’s still just a VBA function that’s available in MDX and not a native MDX function so it’s still going to suffer from all the performance overheads of VBA functions.

      Chris Webb

      September 10, 2012 at 9:44 pm


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 3,310 other followers

%d bloggers like this: