Calculate Variance

As an example of calculating variance in an Essbase cube, consider a calculation of the variance percentage between Budget and Actual values. You can calculate it with one or two passes.

The following shows an outline in which Variance and Variance % are tagged as Dynamic Calc, two-pass members.

Figure 24-1 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:

Performs a default calculation of the cube (CALC ALL).

Alternatively, you can run a default calculation without using a calculation script.
Turns off Intelligent Calculation (SET UPDATECALC OFF).
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 cube (SET CLEARUPDATESTATUS AFTER).

By default, data blocks are marked as clean only after a full calculation.
Cycles through the cube calculating the formula for Variance %.