Skip to main content

TWOPASS... Why TWO PASSES?

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

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

  1. 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?
  2. Do we use TWOPASS only for percentage calculations or is there any other scenario where we use TWOPASS?
 There are two ways how you can perform Calculations in Essbase
  1. Build a calculation script to perform the calculation that you wanted (Batch Calculation)
  2. 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)
Essbase follows a specific order when performing a Batch Calculation or Dynamic Calculation. Understanding the order of calculation is very important which will give a clear picture to choose which option is better to perform a calculation.

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:

  1. Sparse dimensions
    • If the dimension tagged as time is sparse and the database outline uses time series data, Essbase bases the sparse calculation on the time dimension.
    • Otherwise, Essbase bases the calculation on the dimension that it normally uses for a batch calculation.
  2. Dense dimensions
    1. Dimension tagged as accounts, if dense
    2. Dimension tagged as time, if dense
    3. Time series calculations
    4. Remaining dense dimensions
    5. Two-pass calculations
    6. Attributes
 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.

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!!!

Comments

Post a Comment

Popular posts from this blog

EPM Cloud Tips & Tricks - #1

The first EPM cloud product was released in 2014 and it's been six years till date. I was recently part of an FCCS implementation project. I know what you might be thinking. Coming from completely essbase and planning background and been working on it for almost 12 years and doing an FCCS project? Well, it turned out that way and it was a change for me too than being in my comfort zone and took it as a challenge.  I have been very busy for over the past one year and I didn't really had a chance or time to get back to my blogging and sharing my knowledge. The project finally went live and I am going to share my bit of learnings. Some of it you might alread know The first tip is going to be an easy one and those who have worked in FDMEE / Data Management in the cloud, you might already know it. But, this is very important when it comes to FCCS as zeroes are valid from Balance Sheet standpoint Data Management by default doesn't load zeroes. Below is a excerpt from the document...

OAC - New Interface

At ODTUG Kscope18 , Oracle has revealed new features and groundbreaking updates that are going to come in the next release in OAC. Have you ever wondered what it's going to be I was so excited that I kicked-off the promotional offer and fired up my OAC instance. But, I was surprised and felt sad after looking at the instance that is in public cloud and the instance that is going to come in the next release. Not sure if I am missing anything here which will enable me to access the newer interface / newer instance The new interface looks sleek and looks a lot better than the previous interface. I am using the instance as parts of Hands-On lab at ODTUG Kscope18. Not sure till when this environment will be available. I will update this blog and make it more comparitive with what is available right now in public cloud New JET UI. I don't know if that is the right way to call it but that's what it says when you fire up the OAC instance new interface Less clutter and wa...

OAC - Switch Classic to Modern - Trick

Back to back posts today. But, I am excited and wanted to share this cool hack....Not sure if anyone has figured it out.... I did a blogpost on OAC new / modern interface and few features that I really liked. You can find my blogpost here . A good friend (became friends at Kscope18) Vijay Kurian  posted in his blog at theunlockedcube  about the Essbase Cloud features Part I and Part II and he has shown how to switch from classic interface to modern interface For some reason it didn't work for me in the pormotional OAC instance that I was using. However, I was able to switch based on what I poseted in my previous blogpost In general If you want to switch from Classic to Modern, follow the below screenshot However, for some reason that didn't work for me and I didn't knew why What I did might sound pretty silly but now I have the whole new Modern Interface that i can use without having to rely on the Deep Dive environment that I was using earlier. (That's Ora...