IBM Cognos Planning - Improving Breakback Performance

This blog has been written to assist in increasing model performance where breakback calculations have an adverse affect on the processing time of an IBM Cognos Planning model in Analyst.

One of the big selling points of IBM Cognos Planning is the ability to apply breakback to efficiently update detailed data.  Breakback works by entering a value in a calculated cell, and then the formula is reverse engineered and the detail cells are updated based on previous data that has been entered into the cube.  The process is extremely powerful when applying profiling to nominal items.

Example:

In this scenario a 4-4-5 profile is required to allocate quarterly totals across a time dimension.

                                   Jan         Feb        Mar       Q1
Overhead                     4             4            5          13

In the above calculation the value of 13 is insignificant, except for the fact that is shows the sum of Jan, Feb and Mar.  Once the profile has been applied, the Q1 value can be updated to give a result illustrated below:

                                   Jan         Feb        Mar       Q1
Overhead                   462         462        576      1,500

This process can be used in all types of dimension where a calculation has been set, however, performance issues may exist if the calculations are complex or the dimension hierarchy is detailed.  Performance issues can be monitored by run the d-link that updates the d-cube.  If the breakback element that runs across the bottom of the screen seems to take an usually long time to update then the following technique may be used to increase model performance.

Forward Based Calculation to Perform Breakback Result

The forward based calculation technique avoids breakback calculation completely by adding a new calculation dimension to the d-cube.  This does have a sizing impact on the model, so this needs to be assessed before applying the technique.  The calculation should consist of a minimum of 4 items (5 if a weighting item is required) to perform the calculation.  The first step is to run a link to populate the basis of the detailed result profile e.g. last years sales [1].  The second link then populates the TOTAL of this base value [2].  The third link then imports the required value e.g. the value of the item at the total level [3].  The fourth item then calculate the item that performs the breakback result [3]*[1]/[2].

This technique does increase the cube size and increase the number of required links, however, the performance impact can be extremely effective.  An example where this technique was applied increased the performance of a d-cube update from 30 minutes to 2 minutes.  In this example, the cube size was 1.5 million cells and the breakback occurred on a products dimension that contained a complex hierarchy consisting of approx 400 products.  The breakback basis was to import the net sales value in detail (the profile) and the Cost of Sales was entered in Total Products and the calculation process was triggered.  The breakback was then performed over approx 100 calculations.  The new dimension added brought together the net sales and the COS totals to calculate the COS detail by product.

To simplify, it maybe worth thinking of the process in this way.  To perform the forward based calculation, only one calculation is being done in the new dimension.  When breakback was applied to the example above over 100 hierarchy calculations were being re-engineered.

In summary it is always worth checking to see if performance issues exist in Analyst models.  D-cube size is the biggest restraint in an IBM Cognos Planning model, although there are techniques to manage cube size.  It is always advisable to build the forward based calculation alternative in a test library to compare performance.  If performance is increased, develop the revised solution in the live model.