Calculating Variance

This example includes a calculation of the variance percentage between Budget and Actual values.

Figure 135, Variance and Variance % in the Scenario Dimension shows an outline in which Variance and Variance % are tagged as Dynamic Calc, two-pass members.

Figure 135. Variance and Variance % in the Scenario Dimension

This image shows Variance and Variance % tagged as Dynamic Calc, two-pass members.

During a default calculation, Essbase aggregates the values on the Market and Product dimensions. Because percentage values do not aggregate correctly, the Variance % formula must be recalculated after the default calculation.

Because Variance % is tagged as a Dynamic Calc, two-pass member, Essbase dynamically calculates Variance % values when they are retrieved. The dynamic calculation overwrites the incorrect values with the correctly calculated percentages.

If you choose not to tag Variance % as a Dynamic Calc, two-pass member, use the following calculation script—which assumes that Intelligent Calculation is turned on (the default)—to perform a default calculation and to recalculate the formula on Variance %:

CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Variance %";

Essbase performs the following actions:

  1. Performs a default calculation of the database (CALC ALL).

    Alternatively, you can run a default calculation of the database outline without using a calculation script.

  2. Turns off Intelligent Calculation (SET UPDATECALC OFF).

  3. Marks the calculated blocks calculated by the variance formula of the calculation script as clean, even though the variance calculation is a partial calculation of the database (CLEARUPDATESTATUS AFTER).

    By default, data blocks are marked as clean only after a full calculation of the database.

  4. Cycles through the database calculating the formula for Variance %.

See Choosing Two-Pass Calculation Tag or Calculation Script and Using Two-Pass Calculation.

For information on calculating statistical variance, see the Oracle Essbase Technical Reference.