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

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

View

Attributes

Attribute	Mandatory	Description
Name	Yes	Technical Name of Calculated Member
Display Name	No	Display Name of the Calculation
Source System	Yes	Sourse System of the used Dimensions
Dimension	Yes	Technical Dimension Name
Account Type	No	What kind of account (Revenue? Expense? Asset? or Liability?)
Hide From Reports	No	lick
Parent	No	Enter Parent of Calculated Member
MDX Specification	Yes	Actual Calculation. Written in MDX

Create new Cube Calculation

By clicking on + New Cube Calculation button the New Cube Calculation Window is opened. Here, the Name, Source System, Dimension, and MDX Specification are mandatory and Save button is enabled after these fields are filled in.

Additionally, you can check the MDX Set Expression Syntax by Clicking on the Check Button which will bring up a new window at the top of the New Cube Calculatioin Window. There is also a Preview option right next to the Check button.

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