Which Values to Calculate Dynamically

Dynamically calculating some data values decreases batch calculation time and disk usage, but can increase retrieval time. Use the guidelines described in the following topics when deciding which Essbase members to calculate dynamically.

You can tag any member as Dynamic Calc except for level 0 members that do not have a formula, label-only members, and shared members.

Which members you choose to calculate dynamically depends on the database structure and on the balance between (1) the need for reduced calculation time and disk usage and (2) the need for speedy data retrieval for users.

In the Sample Basic database, the Year dimension is Dynamic Calc.

Figure 21-1 Sample Basic Outline Showing Dynamic Calc Members

Year dimension with Dynamic Calc members

In Smart View, users can display visual cues to distinguish dynamically calculated values.

When developing queries that include dynamically calculated values,consider using the Smart View Navigate Without Data option, so that Essbase does not dynamically calculate and store values while test queries are built.

Dense Members and Dynamic Calculation

Good candidates for Essbase Dynamic Calc members in a dense dimension include upper level members, and level 0 members with simple formulas.

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

Good candidates for Essbase Dynamic Calc members in a sparse dimension include upper level members without very many children, and members with formulas.

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.

Parent-Child Relationships and Dynamic Calculation

Parents with One Child

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.

Dynamic Calc Dependencies in Batch Calculation

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 and dense dimensions.

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

Figure 21-2 Year Dimension with Qtr1 as a Dynamic Calc Member

This image shows a Year dimension with Qtr1 expanded to Jan, Feb, and March, and only Qtr1 is dynamic.

Calculation Scripts and Dynamic Calculation

You cannot do a calculation script calculation of a Dynamic Calc member. If you are using a calculation script to calculate a member explicitly, do not tag that member as Dynamic Calc.

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.

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 use Dynamic Calc members in Essbase outline formulas, but not as formula targets in calc scripts.

You can include a dynamically calculated member in a formula when you apply the formula to the 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 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, whether in an outline formula or in a calculation script, Essbase interrupts the regular calculation to do the dynamic calculation, which impacts performance.

Scripts and Dynamically Calculated Members

To avoid overcalculation when there are runtime dependent Dynamic Calc member formulas, you can add SET FRMLRTDYNAMIC OFF to the Essbase calculation script.

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.