Recursion in MDX - How deep can it go (By Mosha Pasumansky)

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:

Read more...