Optimizing Calculations

In This Section:

Designing for Calculation Performance

Monitoring and Tracing Calculations

Using Simulated Calculations to Estimate Calculation Time

Estimating Calculation Affects on Database Size

Using Parallel Calculation

Using Formulas

Using Bottom-Up Calculation

Managing Caches to Improve Performance

Working with the Block Locking System

Using Two-Pass Calculation

Choosing Between Member Set Functions and Performance

Using Reference Cubes to Improve @XREF Performance

Consolidating #MISSING Values

Removing #MISSING Blocks

Identifying Additional Calculation Optimization Issues

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

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 8 Kb to 100 Kb provides optimal performance in most cases.

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

If data blocks are much larger than 100 KB, Intelligent Calculation does not work effectively. See Understanding Intelligent Calculation.

To optimize calculation performance and data storage, balance data block density and data block size by rearranging the dense and sparse dimension configuration of the database. Keep these suggestions in mind:

  • Keep data block size between 8 KB and 100 KB with as high a block density as possible.

  • Run test calculations of the most promising configurations of a database that contains representative data. Check results to determine the configuration that produces the best calculation performance.

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 a tool:

Tool

Topic

Location

Administration Services

Checking Data Block Statistics

Oracle Essbase Administration Services Online Help

ESSCMD

GETDBINFO

