Optimizing Calculations

This chapter provides information on how to optimize the performance of Hyperion Essbase calculations. The following three features are not discussed in this chapter. However, you can use any or all of these features to optimize overall database calculations:

Intelligent Calculation, as discussed in Using Intelligent Calculation to Optimize Calculation
Dynamic calculations, as discussed in Dynamically Calculating Data Values
Partitioning, as discussed in Designing Partitioned Applications and Developing Calc Scripts

This chapter includes the following sections:

Designing for Calculation Performance

You can configure a database to optimize calculation performance. The optimal configuration is highly dependent on the nature and size of the database. The following sections provide guidelines only.

Block Size and Block Density

Generally speaking, you do not want data block size to be too small. A data block size of 8K to 64K provides optimal performance in most cases.

If data blocks are very small, the index is likely to be very large. This configuration causes a performance overhead as Hyperion Essbase writes and retrieves the index from disk. However, if data blocks are too large, Intelligent Calculation does not work effectively. For more information on Intelligent Calculation, see Using Intelligent Calculation to Optimize Calculation.

To optimize calculation performance and data storage, you need to balance data block density and data block size. You can create balance by rearranging the dense and sparse dimension configuration of the database. Therefore, keep these suggestions in mind:

Aim to achieve a data block size of between 8K and 64K with as high a block density as possible.
Run test calculations on the most promising configurations of a database that contains representative data. Check the results to determine the optimal configuration for calculation performance.

You can display information on a database, including the potential and actual number of data blocks and the data block size. In Hyperion Essbase Application Manager, select Database > Information to display the Database Information dialog box. Select the Statistics tab to display data block statistics.

You can also use display database in MaxL or the GETDBINFO command in ESSCMD to view database information. For more information, see the online Technical Reference in the DOCS directory.

Order of Sparse Dimensions

You may achieve improvement in calculation performance by changing the order of the sparse dimensions in the database outline. Order the sparse dimensions by their number of members, starting with the dimension that contains the fewest members.

To achieve the maximum performance benefit from the calculator cache, make the largest sparse dimension the last dimension in the database outline. This arrangement provides approximately a 10% performance improvement if you have a database outline with a very large dimension (for example, containing more than 1000 members).

For more information on the calculator cache, see Setting Memory Cache Sizes.

Incremental Data Loading Considerations

Many people load data incrementally. For example, they load data on a month-by-month basis. Each month they load data for that month.

To optimize calculation performance when you load data incrementally, make the dimension tagged as time a sparse dimension. If the time dimension is sparse, the data for each time period is contained in a separate data block. When you load data, you are loading it into only the required data blocks. Thus, if you have Intelligent Calculation enabled, only the data blocks marked as dirty are recalculated.

For example, if you load data for March, only the data blocks for March are updated. The data blocks for January and February do not change. With Intelligent Calculation enabled, Hyperion Essbase recalculates only the data blocks for March and its dependent parents.

Note:

Making the time dimension sparse when it is naturally dense may significantly increase the size of the index.

If the dimension tagged as time is dense, you still receive some benefit from Intelligent Calculation when you do a partial data load for a sparse dimension. For example, if Product is sparse and you load data for one product, Hyperion Essbase recalculates only the blocks affected by the partial load, even though time is dense and Intelligent Calculation is enabled.

For more information on Intelligent Calculation, see Using Intelligent Calculation to Optimize Calculation.

Performance for Database Outlines with Two or More Flat Dimensions

If a database outline has two or more flat dimensions, calculation performance may be affected. A flat dimension is a dimension in which there are very few parents, and in which each parent has many (thousands) children.

If a database has two or more flat dimensions, you can achieve a performance improvement by doing one of the following:

Adding intermediate levels to the database outline.
Using the SET CALCHASHTBL command in a calc script to optimize calculation of the outline.
You can specify the default setting for optimizing outline calculation in the configuration (ESSBASE.CFG) file by using the CALCOPTCALCHASHTBL setting. You can set the maximum amount of memory that you want the calculator hash table to use by using the CALCHASHTBLMEMORY setting in the configuration file. For more information, see the online Technical Reference in the DOCS directory.

Monitoring and Tracing Calculations

You can display information about how Hyperion Essbase is calculating the database by using the following commands and settings in a calc script:

SET MSG SUMMARY
SET MSG DETAIL
SET NOTICE

SET MSG SUMMARY and SET MSG DETAIL

You can use the SET MSG SUMMARY and SET MSG DETAIL calculation commands in a calc script to do the following:

Display calculation settings (for example, whether completion notice messages are enabled)
Provide statistics on the number of data blocks created, read, and written
Provide statistics on the number of data cells calculated

In addition, the SET MSG DETAIL command provides a detailed information message every time Hyperion Essbase calculates a data block. SET MSG DETAIL is useful for seeing the calculation order of data blocks and for testing intelligent recalculations. Keep in mind that the SET MSG DETAIL command causes a high processing overhead.

SET MSG SUMMARY causes a processing overhead of approximately 1% to 5%, depending on database size.

For more information on SET MSG SUMMARY and SET MSG DETAIL, see the online Technical Reference in the DOCS directory.

SET NOTICE

You can use the SET NOTICE calculation command in a calc script to display calculation completion notices that tell you what percentage of the database has been calculated. You can use the SET MSG SUMMARY command with the SET NOTICE command to show calculation progress between completion notices. Completion notices do not significantly reduce calculation performance, except when used with a very small database.

For more information on this calculation command, see the online Technical Reference in the DOCS directory.

Using Formulas

You may achieve significant improvements in calculation performance by careful use of formulas in the database outline. For example, you may achieve improved calculation performance by placing formulas on members in the database outline instead of placing the formulas in a calc script. For more information, see Developing Formulas.

Consider the questions posed in the following sections to ensure that you use formulas in a way that optimizes calculation performance.

Can you use a consolidation on the database outline?

Using the database outline to roll up values is always more efficient than using a formula to calculate values. For example, consider the following consolidation on the Sample Basic database outline:

Figure 35-1: Consolidation on Sample Basic Outline

