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 25-1 Variance and Variance % in the Scenario Dimension
![This image shows Variance and Variance % tagged as Dynamic Calc, two-pass members. This image shows Variance and Variance % tagged as Dynamic Calc, two-pass members.](img/asscen01.jpg)
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 %.