BW730: Input ready formulas in BW Integrated Planning

Abstract

The following article explains how to model and use input ready formulas in BW Integrated Planning.

Motivation

One main target of BW Integrated Planning is to use and extend BW objects with functionality needed to model Business Planning applications. Besides the concept of planning functions (introduced in Business Planning and Simulation SEM-BPS/BW-BPS or even CO-PA) Integrated Planning allows to use BW queries for manual planning.

In input ready queries – via aggregation levels – one can plan basic key figures (i.e. key figures contained in real-time InfoCubes). It is also possible to plan on any level in queries when disaggregation (automatic top down distribution) is used.

On the other hand a lot of key figures relevant for business planners are formulas. In fact, these types of key figures often are used to set targets in related planning applications or even ‘drive’ the planning process. Simple examples are ‘percent value of total sales’, ‘contribution margin’, ‘percent deviation plan versus actual’ and all kinds of calculations with ‘prices’ (e.g. the ratio of revenue and sales quantity).

The key figures being calculated, a planner usually changes basic key figure values and – after recalculation – checks whether the results are ok with respect to the given targets. To make this ‘simulation’ much easier it is natural to allow some formulas to be input ready. Then the planner can change also results of formula calculations to update basic key figures.

The new concept to support this is called ‘input ready formula’. As usual formulas are defined in the Query Designer. In input ready queries it is now possible to mark a formula as input ready. In this case one has to model how the system has to do the ‘backwards’ calculation. These formulas are called ‘inverse formulas’. In general there exists more than one inversion, so a formula priority is needed to tell the system the inversion to use.

At run time of the query changed formula values will be calculated backwards to input ready basic key figures. Of course, this can be combined with disaggregation. Let’s elaborate this a little more in the following example.

Planning Average Prices: Modeling

Data model:

A real-time InfoCube contains the characteristics ‘Product’, ‘Product Group’, ‘Product Line’, ‘Fiscal Year Period’ and a version characteristic. The key figures being used in the InfoCube are ‘Sales’ and ‘Sales Quantity’.

Query:

The query uses all product related characteristics in the rows, the basic key figures in the data columns. In addition, the query displays aggregated values for all periods of a fiscal year. The ‘Avg. Price’ is modeled as the formula:

The NDIV0() data function is used to ignore division by 0 errors. In the Query Designer the basic key figures ‘Sales’ and ‘Sales Unit’ are marked as input ready. Both key figures use the setting ‘disaggregation with self-reference’ to allow top down distribution of changed values on the fly. Now it is also possible to mark the formula ‘Avg. Price’ as input ready and to define the inverse formulas for backwards calculation:

The formula priority is derived from the order of the list of inverse formulas, i.e. the first inverse formula has the highest priority.

The following screen shot shows how the result of this modeling may look like:

Planning Average Prices: At Run Time

At run time of the query the system will read data from the real-time InfoCube, aggregate the data as needed and calculate the formula values for ‘Avg. Price’. All cells in the result set of the query are input ready and can be changed.

As an aid to control changes in the result set it is possible to fix cell values in BW Web Templates. Assume that one cell value for ‘Sales’ is fixed on the product level and ‘Avg. Price’ was changed on the first subtotal. Then the following steps will be processed:

• The changed value for ‘Avg. Price’ on the subtotal triggers a backwards calculation. There are two options to recalculate ‘Sales’ or ‘Sales Unit’. The first key figure has the higher priority, so ‘Sales’ will be calculated on the subtotal level.
• The changed value of ‘Sales’ triggers a disaggregation. Since one cell contributing to the changed subtotal of ‘Sales’ was fixed by the planner, disaggregation must not change the fixed value but only the other cells contributing to the subtotal.
• As usual in Integrated Planning the changed values of the basic key figures will be collected in the delta buffer and all queries interested in new buffer values will be refreshed. In our case this is only the input ready query used in the BW Web Template.

The following screen shots illustrate these steps:

The context menu of the cell allows locking the data cell; this indicates that the cell value should be fixed. It is also possible to lock all cells in a row or column using the context menu of the row or column, respectively.

The lock is indicated by the usual lock icon. The first subtotal for ‘Avg. Price’ will be changed from 500 to 600 (manual change).

The inverse formula calculation changed ‘Sales’ to 18000. The disaggregation distributed the new value to the products ‘PC Small’ and ‘PC High End’ since the value 5000 for ‘PC Medium’ is fixed and must not be changed.

Of course, one can also unlock cells using the context menu.

This blog can give you only a first impression of the functionality described here. A much more detailed description about the available functionality can be found in note 1236347 (in an attached document).

The functionality described here is also available in BW 7.01, support package 05. To make one familiar with input ready formulas start with simple scenarios like the example given above.

Disclaimer

http://www.sdn.sap.com/irj/sdn/index?rid=/webcontent/uuid/b0b72642-0fd9-2d10-38a9-c57db30b522e