Using outline consolidation is more efficient than applying the following formula to the Colas member:

100-10 + 100-20 + 100-30

Are you using a simple formula?

A simple formula is, for example, a ratio or a percentage. A simple formula does not do any of the following:

Reference values from a different dimension (sparse or dense); for example, Product->Jan.
Use range functions, for example, @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE.
Use relationship or financial functions; for example, @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM, or @GROWTH. For a complete list of relationship and financial functions, see the online Technical Reference in the DOCS directory.

If you use a simple formula, you can place it on either a sparse or a dense dimension without significantly affecting calculation performance. However, consider that the bigger the block size, the more impact simple formulas have on calculation performance. For more information, see Block Size and Block Density. For information on how formulas affect calculation performance, see Understanding Bottom-Up Versus Top-Down Calculation.

Are you using a complex formula?

A complex formula does any of the following:

References a member or members in a different dimension (sparse or dense); for example, Product->Jan.
Uses one or more range functions, for example, @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE.
Uses relationship or financial functions; for example, @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM, or @GROWTH. For a complete list of relationship and financial functions, see the online Technical Reference in the DOCS directory.

If you apply a complex formula to a member in a sparse dimension, Hyperion Essbase checks each possible sparse member combination (possible data block) for that member to see if the block exists. This evaluation causes a significant calculation overhead. If the complex formula uses relationship or financial functions, Hyperion Essbase evaluates each possible sparse member combination, which causes an even greater calculation overhead. For more information about how complex formulas affect calculation performance, see Understanding Bottom-Up Versus Top-Down Calculation.

The lower the ratio of existing data blocks to possible data blocks, the higher the calculation performance overhead.

If you use a complex formula, consider the following:

If possible, apply the formula to a member in a dense dimension.
Use the FIX command in a calc script to calculate only the minimum, required data blocks. See Developing Calc Scripts.
Increase the density of the database (ratio of existing data blocks to possible data blocks). See Block Size and Block Density.

Optimizing Formulas on Sparse Dimensions in Large Database Outlines

You can use the SET FRMLBOTTOMUP calculation command to optimize the calculation of formulas in sparse dimensions in large database outlines. With this command, you can force a bottom-up calculation on sparse member formulas that would otherwise be calculated top-down. For more information, see Understanding Bottom-Up Versus Top-Down Calculation.

Forcing a bottom-up calculation on a top-down formula enables efficient use of the CALC ALL and CALC DIM commands to calculate the database. For more information, see the SET FRMLBOTTOMUP calculation command and the CALCOPTFRMLBOTTOMUP configuration setting in the online Technical Reference in the DOCS directory.

Assigning Constants to Members in a Sparse Dimension

When you assign a constant to a member in a sparse dimension, Hyperion Essbase automatically creates a data block for every combination of sparse dimension members that contains the member.

For example, assume that a member or a calc script formula contains the following expression:

California = 120;

In this formula, California is a member in a sparse dimension and 120 is a constant value. Hyperion Essbase automatically creates all combinations of data blocks for California and assigns the value 120 to all data cells. Many thousands of data blocks may be created.

When assigning constants to sparse dimension members, use the FIX command to ensure that Hyperion Essbase creates only the required data blocks. Consider the following example:

FIX(Colas,Misc,Actual)
California = 120;
ENDFIX

The example script assigns the value 120 to California (in the Market dimension), Actual (in the Scenario dimension), Misc (miscellaneous expenses in the Measures dimension), Colas (in the Product dimension), and all members in the Year dimension (since a specific member of Year is not specified in the script).

In the Sample Basic database, Colas is a member of the sparse Product dimension, Actual is a member of the dense Scenario dimension, and Misc is a member of the dense Measures dimension. Hyperion Essbase creates new data blocks for all combinations of the sparse members, California and Colas. It leaves other Measures and Scenario values set to #MISSING within the new blocks.

For more information on the FIX command, see the online Technical Reference in the DOCS directory.

When you assign a constant to a member in a sparse dimension, you do not need to enable Create Blocks on Equations. However, if you assign anything other than a constant to a member in a sparse dimension, and a data block does not already exist for that member, you still need to enable Create Blocks on Equations in Hyperion Essbase Application Manager.

To enable Create Blocks on Equations:

In Hyperion Essbase Application Manager, select Database > Settings.
Hyperion Essbase displays the Database Settings dialog box.
Check Create Blocks on Equations.
Click OK.

For example, you need to enable Create Blocks on Equations for the following formula:

West = California + 120;

You also can use alter database in MaxL or the SETDBSTATE command in ESSCMD to enable block creation on equations. For information, see the online Technical Reference in the DOCS directory.

Using a Cross-Dimensional Operator (->)

Use caution when using a cross-dimensional operator (->) in the following situations:

On the left side of an equation
In equations in a dense dimension
When doing a two-pass formula calculation on a dimension tagged as accounts. For more information, see Using Two-Pass Calculation.

On the Left Side of an Equation

In a member formula in the database outline, you can use a cross-dimensional operator on the left side of the equation. In a calc script, you can use a cross-dimensional operator on the left side of an equation if you associate the formula with a member. However, it is considerably more efficient to use the FIX command in a calc script.

Consider an example in which you want to increase the Jan->Sales values by 5% in the Sample Basic database. You place the following formula on the Sales member in the database outline:

Sales(Sales->Jan = Sales->Jan * .05;)

As Hyperion Essbase cycles through the database, it calculates the formula for every combination of members. Thus, Hyperion Essbase recalculates the formula for every member in the dimension tagged as time, causing redundant calculations.

You can use the FIX command in a calc script to achieve the same result more efficiently:

FIX(Jan)
       Sales = Sales * .05;
ENDFIX

When you use the FIX command, Hyperion Essbase calculates the formula only for member combinations for Jan.

For more information on calc scripts and the FIX command, see Developing Calc Scripts and the online Technical Reference in the DOCS directory.

In Equations in a Dense Dimension

When you use a cross-dimensional operator in an equation in a dense dimension, Hyperion Essbase does not automatically create the required blocks if the resultant values are from a dense dimension and the operand or operands are from a sparse dimension.

