In This Section:

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

Essbase stores data values in data blocks. Essbase creates a data block for each unique combination of sparse dimension members (providing that at least one data value exists for the combination).

Each data block contains all the dense dimension member values for its unique combination of sparse dimension members.

In the Sample.Basic database, the Year, Measures, and Scenario dimensions are dense; the Product and Market dimensions are sparse. Figure 116, Dimensions from the Sample.Basic Database shows an outline of the dimensions in the Sample.Basic database:

Sample.Basic also contains five attribute dimensions. These dimensions are sparse, Dynamic Calc, meaning that attribute data is not stored in the database. See Working with Attributes. |

Essbase creates a data block for each unique combination of members in the Product and Market dimensions (providing that at least one data value exists for the combination). For example, it creates one data block for the combination of 100-10, New York. This data block contains all the Year, Measures, and Scenario values for 100-10, New York. Figure 117, Product and Market Dimensions from the Sample.Basic Database shows an outline of the Product and Market dimensions in the Sample.Basic database:

In Essbase, member combinations are denoted by the cross-dimensional operator. The symbol for the cross-dimensional operator is -> (a hyphen followed by a greater-than symbol). So 100-10, New York is written as 100-10 -> New York.

You can categorize data blocks in the following ways:

These blocks are created by loading data to cells in a block. Input blocks can be created for (1) sparse, level 0 member combinations or (2) sparse, upper-level member combinations, when at least one of the sparse members is a parent-level member. Input blocks can be level 0 or upper-level blocks.

These blocks are created through calculations. For example, in Sample.Basic, the East -> Cola block is created during a sparse calculation process (that is, the block did not exist before calculation).

These blocks are created for sparse member combinations when all of the sparse members are level 0 members. For example, in Sample.Basic, New York -> Cola is a level 0 block because New York and Cola are level 0 members of their respective sparse dimensions. Level 0 blocks can be input or noninput blocks; for example, a level 0 noninput block is created during an allocation process, where data is loaded at a parent level and then allocated down to level 0.

These blocks are created for sparse member combinations when at least one of the sparse members is a parent-level member. Upper-level blocks can be input or noninput blocks.

See Generations and Levels and Data Blocks and the Index System.

Essbase calculates a database at the data block level, bringing one or more blocks into memory and calculating the required values within the block. Essbase calculates the blocks in order, according to their block numbers. The database outline tells Essbase how to order the blocks. Within each block, Essbase calculates the values in order according to the hierarchy in the database outline. Therefore, overall, Essbase calculates a database based on the database outline.

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:

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 database outline)

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.

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.

Figure 118, Year Dimension from the Sample.Basic Database 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. See Dynamically Calculating Data Values.

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.

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.

If you use dynamic calculations, Essbase may use a different calculation order. See Calculation Order for Dynamic Calculation. |

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

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.

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.

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 119, Calculation Operators in the Database Outline 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.

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 119, Calculation Operators in the Database Outline.

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 in Figure 120, Product Dimension with Forward Calculation References, which has three forward calculation references: two shared members (P100–20 and P300–20) and one nonshared member (P500–20):

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.

To verify the outline, see “Verifying Outlines” in the Oracle Essbase Administration Services Online Help.

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 actual member P100-20. Because the actual member P100-20 has children, Essbase must calculate the actual 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 actual member P300-20. Because the actual member P300-20 has a formula, Essbase must calculate the actual member before it can accurately calculate the shared member P300-20.

P500-20 (+) (“P200-20” + “P300-20”): The formula applied to P500-20 references members that Essbase has not yet calculated. One referenced 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 actual member P200-20. The actual member P200-20 has no calculation dependencies (no children and no formula). Therefore, Essbase does not need to calculate the actual member before the shared member. Essbase simply takes the value of the actual 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 referenced 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 in Figure 121, Changed Product Dimension Without Forward Calculation References, you ensure that Essbase calculates the members in the required order.

The actual member P100-20 before it calculates the shared member P100-20. So, P100-20 no longer has a forward calculation reference.

The actual member P300-20 before the shared member P300-20. So, P300-20 no longer has a forward calculation reference.

The referenced member with a formula, P300-20, before the member P500-20. So, P500-20 no longer has a forward calculation reference.

Essbase calculates blocks in the order in which the blocks are numbered. Essbase takes the first sparse dimension in a database outline as a starting point. It defines the sparse member combinations from this first dimension.

In the Sample.Basic database, Product is the first sparse dimension in the database outline.

The attribute dimensions in the Sample.Basic outline (not shown in the figure above), are not included in the database consolidation and do not affect block calculation order. See Working with Attributes.. |

As shown in Figure 123, Product Dimension from the Sample.Basic Database, Product has 19 members (excluding the shared members, for which Essbase does not create data blocks). Therefore, the first 19 data blocks in the database are numbered according to the calculation order of members in the Product dimension.

The other sparse dimension is Market. The first 19 data blocks contain the first member to be calculated in the Market dimension, which is New York. Table 64 shows the sparse member combinations of each Product member and New York, for the first five of these 19 data blocks:

The next member in the Market dimension is Massachusetts. Essbase creates the next 19 data blocks for sparse combinations of each Product member and Massachusetts. Table 65 shows the sparse member combinations for the block numbers 19 through 23:

Essbase continues until blocks have been created for all combinations of sparse dimension members for which at least one data value exists.

