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

Optimizing Calculations


This chapter provides information on how to optimize the performance of Analytic Services calculations in block storage databases:

In addition, the information provided in the following chapters will help you in your efforts to optimize database calculations:

Designing for Calculation Performance

You can configure a database to optimize calculation performance.

The best configuration for the site depends on the nature and size of the database. Use the information in the following topics 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 Analytic Services to write to and retrieve the index from disk. This process slows down calculation.

If data blocks are much larger than 100Kb, Intelligent Calculation does not work effectively. For a comprehensive discussion of how intelligent calculation aids performance, 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.

To view data block information, use either of the following methods:


Tool
Topic
Location

Administration Services

Checking Data Block Statistics

Essbase Administration Services Online Help

ESSCMD

GETDBINFO

Technical Reference



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, placing the dimension that contains the fewest members first. This arrangement provides a number of possible improvements, depending on the site:

Incremental Data Loading

Many companies 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, the database contains a data block for each time period. When you load data by time period, Analytic Services 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, Analytic Services recalculates only the data blocks for March and the dependent parents of March.

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, Analytic Services recalculates only the blocks affected by the partial load, even though time is dense and Intelligent Calculation is enabled.

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 adding intermediate levels to the database outline.

Formulas and Calculation Scripts

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 Analytic Services cycles through the data blocks in the database as few times as possible during a calculation.

Order commands in 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 detailed information about developing calculation scripts, see Developing Calculation Scripts. For detailed information about multiple calculation passes, see Calculation Passes.

Monitoring and Tracing Calculations

You can display information in the application log about how Analytic Services 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 Analytic Services 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.

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.

Using Simulated Calculations to Estimate Calculation Time

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 that is 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 will take, because the time a simulated calculation takes to run is proportional to the time that the actual calculation takes to run.

For example, if the calculation starts at 9:50:00 AM and the first notice is time-stamped at 09:50:10 AM, and the second is time-stamped at 09:50:20 AM, you know that each of part of the calculation took ten seconds. If you then run a real calculation on only the first portion and note that it took 30 seconds to run, you know that the other portion will also take 30 seconds. If there were only two messages total, then you would know that the real calculation will take approximately 60 seconds (20 /10 * 30 = 60 seconds).

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

Use the following topics to learn how to perform a simulated calculation and how to use a simulated calculation to estimate calculation time:

Performing a Simulated Calculation

Before you can estimate calculation time, you must perform a simulated calculation on a data model that is based on your actual database.

To perform a simulated calculation, use this procedure:

  1. Create a data model that uses all dimensions and all levels of detail about which you want information.

  2. Load all data. This procedure calculates only data loaded in the database.

  3. Create a calculation script with these entries:

    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, Analytic Services reports an error. You can disregard the error.

  4. Run the script.

  5. Find the first sparse calculation message in the application log and note the time in the message.

  6. Note the time for each subsequent message.

  7. Calculate the dense dimensions of the model that are not being dynamically calculated:
    CALC DIM (DENSE_DIM1, DENSE_DIM2, ...); 
    

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

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

Estimating Calculation Time

After you perform a simulated calculation, you record the results and use them to estimate actual calculation time.

To estimate the total time required for a calculation, use the following process:

  1. Note the times of all the intervals between application log messages generated by SET NOTICE HIGH. See Table 82 for an example.

  2. Use the following calculation to estimate the time for a real calculation:

    Total time required for simulated calculation, divided by the first simulated calculation notice interval, multiplied by the first real calculation time interval.


    Table 82: 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 calculation time

    43 seconds

     



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

Factors Affecting Estimate Accuracy

The simulated calculation should return a time accurate to about 5%, excluding the following issues:

When these factors are present, this estimating technique more closely predicts calculation time when Analytic Services reaches about 30 to 40 percent of the simulated calculations (30 to 40 percent of the messages generated by SET NOTICE HIGH).

For more information about the SET NOTICE calculation command and the related CALCNOTICE configuration setting, see the Technical Reference.

Variations Due to a Chain of Influences

Using SET MSG ONLY as a calculation-time estimating technique should be validated against later CALCNOTICE intervals. The results of this estimating technique vary because of the following chain of influences:

  1. Blocks differ in block density through the real consolidation process, therefore

  2. The rate at which Analytic Services writes blocks to the disk differs, therefore

  3. The rate at which blocks are processed in the cache differs, therefore

  4. Actual results may differ from the predicted calculation time.