Consider the following equation, in which result is from a dense dimension and operand is from a sparse dimension:

result = member->operand

Now consider a specific example from Sample Basic, in which you want to create budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension. Budget and Actual are members in the sparse Scenario dimension.

FIX(Budget)
      (Sales = Sales->Actual * 1.1;
       Expenses = Expenses->Actual * .95;)
ENDFIX

The calc script above does not create the required data blocks. Budget data values are not calculated for blocks that do not already exist. The resultant values are dense dimension members (Sales and Expenses). The operand is a sparse dimension member (Actual).

You can solve the problem by preceding the above formulas with a DATACOPY command:

DATACOPY Sales->Actual TO Sales->Budget;
DATACOPY Expenses->Actual TO Expenses->Budget;
FIX(Budget)
      (Sales = Sales->Actual * 1.1;
       Expenses = Expenses->Actual * .95;)
ENDFIX

Hyperion Essbase then copies the data and creates the required blocks. Hyperion Essbase creates blocks that contain the Budget values for each corresponding Actual block that already exists.

Alternatively, you can avoid copying the data by ensuring that the resultant members are not from a dense dimension:

FIX(Sales)
      Budget = Actual * 1.1;
ENDFIX
FIX(Expenses)
      Budget = Actual * .95;
ENDFIX

Or, you can use a member formula that contains the dense member equations:

FIX(Sales, Expenses)
Budget (Sales = Sales->Actual * 1.1;
      Expenses = Expenses->Actual * .95;)
ENDFIX

Understanding Bottom-Up Versus Top-Down Calculation

Hyperion Essbase uses one of two calculation methods to do a full calculation of a database outline: bottom-up calculation or top-down calculation. By default, Hyperion Essbase does a bottom-up calculation of a database. However, if the database outline contains a complex member formula, Hyperion Essbase performs a top-down calculation for that member. When a formula is compiled, if the formula is to be calculated top-down, Hyperion Essbase logs a message in the application log file.

For a bottom-up calculation, Hyperion Essbase determines which data blocks need to be calculated before it calculates the database. Hyperion Essbase then calculates only the blocks that need to be calculated during the full database calculation. The calculation begins with the lowest existing block number and works up through each subsequent block until the last existing block is reached. For more information on block calculation order, see Defining the Calculation Order.

Before starting a calculation, Hyperion Essbase searches the database outline and marks complex formulas that require a top-down calculation, for example, a member formula that contains a cross-dimensional reference. When Hyperion Essbase reaches a member with a top-down formula, it does a top-down calculation only for that member.

When a formula on a member is complex, all possible blocks for that member must be examined to see if an existing block needs to be changed or a new block needs to be created; it is difficult to determine the dependency on other blocks prior to the start of the calculation. The top-down method slows down calculation performance because Hyperion Essbase searches for the appropriate blocks to calculate in order to execute the formula. For more information on complex formulas, see Are you using a complex formula?.

For simple formulas, Hyperion Essbase does a bottom-up calculation to determine which blocks need to be calculated prior to running the calculation. For example, for a simple formula on a member (such as A = B + C), A is calculated only if B or C exists in the database. That is, the dependency of the formula on B and C is known before the calculation is started.

For a complex formula (such as A = B->D + C->D), Hyperion Essbase must examine every possible combination of A to see whether B->D or C->D exists. Unlike a simple formula, the dependencies for a complex formula cannot be determined easily prior to the calculation.

A top-down calculation is less efficient than a bottom-up calculation because more blocks are calculated than is necessary. Although a top-down calculation is less efficient than a bottom-up calculation, top-down calculations are necessary in some cases to ensure that calculation results are correct.

You can force bottom-up versus top-down calculation in the following ways:

Use the CALCOPTFRMLBOTTOMUP configuration setting to make all calculations for the database bottom-up.
Use the @CALCMODE function for specific formulas to be calculated bottom-up or top-down.
Use the SET FRMLBOTTOMUP command in a calc script to force a bottom-up calculation on a specific top-down formula or on all calculations affected by the script; for example, specified before a CALC ALL or CALC DIM command.

For more information about these options and about checking calculation results when using these commands, see the online Technical Reference in the DOCS directory.

Implementing Calc Script Techniques

You may achieve significant improvements in calculation performance by carefully grouping formulas and dimensions in a calc script. In this way, you can ensure that Hyperion Essbase cycles through the data blocks in the database as few times as possible during a calculation. For more information, see Developing Calc Scripts. For more information on calculation passes, see Defining the Calculation Order.

Aim to make the database calculation as simple as possible. If possible, consider applying all formulas to the database outline and using a default calculation (CALC ALL). This method may improve calculation performance.

Setting Memory Cache Sizes

When calculating the database, Hyperion Essbase uses approximately 30 bytes of memory per member in the database outline. So if the database has 5,000 members, Hyperion Essbase needs approximately 150K of memory to calculate the database.

When you run concurrent calculations, each calculation uses separate memory space. For example, if you are running two calc scripts concurrently, Hyperion Essbase requires 60 bytes per member. Hyperion Essbase needs 30 bytes per member for each calculation.

Hyperion Essbase uses memory to optimize calculation performance, especially for large calculations. The amount of memory used is not controllable, except by altering the size of the database outline. However, you can ensure that the memory cache sizes enable Hyperion Essbase to optimize the calculation.

Hyperion Essbase uses four memory caches to coordinate memory usage:

The calculator cache
The index cache
The data cache
The data file cache

Ensure that the calculator cache is large enough to optimize calculation performance. For more information, see the following sections.

The index cache needs to be large enough to reduce the need to keep writing the index pages to the disk. If the database is large, the default index cache is not large enough to provide optimum calculation performance.

For information on sizing the index, data file, and data caches, see Estimating Disk and Memory Requirements for a Database.

Using the Calculator Cache

The calculator cache creates and tracks data blocks during a calculation in order to avoid excessive disk activity. Hyperion Essbase uses the calculator cache's bitmap to determine which blocks exist rather than making a call to the Hyperion Essbase Kernel to obtain this information.

