Define Essbase Calculations

Consolidation of Dimensions and Members

When you define members of standard dimensions, Essbase automatically tags the members with the + consolidation operator (plus sign representing addition), meaning that during consolidation members are added to derive their parent's value. As appropriate, you can change a member consolidation property.

Consolidation is the most frequently used calculation in Essbase. This topic uses the Product dimension to illustrate consolidations.

The TBC application has several consolidation paths:

Individual products roll up to product families, and product families consolidate into Product. The TBC outline also requires multiple consolidation paths; some products must consolidate in multiple categories.
States roll up to regions, and regions consolidate into Market.
Months roll up into quarters, and quarters consolidate into Year.

Consolidation operators define how Essbase rolls up data for each member in a branch to the parent. For example, using the default addition (+) operator, Essbase adds 100‑10, 100‑20, and 100‑30 and stores the result in their parent, 100, as shown below.

Figure 1-7 TBC Product Dimension

This image shows consolidation operators in an outline, as described in the text preceding the image.

The Product dimension contains mostly addition (+) operators, which indicate that each group of members is added and rolled up to the parent. Diet has a tilde (~) operator, which indicates that Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact overall consolidation.

Effect of Position and Operator on Consolidation

Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.

Because Essbase always begins with the top member when it consolidates, the order and the labels of the members is important.

Consolidation of Shared Members

Shared members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Essbase stores the data only once. Shared members must be in the same dimension. Data can be shared by multiple members.

Checklist for Consolidation

Use the following checklist to help define consolidation:

Did you identify the consolidations in the outline?
Did you tag each member with the proper consolidation operator?
Did you specify a shared member tag for designated members?
Would shared members be more efficient if designed within an attribute dimension (other than shared)?

Tags and Operators on Example Measures Dimension

The Measures dimension, which is tagged as Accounts, is the most complex dimension in the fictional TBC outline, because it uses both time and accounts data. It also contains formulas and special tags to help Essbase calculate the outline.

Examine the Measures dimension tags defined by TBC. Many of the properties of the Measures dimension are discussed in previous topics: addition (+), subtraction (–), and no consolidation (~) operators, and accounts and label only tags:

The Inventory and Ratios member names assist the user in data navigation. They do not contain data, and therefore receive a label only tag.
The Measures dimension itself has a label only tag. Some members of Measures have a Dynamic Calc tag.
Some members of Measures have a time balance tag (TB First or TB Last).

Figure 1-8 TBC Measures Dimension

This image shows member properties, such as consolidation operators and tags, in the TBC Measures dimension, as described in the text preceding the image.

Accounts Dimension Calculations

The Essbase dimension tagged as accounts can use special properties that impact calculations. Time balance tags or properties provide instructions to Essbase about how to calculate accounts that are time-period dependent. Variance reporting properties define how Essbase calculates the difference between actual and budget data.

This topic discusses two forms of calculations for a dimension tagged as accounts.

Time Balance Properties

To use time balance tags/properties, you need a dimension tagged as accounts and a dimension tagged as time. The first, last, average, and expense tags are available exclusively for use with accounts dimension members.

In the TBC Measures dimension, Opening Inventory data represents the inventory that TBC carries at the beginning of each month. The quarterly value for Opening Inventory equals the Opening value for the first month in the quarter. Opening Inventory requires the time balance tag, TB first.

Ending Inventory data represents the inventory that TBC carries at the end of each month. The quarterly value for Ending Inventory equals the ending value for the last month in the quarter. Ending Inventory requires the time balance tag, TB last. The following table defines the time balance tags for the accounts dimension.

Table 1-6 Accounts Member Tags

Tags	Description
Time Balance Last	The value for the last child member is carried to the parent. For example, March is carried to Qtr1.
Time Balance First	The value for the first child is carried to the parent. For example, Jan is carried to Qtr1.

In the following table, Qtr1 (second column from the right) and Year (right-most column) show how consolidation in the time dimension is affected by time balance properties in the accounts dimension. Data is shown for the first quarter only.

Table 1-7 TBC Consolidations Affected by Time Balance Properties

Dimensions	Jan	Feb	Mar	Qtr1	Year
Accounts Member1	11	12	13	36	Qtr1 + Qtr2 + Qtr3 + Qtr4
Accounts Member2 (TB First)	20	25	21	20	20
Accounts Member3 (TB Last)	25	21	30	30	Value of Qtr4

Normally, the calculation of a parent in the time dimension is based on the consolidation and formulas of children of the parent. However, if a member in an accounts branch is marked as TB First, any parent in the time dimension matches the member marked as TB First.

For examples, see Time Balance Properties.

Variance Reporting

One TBC Essbase requirement is the ability to perform variance reporting on actual versus budget data. The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag. Inventory members, Total Expense members, and the COGS member each receive an expense reporting tag for variance reporting.

