Cell Calculation Order
The order in which Essbase calculates the cells within each data block depends on how you have configured the database.
Each data block contains all the dense dimension member values for its unique combination of sparse dimension members. Each data value is contained in a cell of the data block.
How you have configured the database determines the calculation order of dense dimension members within each block, as well as the calculation order of blocks that represent sparse dimension members.
See the following examples:
Cell Calculation Order: Example 1
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 table shows a subset of the cells in a data block:
Table 20-3 Calculation Order Example 1: Input Cells and Calculated Cells
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | 112345 | 68754 | 3 |
| Feb | 135788 | 75643 | 4 |
| Mar | 112234 | 93456 | 5 |
| Qtr1 | 1 | 2 | 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 following cells. In the example below, the calculation order for these cells is represented by the numbers 1 through 6 that appear in the cells:
-
Qtr1 -> New York
-
Qtr1 -> Massachusetts
-
Jan -> East
-
Feb -> East
-
Mar -> East
-
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.
Table 20-4 Calculation Order Example 1: Results
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | 112345 | 68754 | 181099 |
| Feb | 135788 | 75643 | 211431 |
| Mar | 112234 | 93456 | 205690 |
| Qtr1 | 360367 | 237853 | 598220 |
Based on the calculation order, if you place a member formula on Qtr1 in the database outline, Essbase ignores it when calculating Qtr1 -> East. If you place a member formula on East in the database outline, the formula is calculated when Essbase consolidates Qtr1 -> East on the Market consolidation path.
If required, you can use a calculation script to calculate the dimensions in the order you choose.
Cell Calculation Order: Example 2
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 example shows a subset of the cells in a data block:
Table 20-5 Calculation Order Example 2: Input Cells and Calculated Cells
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | 112345 | 68754 | 4 |
| Feb | 135788 | 75643 | 5 |
| Mar | 112234 | 93456 | 6 |
| Qtr1 | 1 | 2 | 3/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. In the example below, the calculation order for these cells is represented by the numbers 1 through 7 that appear in the cells:
-
Qtr1 -> New York
-
Qtr1 -> Massachusetts
-
Qtr1 -> East
-
Jan -> East
-
Feb -> East
-
Mar -> East
-
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.
Table 20-6 Calculation Order Example 2: Results
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | 112345 | 68754 | 181099 |
| Feb | 135788 | 75643 | 211431 |
| Mar | 112234 | 93456 | 205690 |
| Qtr1 | 360367 | 237853 | 598220 |
Based on the calculation order, if you place a member formula on Qtr1 in the database outline, its result is overwritten when Essbase consolidates Qtr1 -> East on the Market consolidation path. If you place a member formula on East in the database outline, the result is retained, because the Market consolidation path is calculated last.
Cell Calculation Order: Example 3
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 example shows a subset of the cells in a data block:
Table 20-7 Calculation Order Example 3: Input Cells and #MISSING Values
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | #MISSING | #MISSING | 181099 |
| Feb | #MISSING | #MISSING | 211431 |
| Mar | #MISSING | #MISSING | 205690 |
| Qtr1 | #MISSING | #MISSING |
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.
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.
Table 20-8 Calculation Order Example 3: Results
| Year-Market | New York | Massachusetts | East |
|---|---|---|---|
| Jan | #MISSING | #MISSING | 181099 |
| Feb | #MISSING | #MISSING | 211431 |
| Mar | #MISSING | #MISSING | 205690 |
| Qtr1 | #MISSING | #MISSING | 598220 |
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.
Cell Calculation Order: Example 4
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 the Qtr1, parent level.
The image below shows the Profit branch of the Measures dimension in the Sample.Basic database. This example assumes that Total Expenses is not a Dynamic Calc member.
Figure 20-9 Profit Branch of the Measures Dimension
The following table shows a subset of the cells in a data block:
Table 20-9 Calculation Order Example 4: Input Cells, #MISSING Values, and Calculated Cells
| Measures/Year | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
| Sales | 31538 | 32069 | 32213 | 13 |
| COGS | 14160 | 14307 | 14410 | 14 |
| Margin | 1 | 4 | 7 | 10/15 |
| Marketing | #MISSING | #MISSING | #MISSING | 15839 |
| Payroll | #MISSING | #MISSING | #MISSING | 12168 |
| Misc | #MISSING | #MISSING | #MISSING | 233 |
| Total Expenses | 2 | 5 | 8 | 11/16 |
| Profit | 3 | 6 | 9 | 12/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. Thus, any data that is loaded at parent levels is not overwritten by the #MISSING values and Essbase calculates the cells with multiple consolidation paths twice.
The results are shown below:
Table 20-10 Calculation Order Example 4: Results
| Measures/Year | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
| Sales | 31538 | 32069 | 32213 | 95820 |
| COGS | 14160 | 14307 | 14410 | 42877 |
| Margin | 17378 | 17762 | 17803 | 52943 |
| Marketing | #MISSING | #MISSING | #MISSING | 15839 |
| Payroll | #MISSING | #MISSING | #MISSING | 12168 |
| Misc | #MISSING | #MISSING | #MISSING | 233 |
| Total Expenses | 28240 | |||
| Profit | 17378 | 17762 | 17803 | 12/17 |
Based on the calculation order, if you place a member formula on, for example, Margin in the database outline, its result is overwritten by the consolidation on Qtr1.
Cell Calculation Order for Formulas on a Dense Dimension
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.
When placing a formula on a dense dimension member, carefully consider the cell calculation order. As described in the examples above, the dimension calculated last overwrites previous cell calculations for cells with multiple consolidation paths. If required, you can use a calculation script to change the order in which the dimensions are calculated. See Developing Calculation Scripts for Block Storage Databases and Developing Formulas for Block Storage Databases.