Using Formulas and Formula Calculations

Essbase formulas calculate relationships between members. You can use them in an Essbase outline and in calculation scripts.

To implement formulas, you can:

• Apply formulas to members in the database outline. Use this method if you do not need to control database calculations carefully for accuracy or performance. This method limits formula size to less than 64 KB.

  See Using Functions in Formulas.

• Place formulas in a calculation script. Use this method if you need to control database calculations carefully.

  See Formulas in Calculation Scripts.

The following image shows the Measures dimension from the Sample.Basic database. The Margin %, Profit %, and Profit per Ounce members are calculated using the formulas applied to them.

Figure 18-1 Calculation of Margin %, Profit %, and Profit per Ounce

For formulas applied to members in a database outline, Essbase calculates formulas when you perform the following actions:

• Run a default (CALC ALL) calculation of a database.

• Run a calculation script that calculates the member containing the formula; for example, a CALC DIM of the dimension containing the member, or the member itself.

For a formula in a calculation script, Essbase calculates the formula when it occurs in the calculation script.

If a formula is associated with a dynamically calculated member, Essbase calculates the formula when the user requests the data values. In a calculation script, you cannot calculate a dynamically calculated member or make a dynamically calculated member the target of a formula calculation.

Using dynamically calculated members in a formula on a database outline or in a calculation script can significantly affect calculation performance. Performance is affected because Essbase interrupts the regular calculation to perform the dynamic calculation.

You cannot use substitution variables in formulas that you apply to the database outline.