Using the calculator cache's bitmap significantly improves calculation performance, particularly if you are calculating the database for the first time or if the data in the database is very sparse. The size of the performance improvement depends on the configuration of the database. For more information on the bitmap, see Understanding Calculator Cache Options.

You can use the default calculator cache size, or you can set the size of the calculator cache within a calc script for the duration of the calc script. For more information, see the calc script SET CACHE command and the CALCCACHE configuration setting in the online Technical Reference in the DOCS directory.

The maximum calculator cache size that you can specify is 200,000,000 bytes. The default, if you do not set the calculator cache, is 200,000 bytes. The calculator cache size that you choose depends on the amount of memory the system has available and the configuration of the database. For information on estimating the size of the calculator cache, see Calculating the Required Size of the Calculator Cache.

Hyperion Essbase uses the calculator cache, provided that both of the following conditions are met:

The database has at least two sparse dimensions.
You calculate at least one, full sparse dimension (unless you specify the SET CACHE ALL command in a calc script, in which case Hyperion Essbase uses the calculator cache, even when you do not have at least one full sparse dimension).

Understanding Calculator Cache Options

For the calculator cache, Hyperion Essbase separates the sparse dimensions in the database into two groups:

Bitmap dimensions
The sparse dimensions in the database outline that Hyperion Essbase can fit into the bitmap. Each member combination of these sparse dimensions occupies 1 bit of memory.
"Anchoring" dimensions
The remaining one or more sparse dimensions in the database outline (that is, the dimensions that do not fit into the bitmap).

To determine how many dimensions can fit into the bitmap, Hyperion Essbase starts with the first sparse dimension in the database outline and attempts to fit as many sparse dimensions as possible, based on their size, into the bitmap. The dimensions that fit are the bitmap dimensions. Only full dimensions can be placed in the bitmap. Hyperion Essbase stops the process when it cannot fit any more sparse dimensions into the bitmap, based on the size of the calculator cache. The remaining sparse dimensions are the anchoring dimensions. For anchoring dimensions, Hyperion Essbase cannot use the bitmap and must make calls to the Hyperion Essbase Kernel to determine whether or not blocks exist.

To see which dimensions are anchoring dimensions and which are bitmap dimensions, use the SET MSG DETAIL calculation command to display this information in the application event log. For more information on this command, see the online Technical Reference in the DOCS directory.

So that as many dimensions as possible can be placed into the bitmap, sparse dimensions should be carefully ordered in the outline. Order the sparse dimensions by the number of stored members the dimension contains, starting with the dimension that contains the fewest members. This order allows more dimensions to fit into the bitmap and results in improved calculation performance.

Note:

The order of sparse dimensions in the outline also affects query performance. To optimize the outline for query performance, see Positioning Dimensions and Members.

After the bitmap dimensions are determined, Hyperion Essbase chooses whether to have one bitmap (single) or two bitmaps (multiple):

With a single bitmap:

A single bitmap is used to track child blocks.
A single bitmap uses the least memory but is less efficient than multiple bitmaps.
The calculator cache uses a single bitmap if there is more than one anchoring dimension or if the calculator cache is not large enough to support multiple bitmaps.

With multiple bitmaps:

Two or more bitmaps are used, one to track child blocks and one to track parent blocks.
The use of multiple bitmaps is the optimal method. Multiple bitmaps use more memory but are more efficient than a single bitmap. The performance improvement is particularly high when you are calculating the database for the first time.
The calculator cache can use multiple bitmaps only if there is a single anchoring dimension.
The number of bitmaps used is determined by the maximum number of dependent parents for any of the members in the anchoring dimension.
A member has one dependent parent, unless it has a shared member. For example, consider the Product dimension of the Sample Basic database. The member Cola (100-10) has one parent, which is Colas (100). However, Diet Cola (100-20) has two parents, which are Diet Drinks (Diet) and Colas (100). No members of Product have more than two dependent parents. Therefore, if Product is the anchoring dimension, the maximum number of dependent parents is 2.

Depending on the calculator cache size that you specify, Hyperion Essbase chooses one of three options for the calculation:


Option	Method	Performance Rating
1	Single anchoring dimension, multiple bitmaps	1
2	Single anchoring dimension, single bitmap	2
3	Multiple anchoring dimensions, single bitmap	3

Hyperion Essbase chooses the optimal performance method for the database calculation, based on the size of the calculator cache. If the calculator cache size is too small for any of the above options, Hyperion Essbase does not use a calculator cache. Calculation performance may be significantly impaired.

Calculating the Required Size of the Calculator Cache

The size of the calculator cache depends on the amount of memory the system has available. It also depends on the nature and configuration of the database. You can calculate the calculator cache size that is required for each of the above three options.

Consider an example database with five sparse dimensions (S1 to S5):


Sparse Dimension	# of Members	Dependent Parents
S1	20	Not applicable
S2	20	Not applicable
S3	50	Not applicable
S4	50	Not applicable
S5	200	3

The calculator cache size required for each of the three options can be calculated as follows:

Option 1: Single Anchoring Dimension, Multiple Bitmaps


Bitmap dimensions	S1, S2, S3, S4
Anchoring dimension	S5
Dependent parents in anchoring dimension	3


Bitmap size in bytes	=	(S1 * S2 * S3 * S4)/8
	=	(20 * 20 * 50 * 50)/8
	=	125,000 bytes
Number of bitmaps	=	Maximum number of dependent parents in the anchoring dimension
		+
		2 constant bitmaps
	=	3 + 2
	=	5
Calculator cache	=	Bitmap size * Number of bitmaps
	=	125,000 * 5
	=	625,000 bytes

Option 2: Single Anchoring Dimension, Single Bitmap


Bitmap dimensions	S1, S2, S3, S4
Anchoring dimension	S5
Dependent parents in anchoring dimension	Not applicable


Bitmap size in bytes	=	(S1 * S2 * S3 * S4)/8
	=	(20 * 20 * 50 * 50)/8
	=	125,000 bytes
