1. Database Administrator's Guide for Oracle Essbase
  2. Dynamically Calculating Data Values
  3. Choosing Values to Calculate Dynamically

Choosing Values to Calculate Dynamically

Dynamically calculating some data values decreases calculation time and disk usage and reduces database restructure time but increases retrieval time for dynamically calculated data values.

Use the guidelines described in the following sections when deciding which members to calculate dynamically.

Dense Members and Dynamic Calculation

Consider making the following changes to members of dense dimensions:

  • Tag upper-level members of dense dimensions as Dynamic Calc.

  • Try tagging level 0 members of dense dimensions with simple formulas as Dynamic Calc, and assess the increase in retrieval time.

    Simple formulas do not require Essbase to perform an expensive calculation. Formulas containing financial functions or cross-dimensional operators (->) are complex formulas.

Sparse Members and Dynamic Calculation

Consider making the following changes to members of sparse dimensions:

  • Tag some upper-level members of sparse dimensions that have six or fewer children as Dynamic Calc.

  • Tag sparse-dimension members with complex formulas as Dynamic Calc.

    A complex formula requires Essbase to perform an expensive calculation. For example, any formula that contains a financial function is a complex formula. See Complex Essbase Member Formulas

  • Tag upper-level members in a dimension that you frequently restructure as Dynamic Calc.

  • Do not tag upper-level, sparse-dimension members that have 20 or more descendants as Dynamic Calc.

Two-Pass Members and Dynamic Calculation

To reduce the time needed to perform batch calculations, tag two-pass members as Dynamic Calc. You can tag any Dynamic Calc member as two-pass, even if it is not on an accounts dimension. See Two-Pass Calculation.

For information about the interaction of members tagged as two-pass and attribute members, see Comparing Attribute and Standard Dimensions.

For information about how querying on a two-pass member in a dense dimension impacts the dynamic calculator cache, see Two-Pass Members and Dynamic Calculation.

Parent-Child Relationships and Dynamic Calculation

If a parent member has one child member, and you tag the child as Dynamic Calc, you must also tag the parent as Dynamic Calc.

However, if a parent member has one child member, and the parent is a Dynamic Calc member, you do not have to tag the child as Dynamic Calc.

Calculation Scripts and Dynamic Calculation

When Essbase calculates a CALC ALL or CALC DIM statement in a calculation script, it bypasses the calculation of Dynamic Calc members.

Similarly, if a member set function (for example, @CHILDREN or @SIBLINGS) is used to specify the list of members to calculate, Essbase bypasses the calculation of any Dynamic Calc members in the resulting list.

If you specify a Dynamic Calc member explicitly in a calculation script, the calculation script fails. You cannot do a calculation script calculation of a Dynamic Calc member. To use a calculation script to calculate a member explicitly, do not tag the member as Dynamic Calc.

For example, the following calculation script is valid only if Qtr1 is not a Dynamic Calc member: 

FIX (East, Colas)
   Qtr1;
ENDFIX

Formulas and Dynamically Calculated Members

You can include a dynamically calculated member in a formula when you apply the formula to the database outline. For example, if Qtr1 is a Dynamic Calc member, you can place the following formula on Qtr1 in the database outline: 

Qtr1 = Jan + Feb;

You cannot make a dynamically calculated member the target of a formula calculation in a calculation script; Essbase does not reserve memory for a dynamically calculated value and, therefore, cannot assign a value to it. For example, if Qtr1 is a Dynamic Calc member, Essbase displays a syntax error if you include the following formula in a calculation script: 

Qtr1 = Jan + Feb;

If Qtr1 is a Dynamic Calc member and Year is not, you can use the following formula in a calculation script: 

Year = Qtr1 + Qtr2;

This formula is valid because Essbase does not assign a value to the dynamically calculated member.

Note:

When you reference a dynamically calculated member in a formula in the database outline or in a calculation script, Essbase interrupts the regular calculation to do the dynamic calculation. This interruption can significantly reduce calculation performance.

Scripts and Dynamically Calculated Members

The preprocessing phase of a calculation script cannot determine whether an outline contains dense Dynamic Calc members. If a script contains runtime-dependent formulas, Essbase must calculate all dense Dynamic Calc members when the script is executed. Using the SET FRMLRTDYNAMIC OFF calculation command improves performance by stopping calculation of these Dynamic Calc members.

Dynamically Calculated Children

If the calculation of a member depends on the calculation of Dynamic Calc child members, Essbase must calculate the child members first during the batch database calculation in order to calculate the parent. Therefore, regular calculation time is not reduced. This requirement applies to members of sparse dimensions and members of dense dimensions.

For example, in the figure below, Qtr1 is a Dynamic Calc member. Its children, Jan, Feb, and Mar, are not dynamic members. Its parent, Year, is not a dynamic member. When Essbase calculates Year during a batch database calculation, it must consolidate the values of its children, including Qtr1. Therefore, it must take the additional time to calculate Qtr1, although Qtr1 is a Dynamic Calc member.

Figure 22-2 Sample.Basic Outline, Showing Qtr1 as a Dynamic Calc Member


This image shows an outline in which Qrt1 is a Dynamic Calc member.