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:

  1. Dimension tagged as accounts

  2. Dimension tagged as time

  3. Other dense dimensions (in the order in which they are displayed in the database outline)

  4. Other sparse dimensions (in the order in which they are displayed in the database outline)

• Otherwise, Essbase calculates in this order:

  1. Dense dimensions (in the order in which they are displayed in the database outline)

  2. Sparse dimensions (in the order in which they are displayed in the database outline)

Note:

Attribute dimensions, which are not included in the database consolidation, do not affect calculation order. See Working with 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 Developing Calculation Scripts for Block Storage Databases.

Understanding the Effects of Member Relationships

The order of calculation within each dimension depends on the relationships between members in the database outline. Within each branch 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 is shown on the left. This example assumes that the parent members are not tagged as Dynamic Calc.

Figure 20-3 Year Dimension from the Sample.Basic Database

Jan is the first member in the first branch. Jan has no formula, so it is not calculated. 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 members are added.

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. These members are added.

Determining Member Consolidation

You can choose how Essbase consolidates members by applying any 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 Calculating 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. See Calculation Order for Dynamic Calculation.

Ordering Dimensions in the Database Outline

To ensure the required calculation results, consider the calculation order of the dimensions in the database outline if you do either of these tasks:

• Use calculation operators to divide (/), multiply (*), or calculate percentages (%) for members in the database outline.

• Place formulas on members in the database outline.

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.

See:

• Placing Formulas on Members in the Database Outline

• Using the Calculation Operators *, /, and %

Placing Formulas on Members in the Database Outline

If you place formulas on members in the database 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. See Cell Calculation Order.

Using the Calculation Operators *, /, and %

If you use calculation operators to multiply ( * ), divide ( / ), and calculate percentages ( % ) for members in the database 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 20-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 20-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 20-4.

Avoiding 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 20-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 20-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.