In This Section:
Understanding Formula Calculation
Reviewing the Process for Creating Formulas
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Also see:
Formulas calculate relationships between members in a database outline. With 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 Composing Formulas.
Place formulas in a calculation script. Use this method if you need to control database calculations carefully.
Figure 114, Calculation of Margin %, Profit %, and Profit per Ounce 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.
Essbase provides a comprehensive set of operators and functions, which you can use to construct formula calculations on a database. The topics in this section describe the elements you can place in a formula and provide basic information about formula calculation and syntax:
Table 46 shows the types of operators you can use in formulas:
Table 46. Descriptions of Operator Types
Perform common arithmetic operations. For example, you can add, subtract, multiply, or divide values. For a list of mathematical operators, see the Oracle Essbase Technical Reference. | |
Control the flow of formula executions based on the results of conditional tests. For example, you can use an IF statement to test for a specified condition. For a list of conditional operators, see the Oracle Essbase Technical Reference. For information on writing conditional formulas, see Conditional Tests. | |
Point to the data values of specific member combinations. For example, point to the sales value for a specific product in a specific region. See Working with Member Combinations Across Dimensions. |
For information about using operators with #MISSING, zero, and other values, see the “Essbase Functions” section in the Oracle Essbase Technical Reference.
Functions are predefined routines that perform specialized calculations and return sets of members or data values. The following table shows the types of functions you can use in formulas.
Table 47. Descriptions of Function Types
For a complete list of operators, functions, and syntax, see the Oracle Essbase Technical Reference. Also see Reviewing Examples of Formulas.
You can include dimension and member names in a formula, as illustrated in the following example:
Scenario 100-10 Feb
You can assign a constant value to a member:
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 assign constants in a sparse dimension to only those intersections that require a value, use a FIX statement. See Constant Values Assigned to Members in a Sparse Dimension.
If you assign anything other than a constant to a member in a sparse dimension, and no data block exists for that member, new blocks are not created unless Essbase is enabled to create blocks on equations.
For example, to create blocks for West that did not exist before running the calculation, you must enable Create Blocks on Equations for this formula:
West = California + 120;
You can enable Create Blocks on Equations at the database level, whereby blocks are always created, or you can control block creation within calculation scripts.
To enable the Create Blocks on Equations feature for all calculation scripts for a specific database, use a tool:
Because unnecessary blocks can be created when Create Blocks on Equations is enabled at the application or database level, calculation performance can be affected. To control block creation within a calculation script, use the SET CREATEBLOCKONEQ ON | OFF calculation command. See Nonconstant Values Assigned to Members in a Sparse Dimension.
For formulas applied to members in a database outline, Essbase calculates formulas when you perform the following actions:
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. See Developing Calculation Scripts.
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. See Dynamically Calculating Data Values.
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 has to interrupt the regular calculation to perform the dynamic calculation.
You cannot use substitution variables in formulas that you apply to the database outline. See Using Substitution Variables in Formulas.
When you create member formulas, follow these rules:
End each statement in the formula with a semicolon (;). For example,
Use only saved outline member names. If a substitution variable is used for a member name, the substitution variable value must be a saved outline member name.
Enclose a member name in double quotation marks ("") if the member name meets any of the following conditions:
"Opening Inventory" = "Ending Inventory" - Sales + Additions;
Is the same as an operator, function name, or keyword. See Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, Substitution Variable Values and Environment Variable Values.
Includes any nonalphanumeric character. For example, hyphens (-), asterisks (*), and slashes (/).
Is all numeric or starts with one or more numerals. For example, "100" or "10Prod"
For a full list of member names that must be enclosed in quotation marks, see Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, Substitution Variable Values and Environment Variable Values.
End each IF statement in a formula with an ENDIF statement.
For example, the following formula contains a simple IF...ENDIF statement. You can apply this formula to the Commission member in a database outline:
IF(Sales < 100) Commission = 0; ENDIF;
If you are using an IF statement nested within another IF statement, end each IF with an ENDIF. For example:
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;)
You need not end ELSE or ELSEIF statements with ENDIFs. For example:
IF (@ISMBR(@DESCENDANTS(West)) OR @ISMBR(@DESCENDANTS(East) Marketing = Marketing * 1.5; ELSEIF(@ISMBR(@DESCENDANTS(South))) Marketing = Marketing * .9; ELSE Marketing = Marketing * 1.1; ENDIF;
Ending ENDIF statements with a semicolon (;) is not required, but it is a good practice.
When writing formulas, you can check the syntax using the Formula Editor syntax checker. See Checking Formula Syntax.
For information on syntax for Essbase functions and commands, see the Oracle Essbase Technical Reference.
You use Formula Editor, a tab in the Member Properties dialog box in Outline Editor, to create formulas. You can type the formulas directly into the formula text area, or you can use the Formula Editor UI features to create the formula.
Formulas are plain text. If required, you can create a formula in the text editor of your choice and paste it into Formula Editor.
In Outline Editor, select the member to which to apply the formula.
See “Creating and Editing Formulas in Outlines” in the Oracle Essbase Administration Services Online Help.
See Composing Formulas and “Creating and Editing Formulas in Outlines” in the Oracle Essbase Administration Services Online Help.
See “Creating and Editing Formulas in Outlines” in the Oracle Essbase Administration Services Online Help.
See “Saving Outlines” in the Oracle Essbase Administration Services Online Help.
The topics in this section discuss the main formula types. Also see Reviewing Examples of Formulas.
Before writing formulas, review the guidelines in Understanding Formula Syntax.
You can apply a mathematical operation to a formula to create a basic equation. The equation can be in the database outline or in a calculation script.
In a calculation script, you define basic equations as:
Member = mathematical operation;
where Member is a member name from the database outline and mathematical operation is any valid mathematical operation. For example:
Margin = Sales - COGS;
In this example, Essbase cycles through the database subtracting the values in COGS from the values in Sales and placing the results in Margin.
As another example, you can apply the following formula to a Markup member:
(Retail - Cost) % Retail;
In a calculation script, this formula is:
Markup = (Retail - Cost) % Retail;
In this example, Essbase cycles through the database subtracting the values in Cost from the values in Retail, calculating the resulting values as a percentage of the values in Retail, and placing the result in Markup.
You can define formulas that use a conditional test or a series of conditional tests to control the flow of calculation.
The IF and ENDIF commands define a conditional block. The formulas between the IF and the ENDIF commands are executed only if the test returns TRUE (1). If the test returns FALSE (0), you can use the ELSE and ELSEIF commands to specify alternative actions. The formulas following each ELSE command are executed only if the previous test returns FALSE (0). Conditions following each ELSEIF command are tested only if the previous IF command returns FALSE (0). See Understanding Formula Syntax.
When you use a conditional formula in a calculation script, enclose it in parentheses and associate it with a member in the database outline, as shown in the examples in this section.
In conjunction with an IF command, you can use functions that return TRUE or FALSE (1 or 0, respectively) based on the result of a conditional test. These functions are known as Boolean functions.
Use Boolean functions to determine which formula to use. The decision is based on the characteristics of the current member combination. For example, to restrict a certain calculation to the members in the Product dimension that contain input data, preface the calculation with an IF test based on @ISLEV(Product,0).
If one of the function parameters is a cross-dimensional member, such as @ISMBR(Sales -> Budget), all of the parts of the cross-dimensional member must match the properties of the current cell to return a value of TRUE (1).
You can use the following Boolean functions to specify conditions:
When you place formulas on the database outline, you can use only the IF, ELSE, ELSEIF, and ENDIF commands and Boolean functions to control the flow of the calculations. You can use additional control commands in a calculation script.
For information about how to develop calculation scripts and how to use them to control how Essbase calculates a database, see Developing Calculation Scripts. For information on individual Essbase functions and calculation commands, see the Oracle Essbase Technical Reference.
You can apply the following formula to a Commission member in the database outline.
In the following example, the formula calculates commission at 1% of sales if the sales are greater than 500000:
IF(Sales > 500000) Commission = Sales * .01; ENDIF;
If you place the formula in a calculation script, you must associate the formula with the Commission member as shown:
Commission (IF(Sales > 500000) Commission = Sales * .01; ENDIF;)
Essbase cycles through the database, performing these calculations:
In the next example, the formula tests the ancestry of the current member and then applies the appropriate Payroll calculation formula:
IF(@ISIDESC(East) OR @ISIDESC(West)) Payroll = Sales * .15; ELSEIF(@ISIDESC(Central)) Payroll = Sales * .11; ELSE Payroll = Sales * .10; ENDIF;
If you place the formula in a calculation script, you must associate the formula with the Payroll member as shown:
Payroll(IF(@ISIDESC(East) OR @ISIDESC(West)) Payroll = Sales * .15; ELSEIF(@ISIDESC(Central)) Payroll = Sales * .11; ELSE Payroll = Sales * .10; ENDIF;)
Essbase cycles through the database, performing the following calculations:
The IF statement uses the @ISIDESC function to check whether the current member on the Market dimension is a descendant of either East or West.
If the current member on the Market dimension is a descendant of East or West, Essbase multiplies the value in Sales by 0.15 and moves on to the next member combination.
If the current member is not a descendant of East or West, the ELSEIF statement uses the @ISIDESC function to check whether the current member is a descendant of Central.
If the current member on the Market dimension is a descendant of Central, Essbase multiplies the value in Sales by 0.11 and moves to the next member combination.
If the current member is not a descendant of East, West, or Central, Essbase multiplies the value in Sales by 0.10 and moves to the next member combination.
See About Multidimensional Calculation Concepts. For information on the @ISIDESC function, see the Oracle Essbase Technical Reference.
Use this section to find information about formulas related to values.
Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension at the same time. Some formulas, however, require values from members of the same dimension, and Essbase may not yet have calculated the required values.
A good example is that of cash flow, in which the opening inventory is dependent on the ending inventory from the previous month.
In Sample.Basic, the Opening Inventory and Ending Inventory values must be calculated on a month-by-month basis.
Assuming that the Opening Inventory value for January is loaded into the database, the required calculation:
1. January Ending = January Opening – Sales + Additions 2. February Opening = January Ending 3. February Ending = February Opening – Sales + Additions 4. March Opening = February Ending 5. March Ending = March Opening – Sales + Additions
You can calculate the required results by applying interdependent, multiple equations to one member in the database outline.
The following formula, applied to the Opening Inventory member in the database outline, calculates the correct values:
IF(NOT @ISMBR (Jan)) "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; "Ending Inventory" = "Opening Inventory" - Sales + Additions;
If you place the formula in a calculation script, you must associate the formula with the Opening Inventory member as shown:
"Opening Inventory" (IF(NOT @ISMBR (Jan)) "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF;) "Ending Inventory" = "Opening Inventory" - Sales + Additions;
Essbase cycles through the months, performing the following calculations:
The IF statement and @ISMBR function check that the current member on the Year dimension is not Jan. This step is necessary because the Opening Inventory value for Jan is an input value.
If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.
To calculate the correct results, you must place the above formula on one member, Opening Inventory. If you place the formulas for Opening Inventory and Ending Inventory on their separate members, Essbase calculates Opening Inventory for all months and then Ending Inventory for all months. This organization means that the value of the Ending Inventory of the previous month is not available when Opening Inventory is calculated.
You can use the @VAR and @VARPER functions to calculate a variance or percentage variance between budget and actual values.
You may want the variance to be positive or negative, depending on whether you are calculating variance for members on the accounts dimension that are expense or nonexpense items:
Expense items. You want Essbase to show a positive variance if the actual values are less than the budget values (for example, if actual costs are less than budgeted costs).
Nonexpense items. You want Essbase to show a negative variance if the actual values are less than the budget values (for example, if actual sales are less than budgeted sales).
By default, Essbase assumes that members are nonexpense items and calculates the variance accordingly.
To tell Essbase that a member is an expense item:
When you use the @VAR or @VARPER functions, Essbase shows a positive variance if the actual values are less than the budget values. For example, in Sample.Basic, the children of Total Expenses are expense items. The Variance and Variance % members of the Scenario dimension calculate the variance between the Actual and Budget values.
Allocation functions allow you to allocate values that are input at the parent level across child members in the same dimension or in different dimensions. The allocation is based on a variety of specified criteria.
Values from a member, cross-dimensional member, or value across a member list within the same dimension. | |
Values from a member, cross-dimensional member, or value across multiple dimensions. |
For examples of calculation scripts using @ALLOCATE, see Allocating Costs Across Products; using @MDALLOCATE, see Allocating Values Across Multiple Dimensions.
Forecasting functions allow you to manipulate data for the purposes of interpolating data or calculating future values.
For information about specific Essbase functions, see the Oracle Essbase Technical Reference.
Relationship functions allow you to use the member combination that Essbase is currently calculating to look up specific values.
For information about specific Essbase functions, see the Oracle Essbase Technical Reference.
Substitution variables act as placeholders for information that changes regularly; for example, time-period information. You can use substitution variables in formulas that you apply to the database outline.
When the outline is calculated, Essbase replaces the substitution variable with the value that you have assigned to it. You can create and assign values to substitution variables using Administration Services, MaxL, or ESSCMD.
You can set substitution variables at the server, application, and database levels. Essbase must be able to access the substitution variable from the application and database on which you are running the calculation scripts. See Using Substitution Variables.
To use a substitution variable in a formula, enter an ampersand (&), followed by the substitution variable name.
Essbase treats any text string preceded by & as a substitution variable.
For example, assume that the substitution variable UpToCurr is defined as Jan:Jun. You can use the following @ISMBR function as part of a conditional test:
@ISMBR(&UpToCurr)
At the time Essbase calculates the outline, it replaces the substitution variable, as shown:
@ISMBR(Jan:Jun)
In outline member formulas, you can use system environment variables as placeholders for user-specific system settings. Because environment variables are defined at the operating system level, they are available to all formulas on Essbase Server.
Using environment variables in formulas is the same as using them in calculation scripts. See Using Environment Variables in Calculation Scripts.
Note:
Environment variables cannot be used in MDX queries or in member formulas that are within aggregate storage outlines.
This section provides information about creating formulas that refer to members.
In some functions, you may need to specify more than one member, or you may need to specify a range of members. For example, the @ISMBR function tests to see if a member that is currently being calculated matches any of a list or range of specified members.
You can specify members using the following syntax:
For example: Mar2001 | |
A comma-delimited (,) list of member names. For example: Mar2001, Apr2001, May2001 | |
A range of all members at the same level, between and including the two defining members | The two defining member names separated by a colon (:). For example: Jan2000:Dec2000 |
A range of all members in the same generation, between and including the two defining members | The two defining member names separated by two colons (::). For example: Q1_2000::Q4_2000 |
For a list of member list contents and corresponding functions, see Generating Member Lists. | |
Separate each range, list, and function with a comma (,). For example: |
If you do not specify a list of members or a range of members in a function that requires either, Essbase uses the level 0 members of the dimension tagged as time. If no dimension is tagged as time, Essbase displays an error message.
Member set functions allow you to generate member lists that are based on a specified member or member list.
For information about specific Essbase functions, see the Oracle Essbase Technical Reference.
You can work with member names as character strings by using the following functions:
Use the cross-dimensional operator to point to data values of specific member combinations. Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.
In the following simplified illustration, the shaded data value is
Sales -> Jan -> Actual
The following example, which allocates miscellaneous expenses to each product in each market, illustrates how to use the cross-dimensional operator. The value of Misc_Expenses for all products in all markets is known. The formula allocates a percentage of the total Misc_Expenses value to each Product -> Market combination. The allocation is based on the value of Sales for each product in each market.
Essbase cycles through the database, performing these calculations:
Essbase divides the Sales value for the current member combination by the total Sales value for all markets and all products (Sales -> Market -> Product).
It multiplies the value calculated in step 1 by the Misc_Expenses value for all markets and all products (Misc_Expenses -> Market -> Product).
It allocates the result to Misc_Expenses for the current member combination.
Using the cross-dimensional operator can have significant performance implications. For optimization guidelines, see Using Cross-Dimensional Operators.
The topics in this section discuss formulas that use other types of functions. For more information about specific Essbase functions, see the Oracle Essbase Technical Reference.
Mathematical functions allow you to perform many mathematical operations in formulas.
Return the average value of the values in the specified member list | |
Return the value of e (the base of natural logarithms) raised to power of the specified expression | |
Return the next-lowest integer value of a member or expression | |
Return the logarithm to a specified base of a specified expression | |
Return the maximum value among the expressions in the specified member list | |
Return the maximum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values | |
Return the minimum value among the expressions in the specified member list | |
Return the minimum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values | |
Return the modulus produced by the division of two specified members | |
Return the value of the specified member raised to the specified power | |
Return the member or expression rounded to the specified number of decimal places | |
Return the variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. | |
Return the percentage variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
Statistical functions allow you to calculate advanced statistics in Essbase.
Range functions allow you to execute a function for a range of members.
Financial functions allow you to include financial calculations in formulas.
One member formula cannot contain multiple financial functions (for example, @NPV and @SLN, or multiple instances of @NPV). A member formula that requires multiple financial functions must be broken into separate formulas so that each formula contains only one financial function (for example, MemberName(@NPV(...));Membername(@NPV(...))).
The calculation mode function allows you to specify which calculation mode that Essbase uses to calculate a formula.
You can create custom-defined functions, to be used in formulas and calculation scripts, to perform calculations not otherwise supported by the Essbase calculation scripting language. Custom-developed functions must be written in the Java programming language and registered on the Essbase Server. The Essbase calculator framework calls them as external functions.
Custom-defined functions are displayed in the functions tree in Calculation Script Editor, where you can select them to insert into a formula.
Essbase includes Essbase Server-based formula syntax checking that tells you about syntax errors in formulas. For example, Essbase tells you if you have mistyped a function name. Unknown names can be validated against a list of custom-defined macro and function names. If you are not connected to a server or the application associated with the outline, Essbase may connect you to validate unknown names.
A syntax checker cannot tell you about semantic errors in a formula. Semantic errors occur when a formula does not work as you expect. To find semantic errors, run the calculation and check the results to ensure that they are as you expect.
Essbase displays the syntax checker results at the bottom of the Formula Editor. If Essbase finds no syntax errors, it displays the “No errors” message.
If Essbase finds one or more syntax errors, it displays the number of the line that includes the error and a brief description of the error. For example, if you do not include a semicolon end-of-line character at the end of a formula, Essbase displays a message similar to the following message:
Error: line 1: invalid statement; expected semicolon
If a formula passes validation in Formula Editor or Outline Editor, but Essbase Server detects semantic errors when the outline is saved, check the following:
The incorrect formula is saved as part of the outline, even though it contains errors.
Essbase Server writes a message in the application log that indicates what the error is and displays the incorrect formula.
Essbase Server writes an error message to the comment field of the member associated with the incorrect formula. The message indicates that the incorrect formula was not loaded. You can view this comment in Outline Editor by closing and reopening the outline.
If you do not correct the member formula, and a calculation that includes that member is run, the formula is ignored during the calculation.
After you have corrected the formula and saved the outline, the message in the member comment is deleted. You can view the updated comment when you reopen the outline.
You can estimate the disk size required for a single CALC ALL for a full data load or a partial data load. See Estimating Calculation Affects on Database Size.
An Essbase partition can span multiple Essbase Servers, processors, or computers. See Designing Partitioned Applications and Creating and Maintaining Partitions.
You can use formulas in partitioning, just as you use formulas on your local database. If, however, a formula you use in one database references a value from another database, Essbase has to retrieve the data from the other database when calculating the formula; therefore, ensure that the referenced values are up-to-date and carefully consider the performance impact on the overall database calculation. See Writing Calculation Scripts for Partitions.
With transparent partitions, carefully consider how you use formulas on the data target. See Transparent Partitions and Member Formulas and Performance Considerations for Transparent Partitions.