Number of bitmaps	=	Single bitmap
Number of bitmaps	=	1
Calculator cache	=	Bitmap size * Number of bitmaps
	=	125,000 * 1
	=	125,000 bytes

Option 3: Multiple Anchoring Dimensions, Single Bitmap


Bitmap dimensions	S1, S2, S3
Anchoring dimensions	S4, S5
Dependent parents in anchoring dimensions	Not applicable


Bitmap size in bytes	=	(S1 * S2 * S3)/8
	=	(20 * 20 * 50)/8
	=	2,500 bytes
Number of bitmaps	=	Single bitmap
Number of bitmaps	=	1
Calculator cache	=	Bitmap size * Number of bitmaps
	=	2,500 * 1
	=	2,500 bytes

Conclusion

The following table shows which calculator cache option Hyperion Essbase uses, depending on the calculator cache size specified:


If you specify at least...	Hyperion Essbase uses...
625,000 bytes	Option 1 (provides optimal performance)
125,000 bytes	Option 2
2,500 bytes	Option 3

If you specify a calculator cache size of less than 2,500 bytes, Hyperion Essbase does not use a calculator cache during the calculation. Calculation performance may be significantly impaired.

You can check which calculator cache option Hyperion Essbase is able to use on a database by using the SET MSG SUMMARY command in a calc script. Run the following calc script on the empty database:

SET MSG SUMMARY;
CALC ALL;

Hyperion Essbase displays the calculator cache setting in the ESSCMD window or in the event log file. For more information, see Monitoring and Tracing Calculations.

The size of the calculator, index, data file, and data caches generally has a greater effect on performance if the database calculation is based mainly on aggregations rather than on formula calculations.

Calculating the Database for the First Time

If you are calculating the database for the first time, the size of the calculator cache is particularly significant for calculation performance. If possible, ensure that the calculator cache is large enough for Hyperion Essbase to use the optimal calculator cache option. For more information, see Understanding Calculator Cache Options.

Using the Calculator Cache for Large, Flat Database Outlines

You can use the SET CALCHASHTBL command to optimize how Hyperion Essbase uses the calculator cache when calculating large, flat database outlines (for example, where one member has more than 5000 children). To use SET CALCHASHTBL, you must enable the calculator cache.

When you enable SET CALCHASHTBL, Hyperion Essbase uses a hash table to optimize use of the calculator cache for large, flat databases. Using this feature may significantly improve the performance of a CALC ALL of the database or a CALC DIM of the dimension containing the member with over 5000 children.

For more information, see the SET CALCHASHTBL command and the CALCOPTCALCHASHTBL and CALCHASHTBLMEMORY configuration settings in the online Technical Reference in the DOCS directory.

Locking Blocks During Calculation

When a block is calculated, Hyperion Essbase gets addressability to the block and to the blocks that contain the children of the block being calculated. Hyperion Essbase calculates the block and then releases both the block and the blocks containing its children.

By default, Hyperion Essbase gets addressability to up to 100 blocks concurrently when calculating a block. This number of blocks is sufficient for most database calculations. If you are calculating a formula in a sparse dimension, Hyperion Essbase works most efficiently if it can get addressability to all required children concurrently. Therefore, when calculating a formula in a sparse dimension, you may want to set a number higher than 100 if you are consolidating very large numbers of children (for example, more than 100 children). By increasing the number, you ensure that Hyperion Essbase can get addressability to all required blocks and that performance is not impaired.

You can use the SET LOCKBLOCK command in a calc script and the CALCLOCKBLOCK setting in the ESSBASE.CFG file to specify the maximum number of blocks that Hyperion Essbase can get addressability to concurrently when calculating a block. For more information, see the online Technical Reference in the DOCS directory.

Note:

For aggregations in a sparse dimension, block locking is not a consideration because Hyperion Essbase does not need to get addressability to all the children concurrently.

Hyperion Essbase locking behavior depends on the Hyperion Essbase Kernel Isolation Level setting. For more information, see Ensuring Data Integrity.

Considering Multiple Users

Hyperion Essbase uses a block locking system to provide concurrent access to users. This system ensures that only one user at a time can update or calculate a particular data block. How Hyperion Essbase handles locking blocks and committing data depends on the Hyperion Essbase Kernel Isolation Level setting.

When Hyperion Essbase calculates a data block, it gets addressability to the block with an exclusive lock. Thus, no other user can update or calculate the data block. However, other users can have read-only access to the block. When Hyperion Essbase finishes the calculation, it releases the block. Other users can then update the block if they have the appropriate security access.

When a user is updating a data block, the block is locked. If a database calculation requires a data block that is being updated by another user, the calculation waits for one of the following, depending on the Hyperion Essbase Kernel Isolation Level setting:

For the data block to be released (Uncommitted Access Isolation Level setting)
For the calculation to complete (Committed Access Isolation Level setting)

Hyperion Essbase does not provide a message to say that the calculation is waiting for the data block to be released.

You can prevent calculation delays caused by waiting for locked blocks by using Hyperion Essbase security options to do either of the following:

Deny access to other users
Disconnect users from Hyperion Essbase

For more information on these security options, see Designing and Building a Security System. For information on how Hyperion Essbase handles locks and transactions, see Ensuring Data Integrity.

Note:

When Hyperion Essbase gets addressability to a data block for calculation, it does not put an exclusive lock on the dependent child blocks. Thus, another user can update values in the child blocks. If necessary, you can use the above security options to prevent such updates.

Using Two-Pass Calculation

In some cases, you can achieve a significant performance improvement by tagging an accounts dimension member as two-pass in the database outline rather than by repeating the formula in a calc script. In other cases, you may need to use a calc script to calculate a formula twice.

Note:

You can achieve performance improvement by tagging two-pass members as Dynamic Calc. You can tag Dynamic Calc members as two-pass, even if the members are not in the accounts dimension. For more information, see Dynamically Calculating Data Values.

Some member formulas need to be calculated twice to produce the required value. For example, consider the calculation required for Profit%, where

