Optimizing Calculations

This chapter provides information on how to optimize the performance of Essbase calculations:

You can also use any or all of these features to optimize overall database calculations:

Designing for Calculation Performance

You can configure a database to optimize calculation performance.

The best configuration for your site depends on the nature and size of your database. Use these sections as guidelines only:

Block Size and Block Density

A data block size of 8Kb to 100Kb provides optimal performance in most cases.

If data blocks are much smaller than 8Kb, the index is usually very large, forcing Essbase to write and retrieve the index from disk. This slows down calculation.

If data blocks are much larger than 100Kb, Intelligent Calculation does not work effectively. For more information on how intelligent calculation aids performance improvements, see Optimizing with Intelligent 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:

You can view information about a database, including the potential and actual number of data blocks and the data block size. Use any of these tools to view this information:

Tool
Instructions
For more information

Administration Services

Database Properties window > Statistics tab

Essbase Administration Services Online Help

Application Manager

Open Database > Information, select Statistics tab.

Application Manager help topic: Main Menu > Database Menu > Settings

ESSCMD

GETDBINFO

Technical Reference in the docs directory



Order of Sparse Dimensions

You may improve calculation performance by changing the order of standard (not attribute) sparse dimensions in the database outline. Order standard sparse dimensions by the number of members they contain, starting with the dimension that contains the fewest members. This arrangement provides a number of possible improvements, depending on your site:

Incremental Data Loading Considerations

Many people load data incrementally. For example, a company may load data each month 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, a different data block contains data for each time period. When you load data by time period, Essbase accesses fewer data blocks because fewer blocks contain the relevant time period. 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, Essbase recalculates only the data blocks for March and March's dependent parents.

However, making the time dimension sparse when it is naturally dense may significantly increase the size of the index, creating possibly slower performance due to more physical I/O activity to accommodate the large 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, 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 Optimizing with Intelligent Calculation.

Performance for Database Outlines with Two or More Flat Dimensions

Calculation performance may be affected if a database outline has two or more flat dimensions. A flat dimension has very few parents and each parent has many thousands of children; in other words flat dimensions have many members and few levels.

You can improve performance for outlines with two or more flat dimensions by making any of these changes:

Monitoring and Tracing Calculations

You can display information in the application log about how Essbase is calculating the database by using the following commands in a calculation script:

SET MSG SUMMARY and SET MSG DETAIL

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

The SET MSG DETAIL command also provides a detailed information message every time Essbase calculates a data block. SET MSG DETAIL is useful for reviewing the calculation order of data blocks and for testing intelligent recalculations.

Caution: Because the SET MSG DETAIL command causes a high processing overhead, use it only during test calculations.

SET MSG SUMMARY causes a processing overhead of approximately 1% to 5%, depending on database size, and is therefore appropriate for all calculations.

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

SET NOTICE

You can use the SET NOTICE calculation command in a calculation 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 SET NOTICE, see the Technical Reference in the docs directory.

Simulating Calculations

You can simulate a calculation using SET MSG ONLY in a calculation script. A simulated calculation produces results that help you analyze the performance of a real calculation based on the same data and outline.

By running a simulated calculation with a command like SET NOTICE HIGH, you can mark the relative amount of time each sparse dimension takes to complete. Then, by performing a real calculation on one or more dimensions, you can estimate how long the full calculation would take, because the time a simulated calculation takes to run is proportional to the time that same actual calculation takes to run.

For example, if the caclulation started at 9:50:00 AM, the first notice was time-stamped at 09:50:10 AM, and the second at 09:50:20 AM, you'd know that each of these parts of the calculation took ten seconds. If you then ran a real calculation on only the first portion and noted it took 30 seconds to run, you'd know that the other portions would also take 30 seconds. If there were only two messages total, then you'd know that the real calculation would take approximately 60 seconds (20 /10 * 30 = 60 seconds).

In this manner, you can estimate the length of time it takes a calculation to run using a simulated calculation.

Use these sections to learn how to perform a simulated calculation and how to analyze results from a simulated calculation:

Performing a Simulated Calculation

To perform a simulated calculation, use this procedure:

  1. Create a data model using all the dimensions and level of detail about which you want information.
  2. Load all the data. This procedure only calculates data loaded in the database.
  3. Create a calculation script with these entries:
  4. SET MSG ONLY; 
    SET NOTICE HIGH; 
    CALC ALL; 
    

    If you are using dynamic calculations on dense dimensions, substitute the CALC ALL command with the specific dimensions that you need to calculate, for example CALC DIM EAST.

    Note: If you try to validate the script, Essbase reports an error. You can disregard this error.

  5. Run the script.
  6. Find the first sparse calculation message in the application log and note the time in the message.
  7. Note the time for each subsequent message.
  8. Actually calculate the dense dimensions of the model that are not being dynamically calculated:
  9. CALC DIM (DENSE_DIM1, DENSE_DIM2, ...); 
    

  10. Calculate the sparse dimensions of the model:
  11. CALC DIM (SPARSEDIM1, SPARSEDIM2, ...); 
    

  12. Project the intervals at which notices will occur, then verify against sparse calculation results. You can then estimate how long a calculation will take.

