|
|
|
|
|
|
This chapter describes the order in which Hyperion Essbase calculates a database. If you use dynamic calculations, see Dynamically Calculating Data Values for information on the calculation order for the dynamically-calculated values.
The following information assumes that you understand the concepts of data blocks and of sparse and dense dimensions. It also assumes that you understand the use of levels and generations. For more information, see Designing Hyperion Essbase Applications.
This chapter includes the following sections:
Data Storage in Data BlocksHyperion Essbase stores data values in data blocks. Hyperion 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 28-1: 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 more information on attributes, see Working with Attributes. |
Hyperion 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 28-2: Product and Market Dimensions from the
Sample Basic Database
In Hyperion 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 100-10->New York.
You can categorize data blocks as follows:
For more information on levels and generations, and how Hyperion Essbase stores data in data blocks, see Introducing the Hyperion Essbase Kernel.
Member Calculation OrderHyperion Essbase calculates a database at the data block level, bringing one or more blocks into memory and calculating the required values within the block. Hyperion Essbase calculates the blocks in order, according to their block numbers. The database outline tells Hyperion Essbase how to order the blocks. Within each block, Hyperion Essbase calculates the values in order according to the hierarchy in the database outline. Therefore, overall, Hyperion Essbase calculates a database based on the database outline.
When you perform a default calculation (CALC ALL) on a database, Hyperion Essbase calculates the dimensions in the following 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, Hyperion Essbase calculates as follows:
Otherwise, Hyperion Essbase calculates as follows:
| Note: | Attribute dimensions, which are not included in the database consolidation, do not affect calculation order. For more information on attribute dimensions, see Working with Attributes. |
In the Sample Basic database, the dimensions are calculated in the following order: Measures, Year, Scenario, Product, and Market.
You can override the default order by using a calc script. For more information on developing calc scripts, see Developing Calc Scripts. For more information on accounts and time dimensions, see Calculating Time Series Data.
Member RelationshipsThe 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 28-3 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 more information on Dynamic Calc members, see Dynamically Calculating Data Values.
Figure 28-3: 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.
Hyperion Essbase calculates Qtr1 by consolidating Jan, Feb, and Mar. In this example, these members are added.
Hyperion Essbase then calculates the Qtr2 through Qtr4 branches in the same way.
Finally, Hyperion Essbase calculates the Year member by consolidating the values of Qtr1 through Qtr4. Again, in this example, these members are added.
Member ConsolidationYou can choose how Hyperion Essbase consolidates members by applying any unary operator (+, -, /, *, %, ~) to the members in the database outline.
If an accounts member has a time balance tag (First, Last, or Average), Hyperion Essbase consolidates it accordingly. For more information on time balance calculations, see Calculating Time Series Data.
If a parent member has a label only tag, Hyperion Essbase does not calculate the parent from its children. If a member has a ~ tag, Hyperion Essbase does not consolidate the member up to its parent.
| Note: | If you use dynamic calculations, Hyperion Essbase may use a different calculation order. For information on the calculation order for dynamically-calculated values, see Dynamically Calculating Data Values. |
Ordering Dimensions in the Database OutlineTo ensure the required calculation results, consider the calculation order of the dimensions in the database outline if you do either of the following:
You do not need to consider calculation order if you use only unary 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, Hyperion Essbase calculates the formula when it calculates the Measures dimension. Hyperion Essbase then overwrites the formula when it aggregates the Year dimension. For detailed information, see Cell Calculation Order.
If you use unary 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 Hyperion Essbase aggregates values on the Year dimension. For detailed information, 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 28-4: Unary 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, Hyperion Essbase starts with Child 1, taking the value of Parent 1 (currently #MISSING) and dividing it by Child 1. The result is #MISSING. Hyperion 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. For more information on #MISSING values, see Optimizing Calculations.
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 unary operators on members as in the above example.
Avoiding Forward Calculation ReferencesTo 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 Hyperion Essbase has not yet calculated. In these cases, Hyperion Essbase may not produce the required calculation results.
For example, consider the following Product dimension:
Figure 28-5: Example Product Dimension
This Product dimension has three forward calculation references. Two shared members and one non-shared member have forward calculation references:
Figure 28-6: Example Product Dimension Showing Forward Calculation References
In the Hyperion Essbase Application Manager Outline Editor, you can select Outline > Verify to identify shared members with forward calculation references. Hyperion Essbase displays these members in the Verify Outline dialog box.
| Note: | Selecting Outline > Verify does not identify non-shared members that have forward calculation references. |
You can save and use an outline containing forward calculation references.
Consider the five members under Diet. The members P100-20, P300-20, and P500-20 have forward calculation references:
Hyperion Essbase calculates the shared member P100-20 before it calculates the real member P100-20. Because the real member P100-20 has children, Hyperion Essbase needs to calculate the real member by adding its children before it can accurately calculate the shared member P100-20.
Hyperion Essbase calculates the shared member P300-20 before it calculates the real member P300-20. Because the real member P300-20 has a formula, Hyperion Essbase needs to calculate the real member before it can accurately calculate the shared member P300-20.
The formula applied to P500-20 references members that Hyperion Essbase has not yet calculated. One referenced member, P300-20, has its own formula, and Hyperion Essbase needs to calculate P300-20 before it can accurately calculate P500-20. The members P200-20 and P400-20 calculate correctly, as they do not have forward calculation references:
P200-20 is not a forward calculation reference, even though Hyperion Essbase calculates the shared member P200-20 before it calculates the real member P200-20. The real member P200-20 has no calculation dependencies (no children and no formula). Therefore Hyperion Essbase does not need to calculate the real member before the shared member. Hyperion Essbase simply takes the value of the real member.
P400-20 is not a forward calculation reference, even though the formula that is applied to P400-20 references a member that Hyperion 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. Hyperion 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, you ensure that Hyperion Essbase calculates the members in the required order.
Figure 28-8: Changed Product Dimension Without Forward Calculation References
Now Hyperion Essbase calculates as follows:
Block Calculation OrderHyperion Essbase calculates blocks in the order in which the blocks are numbered. Hyperion 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.
Figure 28-9: 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 more information on attribute dimensions, see Working with Attributes. |
Product has 19 members (excluding the shared members, for which Hyperion 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.
Figure 28-10: 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.
The following 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. Hyperion Essbase creates the next 19 data blocks for sparse combinations of each Product member and Massachusetts.
The following table shows the sparse member combinations for the block numbers 19 through 23.
Hyperion Essbase continues until blocks have been created for all combinations of sparse dimension members for which at least one data value exists.
Hyperion 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 Hyperion Essbase does not create a data block for 200-10->Massachusetts. However, Hyperion 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 Hyperion Essbase skips the block and moves on to the next block. For more information, see Using Intelligent Calculation to Optimize Calculation.
Hyperion Essbase renumbers the data blocks when you do any of the following:
Cell Calculation OrderEach 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 Hyperion 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.
Cell Calculation Order ExamplesThe following examples describe the cell calculation order for different database configurations.
Consider the simplest case in which both of the following are true:
#MISSING values is turned on. For more information, see Calculating #MISSING Values.
In the following example, 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. Hyperion Essbase 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, Hyperion Essbase 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 the following order:
Qtr1->East has multiple consolidation paths. It can be consolidated on Market or on Year. When consolidated on Market, it is an aggregation of Qtr1->New York and Qtr1->Massachusetts. When consolidated on Year, it is an aggregation of Jan->East, Feb->East, and Mar->East.
Hyperion Essbase 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 the following table. Qtr1->East has been calculated only once by aggregating the values for Qtr1.
| 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, Hyperion Essbase ignores it when calculating Qtr1->East. If you place a member formula on East in the database outline, the formula is calculated when Hyperion Essbase consolidates Qtr1->East on the Market consolidation path. If required, you can use a calc script to calculate the dimensions in the order you choose. For more information, see Developing Calc Scripts.
Consider a second case in which both of the following are true:
#MISSING values is turned off (the default). For more information, see Optimizing Calculations.
Again, in the following example, 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. Hyperion Essbase 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, Hyperion Essbase 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 the following order:
In this case Qtr1->East is calculated on both the Year and Market consolidation paths. First, it is calculated as an aggregation of Qtr1->New York and Qtr1->Massachusetts. Second, it is calculated as an aggregation 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 more information, see 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 Hyperion 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.
Now consider the previous case in which both of the following are true:
#MISSING values is turned off (the default). For more information, see Calculating #MISSING Values.
Again, in the following example, 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, Hyperion Essbase 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 aggregating #MISSING values is turned off, Hyperion Essbase does not aggregate 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 would be consolidated and would overwrite the parent values. For example, if Jan->New York contained 50000.00, this value would overwrite the values that were loaded at parent levels.
Hyperion Essbase first correctly calculates the Qtr1->East cell by aggregating Jan->East, Feb->East, and Mar->East. Second, it calculates on the Market consolidation path. However, it does not aggregate the #MISSING values in Qtr1->New York and Qtr1->Massachusetts and so the value in Qtr1->East is not overwritten.
The following 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 |
Hyperion Essbase needs to calculate the Qtr1->East cell twice in order to ensure that a value is calculated for the cell. If it calculated Qtr1->East according to only the last consolidation path, then the result would be #MISSING for Qtr1->East, which is not the required result.
Now consider a case in which all of the following are true:
#MISSING values is turned off (the default). For more information, see Calculating #MISSING Values.
The following 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 28-11: Measures Dimension in Sample Basic Database
Again, the following table shows a subset of the cells in a data block. Data values have been loaded into the input cells. Hyperion Essbase calculates the shaded cells. The numbers in bold show the calculation order for these cells. Cells with multiple consolidation paths are darkly shaded.
Notice that the Marketing, Payroll, and Misc Expenses values have been loaded at the Qtr1, parent level.
As described in Member Calculation Order, Hyperion Essbase 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 aggregating #MISSING values is turned off, Hyperion Essbase does not aggregate the #MISSING values. Thus, any data that is loaded at parent levels is not overwritten by the #MISSING values and Hyperion Essbase calculates the three cells with multiple consolidation paths twice.
The results are shown in the following table.
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 DimensionThe cell calculation order within a data block is not affected by formulas on members. When Hyperion 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 calc script to change the order in which the dimensions are calculated. For more information, see Developing Calc Scripts.
For more information on developing formulas, see Developing Formulas.
Calculation PassesWhenever possible, Hyperion 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, Hyperion Essbase needs to perform more than one calculation pass through a database. On subsequent calculation passes, Hyperion 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), Hyperion Essbase attempts to calculate the database in one calculation pass. If you have dimensions that are tagged as accounts or time, Hyperion Essbase may have to do more than one calculation pass through the database.
The following table shows the number of calculation passes Hyperion Essbase performs if you have dimensions that are tagged as time or accounts, and you have at least one formula on the accounts dimension.
If you are using formulas that are tagged as Two-Pass, Hyperion Essbase may need to do an extra calculation pass to calculate these formulas. For more information on using Two-Pass calculations, see Optimizing Calculations.
When you use a calc script to calculate a database, the number of calculation passes Hyperion Essbase needs to perform depends upon the calc script. For more information, see Calculation Passes and Using Intelligent Calculation to Optimize Calculation. For more information on grouping formulas and calculations, see Optimizing Calculations.
When you calculate a database, Hyperion Essbase automatically displays the calculation order of the dimensions for each pass through the database and tells you how many times Hyperion Essbase has cycled through the database during the calculation.
Hyperion Essbase displays this information in the ESSCMD window and in the Event Log file. To display the Event Log file, select Application > View Event Log from the Hyperion Essbase Application Manager menu.
For each data block, Hyperion 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.
Hyperion Essbase calculates the blocks in the following way:
The sparse member combination of each upper level block contains at least one parent member. Hyperion Essbase aggregates 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 Hyperion Essbase chooses the Product dimension.
If the sparse member combination for the block has more than one parent member, Hyperion 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, Hyperion 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. For more information, see Member Calculation Order.
Based on the chosen sparse dimension, Hyperion Essbase either aggregates the values or performs a sparse calculation on the data block:
Calculating Shared MembersShared 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 28-12: Calculating Shared Members
The members under the Diet parent are shared members. For more information on shared members, see Creating and Changing Database Outlines.
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, Hyperion Essbase calculates the real member.
|
|
|
|
|
 |
Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.