Profit% = Profit % Sales

The following table shows a subset of a data block with Measures and Year as dense dimensions. Measures is tagged as accounts, and Year is tagged as time. The AGGMISSG setting is turned off (the default).

Data values have been loaded into the input cells. Hyperion Essbase calculates the shaded cells. The numbers in bold show the calculation order for the cells. Cells with multiple consolidation paths are darkly shaded.


Measures/Year	Jan	Feb	Mar	Qtr1
Profit	75	50	120	5
Sales	150	200	340	6
Profit%	1	2	3	4/7

Note:

For detailed information on how cell calculation order depends on database configuration, see Defining the Calculation Order.

The calculation order is as follows:

Hyperion Essbase calculates the formula Profit % Sales for Profit%->Jan, Profit%->Feb, Profit%->Mar, and Profit%->Qtr1.
Hyperion Essbase calculates Profit->Qtr1 and Sales->Qtr1 by adding the values for Jan, Feb, and Mar.

Hyperion Essbase calculates Profit%->Qtr1 by adding the values for Profit%->Jan, Profit%->Feb, and Profit%->Mar. This addition of percentages does not produce the correct result.


Measures/Year	Jan	Feb	Mar	Qtr1
Profit	75	50	120	245
Sales	150	200	340	690
Profit%	50%	25%	50%	125%

When Profit% is tagged as two-pass in the database outline, Hyperion Essbase uses the Profit % Sales formula to recalculate the Profit% values and produce the correct results.


Measures/Year	Jan	Feb	Mar	Qtr1
Profit	75	50	120	245
Sales	150	200	340	690
Profit%	50%	25%	50%	36%

Note:

You can tag a member as two-pass only if it is in a dimension tagged as accounts, unless it is a Dynamic Calc or Dynamic Calc And Store member. You can tag Dynamic Calc and Dynamic Calc And Store members as two-pass, even if the members are not in the accounts dimension. For more information, see Dynamically Calculating Data Values.

When you perform a default calculation on the database, Hyperion Essbase automatically recalculates any formulas tagged as two-pass in the dimension tagged as accounts in the database outline.

In some situations, you may need to use a calc script to calculate a two-pass formula. For more information, see Using a Calc Script for Two-Pass Calculations.

In other situations, you may want to use a calc script to ensure efficient use of Intelligent Calculation. For more information, see Using Two-Pass on a Default Calculation.

When you use a calc script, Hyperion Essbase does not automatically recalculate two-pass formulas. You need to use the CALC TWOPASS command. If you are using Intelligent Calculation, consider the implications of marking data blocks as clean. For more detailed information, see Using a Calc Script for Two-Pass Calculations.

Using Two-Pass on a Default Calculation

When you perform a default calculation on a database with two-pass calculation enabled (the default), Hyperion Essbase automatically attempts to calculate any formulas tagged as two-pass in the dimension tagged as accounts in the database outline. This is true even if you have customized the default calc script.

You can perform a default calculation by using any of the following:

MaxL or ESSCMD

Use execute calculation in MaxL or the CALCDEFAULT command in ESSCMD to run the default calculation. For more information, see the online Technical Reference in the DOCS directory.

Hyperion Essbase Application Manager (by selecting Database > Calculate and selecting Default)

To enable two-pass calculation in Hyperion Essbase Application Manager:

Select Database > Settings.
Hyperion Essbase displays the Database Settings dialog box.
On the General tab, check Two-Pass Calculation.

Click OK.

You can also use alter database in MaxL or the SETDBSTATE command in ESSCMD to enable two-pass calculation. For information, see the online Technical Reference in the DOCS directory.

However, in some situations, you may need to use a calc script to calculate the two-pass formulas. For more information, see Using a Calc Script for Two-Pass Calculations.

Hyperion Essbase recognizes formulas on members tagged as two-pass only in a dimension tagged as accounts, unless the member is a Dynamic Calc or Dynamic Calc And Store member. You can tag Dynamic Calc and Dynamic Calc And Store members as two-pass, even if the members are not in the accounts dimension. For more information, see Dynamically Calculating Data Values.

When you are doing a default calculation of the database, you need to ensure that the two-pass calculation option is selected in the Database Settings dialog box in Hyperion Essbase Application Manager. Hyperion Essbase then does the two-pass calculation as part of the default calculation.

Whenever possible, Hyperion Essbase calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Hyperion Essbase does not need to do an extra calculation pass through the database. However, in some situations, Hyperion Essbase needs to calculate the two-pass formulas on an extra calculation pass through the database. For information on calculation passes, see Defining the Calculation Order.

How Hyperion Essbase calculates the two-pass formulas depends on whether there is a dimension tagged as time as well as a dimension tagged as accounts. It also depends on the dense-sparse configuration of these dimensions.

Two scenarios are described in detail in the following sections. If you are using Intelligent Calculation, consider the scenario that matches the configuration of your database; each scenario tells you how to ensure that Hyperion Essbase calculates two-pass formulas accurately.

The following information assumes that you understand the concepts of Intelligent Calculation. For more information, see Using Intelligent Calculation to Optimize Calculation.

Scenario A

Scenario A may be summarized as follows:

No extra calculation pass for two-pass formulas
All data blocks marked as clean

In Scenario A, you place formulas in the outline and then, as appropriate, tag specific formulas as two-pass. Scenario A produces an efficient calculation process.

No extra calculation pass for two-pass formulas

Hyperion Essbase calculates the two-pass formulas while it is calculating the data block. Thus, Hyperion Essbase does not need to do an extra calculation pass through the database.

All data blocks marked as clean

After the calculation, all data blocks are marked as clean for the purposes of Intelligent Calculation. For more information, see Using Intelligent Calculation to Optimize Calculation.

If you have changed the default calculation from the default CALC ALL, the data blocks may not all be marked as clean after a default calculation. For more information, see Using Intelligent Calculation to Optimize Calculation. You can check the default calculation setting by selecting Database > Set Default Calc in Hyperion Essbase Application Manager.

