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.

  1. Expand Cubes and double click the Financial Reporting cube to open the Cube Viewer.
  2. Create and save the Operating Income % Rule Check view and recalculate (as shown below).

Create the Operating Income % rule.

  1. Right click on the Financial Reporting cube and choose Create Rule.

  1. On Line 1, type SKIPCHECK;.
  2. On Line 2 type, This rule will build the Operating Income % Rule for the Financial Reporting Cube.
  3. Highlight Line 2, right-click, select Advanced, and choose to Comment Selection.

  1. Click on the Brackets button () on the toolbar.
  2. Double click the Accounts dimension to open the Subset Editor for the Accounts

  1. Select the Operating Income %
  2. Click the Keep button () and then click OK.

  1. After the last bracket, type =. (Alternatively, use the Insert Qualifier/Operator button () on the toolbar).
  2. Click on the Brackets button ().
  3. Double click the Accounts dimension to open the Subset Editor.
  4. Select Operating Income and click OK.

  1. After the last bracket type .
  2. Click on the Brackets button ()
  3. Double click the Accounts dimension to open the Subset Editor.
  4. Select Net Revenues and click OK.

  1. 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 %.

  1. On Line 5, type FEEDERS;.
  2. On Line 6, type [‘Net Revenues’]=>[‘Operating Income %’];.

  1. From the File menu, click Save.
  2. 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