Variations Due to Outline Structure

Another factor that can make actual results diverge significantly from predicted is the outline structure. Calculations based on CALCNOTICE intervals assume evenly balanced processing time throughout the outline. Factors that can skew this balance include the following situations:

Changing the Outline Based on Results

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

From top to bottom in the 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 members is not always a good predictor.

Estimating Calculation Affects on Database Size

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 database size resulting from a calculation, use the following process (example uses interactive mode):

  1. Load data and issue a CALC ALL command and note the average block size.

  2. Start the MaxL shell, log into Analytic Services, and start an application and database.
    essmsh
    login username password;
    alter system load application appname;
    alter application appname load database dbname;
    

  3. Providing the application and database name, enter the following MaxL statement and note the value that is returned for the number of blocks.
    query database application.dbname get estimated size;
    

  4. Multiply the number of blocks by the average size of the blocks in the database.

    Results are accurate to a precision of plus or minus 10%.

Be aware of the following conditions when you query Analytic Services for an estimate of the full size of a database:

Using Parallel Calculation

This topic discusses parallel calculation and enables you to decide whether parallel calculation improves performance for your site. This topic also outlines the process for enabling parallel calculation:

Parallel Calculation

Analytic Services provides two ways of invoking a calculation:

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

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

See Enabling Parallel Calculation for detailed instructions.

The following topics discuss the details of parallel calculation:

Analytic Services Analysis of Feasibility

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

Analytic Services analyzes the 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 a comprehensive discussion of calculation passes, see Calculation Passes.

If Analytic Services determines that parallel calculation is possible, Analytic Services splits the calculation into smaller tasks that are independent of each other. During the calculation, Analytic Services performs the smaller tasks simultaneously.

However, Analytic Services uses serial calculation even if parallel calculation is enabled if there are complex interdependencies between formulas that participate in the pass. Such interdependencies render parallel calculation impossible.

Parallel Calculation Guidelines

Outline structure and application design determine whether enabling parallel calculation can improve calculation performance. Before you enable parallel calculation, review the following guidelines. If you do not adhere to the guidelines, you may not receive the full benefit of parallel calculation:

Relationship Between Parallel Calculation and Other Analytic Services Features

The following topics discuss the relationship between parallel calculation and other Analytic Services functionality:

Retrieval Performance

Placing the largest sparse dimension at the end of the outline for maximum parallel calculation performance may slow retrieval performance. See Optimizing Query Performance for outline design guidelines for optimizing retrieval.

Formula Limitations

The presence of some formulas may force serial calculation. The following 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 member of the formula 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 about the relevant error messages.

Calculator Cache

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

For example, assume Essbase performs a serial calculation and uses multiple bitmaps and a single anchoring dimension. Without explicit change of the calculator cache size, Analytic Services might perform a parallel calculation might using only a single bitmap and a single anchoring dimension.

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

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

When using parallel calculation in multiple bitmap mode, you may encounter high memory usage. If you encounter this situation, you can use the configuration setting PARCALCMULTIPLEBITMAPMEMOPT to optimize memory use in multiple bitmap mode. This setting can be used together with, or separately from, MULTIPLEBITMAPMEMCHECK. To enable PARCALCMULTIPLEBITMAPMEMOPT, add this line to your essbase.cfg file:

PARCALCMULTIPLEBITMAPMEMOPT TRUE 
 

For a comprehensive discussion of calculator cache and calculator cache bitmaps, see Sizing the Calculator Cache.

Transparent Partition Limitations

Parallel calculation with transparent partitions has the following limitations:

Restructuring Limitation

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, Analytic Services uses the setting value.

Analytic Services 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 the commit threshold value, that is, the number of blocks before a commit, to a very large number for better performance.

To view the current threshold, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Data Integrity Options

Essbase Administration Services Online Help

MaxL

display database dbs_name

Technical Reference

ESSCMD

GETDBINFO: Number of blocks modified before internal commit

Technical Reference



To modify the commit threshold, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Data Integrity Options

Essbase Administration Services Online Help

MaxL

alter database dbs_name set implicit_commit after n blocks

Technical Reference, list of MaxL statements

ESSCMD

SETDBSTATEITEM 21

Example of Specifying Isolation Level Settings with ESSCMD



For a discussion of commit thresholds, see Uncommitted Access.

Isolation Level Limitation