Essbase provides two variance reporting properties—expense and nonexpense (default). Variance reporting properties define how Essbase calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.

When you tag a member as expense, the @VAR function calculates Budget – Actual. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is –10.

Without the expense reporting tag, the @VAR function calculates Actual – Budget. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is 10.

Formulas and Functions

Formulas calculate relationships between members in the Essbase outline. You can apply formulas to members, or in a calculation script. Functions are predefined routines that perform specialized calculations, returning sets of members or data values. Formulas comprise operators and functions, as well as dimension names, member names, and numeric constants.

Essbase supports the following operators:

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

The Essbase functions include more than 175 predefined routines to extend the calculation capabilities of Essbase. Essbase includes 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
Statistical functions, which calculate advanced statistics
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:

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 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.

Note:

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 Calculation Function List. Also see Develop Formulas for Block Storage Cubes.

Dynamic Calculations

When you design database calculations, you may want to define a member as a Dynamic Calc member. When you tag a member as Dynamic Calc, Essbase calculates the combinations of that member when you retrieve the data, instead of precalculating the member combinations during the regular database calculation.

Dynamic calculations shorten regular database calculation time, but may increase retrieval time for dynamically calculated data values.

In the following outline, the TBC Measures dimension contains several members tagged as Dynamic Calc—Profit, Margin, Total Expenses, Margin %, and Profit %.

Figure 1-9 TBC Measures Dimension, Dynamic Calc Tags

This image shows the members in the TBC Measures dimension that are tagged as Dynamic Calc, as described in the text preceding the image.

When an overall database calculation is performed, the Dynamic Calc members and their corresponding formulas are not calculated. These members are calculated when a user queries them, for example, from Smart View. Essbase does not store the queried values; it recalculates the values for every subsequent query.

To decide when to calculate data values dynamically, consider your priorities in the following areas:

Optimum regular calculation time (batch calculation)
Low disk space usage
Reduced database restructure time
Speedy data retrieval for users
Reduced backup time

See Dynamic Calculation of Data Values.

Two-Pass Calculations

The two-pass property works only on members of the Essbase dimension tagged as accounts and on members tagged as Dynamic Calc. This label indicates that some member formulas must be calculated twice to produce the desired value.

In the TBC database, Margin % and Profit % contain the label two-pass.

The following example illustrates why Profit % (based on the formula Profit % Sales) has a two-pass tag. The tables have five columns (column headers are labeled left to right as Dimension, Jan, Feb, Mar, and Qtr1) and three rows (labeled as Profit, Sales, and Profit %). Jan, Feb, Mar, and Qtr1 are members of the Year dimension. Profit, Sales, and Profit % are members of the Measures (accounts) dimension.

The following example defines the initial data to load into Essbase. The data values for Profit -> Jan, Profit -> Feb, and Profit -> Mar are 100. The data value for Sales -> Jan, Sales -> Feb, and Sales -> Mar are 1000.

Table 1-8 Data Loaded into Essbase

Dimension	Jan	Feb	Mar	Qtr1
Profit	100	100	100	N/A
Sales	1000	1000	1000	N/A
Profit %	N/A	N/A	N/A	N/A

First, Essbase calculates the Measures dimension. In the following table, the data values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar are 10%.

Table 1-9 Data After Essbase Calculates the Measures Dimension

Dimension	Jan	Feb	Mar	Qtr1
Profit	100	100	100
Sales	1000	1000	1000
Profit %	10%	10%	10%	N/A

Next, Essbase calculates the Year dimension. The data rolls up across the dimension. In the following table, the data values for Profit -> Qtr1 (300) and Sales -> Qtr1 (3000) are correct. The data value for Profit % -> Qtr1 (30%) is incorrect because Profit % is tagged as a two-pass calculation.

Table 1-10 Data After Essbase Calculates the Year Dimension

Dimension	Jan	Feb	Mar	Qtr1
Profit	100	100	100	300
Sales	1000	1000	1000	3000
Profit %	10%	10%	10%	30%

Essbase then recalculates profit percentage at each occurrence of the member Profit %. In the following table, the data value for Profit % -> Qtr1 (10%) is correct after the second pass.

Table 1-11 Data After Essbase Recalculates Profit Percentage

Dimension	Jan	Feb	Mar	Qtr1
Profit	100	100	100	300
Sales	1000	1000	1000	3000
Profit %	10%	10%	10%	10%

Checklist for Calculations

Use the following checklist when you design an Essbase block storage (BSO) calculation:

Does the default calculation logic achieve accurate results?
Which members require formulas?
Which members require time balance tags?
Which members require variance reporting?
Which members require two-pass calculation?
Which members can be tagged as Dynamic Calc?