TWOPASS... Why do i need TWO PASSES for a calculation to get it right?

So, You use TWOPASS on a member with Dynamic Calc to override the default calculation order.

Note: You can always control the order of calculation if you are using a calculation script to calculate that member

Below is the section of content from Essbase Database Administration Guide

For dynamically calculated values, on retrieval, Essbase calculates the values by calculating the database in the following order:

So, let's say that if your retrieve has multiple members which are dynamic calc (Across multiple dimensions) and you will tag that member as TWOPASS that needs to be calculated at last.

One of my colleague who was new to Essbase asked me.. What is TWOPASS?

As usual, I took the standard example of % Calculation and said that

"When you are performing % calculation using a member formula, you have to calculate again at Total level than to aggregate the percentages from child to parent. In such case, you have to re-calculate again at total level and in order for the essbase to do this, we tag the member as TWOPASS so that it will calculate again in the End. Below is the example i gave

__Calculation Without TWOPASS__
Jan Feb Mar Q1

Sales 1000 1200 800 3000

Profit 20 50 20 90

Profit % 50 24 40

**114**

Profit % has to be re-calculated again at Q1 to provide the correct results

__Calculation With TWOPASS__
Jan Feb Mar Q1

Sales 1000 1200 800 3000

Profit 20 50 20 90

Profit % 50 24 40

**33.33**
When we tag the member Profit % as TWOPASS, it calculates correctly. But, the next question he asked made me think as it's been a while that I was made to think :)

His question was not just one but two

- When we tag a member as dynamic calc and write a formula, it has to calculate correctly. Why do we have to tag as TWOPASS to tell essbase to calculate it again?
- Do we use TWOPASS only for percentage calculations or is there any other scenario where we use TWOPASS?

- Build a calculation script to perform the calculation that you wanted (Batch Calculation)
- Use Member Formula
- You can make the member as Dynamic Calc and essbase would calculate that member on the fly only when you retrieve (Dynamic Calculation)
- Make it stored and calculate that member in Calculation Script (Batch Calculation because the member is stored and is explicitly calculated using a calculation script)

So, You use TWOPASS on a member with Dynamic Calc to override the default calculation order.

Note: You can always control the order of calculation if you are using a calculation script to calculate that member

Below is the section of content from Essbase Database Administration Guide

For dynamically calculated values, on retrieval, Essbase calculates the values by calculating the database in the following order:

You can Achieve the same functionality in ASO using Solve Order Property. Using Solve Order, you can define the order of calculation similar to BSO Application.

**Bottomline:**Always evaluate if you really need TWOPASS as it has to calculate twice and it may take lot of time if you have complex calculations / multiple dynamic calc members.

Hope this information helps you when to use TWOPASS and when not to

Happy Learning!!!

Very nice and clean explanation.

ReplyDelete