To estimate the total time a calculation will take, use this procedure:

  1. Note the times of all the intervals between application log messages generated by SET NOTICE HIGH. See Table 89 for an example.
  2. Use this calculation to estimate the time for a real calculation:
  3. Total time required for simulated calculation, divided by the first simulated calculation notice interval, multiplied by the first real calculation time interval.

Table 89: Sample Intervals Between Log Messages

Calculation Notice Number
Simulated Calculation Time Interval
Sparse dimension Calculation Interval

1

7 seconds

45 seconds

2

5 seconds

 

3

6 seconds

 

4

3 seconds

 

5

4 seconds

 

6

2 seconds

 

7

6 seconds

 

8

4 seconds

 

9

3 seconds

 

10

3 seconds

 

Total

43 seconds

 



In this example, 43 / 7 * 45 = 276.4 seconds, so the real calculation should take 276.4 seconds.

Notes About Using SET MSG ONLY

This calculation time estimating technique should be validated against later CALC NOTICE intervals. The results of this technique will vary because of this chain of influences:

  1. Blocks differ in block density through the real aggregation process, therefore
  2. The rate at which Essbase writes blocks to the disk differ, therefore
  3. The rate at which blocks are processed in cache differs, therefore
  4. Actual results may differ from the predicted calculation time.

This estimating technique, however, improves significantly once Essbase has reached about 30-40 percent of the simulated calculations (30-40 percent of the messages generated by SET NOTICE HIGH).

There is one more factor that will make actual results diverge significantly from predicted, and that is when the model contains 1-2 sparse dimensions that are very large in relation to the other sparse dimensions. This will tend to make the performance of the last 10 to 20th percentile CALC NOTICE much longer than would be expected. Many times, this is the result of 2-3 shared rollups in larger dimensions.

Excluding these issues, the simulated calculation should return a time accurate to about 5%.

Changing Your Outline Based on Results

Once you have estimated and analyzed a simulated calculation, you can make changes in your outline to improve performance.

From top to bottom in your outline, order sparse dimensions to create the fewest percentage increases in upper blocks:

For example:

Use the simulated calculation to generate the upper block count. These numbers may be accurate despite actual dimension sizes as noted next to the items above.

Caution: The largest count of dimension members is not always a good predictor.

Estimating Calculations

Given the current number of blocks in a database, you can estimate the number of blocks that will be produced by a CALC ALL.

To estimate the size of a calculation, use this procedure:

  1. Load data and issue a CALC ALL command.
  2. Log into ESSCMD and select an application and database if you have not already.
  3. Issue the ESTIMATEFULLDBSIZE command and note the value returned, the number of blocks.
  4. Multiply the number returned by the average size of the blocks in the database. If you are not sure of the average size of blocks for your database, load a small sample of data, perform a CALC ALL, and check the average size of blocks.
  5. Results are accurate to a precision of plus or minus 10%.

Be aware of these conditions when you use ESTIMATEFULLDBSIZE:

For more information, see the Technical Reference in the docs directory, in the ESSCMD section for command ESTIMATEFULLDBSIZE.

Using Parallel Calculation

This section provides the information that you need to understand parallel calculation and to decide whether it will improve performance for your site. This section also presents instructions for enabling parallel calculation:

Understanding Parallel Calculation

Essbase provides two ways of invoking a calculation:

Regardless of how a calculation is triggered, Essbase can execute the calculation in one of two modes:

To change from the default serial calculation to parallel calculation, use either of these methods:

See Enabling Parallel Calculation for more details.

Caution: Be sure to read all of this chapter before following the instructions in Enabling Parallel Calculation.

Use the rest of this section to understand parallel calculation in detail:

Essbase Analysis of Feasibility

Essbase evaluates whether using parallel calculation is possible before each calculation pass for which you have enabled parallel calculation.

Essbase analyzes your outline and the calculation requested for each calculation pass. Remember that a single calculation may require more than one pass. A number of situations may create the need for more than one pass, including dynamic calculation, the presence of a member tagged as two-pass, or calculations that create certain kinds of inter-dependencies. For more information, see Calculation Passes.

If Essbase determines that parallel calculation is possible, Essbase splits the calculation into smaller tasks that are independent of one another. During the calculation, Essbase performs these smaller tasks at the same time.

