iterative query

any body know how i can transform a recursive query to iterative one?
if possible i need a simple example witth little explanation

Thanks alot.Please provide a sample of what you have now so that it would be easier to help you.|||

ok thanks before any thing,

it is for learnning purposes, i really have no example, but i have heard about it, so if possible if you can provide a simple scenario. with the conversion

thanks alot.

Iterative Calculations Analysis Server 2000

Hi all

I'm looking for some help on an iterative cell calculation. I read somewhere that this can be done using a combination of the calculation pass value and calculation pass depth. I'm still having trouble though.

The easiest way of explaining this is to use an example. Sales reps get incentives based on profit, however incentives affect expenses which in turn affects profits. Using a starting income statement with no incentive about I need to go through about 20 iterations to come an answer.

So basically we have the following lines - (Abridged version )

Income - 10000

Other Expenses - 5000

Incentives = ( Profit * 0.025 )

Profit = ( Income - Other Expenses - Incentives )

Over 20 passes we should have the following

Income - 10000

Other Expenses - 5000

Incentives = 121.95

Profit = 4878.05

Any help would be appreciated.

Thanks

Garron

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

>>

Here's a link to the AS 2000 BOL section which explains Pass Order:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp

>>

Understanding Pass Order and Solve Order

Two of the most powerful and, correspondingly, most difficult concepts in Microsoft? SQL Server? 2000 Analysis Services, solve order and pass order together determine the manner in which a cube is resolved when queries are processed. This topic assumes that you have a basic understanding of cubes, custom members, calculated members, and custom rollups.

>>

|||

Thanks for the help.

Is it possible to do this as a calculated cell within a cube?

Thanks

Garron Mosley

|||

Sure, you could create the same calculated members and cells in a cube, I just used query-scoped calculations as an illustration...

sql