You must use uncommitted mode for parallel calculation.

To set the isolation level to uncommitted mode, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Data Integrity Options

Essbase Administration Services Online Help

MaxL

alter database dbs_name disable committed_mode

Technical Reference, list of MaxL statements

ESSCMD

SETDBSTATEITEM 18

Example of Specifying Isolation Level Settings with ESSCMD



See Uncommitted Access for a description of how uncommitted mode works.

Checking Current Parallel Calculation Settings

You can check either the server configuration file or the calculation script that 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 essbase.cfg file with a text editor.

  2. Search for the parameter CALCPARALLEL, and check its specified value.

    The number of threads that can simultaneously perform tasks to complete a calculation is specified by the value 1-4. See the Technical Reference 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 all applications and databases on the server. You can disable parallel calculation for individual applications or databases by setting parallel calculation at the server level in the configuration file and then adding application-specific or database-specific entries in a calculation script.

Caution: Be sure to read all of this chapter before attempting to enable parallel calculation.

To enable parallel calculation, use the following process:

  1. If you plan to enable parallel calculation in the configuration file, check the current status to see if an entry already exists; use the process described in Checking Current Parallel Calculation Settings.

  2. Add or modify CALCPARALLEL to the essbase.cfg file on the server, or add SET CALCPARALLEL to a calculation script.

  3. If needed, enable Analytic Services to use more than the one sparse dimension to identify tasks for parallel calculation; use the process described in Identifying Additional Tasks for Parallel Calculation.

  4. If you added entries to the configuration file, restart the server.

  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 the site requires it. For example, you can set CALCPARALLEL as off at the server level, and use a calculation script to enable and disable parallel calculation as often as needed.

Identifying Additional Tasks for Parallel Calculation

By default, Analytic Services 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 situation can lead to uneven load balancing, thus reducing the effectiveness of parallel calculation.

To resolve this situation, you can enable Analytic Services to use additional sparse dimensions in the identification of tasks for parallel calculation. For example, if you have a FIX statement on a member of the last sparse dimension, you can include the next-to-last sparse dimension from the outline as well. Because each unique member combination of these two dimensions is identified as a potential task, more and smaller tasks are 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 the following process:

  1. If you are not sure, verify if parallel calculation is already enabled. See Checking Current Parallel Calculation Settings for instructions. Without CALCPARALLEL (or SET CALCPARALLEL in a calculation script), CALTASKDIMS has no effect.

  2. Add or modify CALCTASKDIMS in the essbase.cfg file on the server, or use the calculation script command SET CALCTASKDIMS at the top of the script. See the Technical Reference for instructions.

  3. If you add or modify CALCTASKDIMS in the essbase.cfg file on the server, restart Analytic Services.

  4. If you are using a calculation script, run the script.

Note: In some cases, Analytic Services uses a lower number of dimensions to identify tasks than is specified by CALCTASKDIMS or SET CALCTASKDIMS. See the Technical Reference for a detailed explanation.

Monitoring Parallel Calculation

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

To view the application log, see "Viewing Logs" in Essbase Administration Services Online Help.

For each calculation pass, Analytic Services 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 a comprehensive discussion of how to develop formulas, see Developing Formulas.

For discussion of how to handle formula issues that affect performance, see the following topics:

Consolidating

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

Figure 244: Consolidation on Sample Basic Outline

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

100-10 + 100-20 + 100-30 
 

Using Simple Formulas

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

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

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

Using Complex Formulas

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

A complex formula is a formula that meets any of the following requirements:

When applied to sparse dimension members, complex formulas create more calculation overhead and therefore slow performance. This problem occurs because the presence of complex formulas requires Essbase to perform calculations on all possible as well as all existing data blocks related to the member with the complex formula. The presence of a relationship or financial function on a sparse dimension member causes Essbase to perform calculations on all blocks, possible as well as existing, increasing the overhead even more.

Thus, a complex formula that includes a relationship or financial function creates a greater overhead increase than does a complex formula that does not include a relationship or financial function.

For a comprehensive discussion about how complex formulas affect calculation performance, see Bottom-Up and Top-Down Calculation.

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 and the slower the performance.

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 a review of methods and a caution, 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 technical details, review the discussions of the SET FRMLBOTTOMUP calculation command and the CALCOPTFRMLBOTTOMUP configuration setting in the Technical Reference.

Constant Values Assigned to Members in a Sparse Dimension