However, Essbase uses serial calculation even if parallel calculation is enabled if complex interdependencies between formulas that participate in the pass are present, rendering parallel calculation impossible.

Requirements for Parallel Calculation

Your outline structure and application design determine whether enabling parallel calculation can improve calculation performance. Before you enable parallel calculation, review this list of requirements. If your site does not meet these requirements, you may not get the full benefit of parallel calculation:

Relationship to Other Essbase Features

Use these sections to help you understand the relationship between parallel calculation and other Essbase functionality:

Retrieval Performance

Placing the largest sparse dimension at the end of the outline for maximum parallel calculation performance may slow retrieval performance. See Designing an Outline to Optimize Performance for more information.

Formula Limitations

The presence of some formulas may force serial calculation. These formula placements are likely to force serial calculation:

If you need to use a formula that might prevent parallel calculation, you can either mark the formula's member as Dynamic Calc or exclude it from the scope of the calculation. To check if a formula is preventing parallel calculation, check the application log. See Monitoring Parallel Calculation for details.

Calculator Hash Tables

Calculator hash tables are not used during parallel calculation. You can leave any of these items in your essbase.cfg configuration file or calculation script, but they are ignored during a parallel calculation:

Calculator Cache

At the start of a calculation pass, Essbase checks the calculator cache size and the degree of parallelism, then uses the calculator cache bitmap option appropriate for maximum performance. Therefore, the option used for parallel calculation may be different from the one used for serial calculation.

For example, assume Essbase performs a serial calculation using multiple bitmaps and with a single anchoring dimension. That same calculation, without explicitly changing the calculator cache size, with a parallel calculation might be performed with only a single bitmap and single anchoring dimension.

You can determine the calculator cache mode which controls the bitmap options by checking the application log at the start of each calculation pass for an entry similar to this:

Multiple bitmap mode calculator cache memory usage has a limit of [50000] bitmaps. 

For more information about the calculator cache and calculator cache bitmaps, see Sizing the Calculator Cache.

Transparent Partition Limitations

Parallel calculation with transparent partitions has these limitations:

Restructuring Limitations

Do not use parallel calculation if you have selected incremental restructuring. Parallel calculation does not support incremental restructuring.

Commit Threshold Adjustments

Essbase checks the commit threshold specified by the database setting "Number of blocks before internal commit." If the setting requires less than 10 MB of data be written before an internal commit, then Essbase automatically increases the commit threshold for the duration of the calculation pass to 10 MB. If the setting is greater than 10 MB, Essbase uses the setting value.

Essbase writes a message to the application log noting the temporary increase if it occurs.

If you can allocate more than 10 MB extra disk space for calculation, consider increasing your commit threshold value to a very large number for better performance.

To view the current threshold, use any of these procedures:

Tool
Instructions
For more information

Administration Services

Click the Transactions tab on the Database Properties window

Essbase Administration Services Online Help

MaxL

display database dbs_name

Technical Reference in the docs directory, list of MaxL statements

ESSCMD

GETDBINFO: Number of blocks modified before internal commit

Technical Reference in the docs directory, GETDBINFO

Application Manager

Database > Settings > Transactions tab, Commit Blocks value

Application Manager Online Help



To modify the commit threshold, use any of these procedures:

Tool
Instructions
For more information

Administration Services

Click the Transactions tab on the Database Properties window

Essbase Administration Services Online Help

MaxL

alter database dbs_name set implicit_commit after <n> blocks

Technical Reference in the docs directory, list of MaxL statements

ESSCMD

SETDBSTATEITEM 21

Specifying settings with ESSCMD

Application Manager

Database > Settings > Transactions tab, Commit Blocks value

Specifying Settings with Application Manager



For more information about commit thresholds, see Uncommitted Access.

Isolation Level

You must use uncommitted mode for parallel calculation.

To set the isolation level to uncommitted mode, use any of these procedures:

Tool
Instructions
For more information

Administration Services

Click the Transactions tab on the Database Properties window

Essbase Administration Services Online Help

MaxL

alter database dbs_name disable committed_mode

Technical Reference in the docs directory, list of MaxL statements

ESSCMD

SETDBSTATEITEM 18

Specifying settings with ESSCMD

Application Manager

Database > Settings > Transactions tab

Specifying Settings with Application Manager



See Isolation Levels for more information about isolation levels and uncommitted mode.

Checking Current Parallel Calculation Settings

You can check either the server configuration file or the calculation script you plan to use to see if parallel calculation is enabled.