If the database configuration allows Hyperion Essbase to use Scenario A, you can achieve a performance improvement by tagging a member formula as two-pass in the outline rather than by repeating the formula in a calc script. When you tag a member formula as two-pass in the outline, Hyperion Essbase does the two-pass calculation while each data block is being calculated. However, when you repeat a formula in a calc script, Hyperion Essbase has to read and write the data blocks to memory in order to recalculate the formula.

Scenario B

Scenario B may be summarized as follows:

Extra calculation pass for two-pass formulas
Data blocks representing two-pass formulas are not marked as clean

Extra calculation pass for two-pass formulas

Hyperion Essbase calculates the database and then does an extra calculation pass to calculate the two-pass formulas. Even though all data blocks are marked as clean after the first database calculation, Hyperion Essbase ignores the clean status on the blocks that represent the two-pass formulas and recalculates these blocks.

Data blocks representing two-pass formulas are not marked as clean

After the first calculation, Hyperion Essbase has marked all the data blocks as clean for the purposes of Intelligent Calculation. In a second calculation pass through the database, Hyperion Essbase recalculates the required data blocks for the two-pass formulas. However, because the second calculation is a partial calculation of the database, Hyperion Essbase does not mark the recalculated blocks as clean. When you recalculate the database with Intelligent Calculation turned on, these data blocks may be recalculated unnecessarily.

If you have changed the default calculation from the default CALC ALL, the data blocks may not be marked as clean after the first calculation. For more information, see Using Intelligent Calculation to Optimize Calculation. You can check the default calculation setting by selecting Database > Set Default Calc in Hyperion Essbase Application Manager.

If the database configuration allows Hyperion Essbase to use Scenario B, consider using a calc script to perform two-pass formula calculations. If you use a calc script, Hyperion Essbase still does an extra calculation pass through the database; however, you can ensure that Hyperion Essbase has marked all the data blocks as clean after the calculation. For more information, see Using a Calc Script for Two-Pass Calculations.

Using a Calc Script for Two-Pass Calculations

You need to use a calc script to calculate a formula twice if the database configuration means that Hyperion Essbase uses Scenario A, as described in Using Two-Pass on a Default Calculation, and if the formula references values from another data block.

You may want to use a calc script to calculate two-pass formulas if the database configuration means that Hyperion Essbase uses Scenario B, as described in Using Two-Pass on a Default Calculation.

Consider an example in which the dimension tagged as accounts is dense. You want to calculate sales for each product as a percentage of sales for all products. The formula might be Sales % Sales->Product.

When Hyperion Essbase calculates the data block for each product, it has not yet calculated the value Sales->Product, so the results for the sales of each product as a percentage of total sales are incorrect.

If you have Intelligent Calculation turned on (the default), Hyperion Essbase calculates only those data blocks that are not marked as clean. When you perform a default calculation of the database with Intelligent Calculation turned on, all the data blocks are marked as clean. Therefore, before you recalculate a two-pass formula, you need to turn off Intelligent Calculation.

To calculate the correct results for Sales %, you can choose one of two options, depending on whether you want to obtain the performance benefits of Intelligent Calculation when performing the first, full calculation of the database.

Option 1: Using Intelligent Calculation with a Large Index

If the index is quite large and you want the benefit of using Intelligent Calculation, you can use any of the following options:

Use a calc script to calculate the formula: Run a calc script to perform a full calculation of the database with Intelligent Calculation turned on. This calculation marks all the data blocks as clean. Then, in the calc script, turn off Intelligent Calculation, tell Hyperion Essbase to mark the recalculated data blocks as clean, and calculate the formula.
```
SET UPDATECALC ON;
CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Share of Sales" = Sales % Sales->Product;
```
Tag a member as two-pass, and use a calc script to calculate the two-pass member: Place a formula in the database outline and tag it as two-pass. Place the formula on the Share of Sales member in the dimension tagged as accounts. In this case, the required calc script is as follows:
```
SET UPDATECALC ON;
CALC ALL;
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
CALC TWOPASS;
```
Do a default calculation and then use a calc script to calculate the formula: Perform a full calculation in Hyperion Essbase Application Manager. Select Database > Calculate from the main menu and select Default (providing that you have not customized the default calc script), or use MaxL or ESSCMD. Ensure that Intelligent Calculation is turned on when you calculate the database. Then, use the second part of the calc script to calculate the formula, as follows:
```
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
"Share of Sales" = Sales % Sales->Product;
```
or:
```
SET UPDATECALC OFF;
SET CLEARUPDATESTATUS AFTER;
CALC TWOPASS;
```

Hyperion Essbase performs the following calculations during each of the three options:

The SET UPDATECALC ON command turns on Intelligent Calculation.
The CALC ALL command calculates the database and marks the data blocks as clean.
The SET UPDATECALC OFF command turns off Intelligent Calculation.
The SET CLEARUPDATESTATUS AFTER command tells Hyperion Essbase to mark the recalculated blocks as clean, even though this calculation is a partial calculation of the database. (If you do not use the SET CLEARUPDATESTATUS AFTER command, Hyperion Essbase marks data blocks as clean only after a full calculation of the database.)
Hyperion Essbase cycles through the database calculating only the formula for Share of Sales or calculating all formulas tagged as two-pass in the database outline.

Note:

For more information on Intelligent Calculation, see Using Intelligent Calculation to Optimize Calculation. For more information on developing formulas and calc scripts, see Developing Formulas and Developing Calc Scripts.

Option 2: Using Intelligent Calculation with a Small Index

If the index is small and you want the benefit of using Intelligent Calculation, you can run a calc script to calculate the database, but tell Hyperion Essbase not to mark the calculated data blocks as clean. Then mark all the data blocks as clean, but do not recalculate the data blocks.

SET CLEARUPDATESTATUS OFF;
CALC ALL;
CALC TWOPASS;
SET CLEARUPDATESTATUS ONLY;
CALC ALL;

Hyperion Essbase performs the following operations:

The SET CLEARUPDATESTATUS OFF command tells Hyperion Essbase not to mark the calculated data blocks as clean.
The CALC ALL command causes Hyperion Essbase to cycle through the database calculating all dirty data blocks. Hyperion Essbase does not mark the calculated data blocks as clean. Hyperion Essbase does not automatically recalculate the formulas tagged as two-pass in the database outline.
Hyperion Essbase cycles through the database recalculating the formulas that are tagged as two-pass in the dimension tagged as accounts in the database outline. Hyperion Essbase recalculates the formulas because the required data blocks are not marked as clean by the previous CALC ALL. Hyperion Essbase does not mark these recalculated data blocks as clean.
The SET CLEARUPDATESTATUS ONLY command tells Hyperion Essbase to mark the data blocks as clean but not to calculate the data blocks. This command disables calculation.
The CALC ALL command causes Hyperion Essbase to cycle through the database and mark all the data blocks as clean. Hyperion Essbase searches through the index and marks the data blocks as clean. It does not calculate the data blocks.

Option 3: Not Using Intelligent Calculation

Use a calc script to turn off Intelligent Calculation, perform a full calculation, and repeat the two-pass formula at the end of a calc script as follows:

SET UPDATECALC OFF;
CALC ALL;
"Share of Sales" = Sales % Sales->Product;

Calculating #MISSING Values

If no data value exists for a unique combination of dimension members, Hyperion Essbase gives the combination a value of #MISSING. Hyperion Essbase treats #MISSING values and zero (0) values differently.

The following table shows how Hyperion Essbase calculates #MISSING values. In this table, X represents any number:

*Table 35-1: How Hyperion Essbase Treats #MISSING Values*
Calculation/Operation	Result
X + `#MISSING`	X
X - `#MISSING` `#MISSING` - X	X -X
X * `#MISSING`	#MISSING
X / `#MISSING` `#MISSING` / X X / 0	#MISSING #MISSING #MISSING
X % `#MISSING` `#MISSING` % X X % 0	#MISSING #MISSING #MISSING
X == `#MISSING`	FALSE, unless X is `#MISSING`
X != `#MISSING` X <> #MISSING	TRUE, unless X is `#MISSING` TRUE, unless X is `#MISSING`
(X <= `#MISSING`)	(X <= 0)
(X >= `#MISSING`)	(X >= 0) or (X == `#MISSING`)
(X > `#MISSING`)	(X > 0)
(X < `#MISSING`)	(X < 0)
X AND `#MISSING`: 1 AND `#MISSING`, where 1 represents any nonzero value 0 AND `#MISSING` `#MISSING` AND `#MISSING`	`#MISSING` 0 `#MISSING`
X OR `#MISSING`: 1 OR `#MISSING`, where 1 represents any nonzero value 0 OR `#MISSING` `#MISSING` OR `#MISSING`	1 `#MISSING` `#MISSING`
IF (`#MISSING`)	IF (0)
f (`#MISSING`)	`#MISSING` for any Hyperion Essbase function of one variable
f (X)	`#MISSING` for any X not in the domain of f and any Hyperion Essbase function of more than one variable (except where specifically noted)

By default, Hyperion Essbase does not aggregate #MISSING values. This default is important to consider when you need to load data at parent, rather than child, levels. For more information, see Loading Data at Parent Levels.

You can change the way Hyperion Essbase aggregates #MISSING values by doing any of the following:

Changing the default setting in Hyperion Essbase Application Manager
Using the SET AGGMISSG command in a calc script
Using the SETDBSTATEITEM command in ESSCMD
Using alter database in MaxL

For information about alter database in MaxL or the SETDBSTATEITEM command in ESSCMD, see the online Technical Reference in the DOCS directory.

To change how #MISSING values are aggregated at the database level, use Hyperion Essbase Application Manager:

Select Database > Settings.
Hyperion Essbase displays the Database Settings dialog box.
Check Aggregate Missing Values.
By default, this setting is turned off.
Click OK.

To change how #MISSING values are aggregated on a per-calculation basis, use the SET AGGMISSG calculation command in a calc script. For more information on this command, see the online Technical Reference in the DOCS directory.

If you never load data at parent levels, you can achieve performance improvements by aggregating #MISSING values. To aggregate, enable the setting for aggregating #MISSING values by using one of the methods described above. The degree of performance improvement you achieve depends on the ratio between upper level blocks and input blocks in the database. For more information, see Defining the Calculation Order.

Note: If you enable the setting for aggregating #MISSING values, the cell calculation order within a data block changes. For more information, see Defining the Calculation Order.

When the setting for aggregating #MISSING values is disabled, note that the performance overhead is particularly high in the following two situations:

When you have a low ratio of calculated data blocks to input data blocks
When you load many data values at parent levels on sparse dimensions; for example, in the Sample Basic database, if you load many data values into East in a sparse Market dimension

In these situations, the performance overhead is between 10% and 30%. If calculation performance is critical, you may want to reconsider the database configuration or reconsider how you load data.

Loading Data at Parent Levels

Sometimes you may need to load data into cells that represent parent members of one or more dimensions. For example, in the Sample Basic database, you may want to load Miscellaneous Expenses (Misc) for each product at the consolidated product level. You load Miscellaneous Expenses for Colas (100), Root Beer (200), Cream Soda (300), and Fruit Soda (400) rather than for each individual product.

When you do a parent load, you do not want the loaded data to be overwritten when Hyperion Essbase aggregates the #MISSING values in the children. By default, Hyperion Essbase does not aggregate #MISSING values and so the data is not overwritten.

However, if you always load data at level 0 and never at parent levels, then you should enable the setting for aggregating #MISSING values. Use of this setting provides a calculation performance improvement of between 1% and 30%. The performance improvement varies, depending on database size and configuration. For more information on setting the behavior for aggregating #MISSING values, see Calculating #MISSING Values, Defining the Calculation Order, and the online Technical Reference in the DOCS directory.

CAUTION: You need to ensure that the setting for aggregating #MISSING values is off (the default) to protect parent-level data only if the child member combinations have #MISSING values. If the child member combinations have any other values, including zeros (0), then Hyperion Essbase aggregates the child values and overwrites the parent values.