Essbase creates a data block only if at least one value exists for the block. For example, if no data values exist for Old Fashioned Root Beer (200-10) in Massachusetts, then Essbase does not create a data block for 200-10 -> Massachusetts. However, Essbase does reserve the appropriate block number for 200-10 -> Massachusetts in case data is loaded for that member combination in the future.

When you run a default calculation (CALC ALL) on a database, each block is processed in order, according to its block number. If you have Intelligent Calculation turned on, and if the block does not need to be calculated, then Essbase skips the block and moves to the next block. For information about how intelligent calculation is used to optimize performance, see Understanding Intelligent Calculation.

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.

The order in which Essbase calculates the cells within each block depends on how you have configured the database. How you have configured the database defines the member calculation order of dense dimension members within each block. It also defines the calculation order of blocks that represent sparse dimension members.

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

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 66 shows a subset of the cells in a data block:

**Table 66. 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 Table 66, the calculation order for these cells is represented by the numbers 1 through 6 that appear in the cells:

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 in Table 67.

**Table 67. 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. See Developing Calculation Scripts for Block Storage Databases.

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 68 shows a subset of the cells in a data block:

**Table 68. 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 Table 68, the calculation order for these cells is represented by the numbers 1 through 7 that appear in the cells:

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, as shown in Table 69, are identical to the results for example 1 (see Table 67, Calculation Order Example 1: Results). However, Qtr1 -> East has been calculated twice. This fact is significant when you need to load data at parent levels (see Cell Calculation Order: Example 3).

**Table 69. 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.

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.

In this example, these conditions are true:

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.

Figure 124, Profit Branch of the Measures Dimension 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.

Table 72 shows a subset of the cells in a data block:

**Table 72. 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:

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 in Table 73:

**Table 73. 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.

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.

Whenever possible, Essbase calculates a database in one calculation pass through the database. Thus, it reads each of the required data blocks into memory only once, performing all relevant calculations on the data block and saving it. However, in some situations, Essbase must perform multiple calculation passes through a database. On subsequent calculation passes, Essbase brings data blocks back into memory, performs further calculations on them, and saves them again.

When you perform a default, full calculation of a database (CALC ALL), Essbase attempts to calculate the database in one calculation pass. If you have dimensions that are tagged as accounts or time, Essbase may have to do multiple calculation passes through the database.

Table 74 shows the number of calculation passes Essbase performs if you have dimensions that are tagged as time or accounts, and you have at least one formula on the accounts dimension:

**Table 74. Calculation Passes For Accounts and Time Dimension**

If you are using formulas that are tagged as Two-Pass, Essbase may need to do an extra calculation pass to calculate these formulas. See Using Two-Pass Calculation.

When you use a calculation script to calculate a database, the number of calculation passes Essbase needs to perform depends upon the calculation script. See Calculation Passes and Understanding Multiple-Pass Calculations. Also see Grouping Formulas and Calculations.

If the isolation level is set for committed access, and multiple passes are required, Essbase writes data values at the end of each pass. Data retrievals that occur between passes can pick up intermediate values.

When you calculate a database, Essbase automatically displays the calculation order of the dimensions for each pass through the database and tells you how many times Essbase has cycled through the database during the calculation. Essbase displays this information in the ESSCMD window and in the application log.

To display the application log, see Viewing the Essbase Server and Application Logs.

For each data block, Essbase decides whether to do a dense or a sparse calculation. The type of calculation it chooses depends on the type of values within the data block. When you run a default calculation (CALC ALL) on a database, each block is processed in order, according to its block number.

Essbase calculates the blocks using this procedure:

If you have Intelligent Calculation turned on, and if the block does not need to be calculated (if it is marked as clean), Essbase skips the block and moves to the next block. See Understanding Intelligent Calculation.

If the block needs recalculating, Essbase checks to see if the block is a level 0, an input, or an upper-level block. See Data Storage in Data Blocks.

If the block is a level 0 block or an input block, Essbase performs a dense calculation on the block. Each cell in the block is calculated. See Cell Calculation Order.

If the block is an upper-level block, Essbase either consolidates the values or performs a sparse calculation on the data block.

The sparse member combination of each upper-level block contains at least one parent member. Essbase consolidates or calculates the block based on the parent member’s dimension. For example, if the upper-level block is for Product -> Florida from the Sample.Basic database, then Essbase chooses the Product dimension.

If the sparse member combination for the block has multiple parent members, Essbase chooses the last dimension in the calculation order that includes a parent member. For example, if the block is for Product -> East, and you perform a default calculation on the Sample.Basic database, Essbase chooses the Market dimension, which contains East. The Market dimension is last in the default calculation order because it is placed after the Product dimension in the database outline. See Member Calculation Order.

Based on the chosen sparse dimension, Essbase either consolidates the values or performs a sparse calculation on the data block:

If a formula is applied to the data block member on the chosen sparse dimension, Essbase performs a formula calculation on the sparse dimension. Essbase evaluates each cell in the data block. The formula affects only the member on the sparse dimension, so overall calculation performance is not significantly affected.

If the chosen sparse dimension is a default consolidation, Essbase consolidates the values, taking the values of the previously calculated child data blocks.

Shared members are those that share data values with other members. For example, in the Sample.Basic database, Diet Cola, Diet Root Beer, and Diet Cream are consolidated under two parents: under Diet and under their product types—Colas, Root Beer, and Cream Soda. The members under the Diet parent are shared members, as shown in Figure 125, Calculating Shared Members. See Understanding Shared Members.

A calculation on a shared member is a calculation on the actual member. If you use the FIX command to calculate a subset of a database and the subset includes a shared member, Essbase calculates the actual member.