recursive calculated measure

Peter Thurston

I am trying to write a calculated member that is an expression of the following series:

A(t) = A(t-1)*(1+R1) + a(t)*(1+R2)

where A(t) represents the aggregate value of the measure and a(t) represents the value observed during the period t. R1, R2 and a(t) are all calculated members themselves that are basically the first level of calculation above my facts. At any point along my time dimension t, I will be running the calculation for either 3 months or 12 months. The code for my calculated member is as follows but it simply runs away and never completes:

[Measures].[BigA]

AS iif(([Measures].[BigA],[Dim Time].[Time].currentmember.prevmember)=null,[measures].[littlea],([Measures].[BigA][Dim Time].[Time].currentmember.prevmember)*(1+[Measures].[R1])) + [measures].[littlea]*(1+[Measures].[R2])

Any suggestions

Bryan C. Smith

So, if I read this right you want to calculate BigA which is the BigA value of the previous period adjusted by some other values. The logic is recursive and would seem to require every calculated value to walk the time dimension back to the last entry in the dimension.

One suggestion would be to pre-calculate the BigA value in your data warehouse using a cursor and "walking forward" through your time dimension over time. Because you are not dividing anything, you should be able to calculate this at the leaf level and then use the SUM aggregation function in SSAS to return the correct value.

Hope that helps,
Bryan

Peter Thurston

I should have specified that R1 and R2 are both functions of the time dimension and other slicer dimensions as well but other then that your interpretation is correct. My other issue is that BigA is a measure of compounded value which is dependent on the current member of the time dimension as well as the number of historical periods the value is compounded over.

Bryan C. Smith

With that kind of complexity, I would definetely see if you could handle the bulk of the calculation in the SQL environment. You may not be able to calculate the whole value but you want to get it to a state where simple aggregations or a relatively simple expression would give you the value you need.

B.

Deepak Puri

"R1, R2 and a(t) are all calculated members themselves that are basically the first level of calculation above my facts.." - could you give some idea of how these calculations are defined

"..but it simply runs away and never completes.." - but do you get any infinite recursion error Does [BigA] work correctly for the trivial case of the initial time member - for the next time member (ie. does it work upto a certain point) If there's a problem even with the initial value, that could be investigated first.