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.