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.
Currently Report Builders can use existing Cube Calculations but can't create their own. This limits unnecessarily their access to the powerful tools provided by the system.
As asked by several clients, Report Builders should have the possibility to create Linked Cube Calculations.
Functional Requirements
The following table shows how the overall user story is subdivided into smaller sub user stories, which are detailed below.
# | Title | User Story | JIRA Story | MoSCoW * |
|---|---|---|---|---|
| 1 | Create & Manage List Cube Calculations | As a Report Builder I want to be able to create Cube Calculations connected to a specific list in order to be able to manage them | ||
| 2 | Create & Manage Report Cube Calculations | As a Report Builder I want to be able to create Cube Calculations connected to a specific report in order to be able to manage them | ||
| 3 | Promote a Linked Cube Calculation to Shared | As a Report Admin I want to promote a linked Cube Calculation to shared Cube Calculation in order to reuse it | ||
| 4 | Provide a permission for editing in MDX | As a Security Admin I want to prevent Report Builders who do not know MDX from editing in MDX in order to avoid possible issues | ||
| 5 | Prevent deleting elements used in Linked Cube Calculations | As a Report Admin I want to prevent deleting elements used in a Linked Cube Calculation to grant they work correctly |
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
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