Oracle Essbase 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 many possible improvements, depending on the site:

    • The calculator cache functions more effectively, providing approximately a 10% performance improvement if you have a database outline with a large dimension (for example, one containing 1000 members).

    • Parallel calculation, if enabled, more likely will be used if the standard sparse dimension with the most members is the last standard sparse dimension in the outline.

    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, 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 and the dependent parents of March are updated.

    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, although time is dense and Intelligent Calculation is enabled.

    For information on incremental loads, see Loading Data into Aggregate Storage Databases.

    Database Outlines with Multiple Flat Dimensions

    Calculation performance may be affected if a database outline has multiple 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 multiple 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 Essbase 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.

    See Developing Calculation Scripts and Calculation Passes.

    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:

    • Display calculation settings, for example, whether completion notice messages are enabled

    • Provide statistics on the number of data blocks created, read, and written

    • Provide statistics on the number of data cells calculated

    SET MSG DETAIL also provides an 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 SET MSG DETAIL 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 such as 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 10 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 also will take 30 seconds. If there were two messages total, then you would know that the real calculation will take approximately 60 seconds (20 / 10 * 30 = 60 seconds).

    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:

    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, Essbase reports an error. 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 calculation time.

    Estimating Calculation Time

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

    *  To estimate total calculation time:

    1. Note the times of all the intervals between application log messages generated by SET NOTICE HIGH.

      See Table 118.

    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 118. Sample Intervals Between Log Messages

      Calculation Notice Number

      Simulated Calculation Time Interval (in seconds)

      Sparse dimension Calculation Interval (in seconds)

      1

      7

      45

      2

      5

       

      3

      6

       

      4

      3

       

      5

      4

       

      6

      2

       

      7

      6

       

      8

      4

       

      9

      3

       

      10

      3

       

      Total calculation time

      43

       

      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 Essbase reaches 30%–40% of the simulated calculations (30%–40% of the messages generated by SET NOTICE HIGH).See the Oracle Essbase 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 Essbase 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:

    • The model contains one or two sparse dimensions that are large in relation to the other sparse dimensions.

    • Larger dimensions have member configurations that result in multiple shared rollups.

    Changing the Outline Based on Results

    After 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:

    • Level 0 blocks following full model load 100,000

    • Upper level blocks after consolidating only sparse dimension 1:    1,000,000

    • Upper level blocks after consolidating only sparse dimension 2:    3,000,000

    • Upper level blocks after consolidating only sparse dimension 3:  10,000,000

    • Upper level blocks after consolidating only sparse dimension 4:       300,000

    • Upper level blocks after consolidating only sparse dimension 5:    5,700,000

    For example:

    • #4 (members = 10,000, 4 levels)

    • #1 (members = 500, 2 levels)

    • #2 (members = 100, 4 levels)

    • #5 (members = 10,000, 4 levels)

    • #3 (members = 20, flat)

    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 a CALC ALL will produce.

    *  To estimate the database size resulting from a calculation using interactive mode:

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

    2. Start the MaxL shell, log into Essbase, 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 appname.dbname get estimated size;
    4. Multiply the number of blocks by the average size of the blocks in the database.

      Results are accurate to ±10%.

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

    • You must perform this query after a CALC ALL. Any other calculation will not produce accurate results.

    • You can obtain accurate results with formulas only if they are on sparse dimensions.

    • You cannot obtain accurate results with top-down calculations on any member in combination with a lock on data (committed access).

    • If you need to estimate partitions, you must query Essbase for a database size estimate on every partition and add the results. If you query for the size of only the source database, the estimate includes only the data on the source database server.

    Using Parallel Calculation

    The following topics discuss parallel calculation and how it might improve performance for your site.

    Parallel Calculation

    Essbase provides two ways of invoking a calculation:

    • The calculation may be implicitly specified by the outline itself.

    • The calculation may be explicitly specified by a calculation script that you create. The script contains formulas and calculation instructions.

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

    • Serial calculation is the default. With serial calculation, each calculation pass is scheduled to run on a single processor. If invoked from a calculation script, the calculations are executed sequentially in the order in which they appear in the calculation script.

    • Parallel calculation breaks each calculation pass into sub-tasks. The sub-tasks that can run independently of one another are scheduled to run simultaneously on up to four threads. Each thread may be on a different processor.

    *  To change from the default serial calculation to parallel calculation, change, at most, two configuration settings and restart the server, or add an instruction to the calculation script.

    See Enabling Parallel Calculation.

      The following topics discuss the details of parallel calculation.

      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 the outline and the calculation requested for each calculation pass. Remember that one calculation may require multiple passes. Some situations may create the need for multiple passes, including dynamic calculation, the presence of a member tagged as two-pass, or calculations that create certain kinds of interdependencies. See Calculation Passes.

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

      However, Essbase 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, which will help you get the full benefit of parallel calculation:

      • Use the uncommitted access isolation level. Parallel calculation is not supported if you use the committed access isolation level. See Uncommitted Access.

      • One or more formulas present in a calculation may prevent Essbase from using parallel calculation even if it is enabled. For a description of formulas that may force serial calculation regardless of parallel calculation settings, see Formula Limitations.

      • Calculation tasks are usually generated along the last sparse dimension of an outline. Order the sparse dimensions in an outline from smallest to largest, based on actual size of the dimension as reported by the ESSCMD command GETDBSTATS. This ordering recommendation is consistent with recommendations for optimizing calculator cache size and consistent with other outline recommendations. For a description of situations that may need to use additional dimensions (more than the last sparse dimension) and for instructions on how to increase the number of sparse dimensions used, see Identifying Additional Tasks for Parallel Calculation.

      • Parallel calculation is effective on nonpartitioned applications and these partitioned applications:

        • Replicated partitions

        • Linked partitions

        • Transparent partitions if the calculation occurs at the target database. The number of sparse dimensions specified by CALCTASKDIMS in the essbase.cfg file or by SET CALCTASKDIMS in a calculation script must be set at 1 (the default value). For information on limitations imposed by the use of parallel calculation with transparent partitions, see Transparent Partition Limitations; for information on using CALCTASKDIMS or SET CALCTASKDIMS, see Identifying Additional Tasks for Parallel Calculation.

      • If you have selected incremental restructuring for a database and have made outline changes that are pending a restructure, do not use parallel calculation. Unpredictable results may occur.

      Relationship Between Parallel Calculation and Other Essbase Features

      The following topics discuss 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 Optimizing Query Performance.

      Formula Limitations

      The presence of some formulas may force serial calculation. The following formula placements likely will force serial calculation:

      • A formula on a dense member, including all stored members and any Dynamic Calc members upon which a stored member may be dependent, that causes a dependence on a member of the dimension that is used to identify tasks for parallel calculation.

      • A formula that contains references to variables declared in a calculation script that uses @VAR, @ARRAY, or @XREF.

      • A sparse dimension member formula using @XREF, and the dimension for the sparse member is fully calculated. @XREF does not force serial calculation when it is on dense Dynamic Calc members that are not dependent on other stored members during the batch calculation.

      • A member formula that causes a circular dependence. For example, member A has a formula referring to member B, and member B has a formula referring to member C, and member C has a formula referring to member A.

      • A formula on a dense or sparse member with a dependency on a member or members from the dimension used to identify tasks for parallel processing.

      • A sparse dimension member formula that contains references to members from other sparse dimensions.

      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 see whether a formula is preventing parallel calculation, check the application log. For relevant error messages, see Monitoring Parallel Calculation.

      Calculator Cache

      At the start of a calculation pass, Essbase 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 that 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, Essbase might perform a parallel calculation 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 so, you can use the configuration setting PARCALCMULTIPLEBITMAPMEMOPT to optimize memory use in multiple bitmap mode. This setting can be used with, or separately from, MULTIPLEBITMAPMEMCHECK. To enable PARCALCMULTIPLEBITMAPMEMOPT, add the following line to your essbase.cfg file:

      PARCALCMULTIPLEBITMAPMEMOPT TRUE

      See Sizing the Calculator Cache.

      Transparent Partition Limitations

      Parallel calculation with transparent partitions has the following limitations:

      • You cannot use parallel calculation across transparent partitions unless the calculation occurs at the target.

      • You must set CALCTASKDIMS or SET CALCTASKDIMS to 1 (the default) so that there is only one anchoring dimension.

      • You must increase the calculator cache so that multiple bitmaps can be used. You can identify 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.

        See Sizing the Calculator Cache.

      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 that 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 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 a tool:

      Tool

      Topic

      Location

      Administration Services

      Setting Data Integrity Options

      Oracle Essbase Administration Services Online Help

      MaxL

      display databasedbs_name

      Oracle Essbase Technical Reference

      ESSCMD

      GETDBINFO: Number of blocks modified before internal commit

      Oracle Essbase Technical Reference

        *  To modify the commit threshold, use a tool:

        Tool

        Topic

        Location

        Administration Services

        Setting Data Integrity Options

        Oracle Essbase Administration Services Online Help

        MaxL

        alter database dbs_name set implicit_commit after n blocks

        Oracle Essbase Technical Reference, list of MaxL statements

        ESSCMD

        SETDBSTATEITEM 21

        Example of Specifying Isolation Level Settings with ESSCMD

          See Uncommitted Access.

          Isolation Level Limitation

          You must use uncommitted mode for parallel calculation.

          *  To set the isolation level to uncommitted mode, use a tool:

          Tool

          Topic

          Location

          Administration Services

          Setting Data Integrity Options

          Oracle Essbase Administration Services Online Help

          MaxL

          alter database dbs_name disable committed_mode

          Oracle Essbase Technical Reference, list of MaxL statements

          ESSCMD

          SETDBSTATEITEM 18

          Example of Specifying Isolation Level Settings with ESSCMD

            See Uncommitted Access.

            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 whether parallel calculation has 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 Oracle Essbase Technical Reference.

            *  To check whether a calculation script sets parallel calculation, look for the SET CALCPARALLEL command. Review the script carefully, because 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:

              • Add or edit the appropriate configuration settings to the essbase.cfg file.

                See CALCPARALLEL and CALCTASKDIMS in the Oracle Essbase Technical Reference.

              • Add the appropriate calculation commands to a calculation script.

                See SET CALCPARALLEL and SET CALCTASKDIMS in the Oracle Essbase Technical Reference.

              Parallel calculation settings use standard precedence rules:

              • The database setting takes precedence over the application setting

              • The application setting takes precedence over the server setting.

              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!

              Read this entire chapter before attempting to enable parallel calculation.

              *  To enable parallel calculation:

              1. If you plan to enable parallel calculation in the configuration file, check the current status to see whether an entry exists.

                Use the process described in Checking Current Parallel Calculation Settings.

              2. Add or modify CALCPARALLEL in the essbase.cfg file on the server, 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.

                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.

              Oracle 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 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 situation can lead to uneven load balancing, reducing parallel calculation effectiveness.

              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 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 improving load balancing.

              *  To increase the number of sparse dimensions used to identify tasks for parallel calculation:

              1. If you are not sure, verify whether parallel calculation is enabled.

                See Checking Current Parallel Calculation Settings. 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 Oracle Essbase Technical Reference.

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

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

              Note:

              In some cases, Essbase uses fewer dimensions to identify tasks than is specified by CALCTASKDIMS or SET CALCTASKDIMS. See the Oracle Essbase Technical Reference.

              Monitoring Parallel Calculation

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

              *  To view the application log, see “Viewing Logs” in the Oracle Essbase Administration Services Online Help.

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

                • If you have enabled parallel calculation and Essbase has determined that parallel calculation can be performed, Essbase writes a message in the application log:

                  Calculating in parallel with n threads

                  n represents the number of concurrent tasks specified in CALCPARALLEL or SETCALCPARALLEL.

                • For each formula that prevents parallel calculation (forces serial calculation), Essbase writes a message to the application log:

                  Formula on ((or backward dependence from) mbr memberName prevents calculation from running in parallel.

                  memberName represents the name of the member where the relevant formula exists. You can look in the application log for such messages and consider removing the formula or, if possible, tagging the relevant member or members as Dynamic Calc so they do not feature in the calculation pass.

                • Essbase writes a message to the application log specifying the number of tasks that can be executed concurrently (based on the data, not the value of CALCPARALLEL or SETCALCPARALLEL):

                  Calculation task schedule [576,35,14,3,2,1]

                  The example message indicates that 576 tasks can be executed concurrently. After the 576 tasks complete, 35 more can be performed concurrently, and so on.

                  The benefit of parallel calculation is greatest in the first few steps and diminishes as fewer concurrent tasks are performed.

                  The degree of parallelism depends on the number of tasks in the task schedule. The greater the number, the more tasks that can run in parallel, and the greater the performance gains.

                • Essbase writes a message to the application log indicating how many tasks are empty (contain no calculations):

                  [Tue Jun 27 12:30:44 2007]Local/CCDemo/Finance/essexer/
                  Info(1012681) Empty tasks [291,1,0,0,0,0]

                  In the example, Essbase indicates that 291 of the tasks at level 0 were empty.

                  If the ratio of empty tasks to the tasks specified in the calculation task schedule is greater than 50% (for example, 291 / 576), parallelism may not be giving you improved performance because of the high sparsity in the data model.

                  You can change dense-sparse assignments to reduce the number of empty tasks and increase the performance gains from parallel calculation.

                Using Formulas

                You may achieve significant improvements in calculation performance by carefully using 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. See Developing Formulas.

                The following sections discuss how to handle formula issues that affect performance.

                Consolidating

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

                Figure 159. Consolidation on Sample.Basic Outline

                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 and meets the following requirements:

                • Does not reference values from a different dimension (sparse or dense). For example, a simple formula cannot reference Product -> Jan.

                • Does not use range functions. For example, a simple formula cannot use @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE.

                • Does not use relationship or financial functions. For example, a simple formula cannot use @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM, or @GROWTH. For a complete list of relationship and financial functions, see the Oracle Essbase Technical Reference.

                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:

                • If possible, apply the formula to a member in a dense dimension.

                • Use the FIX command in a calculation script to calculate only required data blocks. See Using the FIX Command.

                • Increase the density of the database (ratio of existing data blocks to possible data blocks). See Block Size and Block Density.

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

                • References a member or members in a different dimension (sparse or dense); for example, Product -> Jan.

                • Uses one or more range functions, for example, @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE.

                • Uses relationship or financial functions; for example, @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM, or @GROWTH. For a complete list of relationship and financial functions, see the Oracle Essbase Technical Reference.

                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 discussion about how complex formulas affect calculation performance, see Bottom-Up and Top-Down Calculation.

                Two examples illustrate complex formula overhead:

                • If a database has 90 existing data blocks and 100 potential data blocks, the overhead for complex formulas is not large, not more than 10 extra blocks to read and possibly write values to.

                • If a database has 10 existing data blocks and 100 potential data blocks, the overhead is as much as ten times what it would be without the complex formula (depending on the outline structure and other factors), as many as 90 extra blocks to read and possibly write to.

                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 otherwise would be calculated top-down. 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. Review the discussions of the SET FRMLBOTTOMUP calculation command and the CALCOPTFRMLBOTTOMUP configuration setting in the Oracle Essbase Technical Reference.

                Constant Values Assigned to Members 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 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 more sparse dimensions existed, Essbase would ensure data blocks for all combinations of the sparse dimensions with California and Colas, creating blocks if necessary. Within the new blocks, Essbase sets Measures and Scenario values (other than those assigned the value 120) to #MISSING.

                  Nonconstant Values Assigned to Members in a Sparse Dimension

                  If you assign nonconstant values to members of a sparse dimension, 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. (See Nonconstant Values.)

                  Consider the effects of the following calculation when West does not have a 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 nonconstant values to members of a sparse dimension, use the SET CREATEBLOCKONEQ ON | OFF command. The following script includes calculations with this setting off and then 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:

                  Using 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 information about using SET CREATEBLOCKEQ ON | OFF in calculation scripts, see the Oracle Essbase Technical Reference.

                  Using Cross-Dimensional Operators

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

                  On the Left of an Equation

                  For faster calculation script performance, use FIX in the calculation script to qualify the use of a formula rather than a formula that includes a cross-dimensional operator on the left 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:

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

                  With the FIX command, Essbase 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 Essbase 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, and so on), although only January combinations need to be calculated.

                  See Using the FIX Command and the Oracle Essbase Technical Reference.

                  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:

                  • Resultant values are from a dense dimension.

                  • The operand or operands are from a sparse dimension.

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

                  • Ensure that the results members are from a sparse dimension, not from a dense dimension. In this example, the results member Budget is from a sparse dimension:

                    FIX(Sales)
                        Budget = Actual * 1.1;
                    ENDFIX
                    FIX(Expenses)
                        Budget = Actual *  .95;
                    ENDFIX 
                  • Use the DATACOPY calculation command to create and then calculate the required blocks. See Using DATACOPY to Copy Existing Blocks.

                  • Use a member formula that contains the dense member equations:

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

                  Managing Formula Execution Levels

                  Formulas in a block storage outline can have dependencies on one another such that they cause a nested execution of formulas within one or more blocks. Such formulas are called recursive formulas. Sometimes recursive formulas result in large or unending loops that result in abnormal termination of the server.

                  To avoid abnormal termination, you can use the CALCLIMITEFORMULARECURSION configuration setting to stop a formula execution that reaches 31 execution levels. See the Oracle Essbase Technical Reference

                  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.

                  The following topics describe which calculation to use in different situations:

                  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.

                  For a bottom-up calculation, Essbase determines which data blocks must be calculated before it calculates the database. Essbase then calculates only the blocks that must 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. See Block Calculation Order.

                  If the database outline contains a complex member formula, Essbase 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, Essbase does a bottom-up calculation to determine which blocks must be calculated before 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 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 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 must be changed or a new block created; it is difficult to determine the dependency that blocks have on other blocks before the start of the calculation. The top-down method slows calculation performance because Essbase must search for appropriate blocks to calculate 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.

                  Consider the following complex formula:

                  A = B -> D + C -> D

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

                  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 a tool:

                  Method

                  Topic Where Discussed

                  Location

                  Calculation function

                  @CALCMODE in a formula

                  Oracle Essbase Technical Reference

                  Calculation script command

                  SET FRMLBOTTOMUP

                  Oracle Essbase Technical Reference

                  essbase.cfg file setting

                  CALCOPTFRMLBOTTOMUP

                  or

                  CALCMODE

                  Oracle Essbase Technical Reference

                    Forcing a bottom-up calculation on a formula usually 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 different from those 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, Essbase uses approximately 30 bytes of memory per member in the database outline. So if the database has 5,000 members, Essbase needs approximately 150 KB of memory to calculate the database.

                    Note:

                    You can avoid excess memory use by combining calculation scripts. You can obtain good performance by using parallel calculation with a single calculation script. 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 memory caches to coordinate memory usage:

                    • Calculator cache. Ensure that the calculator cache is large enough to optimize calculation performance.

                    • Dynamic calculator cache.

                    • Index cache. If the database is large, the default index cache is not large enough to provide optimum calculation performance.

                    • Data cache.

                    • Data file cache.

                    Note:

                    When you first calculate a database, the size of the calculator cache is significant for calculation performance. If possible, ensure that the calculator cache is large enough for Essbase to use the optimal calculator cache option.

                    See Sizing Caches. Read the entire topic before making changes.

                    Working with the Block Locking System

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

                    By default, Essbase 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, Essbase 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). By increasing the number, you ensure that Essbase can lock all required blocks, and performance is not impaired.

                    Essbase locking behavior depends on the isolation level setting. See Locking Under Committed Access and Locking Under Uncommitted Access.

                    Note:

                    For consolidations in a sparse dimension, block locking is not a consideration, because Essbase 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 Essbase 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 require more time than expected.

                    Managing Concurrent Access for Users

                    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; other users cannot update or calculate it, but they can have read-only access. 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 conditions:

                    • For the data block to be released if the isolation level setting is Uncommitted Access.

                    • For the calculation to complete if the isolation level setting is Committed Access.

                    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:

                    • Deny access to other users

                    • Disconnect users from Essbase

                    For information about security options, see Disconnecting Users and Terminating Requests in Native Security Mode and Managing Passwords and User Names in Native Security Mode.

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

                    Note:

                    When Essbase locks a block for calculation, it does not put an exclusive lock on the dependent child blocks, so 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. 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 18, Differences Between Attribute and Standard Dimensions.

                    Understanding Two-Pass Calculation

                    You can use a two-pass calculation on member formulas that must 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 need not 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 and on the dense-sparse configuration of the time and account dimensions.

                    Reviewing a Two-Pass Calculation 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

                    4 / 7

                    Note:

                    For information on how cell calculation order depends on database configuration, see Cell 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.

                      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, 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 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 Essbase accurately calculates two-pass formulas.

                    These scenarios require that you understand the concepts of Intelligent Calculation. See Understanding Intelligent Calculation.

                    Scenario A

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

                    No Extra Calculation Pass for Two-Pass Formulas

                    Because Essbase calculates the two-pass formulas while it is calculating the data block, Essbase need not 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, Essbase does the two-pass calculation while each data block is being calculated. However, when you repeat a formula in a calculation script, Essbase must read the data blocks and write them to memory to recalculate the formula.

                    Scenario B

                    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 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. See Creating Calculation Scripts for Two-Pass and Intelligent Calculation.

                    Choosing Two-Pass Calculation Tag or Calculation Script

                    Although 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:

                    • You can tag a member as two-pass if it is in a dimension tagged as accounts. When you perform a default calculation on the database, Essbase automatically recalculates any formulas tagged as two-pass if they are in the dimension tagged as accounts in the database outline.

                    • You can tag a member as two-pass if it is a Dynamic Calc or Dynamic Calc and Store member of any dimension. See Dynamically Calculating Data Values.

                    • You may need to use a calculation script to calculate a two-pass formula to obtain accurate results, even if the two-pass tag would provide performance benefits. See Creating Calculation Scripts for Two-Pass and Intelligent Calculation.

                    • Use a calculation script instead of the two-pass tag to ensure efficient use of Intelligent Calculation. See Understanding the Interaction of Two-Pass Calculation and Intelligent Calculation.

                    • You must use a calculation script to calculate a formula twice if the database configuration means that Essbase uses Scenario A, as described in Scenario A, and if the formula references values from another data block.

                    • You may want to use a calculation script to calculate two-pass formulas if the database configuration means that Essbase uses Scenario B, as described in Scenario B.

                    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), Essbase automatically attempts to calculate 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 a tool:

                    Tool

                    Topic

                    Location

                    Administration Services

                    Calculating Block Storage Databases

                    Oracle Essbase Administration Services Online Help

                    MaxL

                    execute calculation

                    Oracle Essbase Technical Reference

                    ESSCMD

                    CALCDEFAULT

                    Oracle Essbase Technical Reference

                      *  To enable two-pass calculation, use a tool:

                      Tool

                      Topic

                      Location

                      Administration Services

                      Using Two-Pass on a Default Calculation

                      Oracle Essbase Administration Services Online Help

                      MaxL

                      alter database

                      Oracle Essbase Technical Reference

                      ESSCMD

                      SETDBSTATE

                      Oracle Essbase 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:

                        • Before the calculation script command that recalculates a two-pass formula, add the SET UPDATECALC OFF command to disable Intelligent Calculation. If you have Intelligent Calculation enabled (the default), Essbase calculates only the data blocks that are not marked as clean, but when you perform a default calculation of the database with Intelligent Calculation enabled, all data blocks are marked as clean, so Essbase does not perform the two-pass formula recalculation.

                        • When you use a calculation script, Essbase does not automatically recalculate two-pass formulas. Use the CALC TWOPASS command.

                        • If you have changed the default calculation of CALC ALL, and Intelligent Calculation is enabled, the data blocks may not be marked as clean after the first calculation. See Understanding Intelligent Calculation. Also see “Setting Default Calculations” in the Oracle Essbase Administration Services Online Help.

                        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 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 large, and you want to use Intelligent Calculation, you can use any of the following options for the best performance. All three 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

                        *  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

                        *  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 119, Methods for Performing a Full Calculation .

                        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 119. Methods for Performing a Full Calculation

                          Tool

                          Topic

                          Location

                          Administration Services

                          Calculating Databases

                          Oracle Essbase Administration Services Online Help

                          MaxL

                          execute calculation

                          Oracle Essbase Technical Reference

                          ESSCMD

                          CALCDEFAULT

                          Oracle Essbase Technical Reference

                        See Understanding Intelligent Calculation, Developing Formulas, and Developing Calculation Scripts.

                        Intelligent Calculation with a Small Index

                        *  To use Intelligent Calculation when the index is small:

                        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.

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

                          With the example script, Essbase performs these tasks:

                        3. The SET CLEARUPDATESTATUS OFF command tells Essbase not to mark the calculated data blocks as clean.

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

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

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

                        7. The last CALC ALL command causes Essbase to cycle through the database and mark all the data blocks as clean. Essbase searches 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

                        *  To turn Intelligent Calculation off for a Two-Pass formula, create a calculation script that performs these tasks:

                        • Disables Intelligent Calculation.

                        • Performs a full calculation.

                        • Repeats the following two-pass formula:

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

                          Choosing Between Member Set Functions and Performance

                          Queries and calculations that 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:

                          • @CURRMBR

                          • @PARENT

                          • @SPARENTVAL

                          • @ANCEST

                          • @SANCESTVAL

                          If you are experiencing slow performance, consider either removing the dynamic calculation tag or removing these functions from the attached formula.

                          Using Reference Cubes to Improve @XREF Performance

                          The @XREF function pulls information from one database to another; for example, to get inflation rates from a different database than the database containing the sales data to be calculated. (See the Oracle Essbase Technical Reference.) Accessing data across databases in different applications can involve multiple data transfers, increasing performance time. Depending on size characteristics of the referenced database, using reference cubes can decrease data transfer time.

                          Understanding Reference Cubes

                          The @XREF function is executed in a calculation on the target database. The @XREF syntax identifies the source database containing the desired information, and a member list statement that qualifies what member information is needed from the source database. When reference cubes are created, a copy of the source database is copied to the memory with the target (requesting) database. By associating the information with the target database, the number of data transfers is minimized, improving performance.

                          Depending on the volatility of the source data values and how @XREF is used, two types of reference cubes are available: active and passive. A creation parameter defines a reference cube as active or passive.

                          Use active reference cubes when the source data is relatively stable or when @XREF is included in dynamic calculations on the target database. When an active reference cube is loaded to the target (written to memory), the reference cube is registered with the source database so that source database changes can update reference cube values dynamically. Unloading or deleting a reference cube deregisters it.

                          Passive reference cubes are not registered, because the source database does not dynamically update passive reference cubes with value changes. Use passive reference cubes when source data values change frequently or when the @XREF function is used in batch calculations on the target database. Once a passive reference cube has been created and loaded, it gets refreshed at the start of every batch calculation.

                          Note:

                          If an @XREF function associated with a dynamically calculated target member references a passive reference cube, the reference cube is ignored, and the @XREF request goes directly to the source database.

                          The following features and situations do not support reference cubes:

                          • Aliases. Use actual member names, not aliases.

                          • Duplicate member names

                          • Dynamic Time Series members

                          • The presence of attribute dimensions on the source database

                          • LROs within the reference cube data

                          • Cascading changes through a series of active reference cubes. For example, database A uses database B as a reference cube source. Database B uses database C as a reference cube source. Change updates on data from database C update database B reference cubes. However, if the updated values on database B affect a reference cube on database A, database A is not updated.

                          A reference cube probably will not improve @XREF performance when the referenced database has any of the following characteristics:

                          • Any dimension contains more than 100 members

                          • More than four or five dimensions exist

                          • Total database contains more than 1,000,000 cells

                          • The referenced slice is less than 10%–20% of the referenced cube size (applies to passive reference cubes)

                          Working with Reference Cubes

                          Before a reference cube can be used, it must be created and loaded. Creating a reference cube defines the source database and member content of the reference cube. Creating a reference cube also loads it. You can unload a reference cube and reload it. Loading a reference cube retrieves reference cube values, placing them in target memory.

                          You can display information about active reference cubes on the target or about reference cubes registered on a particular source.

                          *  To manage reference cubes, use MaxL statements (details in the Oracle Essbase Technical Reference).

                          ActionMaxL Statement
                          Create a reference cubecreate passive|active reference_cube
                          Delete a reference cubedrop reference_cube
                          Load or unload a reference cubealter database
                          List active reference cube informationdisplay reference_cube
                          List information about registered reference cubesdisplay reference_cube_reg

                            Note:

                            If a database works with many reference cube registrations, calculation time may be improved by using MaxL alter database to increase the implicit commit row or block settings.

                            Sizing Reference-Cube Memory Impact

                            Individual reference cubes are the size of the source database. Because databases can grow, when you create a reference cube you must specify a maximum size. Databases can have multiple reference cubes. The REFERENCECUBESIZELIMIT configuration setting uses the following syntax to specify the maximum memory to be set aside for all the reference cubes loaded at the same time for a specific database:

                            REFERENCECUBESIZELIMIT TargetApp TargetDB “max_cell_k_count_size”

                            For example:

                            REFERENCECUBESIZELIMIT Sample.Basic 100

                            All sizes are specified in terms of 1000 cells. To calculate the number of cells for a database, you must know the number of cells in an expanded block (including stored and dynamic members) and the number of potential blocks. The number of cells in an expanded block is the product of the number of members across the dense dimensions. Multiply this product by the potential number of blocks (shown on the Database Statistics tab of the Database Properties window in Administration Services Console). For a database with multiple reference cubes, sum the cell counts of its reference cubes. Divide the cell count by 1000 for the number to be used in the REFERENCECUBESIZELIMIT configuration setting and the create passive|active reference cube MaxL statement. The default size for REFERENCECUBESIZELIMIT is 8 (8000 cells).

                            During operations, when the sum of the cell-count sizes of multiple reference cubes reaches the maximum set by the configuration setting, no more reference cubes are loaded.

                            Consolidating #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

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

                            Table 120. 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
                            
                              0 AND #MISSING
                            
                              #MISSING AND #MISSING
                            
                            #MISSING
                            
                            
                            0
                            
                            #MISSING
                            X OR #MISSING:
                            
                              Y OR #MISSING, where Y
                              represents any nonzero value
                            
                              0 OR #MISSING  
                            
                              #MISSING OR #MISSING
                            
                            
                            Y
                            
                            #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 roll up #MISSING values. However, if you always load data at level 0 and never at parent levels, you should enable the setting for consolidating #MISSING values. This setting provides a calculation performance improvement of 1%–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), Essbase 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 how Essbase consolidates #MISSING values, use a tool:

                            Tool

                            Topic

                            Location

                            Administration Services

                            Aggregating Missing Values During Calculation

                            Oracle Essbase Administration Services Online Help

                            Calculation script

                            SET AGGMISSG

                            Oracle Essbase Technical Reference

                            MaxL

                            alter database

                            Oracle Essbase Technical Reference

                            ESSCMD

                            SETDBSTATEITEM

                            Oracle Essbase Technical Reference

                            Note:

                            If you enable the setting for consolidating #MISSING values, the cell calculation order within a data block changes. See Cell Calculation Order.

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

                              • When the ratio of calculated data blocks to input data blocks is low

                              • When you load many data values at parent levels on sparse dimensions; for example, in the Sample.Basic database, if you load many data values into East in a sparse Market dimension

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

                              For a information on how Essbase calculates #MISSING values, see Consolidating #MISSING Values.

                              Removing #MISSING Blocks

                              You can use the CLEARDATA command to change the value of cells in a block to #MISSING. It does not remove the data blocks. These extra blocks can slow retrieval and calculation performance.

                              If the #MISSING blocks are slowing performance, perform either action:

                              • Use the CLEARBLOCK command to remove the data blocks.

                              • Export the data and re-import it (see the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide).

                              Note:

                              Removing empty blocks improves performance when data values already have been loaded. However, data load process time increases if new values require that blocks be created.

                              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 Oracle Essbase 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 Essbase recalculates all the converted blocks when you recalculate the database. See Understanding Intelligent Calculation.