Member Calculation Order
The default order in which Essbase calculates outline members is, in general: Accounts, Time, dense, and sparse, taking into account the order of dimensions in the outline, and whether formulas are applied.
When you perform a default calculation (CALC ALL) on a database, Essbase calculates the dimensions in this order:
-
If both a dimension tagged as accounts and a dimension tagged as time exist, and if formulas are applied to members on the accounts dimension, Essbase calculates in this order:
-
Dimension tagged as accounts
-
Dimension tagged as time
-
Other dense dimensions (in the order in which they are displayed in the database outline)
-
Other sparse dimensions (in the order in which they are displayed in the database outline)
-
-
Otherwise, Essbase calculates in this order:
-
Dense dimensions (in the order in which they are displayed in the database outline)
-
Sparse dimensions (in the order in which they are displayed in the outline)
-
Note:
Attribute dimensions, which are not included in the database consolidation, do not affect calculation order. See Work with Essbase Attributes.
In the Sample Basic database, the dimensions are calculated in this order: Measures, Year, Scenario, Product, and Market.
You can override the default order by using a calculation script. See Develop Calculation Scripts for Block Storage Cubes.
Calculation Effects of Member Relationships
The order of calculation within each Essbase dimension depends on the relationships between members in the outline. The way members in the outline roll up (consolidate) is comparable to an odometer.
Within each branch (hierarchy) of a dimension, level 0 values are calculated first, followed by their level 1, parent value. Then the level 0 values of the next branch are calculated, followed by their level 1, parent value. The calculation continues in this way until all levels are calculated.
The following image shows the Year dimension from the Sample Basic database. The calculation order of its members are indicated on the left of each member. This example assumes that the parent members are not tagged as Dynamic Calc.
Figure 19-3 Calculation Order for Year Dimension of Sample Basic
Jan is the first member in the first branch. Jan has no formula, so it is not evaluated. The same applies to Feb and Mar, the other two members in the branch.
Essbase calculates Qtr1 by consolidating Jan, Feb, and Mar. In this example, these months are added, as indicated by the + consolidation operator, to provide a value for Qtr1.
Essbase then calculates the Qtr2 through Qtr4 branches in the same way.
Finally, Essbase calculates the Year member by consolidating the values of Qtr1 through Qtr4. As indicated by the + consolidation operators, these quarters are added to provide a value for Year.
How Members Consolidate
You can choose how Essbase consolidates members in a block storage cube by applying a calculation operator (+, -, /, *, %, ~, ^) to the members in the database outline.
If an accounts member has a time balance tag (First, Last, or Average), Essbase consolidates it accordingly. See Calculate First, Last, and Average Values.
If a parent member has a label only operator, Essbase does not calculate the parent from its children.
If a member has a ~ operator, Essbase does not consolidate the member up to its parent.
If a member has a ^ operator, Essbase does not consolidate the member in any dimension.
Note:
If you use dynamic calculations, Essbase may use a different calculation order. Refer to Calculation Order for Dynamic Calculation.
Order of Dimensions in the Database Outline
To ensure the required calculation results, consider the calculation order of the dimensions in the Essbase outline if it uses calculation operators to divide (/), multiply (*), or calculate percentages (%) for members, or if some members have formulas.
You need not consider calculation order if you use only calculation operators to add (+) and subtract (–) members in the database outline and you do not use formulas in the outline.
Formulas on Members in the Database Outline
If you place formulas on members in the Essbase outline, consider the calculation order of the dimensions. A formula that is attached to a member on one dimension may be overwritten by a subsequent calculation on another dimension.
For example, the Sample Basic database has a Measures dimension, tagged as accounts, and a Year dimension, tagged as time.
Measures is calculated first and Year second. If you attach a formula to Margin on the Measures dimension, Essbase calculates the formula when it calculates the Measures dimension. Essbase then overwrites the formula when it consolidates the Year dimension. Refer to Cell Calculation Order.
Use of the Calculation Operators *, /, and %
If you use calculation operators to multiply ( * ), divide ( / ), and calculate percentages ( % ) for members in the Essbase outline, consider the calculation order of the dimensions. The required calculated values may be overwritten by a subsequent calculation on another dimension.
For example, the Sample Basic database has a Measures dimension, tagged as accounts, and a Year dimension, tagged as time. Measures is calculated first and Year second. If you multiply members on the Measures dimension, the calculated results may be overwritten when Essbase consolidates values on the Year dimension. See Cell Calculation Order.
When you use a multiplication ( * ), division ( / ), or percentage ( % ) operator to consolidate members, carefully order the members in the branch to achieve the required result.
Figure 19-4 shows calculations operators as they appear in an outline. Assume that the user wants to divide the total of Child 2 and Child 3 by Child 1. However, if Child 1 is the first member, Essbase starts with Child 1, starting with the value #MISSING, and dividing it by Child 1. The result is #MISSING. Essbase then adds Child 2 and Child 3. Obviously, this result is not the required one.
Figure 19-4 Calculation Operators in the Database Outline
To calculate the correct result, make Child 1 the last member in the branch.
You can apply a formula to a member on the database outline to achieve the same result. However, it is far more efficient to use these calculation operators on members as shown in Figure 19-4.
Avoid Forward Calculation References
To obtain the calculation results you expect, ensure that the outline does not contain forward calculation references. Forward calculation references occur when the value of a calculating member is dependent on a member that Essbase has not yet calculated. In these cases, Essbase may not produce the required calculation results.
For example, consider the Product dimension shown below, which has three forward calculation references: two shared members (P100–20 and P300–20) and one nonshared member (P500–20):
Figure 19-5 Product Dimension with Forward Calculation References
In Outline Editor, when you verify the outline, Essbase identifies shared members with forward calculation references. Verifying the outline does not identify nonshared members that have forward calculation references. You can save and use an outline containing forward calculation references.
Consider the five members under Diet. The members P100-20, P300-20, and P500-20 have forward calculation references:
-
P100-20 (+) (Shared Member): Essbase calculates the shared member P100-20 before it calculates the prototype member P100-20. Because the prototype member P100-20 has children, Essbase must calculate the prototype member by adding its children before it can accurately calculate the shared member P100-20.
-
P300-20 (+) (Shared Member): Essbase calculates the shared member P300-20 before it calculates the prototype member P300-20. Because the prototype member P300-20 has a formula, Essbase must calculate the prototype member before it can accurately calculate the shared member P300-20.
-
P500-20 (+) (“P200-20” + “P300-20”): The formula applied to P500-20 refers to members that Essbase has not yet calculated. One such prototype member, P300-20, has its own formula, and Essbase must calculate P300-20 before it can accurately calculate P500-20. The members P200-20 and P400-20 calculate correctly, because they do not have forward calculation references.
-
P200-20 (+) (Shared Member): P200-20 is not a forward calculation reference, although Essbase calculates the shared member P200-20 before it calculates the prototype member P200-20. The prototype member P200-20 has no calculation dependencies (no children and no formula). Therefore, Essbase does not need to calculate the prototype member before the shared member. Essbase simply takes the value of the prototype member.
-
P400-20 (+) (“P200-10” * 2): P400-20 is not a forward calculation reference, although the formula that is applied to P400-20 references a member that Essbase has not yet calculated. The member prototype in the formula does not itself have calculation dependencies. P200-10 is the only member in the formula, and P200-10 does not itself have children or a formula. Essbase accurately calculates P400-20.
To get accurate calculation results for P100-20, P300-20, and P500-20, change the order of members in the outline. By placing the Diet shared members after the Regular members, as shown below, you ensure that Essbase calculates the members in the required order.
Figure 19-6 Changed Product Dimension Without Forward Calculation References
Now Essbase calculates:
-
The prototype member P100-20 before it calculates the shared member P100-20. So, P100-20 no longer has a forward calculation reference.
-
The prototype member P300-20 before the shared member P300-20. So, P300-20 no longer has a forward calculation reference.
-
The member with a formula, P300-20, before the member P500-20. So, P500-20 no longer has a forward calculation reference.