Defining Calculation Order Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Defining Calculation Order


This chapter describes the order in which Analytic Services calculates a block storage database. You should understand the concepts of data blocks and of sparse and dense dimensions before using this information. For a review of this information, see Sparse and Dense Dimensions. You should also understand the use of levels and generations. For a review of this information, see Generations and Levels. If you use dynamic calculations, see Understanding How Dynamic Calculation Changes Calculation Order for information on the calculation order for the dynamically calculated values.

Calculation order does not apply to aggregate storage databases.

This chapter includes these sections:

Data Storage in Data Blocks

Analytic Services stores data values in data blocks. Analytic Services 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 178: Dimensions from the Sample Basic Database

Note: Sample Basic also contains five attribute dimensions. These dimensions are sparse, Dynamic Calc, meaning that attribute data is not stored in the database. For a comprehensive discussion of attributes, see Working with Attributes.

Analytic Services 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 179: Product and Market Dimensions from the Sample Basic Database

In Analytic Services, 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 100-10 -> New York.

You can categorize data blocks as follows:

For more information on levels and generations, see Generations and Levels. For information on how Analytic Services stores data in data blocks, see Data Blocks and the Index System.

Member Calculation Order

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

When you perform a default calculation (CALC ALL) on a database, Analytic Services 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, Analytic Services calculates as follows:

  1. The dimension tagged as accounts

  2. The dimension tagged as time

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

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

Otherwise, Analytic Services calculates in this order:

  1. Dense dimensions (in the order they display in the database outline)

  2. Sparse dimensions (in the order they display in the database outline)

Note: Attribute dimensions, which are not included in the database consolidation, do not affect calculation order. For a comprehensive discussion of attribute dimensions, 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. For a comprehensive discussion of how to develop and use calculation scripts, see Developing Calculation Scripts.

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.

Figure 180 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. For a comprehensive discussion of dynamic calculation, see Dynamically Calculating Data Values.

Figure 180: 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.

Analytic Services calculates Qtr1 by consolidating Jan, Feb, and Mar. In this example, these members are added.

Analytic Services then calculates the Qtr2 through Qtr4 branches in the same way.

Finally, Analytic Services calculates the Year member by consolidating the values of Qtr1 through Qtr4. Again, in this example, these members are added.

Determining Member Consolidation

You can choose how Analytic Services 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), Analytic Services consolidates it accordingly. For information on time balance calculations, see Calculating First, Last, and Average Values.

If a parent member has a label only tag, Analytic Services does not calculate the parent from its children. If a member has a ~ tag, Analytic Services does not consolidate the member up to its parent.

Note: If you use dynamic calculations, Analytic Services may use a different calculation order. For information on the calculation order for dynamically calculated values, 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:

You do not need to 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.

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, Analytic Services calculates the formula when it calculates the Measures dimension. Analytic Services then overwrites the formula when it consolidates the Year dimension. For detailed examples of cell calculation order, 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 Analytic Services consolidates values on the Year dimension. For detailed examples of cell calculation order, 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 181: Calculation Operators in the Database Outline

