Essbase - Formulas

About

Formulas calculate relationships between member in the database outline. You can :

• apply formulas to members in the outline,
• or place formulas in a calculation script.

This topic explains how TBC optimized the performance of its database by using formulas.

Formulas comprise :

• operators
• functions, as well as
• dimension names,
• member names,
• and numeric constants.

Article Related

• Essbase - Calculations
• Essbase - Consolidation Operator
• Essbase - Data Source
• Essbase - The OLAP Design Cycle (to create an optimized database)
• Essbase - Dimension build rules files
• Essbase - Dynamic Calculations
• Essbase - MDX
• Essbase - Outline (Database Outline)
• Essbase - Outline-based Calculations
• Essbase - Outline Creation and Management using Outline Editor
• Essbase - Time balance properties
• Essbase - Two-Pass Calculations

Operators

Essbase supports the following operators:

• Mathematical operators that perform arithmetic operations
• Conditional operators that build logical conditions into calculations
• Cross-dimensional operators that point to data values of specific database member combinations

Example

The Measures dimension uses the following formulas:

• Margin = Sales – COGS
• Total Expenses = Marketing + Payroll + Miscellaneous
• Profit = Margin – Total Expenses
• Profit % = Profit % Sales
• Margin % = Margin % Sales
• Profit per Ounce = Profit / @ATTRIBUTEVAL(@NAME(Ounces))

Essbase uses Essbase - Consolidation Operator 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.

Documentation / Reference

• For a complete list of operators, functions, and syntax, see the Oracle Essbase Technical Reference.