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

When you select to manage the Cube Calculations, the list of all avaliable Cube Calculations will be listed. From the list, you can see the Name, Display Name, Source System and Dimension of all of them.

At the right  side of the screen, you may see the all the details of the Cube Calclutation that you will select. Additionally, here, you may click on the Edit button which will open the Change Cube calculation Screen from where you can change attributes.

Filtering

You can also filter the list by the same mentioned criteria: typing the Name, typing the Display Name, choosing one of the avaliable Source Systems or selecting one from the Dimensions.

Options

The actions that can be performed on a list by clicking on the Settings Button next to the Dimension column are Copy and Delete.  Copy will Copy the list by adding (Copy) at the end of the Name. Delete is for Deleting the Cube Calculation.

Attributes

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.

Considerations around cube calculations and performance - see examples

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