THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Recursion in MDX - How deep can it go ?

Recursive calculations are not uncommon in MDX. For example, one way to compute rolling sum over Time is to define calculated member as

CREATE MEMBER [Measures].[RollingSales] AS ' [Time].PrevMember + [Measures].[Sales] '

To understand how this expression works, it may help to rewrite it to the identical expression, but a little bit more explicitly:

CREATE MEMBER [Measures].[RollingSales] AS 
   ' ([Time].PrevMember, [Measures].[RollingSum]) + ([Time].CurrentMember, [Measures].[Sales]) '

Basically, it goes to the previous time period applying the same calculation RollingSum recursively, and then adds the values of Sales for the current time period. It would be very interesting discussion to compare performance of this approach to more conventional SUM(Ytd()) - but perhaps some other time. Another example of using recursion is given in the "MDX Solutions" book, in the "Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance" chapter. Basically there expression goes back in Time until it hits the time period which is not empty. This is classic LastNonEmpty aggregation function, which is now natively supported in Analysis Services 2005, but to since it is simple enough example which illustrates recursion, we will use it here:

CREATE MEMBER [Measures].[LastNonEmptyPrice] AS 
   ' Iif(NOT IsEmpty([Measures].[Price], [Measures].[Price], [Time].PrevMember) '  *

*  ("MDX Solutions" book then goes into additional complications to account for the case when [Time].PrevMember is empty member, but this is really not needed neither in first example, nor in this one, since in that case it will evaluate to NULL member, which will automatically break recursion and return empty value).

There are more examples where recursion in MDX comes handy, but what we want here is to answer the question that George Spofford asks at page 68 of "MDX Solutions":

We do not know whether to recommend recursive functions as general style because we do not know how deep the stack that the recursion relies on is. It seems to be deep enough for all applications that we have tried.

Analysis Services uses thread's stack for the recursive MDX function calls. Prior to AS2K SP3, there was a scalability bug, which caused about 150 KBs to be used per single recursion iteration. What it meant, that with the stack size of 4 MBs - there was space for only about 30 recursive calls. It is pure luck, that George didn't run into this limitation when he wrote a book, because if the data on which he tested was sparse enough to have 30 months in which price didn't change - the recursion calculation would've exhausted the thread stack and returned an error. In AS2KSP3 this problem was fixed, and depending on the MDX functions which are being executed, the cost of one recursion call went down to few KBs, which left room for hundreds of recursion iterations before the limit was reached. But we didn't stop there in SP3 ! What we have done, is whenever we saw that we are about to use all the stack from the current stack, we would create new thread with twice as much stack size as the current one and so on. Basically, the stack size is made virtually infinite, i.e. limited only by the amount of memory in the machine. In Analysis Services 2005 the implementation is very similar, with the difference that instead of creating new threads it creates new fibers, which are lighter and should put less load on the Windows.

Recursive calculations in MDX are cool, and often in AS2K they can outperform equivalent calculations rewritten to use loops over tuples. However, please don't consider this writeup as unconditional endorsement of recursion - just like any other tool it should be used with care and only when applicable. One of the drawbacks of recursion is that query optimizer often gives up when it sees recursion, whereas it can perform optimization when it sees iteration over set. As Analysis Services 2005 is tuning up its query optimizer - expect to see very different performance profiles from AS2K.

Published Sunday, January 23, 2005 2:53 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement