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
| 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 | Select to make the calculated member not visible in reports |
| 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: https://msdn.microsoft.com/en-us/library/ms145970.aspx