Handerson


Is there any way to solve a circular reference in Analysis Service calculation

I'm migrating a Excel model to Analysis Service cube, but in Excel I can iterate to solve circular reference, but in Analysis Service calculations I can find a way to do that.





Re: How to solve Circular Reference in Analysis Service

Deepak Puri


This thread has an example of an iterative calculation implemented in AS 2000:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=203198&SiteID=1

Re: Iterative Calculations Analysis Server 2000
Mark as correct answer
Edit Post | Delete Post(s) | Split Posts |
Lock Post

Hi Garron,

Since your sample only involves iteration, without using data from any specific OLAP cube, the MDX below uses the Foodmart Sales cube only as a place-holder. The CALCULATION_PASS_DEPTH=20 specifies the number of iterations, which should culminate at CALCULATION_PASS_NUMBER = 21. In the Sample MDX App, the Ivalues returned are: Incentives = 121.951, Profit = 4878.049

>>

With Member [Measures].[Income] as '10000'
Member [Measures].[OtherExpenses] as '5000'
Member [Measures].[Incentives] as '0'
Member [Measures].[Profit] as
'[Measures].[Income] - [OtherExpenses]
- [Measures].[Incentives]'

Cell Calculation [IterativeIncentives]
for '({ [Measures].[Incentives] })'
as 'CalculationPassValue([Measures].[Profit],
-1, RELATIVE) * 0.025',
CALCULATION_PASS_NUMBER = 21,
CALCULATION_PASS_DEPTH = 20

select {[Measures].[Income],
[Measures].[OtherExpenses],
[Measures].[Incentives],
[Measures].[Profit] } on columns
from Sales

>>







Re: How to solve Circular Reference in Analysis Service

Handerson

Thanks, that's very helpfull but how can I use CALCULATION_PASS_NUMBER and CALCULATION_PASS_DEPTH in the MDX Script (AS2005 - cell calculation)







Re: How to solve Circular Reference in Analysis Service

Mosha Pasumansky

You can use CALCULATION_PASS_NUMBER and CALCULATION_PASS_DEPTH in AS2005 in exactly same way you did with AS2000, only with AS2005 you don't need to purchase Enterprise Edition to use them !

Just paste the CREATE CELL CALCULATION statement inside the script view - and it will work fine. AS2005 is backward compatible with AS2000 on MDX level.