To check if parallel calculation has already been enabled in the server configuration file:

  1. Open the server configuration file essbase.cfg with a text editor.
  2. Search for the parameter CALCPARALLEL, and check its specified value. A value of 0 means parallel calculation is not enabled, and values 1-4 mean that parallel calculation is enabled. The number of threads that can simultaneously perform tasks to complete a calculation is specified by the value 1-4. See the Technical Reference in the docs directory for details.

To check if a calculation script sets parallel calculation, look for the SET CALCPARALLEL command. Review the script carefully, as the script may enable or disable parallel calculation more than once.

Enabling Parallel Calculation

To use parallel calculation, enable it at the server level, application level, or database level using either of these methods:

Parallel calculation settings use standard precedence rules:

Setting parallel calculation at the server level enables it for all calculations performed on every application and database on the server. You can disable parallel calculation for individual applications or databases by setting it at the server level in the configuration file, then adding application- or database-specific entries in a calculation script.

To enable parallel calculation, use this procedure:

  1. Check the current status using the procedure in Checking Current Parallel Calculation Settings if you plan to enable parallel calculation in the configuration file, to see if an entry already exists.
  2. Add or modify CALCPARALLEL to the server configuration file, or add SET CALCPARALLEL to a calculation script.
  3. If needed, enable Essbase to use more than the one sparse dimension to identify tasks for parallel calculation, using the procedure in Identifying Additional Tasks for Parallel Calculation.
  4. Restart the server if you added entries to the configuration file.
  5. Run the calculation.

Hyperion recommends that you set the value of CALCPARALLEL to be one less than the number of processors available for calculation. This extra processor can then be used by either the operating system or by the Essbase process responsible for writing out dirty blocks from the cache.

Tip: You can combine the use of CALCPARALLEL and SET CALCPARALLEL if your site requires it. For example, you could set CALCPARALLEL off at the server level, then use a calculation script to enable and disable parallel calculation as often as needed.

Identifying Additional Tasks for Parallel Calculation

By default, Essbase uses the last sparse dimension in an outline to identify tasks that can be performed concurrently. But the distribution of data may cause one or more tasks to be empty, that is, there are no blocks to be calculated in the part of the database identified by a task. This can lead to uneven load balancing, thus reducing the effectiveness of parallel calculation.

To resolve this situation, you can enable Essbase to use additional sparse dimensions in the identification of tasks for parallel calculation. For example, if you had a FIX statement on a member of the last sparse dimension, you could include the next-to-last sparse dimension from the outline as well. Since each unique member combination of these two dimensions is identified as a potential task, more and smaller tasks would be created, increasing the opportunities for parallel processing and providing better load balancing.

To increase the number of sparse dimensions used to identify tasks for parallel calculation, use this procedure:

  1. If you are not sure, verify if parallel calculation is already enabled. See Checking Current Parallel Calculation Settings for details. Without CALCPARALLEL (or SET CALCPARALLEL in a calculation script), CALTASKDIMS has no effect.
  2. Add or modify CALCTASKDIMS in the essbase.cfg server configuration file, or use the calculation script command SET CALCTASKDIMS at the top of the script. See the Technical Reference in the docs directory for details.
  3. Restart Essbase if you add or modify CALCTASKDIMS in the essbase.cfg server configuration file. If you are using a calculation script, run the script.

Note: In some cases, Essbase uses a lower number of dimensions to identify tasks than is specified by CALCTASKDIMS or SET CALCTASKDIMS. See the Technical Reference in the docs directory of your Essbase installation for details.

Monitoring Parallel Calculation

You can view events related to parallel calculation in the application log:

For instructions about how to view the application log in Application Manager, see Viewing the Server and Application Logs.

For each calculation pass, Essbase writes several types of information to the application log to support parallel calculation:

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 calculation script. For more information, see Developing Formulas.

To increase performance with formulas, use any of these methods:

Consolidation

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 shown in Figure 546.

Figure 546: 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 

Simple Formulas

If you use a simple formula, you can place it on either a sparse or a dense dimension without significantly affecting calculation performance if the block size is not unusually large. The bigger the block size, the more impact simple formulas have on calculation performance. For more information, see Block Size and Block Density.

A simple formula is, for example, a ratio or a percentage. A simple formula meets all of these requirements:

For information on how formulas affect calculation performance, see Understanding Bottom-Up and Top-Down Calculation.

Complex Formulas

If you use a complex formula, you can improve performance by following these guidelines:

A complex formula is any formula that meets any of these requirements:

Complex formulas create more calculation overhead and therefore slow performance when applied to sparse dimension members. This is true because the presence of complex formulas requires Essbase to perform calculations on possible data blocks related to the member with the complex formula (not just the existing blocks). The presence of relationship or financial functions on a sparse dimension member causes Essbase to perform calculations on all possible blocks with any sparse member combinations, increasing the overhead even more.

