In this example, which is the simplest case, these conditions are true:

• No dimensions have time or accounts tags.

• The setting for consolidating #MISSING values is turned on.

• Market and Year are dense dimensions.

  Essbase calculates dense dimensions in the order in which they are defined in the database outline. Assume that the Year dimension is positioned in the database outline before the Market dimension and is calculated first.

The following grid shows a slice (a subset of the cells in a data block). In the grid, the calculation order for the slice's cells is represented by the numbers 1 - 6.

Data values have been loaded into the following input cells:

• Jan -> New York

• Feb -> New York

• Mar -> New York

• Jan -> Massachusetts

• Feb -> Massachusetts

• Mar -> Massachusetts

Essbase calculates the cells in the following order.

1. Qtr1 -> New York

2. Qtr1 -> Massachusetts

3. Jan -> East

4. Feb -> East

5. Mar -> East

6. Qtr1 -> East

Qtr1 -> East has multiple consolidation paths; it can be consolidated on Market or on Year. When consolidated on Market, it is a consolidation of Qtr1 -> New York and Qtr1 -> Massachusetts. When consolidated on Year, it is a consolidation of Jan -> East, Feb -> East, and Mar -> East.

Essbase knows that Qtr1 -> East has multiple consolidation paths. Therefore, it calculates Qtr1 -> East only once by consolidating the values for Qtr1 and uses the consolidation path of the dimension calculated last (in this example, the Market dimension), as shown below.

Based on the calculation order, if you place a member formula on Qtr1, Essbase ignores it when calculating Qtr1 -> East. If you place a member formula on East, the formula is calculated when Essbase consolidates Qtr1 -> East along Market.

If required, you can use a calculation script to calculate the dimensions in the order you choose.

In this example, these conditions are true:

• No dimensions have time or accounts tags.

• The setting for consolidating #MISSING values is turned off (the default).

• Market and Year are dense dimensions.

  Essbase calculates dense dimensions in the order in which they are defined in the database outline. Assume that the Year dimension is positioned in the database outline before the Market dimension and is calculated first.

The following grid shows a slice (a subset of the cells in a data block). In the grid, the calculation order for the slice's cells is represented by the numbers 1 - 7.

Data values have been loaded into the following input cells:

• Jan -> New York

• Feb -> New York

• Mar -> New York

• Jan -> Massachusetts

• Feb -> Massachusetts

• Mar -> Massachusetts

Essbase calculates the Qtr1 cells for New York, Massachusetts, and East and the East cells for Jan, Feb, and March.

1. Qtr1 -> New York

2. Qtr1 -> Massachusetts

3. Qtr1 -> East

4. Jan -> East

5. Feb -> East

6. Mar -> East

7. Qtr1 -> East

Qtr1 -> East is calculated on both the Year and Market consolidation paths. First, Qtr1 -> East is calculated as a consolidation of Qtr1 -> New York and Qtr1 -> Massachusetts. Second, Qtr1 -> East is calculated as a consolidation of Jan -> East, Feb -> East, and Mar -> East.

The results are identical to the results for example 1. However, Qtr1 -> East has been calculated twice. This fact is significant when you need to load data at parent levels.

Based on the calculation order, if you place a member formula on Qtr1, its result is overwritten when Essbase consolidates Qtr1 -> East along Market. If you place a member formula on East, the result is retained, because Market is calculated last.

In this example, these conditions are true:

• No dimensions have time or accounts tags.

• The setting for consolidating #MISSING values is turned off (the default).

• Data values have been loaded at parent levels.

• Market and Year are dense dimensions.

  Essbase calculates dense dimensions in the order in which they are defined in the database outline. Assume that the Year dimension is positioned in the database outline before the Market dimension and is calculated first.

The following grid shows a slice (subset of the cells in a data block) that needs calculating.

The cells are calculated in the same order as in Cell Calculation Order: Example 2. Qtr1 -> East is calculated on both the Year and Market consolidation paths.

Because the setting for consolidating #MISSING values is turned off, Essbase does not consolidate the #MISSING values. Thus, the data that is loaded at parent levels is not overwritten by the #MISSING values below it.

However, if any of the child data values are not #MISSING, these values are consolidated and overwrite the parent values. For example, if Jan -> New York contains 50000.00, this value overwrites the values loaded at parent levels.

Essbase must calculate the Qtr1 -> East cell twice to ensure that a value is calculated for the cell. If Qtr1 -> East is calculated according to only the last consolidation path, the result is #MISSING, which is not the required result.

The results show that Essbase first correctly calculates the Qtr1 -> East cell by consolidating Jan -> East, Feb -> East, and Mar -> East, and then calculates on the Market consolidation path. However, it does not consolidate the #MISSING values in Qtr1 -> New York and Qtr1 -> Massachusetts; therefore, the value in Qtr1 -> East is not overwritten.

In this example, these conditions are true:

• The Year dimension is tagged as Time.

• The Measures dimension is tagged as Accounts.

  Essbase calculates a dimension tagged as Accounts first, followed by a dimension tagged as Time. Therefore, in this example, Measures is calculated before Year.

• The setting for consolidating #MISSING values is turned off (the default).

• The Marketing, Payroll, and Misc Expenses values have been loaded at quarters (level 1).

The image below shows the Profit branch of the Measures dimension in the Sample Basic database. This example assumes that Total Expenses is stored (not a Dynamic Calc member).

Since there are no formulas and #MISSING does not consolidate, the upper level values are not overwritten. Data values with two consolidation paths (the non level-0 measures) are calculated twice.

The following grid shows a slice (a subset of the cells in a data block). In the grid, the calculation order for the slice's cells is represented by the numbers 1 - 17.

The following cells have multiple consolidation paths:

• Margin -> Qtr1

• Total Expenses -> Qtr1

• Profit -> Qtr1

Because the setting for consolidating #MISSING values is turned off, Essbase does not consolidate the #MISSING values. Any data loaded at parent levels is not overwritten by #MISSING values, and Essbase calculates the cells with multiple consolidation paths twice.

Based on the calculation order, if you placed a formula on Margin, its result would be overwritten by the consolidation on Qtr1.

The results are shown below:

The cell calculation order within a data block is not affected by formulas on members. When Essbase encounters a formula in a data block, it locks any other required data blocks, calculates the formula, and proceeds with the data block calculation.

If required, you can use a calculation script to change the order in which dimensions are calculated. See Develop Calculation Scripts for Block Storage Cubes and Develop Formulas for Block Storage Cubes.