In this example, these conditions are true:
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.
Table 70 shows a subset of the cells in a data block:
Table 70. 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, as shown in Table 71, 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 71. 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.