BW730: Input ready formulas in BW Integrated Planning
The following article explains how to model and use input ready formulas in BW Integrated Planning.
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
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’.
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:
|‘Avg. Price’ = NDIV0( ‘Sales’ / ‘Sales Unit’ )|
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:
|‘Sales’ = ‘Avg. Price’ * ‘Sales Unit’|
|‘Sales Unit’ = NDIV0(‘Sales’ / ‘Avg. Price’ )|
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.
there are rumors in the market that IP is dead.
Now it looks like that there is movement in the area of IP.
What will be the future direction of integrated planning?
All the best,
BW-IP was always part of the planning roadmap of SAP and continues to be. As such investments into BW-IP development continued.
To outline the future direction of BW-IP we are currently working on an updated roadmap that we plan to release soon.
is this updated roadmap for BW-IP already available? Where can I find it?
i am having issues with values for two fields after Uploading files and refreshed the data,
System transposes the the values of the two fields to each other.
e.g before the download lets say field A- 1.000 and field B- 2.000, After the upload and refresh the Data the value Transposes to A- 2.000 while B- 1.000. please what might cause the problem and possible way to fix this.
My email is firstname.lastname@example.org.
I don't understand what 'inverse formulas' have to do with effects you observe 'uploading files'. I don't know what kind of 'upload' your are using but I would check the file and the 'mapping' of the file columns to the InfoObjects in BW, maybe this is simply wrong. But this has nothing to do with inverse formulas.
I am having issues - a have two forms input ready.
In form 2 there is a price (input-ready formula), sales volume, sales.
Sales change in form 1, the price should be of low priority (only input by the manager himself, not calculation).
The process - manager input sales, then goes into form 2, input a price, system calculates "on the fly" volume and when saving data, save in the database.
Then at some point, the manager can correct sales in form 1, and open form 2 the price should not change, the volume should calculate and be preserved in the system.
I used the symmetric method of calculation, but the price is still calculated first when the form is opened, Although I changed priorities, for the price is the lowest.
How to solve it?
Or is this a problem that can not be solved?
I would be grateful for any help.
a query contains two kind of formuals
The formula priority is a query local setting, i.e. changing sales in form 1 will be send to the planning buffer and the read in form 2 and because of 1 the reporting formulas will update price.
I see two options:
The second option is more work, e.g. when input on sub-total should be possible. With planning functions price also has to be a base key figure, i.e. it has to be stored on DB (one should use a DSO or advanced DSO for this kind on non-aggregating key figure).
Maybe one can also use versions to split initial sales planning and the adjustment; but in any case the adjustment has to be done in a form that includes sales, sales volume and price.