For more information about how complex formulas affect calculation performance, see Understanding Bottom-Up and Top-Down Calculation.

Complex Formula Performance Example

When a complex formula, relationship function or financial function is attached to a sparse dimension member, the calculation overhead increases the higher the ratio of existing data blocks to possible data blocks, and therefore the slower the performance.

Two examples illustrate complex formula overhead:

In all cases, the lower the ratio of existing data blocks to possible data blocks, the higher the calculation performance overhead.

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 Forcing a Bottom-Up Calculation.

Forcing a bottom-up calculation on a top-down formula enables efficient use of the CALC ALL and CALC DIM commands. For more information, review the discussions of the SET FRMLBOTTOMUP calculation command and the CALCOPTFRMLBOTTOMUP configuration setting in the Technical Reference in the docs directory.

Improving Performance for Constants in a Sparse Dimension

If you assign a constant to a member in a sparse dimension, 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 calculation 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. Essbase automatically creates all possible data blocks for California and assigns the value 120 to all data cells. Many thousands of data blocks may be created. To improve performance, create a formula that doesn't create unnecessary values.

To assign constants in a sparse dimension to only those intersections that require a value, use FIX in a manner similar to this example:

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

This example assigns the value 120 to any intersection of California (in the Market dimension), Actual (in the Scenario dimension), Misc (in the Measures dimension), Colas (in the Product dimension), and any member in the Year dimension, because 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. Essbase creates new data blocks for all combinations of the sparse members, California and Colas. Within the new blocks, Essbase sets Measures and Scenario values (other than those assigned the value 120) to #MISSING.

For more information about FIX, see the Technical Reference in the docs directory.

Using Caution with a Cross-Dimensional Operator (->)

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

On the Left Side of an Equation

Replace formulas that use a cross-dimensional operator on the left side of an equation with a formula that uses FIX in a calculation script instead for faster performance.

For example, assume you want to increase the Jan -> Sales values by 5% in Sample Basic.To improve performance by calculating only the relevant combinations of members, use the FIX command in a calculation script:

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

With the FIX command instead of a cross-dimensional operator, Essbase calculates the formula only for the specified member combinations, in this example, Jan.

Compare this technique to using the slower cross-dimensional operator approach. For the previous example, you would place this formula on the Sales member in the database outline:

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

As Essbase cycles through the database, it calculates the formula for every combination of members.Thus Essbase calculates the formula for every member in the dimension tagged as time (Jan, Feb, Mar, etc.), even though only January members need to be calculated.

For more information on calculation scripts and the FIX command, see Developing Calculation Scripts and the 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, Essbase does not automatically create the required blocks if both of these conditions apply:

When the blocks are not created automatically, performance is slower than necessary. You can prevent this performance issue by using a DATACOPY command in the calculation script that contains the relevant equation.

For example, assume this equation must be applied to a member in a dense dimension:

result = member -> operand

"result" is the member of a dense dimension and "operand" is the member of a sparse dimension.

Now assume you need to apply this equation in Sample Basic, 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.

Create a calculation script for the above formulas using 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

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

Compare this calculation script to the solution which has the slowest performance:

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

The calculation 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).

Other solutions

If you cannot use DATACOPY, you can avoid it without slowing performance 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 

Using Bottom-Up 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.

Use these sections to understand bottom-up and top-down calculation, and then find instructions for forcing a bottom-up calculation only if it is appropriate for your site:

Understanding Bottom-Up and Top-Down Calculation

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, Essbase does a bottom-up calculation of a database. However, if the database outline contains a complex member formula, Essbase performs a top-down calculation for that member.

Use this information to learn more about simple and complex formula interactions with bottom-up and top-down calculation:

Bottom-Up Calculations and Simple Formulas

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

For simple formulas, Essbase does a bottom-up calculation to determine which blocks need to be calculated prior to running the full 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.

Top-Down Calculations and Complex Formulas

Before starting a calculation, 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 Essbase reaches a member with a top-down formula, it does a top-down calculation for that member.

When a formula on a member is complex, all possible blocks for the 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 blocks may have on other blocks prior to the start of the calculation. The top-down method slows down calculation performance because Essbase must search for the appropriate blocks to calculate in order to execute the formula.

When a formula is compiled, if the formula is to be calculated top-down, Essbase logs a message in the application log file.

For more information about complex formulas, see Complex Formulas.

A Complex Formula Example

Consider this complex formula:

A = B -> D + C -> D

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, so Essbase uses a top-down calculation by default.

Forcing a Bottom-Up Calculation

If it is appropriate for your site, you can force a bottom-up calculation on a top-down formula. Use any of these methods to force a bottom-up calculation:

Tool
Instructions
For More Information

Calculation Function

@CALCMODE in a formula