If you assign a constant to a member in a sparse dimension, Analytic Services 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. Analytic Services 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 does not 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 the following example:

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

In this example, Colas is a member of the sparse dimension, Product; Actual is a member of the dense dimension, Scenario; and Misc is a member of the dense dimension, Measures. The value 120 is assigned 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.

Because Sample Basic includes only two sparse dimensions, this example affects only one block. If there were additional sparse dimensions, Essbase would ensure that there are data blocks for all combinations of the sparse dimensions with California and Colas, creating new blocks if they do not exist. Within the new blocks, Analytic Services sets Measures and Scenario values (other than those assigned the value 120) to #MISSING.

Non-Constant Values Assigned to Members in a Sparse Dimension

If you assign non-constant values to members of a sparse dimension, new blocks are created based on the Create Blocks on Equations setting. The Create Blocks on Equations setting is defined at the database level, as a database property. Within calculation scripts you can temporarily override the Create Blocks on Equations setting. (For basic information about this setting, see Non-Constant Values.) Consider the effects of the following calculation when West has no value and the Create Blocks on Equations setting is ON.

West = California + 120; 
 

Unneeded blocks may be created for all sparse-member intersections with West, even if the corresponding block value is #MISSING for all of the children of West. Especially in a large database, creation and processing of unneeded blocks requires additional processing time.

To control creation of blocks when you assign non-constant values to members of a sparse dimension, use the SET CREATEBLOCKONEQ ON|OFF command. The following script includes calculations with this setting off and on.

FIX (Colas);
SET CREATEBLOCKONEQ OFF
West = California + 120;					 
SET CREATEBLOCKONEQ ON
East = "New York" + 100;					 
ENDFIX 
 

Because the Create Block on Equation setting is disabled at the beginning, West blocks are created only when values exist for the children of West. Later, because the Create Block on Equation setting is enabled, all blocks for East are created.

Note: Use of SET CREATEBLOCKONEQ affects only creation of blocks during the execution of the calculation script that contains this command. This command does not change the overall database Create Blocks on Equations setting.

For details regarding use of SET CREATEBLOCKEQ ON|OFF in calculation scripts, see the Technical Reference.

Using Cross-Dimensional Operators (->)

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

On the Left Side of an Equation

For faster calculation script performance, use FIX in the calculation script to qualify the use of a formula instead of using a formula that includes a cross-dimensional operator on the left side of an equation.

For example, assume that you want to increase the Jan -> Sales values in Sample Basic by 5%. 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, Analytic Services calculates the formula only for specified member combinations, in this example, for combinations that include Jan.

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

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

As Analytic Services cycles through the database, it calculates the formula for every member combination that includes a member from the dimension tagged as time (Jan, Feb, Mar, etc.), even though only January combinations need to be calculated.

For detailed information on calculation scripts and the FIX command, see Using the FIX Command and the Technical Reference.

In Equations in a Dense Dimension

When you use a cross-dimensional operator in an equation in a dense dimension, Analytic Services does not automatically create the required blocks if both of these conditions apply:

You can use the following techniques to create the blocks and avoid the performance issue.

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 the following topics to determine whether bottom-up and top-down calculation is more appropriate for a particular situation:

Bottom-Up and Top-Down Calculation

Analytic Services uses one of two calculation methods to do a full calculation of a database outline-bottom-up calculation or top-down calculation. By default, Analytic Services does a bottom-up calculation of a database.

For a bottom-up calculation, Analytic Services determines which data blocks need to be calculated before it calculates the database. Analytic Services 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 existing block with the highest block number is reached. For a detailed explanation of block calculation order, see Block Calculation Order.

If the database outline contains a complex member formula, Analytic Services performs a top-down calculation for the relevant member.

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

Bottom-Up Calculations and Simple Formulas

For simple formulas, Analytic Services 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, Analytic Services searches the database outline and marks complex formulas that require top-down calculation; for example, a member formula that contains a cross-dimensional reference. When Analytic Services reaches a member with a top-down formula, it does a top-down calculation for the 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 that blocks may have on other blocks prior to the start of the calculation. The top-down method slows calculation performance because Analytic Services must search for appropriate blocks to calculate in order to execute the formula.

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

Consider the following complex formula:

A = B -> D + C -> D 
 

To calculate the formula, Analytic Services must examine every possible combination of A to see whether B -> D or C -> D exists.

For descriptions and examples of complex formulas, see Using Complex Formulas.

