Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter explains how to develop and use formulas to calculate a database. It provides detailed examples of formulas, which you may want to adapt for your own use. For more examples, see Reviewing Examples of Formulas.
The information in this chapter does not apply to aggregate storage outlines. For information about developing formulas in MDX for aggregate storage outline members, see Developing Formulas on Aggregate Storage Outlines.
This chapter includes the following topics:
Using formulas can have significant implications for calculation performance. After reading this chapter, use the information in Optimizing Calculations to design and create formulas optimized for performance.
For information on using formulas with Hybrid Analysis, see Using Formulas with Hybrid Analysis.
Formulas calculate relationships between members in a database outline. You can use formulas in two ways:
The following figure 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 173: Calculation of Margin %, Profit %, and Profit per Ounce
Analytic Services provides a comprehensive set of operators and functions, which you can use to construct formula calculations on a database. The rest of this section provides a description of the elements you can place in a formula, and provides basic information about formula calculation and syntax:
The following table shows the types of operators you can use in formulas:
Operator Type |
Description |
---|---|
Perform common arithmetic operations. For example, you can add, subtract, multiply, or divide values. For a complete list of the mathematical operators, see the 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 the conditional operators, see the 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. For examples of how to use the cross-dimensional operator, see Working with Member Combinations across Dimensions. |
For information about using operators with
#MISSING, zero, and other values, see the "Analytic Services Functions" section in the 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.
For detailed examples of formulas, see Reviewing Examples of Formulas.
For a complete list of operators, functions, and syntax, see the Technical Reference.
Note: Abbreviations of functions are not supported. Some commands may work in an abbreviated form, but if there is another function with a similar name, Analytic Services may use the wrong function. Use the complete function name to ensure correct results.
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. Analytic Services 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 FIX as described in 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 may not be created unless Analytic Services is enabled to create blocks on equations.
For example, to create blocks for West that didn't exist prior to running the calculation, you need to 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 any of the following methods:
Tool |
Topic |
Location |
---|---|---|
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 CREATEBLOCKEQ ON|OFF calculation command as described in Non-Constant Values Assigned to Members in a Sparse Dimension.
For formulas applied to members in a database outline, Analytic Services calculates formulas when you do the following:
For a formula in a calculation script, Analytic Services calculates the formula when it occurs in the calculation script.
If a formula is associated with a dynamically calculated member, Analytic Services 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. For an explanation of how you calculate data values dynamically and how you benefit from doing so, 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 Analytic Services 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. For an explanation of how substitution variables can be used, see Using Substitution Variables.
When you create member formulas, make sure the formulas follow these rules:
Margin % Sales;
"Opening Inventory" = "Ending Inventory" - Sales + Additions;
For a complete list of member names that must be enclosed in quotation marks, see Understanding the Rules for Naming Dimensions and Members.
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, as illustrated in the following example:
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;)
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;
Note: If you use ELSE IF (with a space in between) rather than ELSEIF (one word) in a formula, you must supply an ENDIF for the IF statement.
When writing formulas, you can check the syntax using the Formula Editor syntax checker. For a comprehensive discussion, including examples, of the main types of formulas, see Checking Formula Syntax.
For detailed information on syntax for Analytic Services functions and commands, see the Technical Reference.
You use Formula Editor to create formulas. Formula Editor is a tab in the Member Properties dialog box in Outline Editor. You can type the formulas directly into the formula text area, or you can use the Formula Editor user interface 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.
To create a formula, follow this process:
For more information, see "Creating and Editing Formulas in Outlines" in the Essbase Administration Services Online Help.
For more information on entering the formula text in the Formula Editor, see "Creating and Editing Formulas in Outlines" in the Essbase Administration Services Online Help. For more information about composing the formula itself, see Composing Formulas.
For more information, see Checking Formula Syntax.
For more information, see "Creating and Editing Formulas in Outlines" in the Essbase Administration Services Online Help.
For more information, see "Saving Outlines" in the Essbase Administration Services Online Help.
To display an existing formula, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
The following sections discuss and give examples of the main types of formulas:
For detailed examples of formulas, 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. For example, you can apply the following formula to the Margin member in Sample Basic.
Sales - COGS;
In a calculation script, you define basic equations as follows:
Member = mathematical operation;
where Member is a member name from the database outline and mathematical operation is any valid mathematical operation, as illustrated in the following example:
Margin = Sales - COGS;
Whether the example equation is in the database outline or in a calculation script, Analytic Services 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 as follows:
Markup = (Retail - Cost) % Retail;
In this example, Analytic Services 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.
For an explanation of the nature of multidimensional calculations, see About Multidimensional Calculation Concepts
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). You can use the ELSE and ELSEIF commands to specify alternative actions if the test returns FALSE (0). 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).
For information about and examples of the syntax of the IF and ENDIF commands, see Understanding Formula Syntax.
When you use a conditional formula in a calculation script, you must 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.
You use Boolean functions to determine which formula to use. The decision is based on the characteristics of the current member combination. For example, you might want to restrict a certain calculation to the members in the Product dimension that contain input data. In this case, you 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 Analytic Services calculates a database, see Developing Calculation Scripts. For information on individual Analytic Services functions and calculation commands, see the Technical Reference.
You can apply the following formula to a Commission member in the database outline. In the first 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 need to associate the formula with the Commission member as follows:
Commission(IF(Sales > 500000) Commission = Sales * .01; ENDIF;)
Analytic Services 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 need to associate the formula with the Payroll member as follows:
Payroll(IF(@ISIDESC(East) OR @ISIDESC(West)) Payroll = Sales * .15; ELSEIF(@ISIDESC(Central)) Payroll = Sales * .11; ELSE Payroll = Sales * .10; ENDIF;)
Analytic Services cycles through the database, performing the following calculations:
For information on the nature of multidimensional calculations, see About Multidimensional Calculation Concepts. For information on the @ISIDESC function, see the Technical Reference.
Use this section to find information about formulas related to values:
Analytic Services optimizes calculation performance by calculating formulas for a range of members in the same dimension at the same time. However, some formulas require values from members of the same dimension, and Analytic Services 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 need to be calculated on a month-by-month basis.
Jan |
Feb |
Mar |
|
---|---|---|---|
Opening Inventory |
|||
Sales |
|||
Addition |
|||
Ending Inventory |
Assuming that the Opening Inventory value for January is loaded into the database, the required calculation is as follows:
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 a single 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 need to associate the formula with the Opening Inventory member as follows:
"Opening Inventory" (IF(NOT @ISMBR (Jan))
"Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;
"Ending Inventory" = "Opening Inventory" - Sales + Additions;)
Analytic Services cycles through the months, performing the following calculations:
Note: To calculate the correct results, it is necessary to place the above formula on a single member, Opening Inventory. If you place the formulas for Opening Inventory and Ending Inventory on their separate members, Analytic Services 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 non-expense items:
By default, Analytic Services assumes that members are non-expense items and calculates the variance accordingly.
To tell Analytic Services that a member is an expense item, use this procedure:
See "Creating and Editing Formulas in Outlines" in the Essbase Administration Services Online Help.
When you use the @VAR or @VARPER functions, Analytic Services shows a positive variance if the actual values are lower 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.
You can allocate values that are input at the parent level across child members in the same dimension or in different dimensions by using the following allocation functions.
Note: For examples of calculation scripts using the @ALLOCATE and @MDALLOCATE functions, see Allocating Values Within or Across Dimensions and the Technical Reference.
You can manipulate data for the purposes of smoothing data, interpolating data, or calculating future values by using the following forecasting functions.
For information about specific Analytic Services functions, see the Technical Reference.
You can use the member combination that Analytic Services is currently calculating to look up specific values. These functions are referred to as relationship functions.
For information about specific Analytic Services functions, see the 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 include in a calculation script. You cannot use substitution variables in formulas that you apply to the database outline.
When you run a calculation script, Analytic Services replaces the substitution variable with the value you have assigned to it. You can create and assign values to substitution variables using Essbase Administration Services or ESSCMD.
You can set substitution variables at the server, application, and database levels. Analytic Services must be able to access the substitution variable from the application and database on which you are running the calculation script.
For information on creating and assigning values to substitution variables, see Using Substitution Variables.
To use a substitution variable in a calculation script, type an ampersand (
&
) followed by the substitution variable name.
Analytic Services 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 in a calculation script:
@ISMBR(&UpToCurr)
Before Analytic Services runs the calculation script, it replaces the substitution variable, as follows:
@ISMBR(Jan:Jun)
This section provides information you need to create 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:
Member List or Range |
Syntax |
---|---|
A comma-delimited (,) list of member names. |
|
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 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, Analytic Services uses the level 0 members of the dimension tagged as time. If no dimension is tagged as time, Analytic Services displays an error message.
You can generate member lists that are based on a specified member by using the these member set functions.
For information about specific Analytic Services functions, see the 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 leave spaces in between the cross-dimensional operator and the member names.
For example, in this simplified illustration, the shaded data value is Sales -> Jan -> Actual.
Figure 175: Defining a Single Data Value by Using the Cross-Dimensional Operator
The following example illustrates how to use the cross-dimensional operator. This example allocates miscellaneous expenses to each product in each market.
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.
Misc_Expenses = Misc_Expenses -> Market -> Product * (Sales / ( Sales -> Market -> Product));
Analytic Services cycles through the database, performing these calculations:
Consider carefully how you use the cross-dimensional operator, as it can have significant performance implications. For information about optimizing and the cross-dimensional operator, see Using Cross-Dimensional Operators (->).
Use this section to find information about formulas that use other types of formulas:
You can perform many mathematical operations in formulas by using the following mathematical functions.
Operation |
Function |
---|---|
To return the average value of the values in the specified member list |
|
To return the value of e (the base of natural logarithms) raised to power of the specified expression |
|
To return the next lowest integer value of a member or expression |
|
To return the logarithm to a specified base of a specified expression |
|
To return the maximum value among the expressions in the specified member list |
|
To return the maximum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values |
|
To return the minimum value among the expressions in the specified member list |
|
To return the minimum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values |
|
To return the modulus produced by the division of two specified members |
|
To return the value of the specified member raised to the specified power |
|
To return the member or expression rounded to the specified number of decimal places |
|
To return the variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
|
To return the percentage variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
For information about specific Analytic Services functions, see the Technical Reference.
You can use these statistical functions to calculate advanced statistics in Analytic Services.
For information about specific Analytic Services functions, see the Technical Reference.
You can execute a function for a range of members by using these range functions.
For information about specific Analytic Services functions, see the Technical Reference.
You can include financial calculations in formulas by using these financial functions.
For information about specific Analytic Services functions, see the Technical Reference.
You can use dates with other functions by using this date function.
Date Conversion |
Function To Use |
---|---|
Convert date strings to numbers that can be used in calculation formulas |
You can specify which calculation mode that Analytic Services uses to calculate a formula by using @CALCMODE.
Specification |
Function To Use |
---|---|
To specify that Analytic Services uses cell, block, bottom-up, and top-down calculation modes to calculate a formula. |
Note: You can also use the configuration setting CALCMODE to set calculation modes to BLOCK or BOTTOMUP at the database, application, or server level. For details, see the Technical Reference, under "essbase.cfg
Settings" for CALCMODE or "Analytic Services Functions" for @CALCMODE.
Custom-defined functions are calculation functions that you create to perform calculations not otherwise supported by the Analytic Services calculation scripting language. You can use custom-defined functions in formulas and calculation scripts. These custom-developed functions are written in the Java programming language and registered on the Analytic Server. The Analytic Services calculator framework calls them as external functions.
Custom-defined functions are displayed in the functions tree in Calculation Script Editor. From this tree, you can select a custom-defined function to insert into a formula.
For a detailed explanation of how to develop and use custom-defined functions, see Developing Custom-Defined Calculation Functions.
Analytic Services includes Analytic Server-based formula syntax checking that tells you about syntax errors in formulas. For example, Analytic Services 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, Analytic Services 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.
Analytic Services displays the syntax checker results at the bottom of the Formula Editor. If Analytic Services finds no syntax errors, it displays the "No errors" message.
If Analytic Services 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, Analytic Services displays a message similar to the message shown in Figure 176.
Figure 176: Formula Editor Syntax Checker, Syntax Error Message
Error: line 1: invalid statement; expected semicolon
If a formula passes validation in Formula Editor or Outline Editor, but Analytic Server detects semantic errors when the outline is saved, check the following:
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.
To check formula syntax, see "Creating and Editing Formulas in Outlines" in the Essbase Administration Services Online Help.
You can estimate the disk size required for a single CALC ALL given either a full data load or a partial data load. For more information, see Estimating Calculation Affects on Database Size.
To estimate disk size for a calculation, see ESTIMATEFULLDBSIZE in the Technical Reference.
An Analytic Services partition can span multiple Analytic Servers, processors, or computers. For a comprehensive discussion of partitioning, see Designing Partitioned Applications and Creating and Maintaining Partitions.
You can use formulas in partitioning, just as you use formulas on your local database. However, if a formula you use in one database references a value from another database, Analytic Services has to retrieve the data from the other database when calculating the formula. In this case, you need to ensure that the referenced values are up-to-date and to consider carefully the performance impact on the overall database calculation. For a discussion of how various options affect performance, see Writing Calculation Scripts for Partitions.
With transparent partitions, you need to consider carefully how you use formulas on the data target. For a detailed example of the relationship between member formulas and transparent partitioning, see Transparent Partitions and Member Formulas. For a discussion of the performance implications, see Performance Considerations for Transparent Partition Calculations.
![]() |