Technical Reference in the docs directory

Calculation Script Command

SET FRMLBOTTOMUP

essbase.cfg file setting

CALCOPTFRMLBOTTOMUP

or

CALCMODE



Forcing a bottom-up calculation on a formula, which ordinarily increases performance over a top-down calculation, may produce results that are inconsistent with a top-down calculation if the formula contains complex functions (for example, range functions), or if the formula's dependencies are not straightforward.

Caution: Before changing the setting CALCOPTFRMLBOTTOMUP or using the calculation script command SET FRMLBOTTOMUP in a production environment, check the validity of calculation results by comparing results to a top-down calculation of the same data.

Designing Calculation Scripts for Performance

You may achieve significant improvements in calculation performance by carefully grouping formulas and dimensions in a calculation script. In this way, you can ensure that Essbase cycles through the data blocks in the database as few times as possible during a calculation.

Order commands in your calculation scripts to make the database calculation as simple as possible. Consider applying all formulas to the database outline and using a default calculation (CALC ALL). This method may improve calculation performance.

For more information about developing calculation scripts, see Developing Calculation Scripts. For more information about calculation passes, see Calculation Passes

Managing Caches to Improve Performance

When calculating the database, Essbase uses approximately 30 bytes of memory per member in the database outline. So if the database has 5,000 members, 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 calculation scripts concurrently, Essbase requires 60 bytes: 30 bytes per member, per script. Concurrent calculations do share the caches.

Note: You can avoid the excess memory use by combining calculation scripts, and get good performance by using parallel calculation with the single calculation script. For more information about parallel calculation, see Using Parallel Calculation.

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 Essbase to optimize the calculation.

Essbase uses four memory caches to coordinate memory usage:

Note: 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 Essbase to use the optimal calculator cache option.

For information about sizing caches, see Sizing Caches. Make sure you read the entire chapter before making any changes.

Locking Blocks During Calculation

When a block is calculated, Essbase locks the block and all blocks that contain its children. Essbase calculates the block and then releases both the block and the blocks containing its children.

By default, Essbase locks 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, Essbase works most efficiently if it can lock 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 Essbase can lock all required blocks and therefore performance is not impaired.

Essbase locking behavior depends on the isolation level setting. For more information, see Isolation Levels.

Note: For aggregations in a sparse dimension, block locking is not a consideration because Essbase does not need to lock all blocks containing the children concurrently.

How to Change Locking for Performance

You can use the SET LOCKBLOCK command in a calculation script along with the CALCLOCKBLOCK setting in the essbase.cfg file to specify the maximum number of blocks that Essbase can lock concurrently when calculating a block. Do this if the default 100 blocks is not sufficient during calculation, or your calculation may be slower.

For more information, see the Technical Reference in the docs directory.

Multiple Users and Locking

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

When Essbase calculates a data block, it creates 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 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:

Note: You can view the isolation level settings in the Application Manager by selecting Database > Settings from the main menu when the server containing the application is active.

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 Essbase security options to do either of the following:

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

Note: When Essbase locks a 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

You can improve performance significantly by tagging an accounts dimension member as two-pass in the database outline, if it is appropriate for your application. Your combination of data and calculation needs may require the use a calculation script to calculate a formula twice, instead of two-pass tagging to preserve accuracy.

Use these sections to understand more about two-pass calculation, and decide whether you can tag an accounts dimension member as two-pass to improve performance or whether you must use a calculation script to calculate a formula twice. This section also provides information about how to enable two-pass calculation or create a calculation script for two-pass calculation:

For information about the interaction of two-pass calculation and attribute members, see Table 14.

Understanding Two-Pass Calculation

You can use a two-pass calculation on member formulas that need to be calculated twice to produce the correct value.

Whenever possible, Essbase calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Essbase does not need to do an extra calculation pass through the database. However, in some situations, Essbase needs an extra calculation pass through the database.

How 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 the time and account dimensions.

A Two-Pass Calculation Example

For example, consider this calculation required for Profit%:

Profit % = Profit % Sales 

Assume that 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. 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

240

6

Profit%

1

2

3

0%

4125% 7



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

Essbase uses this calculation order:

  1. Essbase calculates the formula Profit % Sales for Profit % -> Jan, Profit % -> Feb, Profit % -> Mar, and Profit % -> Qtr1 (1, 2, 3, 4 above).
  2. Essbase calculates Profit -> Qtr1 and Sales -> Qtr1 by adding the values for Jan, Feb, and Mar (5, 6 above).
  3. Essbase calculates Profit % -> Qtr1 by adding the values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar (7 above). This addition of percentages produces the value %125, not the correct result.
  4. If you tag Profit % as two-pass in the database outline, 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 (5)