Forcing a Bottom-Up Calculation

If it is appropriate for the site, you can force a bottom-up calculation on a top-down formula.

To force a bottom-up calculation, use any of the following methods:


Method
Topic Where Discussed
Location

Calculation function

@CALCMODE in a formula

Technical Reference

Calculation script command

SET FRMLBOTTOMUP

Technical Reference

essbase.cfg file setting

CALCOPTFRMLBOTTOMUP

or

CALCMODE

Technical Reference



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

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, relative to the same data, the results of a bottom-up calculation and the results of a top-down calculation.

Managing Caches to Improve Performance

The following section describes the caches that are used with block storage databases. For information about the aggregate storage cache, see Managing the Aggregate Storage Cache.

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

Note: When you run concurrent calculations, each calculation uses separate memory space. For example, if you are running two calculation scripts concurrently, Analytic Services requires 60 bytes per member: 30 bytes per member per script. Concurrent calculations use the same memory caches. You can avoid excess memory use by combining calculation scripts. You can obtain good performance by using parallel calculation with a single calculation script. For a comprehensive discussion of parallel calculation, see Using Parallel Calculation.

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

Analytic Services uses memory caches to coordinate memory usage:

Note: When you calculate a 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 a comprehensive discussion of cache sizing, see Sizing Caches. Make sure that you read the entire topic before making any changes.

Working with the Block Locking System

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

By default, Analytic Services locks up to 100 blocks concurrently when calculating a block. This number of block locks is sufficient for most database calculations. If you are calculating a formula in a sparse dimension, Analytic Services works most efficiently if it can lock all required child blocks concurrently. Therefore, when calculating a formula in a sparse dimension, you may want to set a lock 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 Analytic Services can lock all required blocks, and therefore, performance is not impaired.

Analytic Services locking behavior depends on the isolation level setting. For a detailed explanation of locking behavior, see Locking Under Committed Access and Locking Under Uncommitted Access.

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

Using SET LOCKBLOCK and CALCLOCKBLOCK

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 Analytic Services can lock concurrently when calculating a block. If you do not modify the default setting and the default 100 blocks is not sufficient during calculation, the calculation may be require more time than expected.

Managing Concurrent Access for Users

Analytic Services 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 Analytic Services handles locking blocks and committing data depends on the isolation level setting.

When Analytic Services 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 Analytic Services 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:

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

For detailed information about and instructions for the security options, see Disconnecting Users and Terminating Requests and Managing Passwords and User Names.

For information on how Analytic Services handles locks and transactions, see Understanding How Analytic Services Handles Transactions and Data Locks.

Note: When Analytic Services 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 the application. The combination of data and calculation needs may require the use of 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 12.

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, Analytic Services calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Analytic Services does not need to do an extra calculation pass through the database. However, in some situations, Analytic Services needs an extra calculation pass through the database.

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

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

4/ 7



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

Analytic Services uses this calculation order:

  1. Analytic Services calculates the formula Profit % Sales for Profit % -> Jan, Profit % -> Feb, Profit % -> Mar, and Profit % -> Qtr1 (1, 2, 3, 4 above).

  2. Analytic Services calculates Profit -> Qtr1 and Sales -> Qtr1 by adding the values for Jan, Feb, and Mar (5, 6 above).

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


    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)



  4. If you tag Profit % as two-pass in the database outline, Analytic Services 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 detailed information about multiple calculation passes, see Calculation Passes.

Understanding the Interaction of 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 the database; each scenario tells you how to ensure that Analytic Services calculates two-pass formulas accurately.

These scenarios require that you understand the concepts of Intelligent Calculation. For a comprehensive discussion, 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

Analytic Services calculates the two-pass formulas while it is calculating the data block. Thus, Analytic Services 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.

When you tag a member formula as two-pass in the outline, Analytic Services does the two-pass calculation while each data block is being calculated. However, when you repeat a formula in a calculation script, Analytic Services 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

Analytic Services 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, Analytic Services 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, Analytic Services has marked all the data blocks as clean for the purposes of Intelligent Calculation. In a second calculation pass through the database, Analytic Services recalculates the required data blocks for the two-pass formulas. However, because the second calculation is a partial calculation of the database, Analytic Services 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 Analytic Services to use Scenario B, consider using a calculation script to perform two-pass formula calculations. If you use a calculation script, Analytic Services still does an extra calculation pass through the database; however, you can ensure that Analytic Services has marked all the data blocks as clean after the calculation. For a review of methods, see Creating 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 Default Calculations

