Introduction

Cube Calculations are a powerful way to enrich data and are used in CXO-Cockpit when:

The calculation is not done in the source system
In some organizations, adding a calculation in the source system can be a very cumbersome or time consuming tasks, due to for example compliance rules.
The calculation can’t be done in the source system.
In some underlying systems (for example HFM) the calculation options are limited (such as dynamic rules in HFM) which can’t deal with more complex or advanced rules
The calculation can be done more easily or with less maintenance in CXO-Cockpit. In CXO-Cockpit we have the most powerful and maintenance friendly language (MDX) available.

CONTENT

Cube Calculations

What are Cube Calculations Technically?

Cube Calculations are Performed in the OLAP Cube
Calculations are made dynamically when a report is opened
Cube Calculations are written in MDX
The MDX calculation is applied at the moment of executing the query

Attributes

Basic Attributes

Attribute	Mandatory	Description
Dimension	Yes	Technical Dimension Name
Cockpit Dimension	Yes	The Cockpit name of the Dimension
Name	Yes	Technical Name of Calculated Member
Calculation	Yes	Actual Calculation. Written in MDX (Press Button to Edit Code)
Language 1	No	Description of Calculated Member
Account Type	No	What kind of account (Revenue? Expense? Asset? or Liability?)
Comment	No	Textual comment on Calculated member (for Administrators)

Advanced Attributes

Attribute	Mandatory	Description
Calculation Order	No	Solve Order of calculations, lower numbers are calculated first and are available for use in later calculations note: this attribute is not applicable for Hyperion Financial Management (HFM) applications
Language 2	No	Description of Calculated Member in second language
Language 3	No	Description of Calculated Member in third language
Visible	Yes	Select to make the calculated member visible in the Point of View?
Parent	No	Enter Parent of Calculated Member
Member Order	No	Used for sorting within the Point of View
Times Used	Yes	Displays how many times the calculated member is used (Press button to view where used)

Use of Variables

Variables as defined in Maintain Variables, @DIM_... (e.g. @YER_Current) and the @@CUR system variables are allowed within the MDX statement and will be evaluated at run time.

The placeholder for either of the variables is: << .... >>

for example

@DIM Variables

CASE WHEN
[YER].[YER].CurrentMember.Name = ‘<<@YER_Current>>’
THEN
...

Descendants ([ENT].[ENT].[<<@ENT_Top>>], [ENT].[ENT].[Level 02])

@@CUR Variables

CASE WHEN
[PER].[PER].Currentmember.Name = ‘<<@@CUR(PER)>>’
THEN
…

Note: too indicate the content of @@CUR , specify the Dimension name in Brackets (PER) , @@CUR will equate to either the dimension member specified in the POV or in the Report Dimensions

Splited Dimensions

In the case of a dimension split , the << ... >> placeholder must contain a comma-seperated list of variables from all of the dimensions included in the split.

for example (Essbase Model)

In the case of a Time dimension split into Year and Month then the placeholder should be entered as

CASE WHEN

IS ([Time].CurrentMember, [<<@@CUR(YER), @PER_Current>>])
THEN
…

Note the order of the dimensions is not important

Best Practices

Examples of cube calculations that are often used

A rolling 12 month total for profit and loss items.
With cube calculations we can set this up in a very dynamic way
A profit and loss as a percentage of Sales or a profit and loss per FTE
We can calculate these percentages for all lines in the profit and loss in one formula
A Year-on-Year growth percentage

Examples of all of these calculations are available in reports in our on-line demo environment.

In addition, the cube calculations are also often used for simple calculations such as the sum of accounts or entities.

For an overview of supported MDX functions check the Microsoft website: http://msdn.microsoft.com/en-us/library/ms145970.aspx