Sales

150

200

240

590 (6)

Profit%

50% (1)

25% (2)

50% (3)

0% (4)

125% (7)

42% (8)



For more information about calculation passes, see Calculation Passes.

Understanding Two-Pass Calculation and Intelligent Calculation

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

These scenarios require that you understand the concepts of Intelligent Calculation. For more information, see Optimizing with Intelligent Calculation.

Scenario A

Scenario A demonstrates two key approaches:

In this scenario, you place formulas in the outline and then, as appropriate, tag specific formulas as two-pass for best performance.

No Extra Calculation Pass For Two-pass Formulas

Essbase calculates the two-pass formulas while it is calculating the data block. Thus, 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 Optimizing with Intelligent Calculation.

When you tag a member formula as two-pass in the outline, Essbase does the two-pass calculation while each data block is being calculated. However, when you repeat a formula in a calculation script, Essbase has to read the data blocks and write them to memory in order to recalculate the formula.

Scenario B

Scenario B illustrates two key approaches:

In this scenario, you create a calculation script to perform the formula calculation for best performance.

Extra Calculation Pass For Two-pass Formulas

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, Essbase ignores the clean status on the blocks that are relevant to the two-pass formula and recalculates these blocks.

Data Blocks For Two-pass Formulas Not Marked As Clean

After the first calculation, Essbase has marked all the data blocks as clean for the purposes of Intelligent Calculation. In a second calculation pass through the database, Essbase recalculates the required data blocks for the two-pass formulas. However, because the second calculation is a partial calculation of the database, 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 the database configuration allows Essbase to use Scenario B, consider using a calculation script to perform two-pass formula calculations. If you use a calculation script, Essbase still does an extra calculation pass through the database; however, you can ensure that Essbase has marked all the data blocks as clean after the calculation. For more information, see Creating a Calculation Scripts for Two-Pass and Intelligent Calculation.

Choosing Two-Pass Calculation Tag or Calculation Script

Even though tagging an accounts member as two-pass may bring performance benefits, some applications cannot use this method. Check these qualifications to see whether you should apply a two-pass tag or create a calculation script that performs a calculation twice for best performance and accuracy:

Enabling Two-Pass on a Default Calculation

When you perform a default calculation on a database with two-pass calculation enabled (the default), 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 calculation script.

You can perform a default calculation using any of these methods:

Tool
Instructions
For More Information

Application Manager

Select Database > Calculate, and choose Default

Application Manager Help topic: Main Menu > Database Settings > Calculate

MaxL

execute calculation

Technical Reference in the docs directory

ESSCMD

CALCDEFAULT

Technical Reference in the docs directory



To enable two-pass calculation, use any of these methods:

Tool
Instructions
For More Information

Application Manager

Select Database > Settings, General tab, and select Two-Pass Calculation

Essbase Application Manager Online Help topic: Main Menu > Database Settings > General

Administration Services

Database Properties window > General tab.

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference in the docs directory

ESSCMD

SETDBSTATE

Technical Reference in the docs directory



Creating a Calculation Scripts for Two-Pass and Intelligent Calculation

Use these methods to create calculation scripts to perform two-pass calculations with Intelligent Calculation, so that your calculation is accurate and as fast as possible:

To obtain the performance benefits of Intelligent Calculation when performing the first, full calculation of the database, use one of these methods, depending on your calculation needs and outline structure:

These three options all use the following example situation:

The outline has a dimension tagged as accounts, and it is a dense dimension. You want to calculate sales for each product as a percentage of sales for all products. Assume this formula should calculate the dimension:

Sales % Sales -> Product 

When 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.

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 for the best performance:

All three of these options perform the same tasks:

  1. Enable Intelligent Calculation.
  2. Calculate the full database and marks the data blocks as clean.
  3. Disable Intelligent Calculation.
  4. Mark the recalculated blocks as clean, even though this calculation is a partial calculation of the database. If you do not use the command SET CLEARUPDATESTATUS AFTER, Essbase marks data blocks as clean only after a full calculation of the database.
  5. Essbase cycles through the database calculating only the formula for the relevant member (Share of Sales in our example), or calculating all formulas tagged as two-pass in the database outline.

Use a Calculation Script

Use this model to create a calculation script that performs a full calculation of the database with Intelligent Calculation enabled:

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

Use a Calculation Script and the Two-Pass Tag

Use this procedure to tag a member as two-pass, and use a calculation script to calculate first the full database, then the two-pass member:

  1. Place a formula in the database outline and tag it as two-pass.
  2. Place the formula on the appropriate member in the dimension tagged as accounts, in our example, Share of Sales.
  3. Create a calculation script that performs a full database calculation and then a two-pass calculation:
  4. SET UPDATECALC ON;
    CALC ALL;
    SET UPDATECALC OFF;
    SET CLEARUPDATESTATUS AFTER;
    CALC TWOPASS; 
    