A database setting enables two-pass calculation in default calculations. When you perform a default calculation on a database with two-pass calculation enabled (the default setting), Analytic Services 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.

To perform a default calculation, use any of the following methods:


Tool
Topic
Location

Administration Services

Calculating Block Storage Databases

Essbase Administration Services Online Help

MaxL

execute calculation

Technical Reference

ESSCMD

CALCDEFAULT

Technical Reference



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


Tool
Topic
Location

Administration Services

Using Two-Pass on a Default Calculation

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATE

Technical Reference



Creating 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 the 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 the 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 Analytic Services 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, Analytic Services marks data blocks as clean only after a full calculation of the database.

  5. Analytic Services 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:
    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 tools listed in Table 83.

  3. Use a calculation script similar to this example to disable Intelligent Calculation and calculate the formula:
    SET UPDATECALC OFF;
    SET CLEARUPDATESTATUS AFTER;
    "Share of Sales" = Sales % Sales -> Product;
    

    or:

    SET UPDATECALC OFF; SET CLEARUPDATESTATUS AFTER; CALC TWOPASS;


Table 83: Methods for Performing a Full Calculation

Tool
Topic
Location

Administration Services

Calculating Databases

Essbase Administration Services Online Help

MaxL

execute calculation

Technical Reference

ESSCMD

CALCDEFAULT

Technical Reference



For a comprehensive discussion of Intelligent Calculation, see Optimizing with Intelligent Calculation.

For detailed 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 Analytic Services not to mark the calculated data blocks as clean

  2. Mark all data blocks as clean, and do not recalculate the data blocks.
    SET CLEARUPDATESTATUS OFF;
    CALC ALL;
    CALC TWOPASS;
    SET CLEARUPDATESTATUS ONLY;
    CALC ALL;

With the example script, Analytic Services performs these tasks:

  1. The SET CLEARUPDATESTATUS OFF command tells Analytic Services not to mark the calculated data blocks as clean.

  2. The first CALC ALL command causes Analytic Services to cycle through the database calculating all dirty data blocks. Analytic Services does not mark the calculated data blocks as clean. Analytic Services does not automatically recalculate the formulas tagged as two-pass in the database outline.

  3. The CALC TWOPASS command causes Analytic Services to cycle through the database recalculating the formulas that are tagged as two-pass in the dimension tagged as accounts in the database outline. Analytic Services recalculates the formulas because the required data blocks are not marked as clean by the previous CALC ALL. Analytic Services does not mark the recalculated data blocks as clean.

  4. The SET CLEARUPDATESTATUS ONLY command tells Analytic Services 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 Analytic Services to cycle through the database and mark all the data blocks as clean. Analytic Services searches through the index and marks the data blocks as clean. It does not calculate the data blocks.

Intelligent Calculation Turned Off 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:
    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.

Consolidating #MISSING Values

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

Understanding #MISSING calculation

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


Table 84: How Analytic Services Treats #MISSING Values (Continued)

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 Analytic Services function of one variable

f (X)

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



By default, Analytic Services does not roll up #MISSING values. However, if you always load data at level 0 and never at parent levels, then you should enable the setting for consolidating #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 consolidating #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 Analytic Services rolls up the child values and overwrites the parent values correctly, so you can safely change the default.

Changing Consolidation for Performance

To consolidate, enable the setting for consolidating #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.

To change the way Analytic Services consolidates #MISSING values, use any of the following methods:


Tool
Topic
Location

Administration Services

Aggregating Missing Values During Calculation

Essbase Administration Services Online Help

Calculation script

SET AGGMISSG

Technical Reference

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM

Technical Reference



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

When the setting for consolidating #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 a detailed explanation of how Analytic Services calculates #MISSING values, see Consolidating #MISSING Values.

Removing #MISSING 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 Additional Calculation Optimization Issues

The relationship between calculation and performance is also described in the following chapters:

For the relationship of two-pass calculation and the SET CLEARUPDATESTATUS command, see the Technical Reference.

When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty for the purposes of Intelligent Calculation. This means that Analytic Services recalculates all the converted blocks when you recalculate the database. For a comprehensive discussion of Intelligent Calculation, see Optimizing with Intelligent Calculation.



Hyperion Solutions Corporation link