Context: You need to design an Essbase/Planning script or rule and wants to optimize calculation based on calculation performed.
Objective : Activate the ideal calculation mode to reach best performance while satisfying calculation functional objectives.
In an Essbase script or Planning business rule, various calculation method can be applied depending on type of calculation performed.
We’ll try to explain in this article in which case options @calcmode(block) or @calcmode(cell) should be activated.
1.How it works? – @CALCMODE(BLOCK) :
This calculation mode group the cells considered in the calculation and treat them all at once.
As cells are calculated simultaneously, the calculation will run faster, but you must carefully check result, especially in the case where some dependencies exist between the cells (especially when you use IF function).
2. How it works? – @CALCMODE(CELL) :
Cells involved in calculation are calculated one by one, sequentially. This is safer on the functional side, but calculation will take more time.
3.Syntaxe:
Activating one mode or another should be done just before calculation formula in the script.
Syntaxe will be like:
@CALCMODE(BLOCK);
or
@CALCMODE(CELL);
4. Application :
Warning : both mode can be activated by default depending on the function you use for the calculation.
For instance, cell calculation method will be activated by default with the following functions:
@ANCEST / @CURRMBR / @ISBMR on a dense dimension / @MDANCESTVAL / @MDPARENTVAL / @MDSHIFT / @NEXT / @ PARENT / @PARENTVAL / @PRIOR / @SANCESTVAL / @SPARENTVAL / @SHIFT.
Other functions will have the mode “block” activated.
5.Classic cases:
Case of the use of @PRIOR function:
When using PRIOR function to calculate a given account along the period dimension, we typically are in the case of a dependency between calculated data.
In the example below, we want the cumulated account to be calculated as a sum between the period movement added to the last period cumulated value. So to calculate the cumulated value of a given period we definitely need the previous period to be calculated.
Therefore, we will have to perform the calculation in CELL mode, as cells should be calculated sequentially.
In BLOCK mode, all cells will be calculated at once so functional result will be wrong due to data dependencies along the period dimensions.
Please find below the calculation result for both calculation mode:
Calculation mode Block:
Calculation mode Cell:
Case of the use of IF function:
With an “IF” function, multiple conditions can be used to apply different calculation depending on the context.
In this context, the use of the “BLOCK” mode will be interesting as the conditions will be parsed one by one and cells will be calculated simultaneously for each condition. This will make the calculation run faster.
But user must carefully check the dependancies between cells on different conditions of the “IF” function.
As the conditions are treated one by one in the sequence of their appearance in the IF function, use must check if any cell depend of another calculated in a following condition.
If so, then final result might be wrong.
Let’s check the example below:
Let’s consider 3 accounts “a”,”b”,”c” calculated through the simple following “IF” function:
In this case we have C depending on B, which is calculated in the “elseif” condition.
Activating the “Block” mode will calculates first first condition, with the “c” account calculation, and then the second condition, for “b” account.
In this case there is obviously a data dependancy and “c” is dependant from “b”, therefore block mode will provide uncorrect result, as seen below:
Considering the calculation syntax, user should activate the “cell” calculation mode:
