IBM Planning Analytics’ Calculation Types
IBM Planning Analytics is well known for the speed with which it performs on-the-fly, on-demand aggregations, consolidations and other calculations.
The calculation of a specific number in a Planning Analytics cube is not performed until it is requested, for example by a DBR() formula in a spreadsheet, and when it is performed the answer is delivered very quickly – often instantaneously.
Most competing products have some concept of a batch pre-calculation, which means that if base level numbers change in a cube, end users will have to wait for a longer (minutes or hours) process to complete before calculated values are available.
Planning Analytics allows calculations to be defined in two different ways:
- Consolidations as part of a dimension definition (C: members)
- As rules calculations, associated with a cube
Dimension element consolidations can perform addition, subtraction and some multiplication; division cannot be done in a consolidation. Dimension calculations are more efficient than rule calculations. Rule of thumb: if the calculation can be done in the dimension with C level elements, do not use rules.
Objectives
We want to see the impact of rules on a cube. We will use the Financial Reporting cube and create a rule to calculate the Operating Income %. First, we will create a validation slice from the Financial Reporting cube to check the rule calculated results.
End Results:
Tasks
In order to create the Operating Income % rule, we will need to:
- Create a validation slice including the following dimensions:
- Version = Actual
- Products = All Products
- Finance Measures = Amount
- Region = All Regions
- Periods Months (columns) = Total CY2014 and all elements of the Total CY2014
- GL Accounts (rows) =
- Operating Income
- Net Revenues
- Operating Income %
- Create the Operating Income % rule (and add a comment):
[‘Operating Income %’]=[‘Operating Income’][‘Net Revenues’];
- Add a feeder:
Feeders;
[‘Net Revenues’]=>[‘Operating Income %’];
- Validate the rule
Step by Step
Create a validation slice.
- Expand Cubes and double click the Financial Reporting cube to open the Cube Viewer.
- Create and save the Operating Income % Rule Check view and recalculate (as shown below).
Create the Operating Income % rule.
- Right click on the Financial Reporting cube and choose Create Rule.
- On Line 1, type SKIPCHECK;.
- On Line 2 type, This rule will build the Operating Income % Rule for the Financial Reporting Cube.
- Highlight Line 2, right-click, select Advanced, and choose to Comment Selection.
- Click on the Brackets button () on the toolbar.
- Double click the Accounts dimension to open the Subset Editor for the Accounts
- Select the Operating Income %
- Click the Keep button () and then click OK.
- After the last bracket, type =. (Alternatively, use the Insert Qualifier/Operator button () on the toolbar).
- Click on the Brackets button ().
- Double click the Accounts dimension to open the Subset Editor.
- Select Operating Income and click OK.
- After the last bracket type .
- Click on the Brackets button ()
- Double click the Accounts dimension to open the Subset Editor.
- Select Net Revenues and click OK.
- After the Net Revenues end bracket, type ;. Your rule should look as follows:
Note: If you save the file without typing the feeder, go back to the validation view, make certain you are at a numeric level in every dimension, and click the recalculate icon. You will notice that there are values in Operating Income %. If you click the zero-suppression icon, the Operating Income % disappears. This is because at the consolidated levels, Planning Analytics does not know where there may be values other than zero for Operating Income %.
- On Line 5, type FEEDERS;.
- On Line 6, type [‘Net Revenues’]=>[‘Operating Income %’];.
- From the File menu, click Save.
- Open the Operating Income % Rule Check view and click the Recalculate button (). You should see the following results:
Demonstration Summary
In this demonstration, we accomplished the following:
- Created a validation report
- Created a rule to calculate the Operating Income %
- Added a feeder to the rule
- Validated the results of the calculation