Writing Optimized Business Rules: Optimizing Essbase Calculation Scripts by Using NOT in IF Statements
In Oracle Essbase calculation scripts, you use
the if
statement to execute code if a condition is true,
else
and else if
statements to specify each new
conditions to test if the previously evaluated condition is false.
You can optimize code that use if
and else if
statements by analyzing the condition that meets the majority of your data and then
moving the evaluation of that condition to the beginning of the code so that it is
executed as early as possible.
Consider the following Calculation Script example for Sample.Basic, where Accounts and Time are dense:
SET UPDATECALC OFF;
"SalesYTD"(
IF(@ismbr("Jan"))
SalesYTD" = "Sales";
Else
"SalesYTD"="Sales" + @prior("SalesYTD");
Endif
)
In this case, over 90% of the data meets the criterion of the else
condition. Only about 10% of the data meets the criterion of the if
condition. Because of the @prior
function in the else
condition, January must be calculated before all other months. On running this
calculation, the total elapsed time is reported as:
Total Calc Elapsed Time for [IF.csc] : [0.203] seconds
In this calculation, note that the "SalesYTD" member is calculated in Cell mode,
which means that January, because it appears before other months in the outline, is
calculated first, regardless of the order of the if
conditional
statements.
You can optimize such scripts by reordering the statements and adding a
NOT
operator to the first condition as shown in this example:
SET UPDATECALC OFF;
"SalesYTD"(
IF(NOT(@ismbr("Jan")))
"SalesYTD" = "Sales" + @prior("SalesYTD");
Else
"SalesYTD"="Sales";
Endif
)
The calculation, in this case, takes less than a quarter of the time compared to the
previous example because over 90% of the data meets the criterion in the
if
statement.
Note:
In this example, it is imperative to calculate the formula in outline order (February must be calculated after January, March after February, and so on) to get the correct results. You can use the@calcmode(Cell)
to force order if it does not
happen automatically.