In the above example, 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, Analytic Services starts with Child 1, taking the value of Parent 1 (currently #MISSING) and dividing it by Child 1. The result is #MISSING. Analytic Services 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. For more information on #MISSING values, see Consolidating #MISSING Values.

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 in the above example.

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 Analytic Services has not yet calculated. In these cases, Analytic Services may not produce the required calculation results.

For example, consider this Product dimension:

Figure 182: Example Product Dimension

This Product dimension has three forward calculation references. Two shared members and one non-shared member have forward calculation references:

Figure 183: Example Product Dimension Showing Forward Calculation References

In Outline Editor, when you verify the outline, Analytic Services identifies shared members with forward calculation references. Verifying the outline does not identify non-shared 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 Essbase Administration Services Online Help.

Consider the five members under Diet. The members P100-20, P300-20, and P500-20 have forward calculation references:

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, you ensure that Analytic Services calculates the members in the required order.

Figure 184: Changed Product Dimension Without Forward Calculation References

Now Analytic Services calculates as follows:

Block Calculation Order

Analytic Services calculates blocks in the order in which the blocks are numbered. Analytic Services 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.

Figure 185: Dimensions in the Sample Basic Database

Note: 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. For a comprehensive discussion of attribute dimensions, see Working with Attributes.

Product has 19 members (excluding the shared members, for which Analytic Services 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.

Figure 186: Product Dimension from the Sample Basic Database

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.

This table shows the sparse member combinations for the first 5 of these 19 data blocks.


Block #
Product Member
Market Member

0

Cola (100-10)

New York

1

Diet Cola (100-20)

New York

2

Caffeine Free Cola (100-30)

New York

3

Colas (100)

New York

4

Old Fashioned (200-10)

New York



The next member in the Market dimension is Massachusetts. Analytic Services creates the next 19 data blocks for sparse combinations of each Product member and Massachusetts.

This table shows the sparse member combinations for the block numbers 19 through 23.


Block #
Product Member
Market Member

19

Cola (100-10)

Massachusetts

20

Diet Cola (100-20)

Massachusetts

21

Caffeine Free Cola (100-30)

Massachusetts

22

Colas (100)

Massachusetts

23

Old Fashioned (200-10)

Massachusetts



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

Analytic Services 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 Analytic Services does not create a data block for 200-10 -> Massachusetts. However, Analytic Services 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 Analytic Services skips the block and moves on to the next block. For a comprehensive discussion of how intelligent calculation is used to optimize performance, see Optimizing with Intelligent Calculation.

Data Block Renumbering

Analytic Services renumbers the data blocks when you make any of these changes:

Cell Calculation Order

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 Analytic Services 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.

Use these sections to understand cell calculation order in more detail:

Cell Calculation Order: Example 1

Consider the simplest case in which both of these conditions are true:

Market and Year are both dense dimensions. The table shows a subset of the cells in a data block. Data values have been loaded into the input cells. Analytic Services calculates the shaded cells. The numbers in bold show the calculation order for these cells. The cell with multiple consolidation paths is darkly shaded.


Year -> Market
New York
Massachusetts
East

Jan

112345.00

68754.00

3

Feb

135788.00

75643.00

4

Mar

112234.00

93456.00

5

   Qtr1

1

2

6



As described in Member Calculation Order, Analytic Services calculates dense dimensions in the order that they display in the database outline. Assuming that the Year dimension is displayed before the Market dimension in the database outline, the Year dimension is calculated before the Market dimension.

The cells are calculated in this 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 consolidation:missing values:effects on calculation order;consolidated on Market or on Year. When consolidated on Market, it is an consolidation of Qtr1 -> New York and Qtr1 -> Massachusetts. When consolidated on Year, it is an consolidation of Jan -> East, Feb -> East, and Mar -> East.

Analytic Services knows that Qtr1 -> East has multiple consolidation paths. Therefore, it calculates Qtr1 -> East only once and uses the consolidation path of the dimension calculated last. In the above example, this dimension is Market.

The results are shown in this table:


Year/Market
New York
Massachusetts
East

Jan

112345.00

68754.00

181099.00

Feb

135788.00

75643.00

211431.00

Mar

112234.00

93456.00

205690.00

   Qtr1

360367.00

237853.00

598220.00



Note: Qtr1 -> East has been calculated only once by consolidating the values for Qtr1.

From the calculation order, you can see that if you place a member formula on Qtr1 in the database outline, Analytic Services ignores it when calculating Qtr1 -> East. If you place a member formula on East in the database outline, the formula is calculated when Analytic Services 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. For a comprehensive discussion of how to develop and use calculation scripts, see Developing Calculation Scripts.

Cell Calculation Order: Example 2

Consider a second case in which both of these conditions are true:

Market and Year are both dense dimensions. The table shows a subset of the cells in a data block. Data values have been loaded into the input cells. Analytic Services calculates the shaded cells. The numbers in bold show the calculation order for these cells. The cell with multiple consolidation paths is darkly shaded.


Year -> Market
New York
Massachusetts
East

Jan

112345.00

68754.00

4

Feb

135788.00

75643.00

5

Mar

112234.00

93456.00

6

   Qtr1

1

2

3/7



As described in Member Calculation Order, Analytic Services calculates dense dimensions in the order they are defined in the database outline. Assuming the Year dimension is positioned before the Market dimension in the database outline, the Year dimension is calculated before the Market dimension.

The cells are calculated in this order:

  1. Qtr1 -> New York

  2. Qtr1 -> Massachusetts

  3. Qtr1 -> East

  4. Jan -> East

  5. Feb -> East

  6. Mar -> East

  7. Qtr1 -> East

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

The results are identical to the previous case. However, Qtr1 -> East has been calculated twice. This fact is significant when you need to load data at parent levels. For an example in which data is loaded at the parent level, see Cell Calculation Order: Example 3.


Year/Market
New York
Massachusetts
East

Jan

112345.00

68754.00

181099.00

Feb

135788.00

75643.00

211431.00

Mar

112234.00

93456.00

205690.00

   Qtr1

360367.00

237853.00

598220.00



From the calculation order, you can see that if you place a member formula on Qtr1 in the database outline, its result is overwritten when Analytic Services 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

Consider the previous case in which both of these conditions are true:

Market and Year are both dense dimensions. The table shows a subset of the cells in a data block. Data values have been loaded into cells at the parent level.


Year -> Market
New York
Massachusetts
East

Jan

#MISSING

#MISSING

181099.00

Feb

#MISSING

#MISSING

211431.00

Mar

#MISSING

#MISSING

205690.00

   Qtr1

#MISSING

#MISSING

 



As described in Member Calculation Order, Analytic Services calculates dense dimensions in the order that they are defined in the database outline. Assuming the Year dimension is positioned before the Market dimension in the database outline, the Year dimension is calculated before the Market dimension.

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

Because the setting for consolidating #MISSING values is turned off, Analytic Services 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 were 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.

Analytic Services first correctly calculates the Qtr1 -> East cell by consolidating Jan -> East, Feb -> East, and Mar -> East. Second, it calculates on the Market consolidation path. However, it does not consolidate the #MISSING values in Qtr1 -> New York and Qtr1 -> Massachusetts and so the value in Qtr1 -> East is not overwritten.

This table shows the results:


Year/Market
New York
Massachusetts
East

Jan

#MISSING

#MISSING

181099.00

Feb

#MISSING

#MISSING

211431.00

Mar

#MISSING

#MISSING

205690.00

   Qtr1

#MISSING

#MISSING

598220.00



Analytic Services needs to calculate the Qtr1 -> East cell twice in order 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

Consider a case in which all of these conditions are true:

Figure 187 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. For more information on Dynamic Calc members, see Dynamically Calculating Data Values.

Figure 187: Profit Branch of the Measures Dimension in the Sample Basic Database

This table shows a subset of the cells in a data block. Data values have been loaded into the input cells. Analytic Services calculates the shaded cells. The numbers in bold show the calculation order for these cells. Cells with multiple consolidation paths are darkly shaded.

The Marketing, Payroll, and Misc Expenses values have been loaded at the Qtr1, parent level.


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



As described in Member Calculation Order, Analytic Services calculates a dimension tagged as accounts first, followed by a dimension tagged as time. Therefore, in the above example, Measures is calculated before Year.

Three cells have multiple consolidation paths:

Because the setting for consolidating #MISSING values is turned off, Analytic Services does not consolidate the #MISSING values. Thus, any data that is loaded at parent levels is not overwritten by the #MISSING values and Analytic Services calculates the three cells with multiple consolidation paths twice.

The results are shown in this table.


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

52943



From the calculation order, you can see that 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 Analytic Services 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. For a comprehensive discussion of how to develop and use calculation scripts, see Developing Calculation Scripts.

For a comprehensive discussion of how to develop and use formulas, see Developing Formulas.

Calculation Passes

Whenever possible, Analytic Services 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, Analytic Services needs to perform more than one calculation pass through a database. On subsequent calculation passes, Analytic Services 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), Analytic Services attempts to calculate the database in one calculation pass. If you have dimensions that are tagged as accounts or time, Analytic Services may have to do more than one calculation pass through the database.

This table shows the number of calculation passes Analytic Services performs if you have dimensions that are tagged as time or accounts, and you have at least one formula on the accounts dimension.


Dimension Tagged As:
Calculation Passes
During each calculation pass, Analytic Services calculates based on:
Accounts
Time

Dense or Sparse

None

1

All dimensions

Dense

Dense

1

All dimensions

Dense

Sparse

2

Pass 1: Accounts and time dimensions

Pass 2: Other dimensions

Sparse

Sparse

2

Pass 1: Accounts and time dimensions

Pass 2: Other dimensions

Sparse

Dense

2

Pass 1: Accounts dimension

Pass 2: Other dimensions



If you are using formulas that are tagged as Two-Pass, Analytic Services may need to do an extra calculation pass to calculate these formulas. For a comprehensive discussion of two-pass calculations, see Using Two-Pass Calculation.

When you use a calculation script to calculate a database, the number of calculation passes Analytic Services needs to perform depends upon the calculation script. For a discussion of single pass and multiple pass calculations, see Calculation Passes and Understanding Multiple-Pass Calculations. For information on grouping formulas and calculations, see Grouping Formulas and Calculations.

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

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

For each data block, Analytic Services 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.

Analytic Services calculates the blocks using this procedure:

Calculation of Shared Members

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 different parents. They are consolidated under Diet. They are also consolidated under their individual product types-Colas, Root Beer, and Cream Soda.

Figure 188: Calculating Shared Members

The members under the Diet parent are shared members. For a comprehensive discussion of shared members, see Understanding Shared Members.

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



Hyperion Solutions Corporation link