Formulas calculate relationships between members in the database outline. You can apply formulas to members in the outline, or you can place formulas in a calculation script. This topic explains how TBC optimized the performance of its database by using formulas.
Functions are predefined routines that perform specialized calculations and return sets of members or sets of data values. Formulas comprise operators and functions, as well as dimension names, member names, and numeric constants.
Essbase supports the following operators:
The Essbase functions include more than 100 predefined routines to extend the calculation capabilities of Essbase. Essbase supports the following functions:
Boolean functions, which provide a conditional test by returning a TRUE or FALSE value
Mathematical functions, which perform specialized mathematical calculations
Relationship functions, which look up data values within a database during a calculation based on the position of the current member
Range functions, which declare a range of members as an argument to another function or to a command
Financial functions, which perform specialized financial calculations
Member set functions, which are based on a specified member and which generate lists of members
Allocation functions, which allocate values that are input at a parent level across child members
Forecasting functions, which manipulate data for the purpose of smoothing data, interpolating data, or calculating future values
Date and time functions, which use date and time characteristics in calculation formulas
Calculation mode functions, which specify the calculation mode that Essbase uses to calculate a formula
The Measures dimension uses the following formulas:
Essbase uses consolidation operators to calculate the Margin, Total Expenses, and Profit members. The Margin% formula uses a % operator, which means “express Margin as a percentage of Sales.” The Profit% formula uses the same % operator. The Profit per Ounce formula uses a division operator (/) and a function (@ATTRIBUTEVAL) to calculate profitability by ounce for products sized in ounces.
In the Profit per Ounce formula, the @NAME function is also used to process the string “Ounces” for the @ATTRIBUTEVAL function. |
For a complete list of operators, functions, and syntax, see the Oracle Essbase Technical Reference. Also see Developing Formulas for Block Storage Databases.