Calculation Scripts:- Session 4

Consolidation Order: Lets understand how Essbase calculates the value of members when 

they are calculated using hierarchical Calculations.

Lets assume we have the following hierarchy:

As per hierarchy A will be calculated here as:

A = A1 * A2 / A3 % A4 + A5

We passed few values to descendants of A:

Here we will get 44 as the value of A, here is the answer is different from our BODMAS 

because Essbase follows Top Down Approach to calculate the values,  if you want to define it 

Mathematically then it actually:

A = ((((A1 * A2) / A3) % A4) + A5);

But their is a easier way to undestand it, yeah Top Down Approach:

Above example gives us the consolidation order in a simple hierarchy. Lets calculate the same 

in our our hierarchy which we were using since beginning, before we do that lets add some 

complexity to it. Here I will be adding three more members with consolidation property as + 

below COGS:

In above outline when we will be calcuating Margin with few test values then flow will be like 

(Here after locking and sending the test values I ran Default Calculation):

Theoretical Flow of Calculation:

Step 1: Control will go to 1 the consolidation operator will become the sign of member. ex: if the 

value is 50 and operator is + then value will remain 50, if the operator is - then value will be 

(-50), if any other operator is encountered as the first operation then the value at first child is 

ignored in the calculation. In our Example 10,000 is still 10,000 because the consolidation 

operator is +.

Step 2: Value from Step one i.e. 10,000 with operated with "Third party" with consolidation 

operator as + value became 15,000 now in temp memory.

Step 3: Value from Step two (15,000) is operated with "IC"'s value which is 3000 with 

consolidation operator +, now the value is 18000.

Step 4: Control comes to "Sales" where the value 18000 is assigned to "Sales".

Step 5: Once the First first child of Margin is calculated control goes to first child of Second 

child of Parent, in our case its Labor as this one is the first child  of its parent rule mentioned in 

the first step will come into picture and value will remain +1000.

Step 6: Value of RawMaterial is added to Labor and kept in temp.

Step 7: value from above calculation is added to Overhead.

Step 8: The value is stored in COGS.

Step 9: Value of COGS is subtracted from Sales and stored in Margin.

Types of Data Storage and how they are realted to calcualtions:

Essbase provides 6 data storage types, Each has its own properties, as we mentioned earlier 

we will keep on discussing these member types, lets discuss Label Only and Dynamic Calc 

Members for now ( Other later when will make an actual sense to us).

Label Only: These members do no actually store data, they are created for giving more sense 

to our outline, for grouping members, for ease of navigation and reporting purposes. When we 

try to retrieve the value for a label member it always give us the value of its first child. In other 

words, label only members has a pointer to the data of its first child.Label Only members cant 

be Level 0, i.e. they should have child below them. Let take an example: I have created three 

more members in our hierarchy, withe member Inventory as Label only member:

here the value for NA1's Opening Inventory For Jan is 100, if we lock and send this data back 

to essbase and then when try to retrieve the data we will get 100 for the Label only inventory as 

well because its the pointing to the value of its firs child

Dynamic Calc Members: These types of members are calculated on the fly when their value 

is requested rather during batch calculation process. We need not to run any calculation to get

the values for these members. In our example, Sales has three child which aggregates to give 

us the values of sales, here if we make Sales as Dynamic Calc then we need not to execute 

default calc, or any script to calculate the value for this member. I did the same for COGS, and 

there is no point of having Dynamic Calc Member consolidating to Stored Member so we 

made Margin as Dynamic Calc (will make more sense later).

Now the moment send any values for Children of Sales and COGS, the moment you will try to 

retrieve the data it will calculate the values of Sales, Cogs and Margin automatically. yeah we 

haven't ran any calculation to get the values of these members:

Here comes the last topic of this session, in our last session we talked about CALC ALL and 

AGG, here is AGG in detail:

AGG: This command consolidates database values on the basis of hierarchical relationship 

defined in the database outline but when we AGG our database it ignores all the member 

formulas, It is just a consolidation of members.We cannot AGG Dense Dimension. When a 

dimension contains less than six consolidation levels, AGG is

typically faster than CALC. Otherwise, the CALC command is usually

faster on dimensions with six or more levels.

AGG("Entity", "Product" );

We can Aggregate single or list of dimensions using AGG command.



Cheers...!!!


Rahul