Use a Client and a Calculation Script

Use this procedure to perform a default calculation from a client and then use a calculation script to perform the formula calculation:

  1. Enable Intelligent Calculation if this default has been changed.
  2. Perform a full calculation, using any of the clients listed in Table 90.
  3. Use a calculation script similar to this example to disable Intelligent Calculation and calculate the formula:
  4. SET UPDATECALC OFF;
    SET CLEARUPDATESTATUS AFTER;
    "Share of Sales" = Sales % Sales -> Product; 
    

    or:

    SET UPDATECALC OFF; SET CLEARUPDATESTATUS AFTER; CALC TWOPASS;

Table 90: Methods for Performing a Full Calculation

Tool
Instructions
For More Information

Administration Services

Calculate Database dialog box

Essbase Administration Services Online Help

MaxL

execute calculation

Technical Reference in the docs directory

ESSCMD

CALCDEFAULT

Technical Reference in the docs directory

Application Manager

Select Database > Calculate, and choose Default, or your customized default calculation script.

Application Manager help topic: Main Menu > Database > Calculate



For more information on Intelligent Calculation, see Optimizing with Intelligent Calculation.

For more information on developing formulas and calculation scripts, see Developing Formulas, and Developing Calculation Scripts.

Intelligent Calculation with a Small Index

If the index is small and you want the benefit of using Intelligent Calculation, use this procedure:

  1. Create a calculation script to calculate the database, but tell Essbase not to mark the calculated data blocks as clean
  2. Mark all data blocks as clean, and do not recalculate the data blocks.
  3. SET CLEARUPDATESTATUS OFF;
    CALC ALL;
    CALC TWOPASS;
    SET CLEARUPDATESTATUS ONLY;
    CALC ALL; 
    

With the example script, Essbase performs these tasks:

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

Turning Off Intelligent Calculation for a Two-Pass Formula

Create a calculation script that performs these tasks:

  1. Disables Intelligent Calculation.
  2. Performs a full calculation.
  3. Repeats the two-pass formula:
  4. SET UPDATECALC OFF;
    CALC ALL;
    "Share of Sales" = Sales % Sales -> Product; 
    

Choosing Between Member Set Functions and Performance

Queries and calculations which reference a member that has been tagged as Dynamic Calc or Dynamic Calc and Store may be significantly slower than queries and calculations involving the same members, if the member has formulas involving any of these functions:

If you are experiencing slow performance, you may wish to either remove the dynamic calculation tag or remove these functions from the attached formula.

Aggregating #MISSING Values

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

Understanding #MISSING calculation

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

Table 91: How 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:
Y AND #MISSING, where Y
represents any nonzero value

#MISSING

0 AND #MISSING
#MISSING
AND #MISSING

0
#MISSING

X OR #MISSING:
Y OR #MISSING, where Y
represents any nonzero value
0 OR #MISSING
#MISSING
OR #MISSING


1

#MISSING
#MISSING

IF (#MISSING)

IF (0)

f (#MISSING)

#MISSING for any Essbase function of one variable

f (X)

#MISSING for any X not in the domain of f and any Essbase function of more than one variable (except where specifically noted)



By default, Essbase does not aggregate #MISSING values.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.

Caution: The default, not aggregating #MISSING values must be in effect if you load data at parent, rather than child, levels.if any child member combinations have #MISSING values. If all child member combinations have any other values, including zero (0), then Essbase aggregates the child values and overwrites the parent values correctly, so you can safely change the default.

Changing Aggregation for Performance

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.

You can change the way Essbase aggregates #MISSING values using any of these methods:

Tool
Instructions
For More Information

Administration Services

Database Properties window > General tab

Essbase Administration Services Online Help

Calculation Script

Use SET AGGMISSG

Technical Reference in the docs directory

MaxL

alter database

Technical Reference in the docs directory

ESSCMD

SETDBSTATEITEM

Technical Reference in the docs directory

Application Manager

Database > Settings, then check Aggregate Missing Values

Essbase Application Manager Online Help topic:



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:

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.

For more information on setting the behavior for aggregating #MISSING values, see Aggregating #MISSING Values, Defining the Calculation Order, and the Technical Reference in the docs directory.

Removing #MISSSING Blocks

CLEARDATA changes the value of cells in a block to #MISSING. It does not remove the data blocks. These extra blocks can slow performance.

If the #MISSING blocks are slowing performance, perform either of these tasks:

Identifying Other Calculation Optimization Issues

This map identifies the location in other sections that discuss the relationship between calculation and performance:




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com