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…
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!
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.
Hi Chris,
any way to do recursive calculations in DAX?
No, it’s not possible