Essbase Formula Optimization
To optimize Essbase block storage formula processing for calc and query, you can avoid formulas by using consolidation instead of calculation, use simpler formulas when possible, take steps to tune performance for complex formulas, or switch to hybrid mode.
You may achieve significant improvements in calculation performance by carefully using formulas in the cube 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 Develop Formulas for Block Storage Cubes.
The sections that follow discuss how to handle formula issues that affect calculation performance in non-hybrid mode, block storage cubes.
For hybrid mode optimization, refer instead to Hybrid Mode for Fast Analytic Processing.
Essbase Member Consolidation
Using the cube outline to roll up Essbase data values is more efficient than using a formula to calculate them.
For example, the consolidation of members 100-10, 100-20, and 100-30 into member 100, as shown below, is more efficient than applying the following formula to member 100:
100-10 + 100-20 + 100-30
Figure 35-1 Consolidation Example
![This image shows an outline, in which members 100-10, 100-20, and 100-30 are consolidated into member 100. This image shows an outline, in which members 100-10, 100-20, and 100-30 are consolidated into member 100.](img/asprd100.jpg)
Refer to Examples of Member Consolidation.
Simple Essbase Member Formulas
If an Essbase formula is simple, it affects block storage calculation performance less than complex formulas do. Block size is also a factor in performance. Simpler formulas are ones that don't use cross dimensional references, range functions, financial functions, or relationship functions.
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.
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.
References
For information on the relationship between block size and calculation performance, refer to Block Size and Block Density.
For information on how formulas affect calculation performance, refer to Bottom-Up and Top-Down Calculation.
Range and Financial FunctionsComplex Essbase Member Formulas
If an Essbase formula is complex, it affects block storage calculation performance more than simpler formulas do. To optimize complex formulas, apply them in dense dimensions, increase the density, and use FIX to narrow the scope.
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. If not possible, determine whether you can change top-down calculations to bottom-up (refer to Formulas on Sparse Dimensions in Large Outlines).
-
Use the FIX command in a calculation script to calculate only required data blocks.
-
Increase the density of the database (ratio of existing data blocks to possible data blocks).
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.
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.
Two examples illustrate complex formula overhead:
-
If the cube 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 the cube 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.
References
For information on the relationship between block size and calculation performance, refer to Block Size and Block Density.
For information on how formulas affect calculation performance, refer to Bottom-Up and Top-Down Calculation.
Range and Financial FunctionsFormulas on Sparse Dimensions in Large Outlines
To optimize the calculation of complex formulas on sparse dimensions in large Essbase block storage cube outlines, you can force a bottom-up calculation of the formulas.
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. Refer to Bottom-Up and Top-Down Calculation.
Forcing a bottom-up calculation on a top-down formula enables more efficient use of the CALC ALL and CALC DIM commands. Review the discussions of the SET FRMLBOTTOMUP calculation command and the CALCOPTFRMLBOTTOMUP configuration setting.
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. (See Nonconstant Values.)
Within calculation scripts, you can temporarily override the Create Blocks on Equations setting. Consider the effects of the following calculation when West does not have a value and Create Blocks on Equations is enabled:
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 calculation command, as shown in the following script:
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 of the script, 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 setting for Create Blocks on Equations.
Cross-Dimensional Operators in Member Formulas
When designing Essbase block storage member formulas, use caution when using a cross-dimensional operator ( -> ) on the left side of an equation, or in equations on dense dimensions.
Left Side 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 Use the FIX Command.
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 Copy Existing Blocks with DATACOPY.
-
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 CALCLIMITFORMULARECURSION configuration setting to stop a formula execution that reaches beyond a default number of execution levels.