For many customers budgeting is a key technique in their planning process. Usually a (top down) budgeting process comprises the action of setting a fixed budget (usually done by manager) and an action where this budget is spent of further attributes to different entities.
In this blog post we want to show how we can easily realize a budgeting process in SAP NetWeaver BusinessObjects Planning and Consolidation 10.1 embedded. BPC embedded relies on the data model of the underlying BW planning (PAK or BW-IP) and for setting up our budgeting example we will rely on the features provided by aggregation levels. Thus we will use this example also to introduce the concept of aggregation levels, will show the advantages of this concept, and will explain why we need them in certain scenarios.
Let us assume we do a simplified profit center planning and have two planners. We have the manager who is responsible to set a budget for all profit centers (here we omit additional dimensions). And we have a person who is responsible to manually distribute the budget to the different profit centers.
In our simplified example we use an InfoCube (or cube-like aDSO) that contains the three dimensions year, version, and profit center and a key figure (=measure) amount.
Let us start with the manager. The manager should enter a budget for a certain year and a certain version (we will need the version for technical reasons). The data should NOT be entered by profit center. Thus we create an aggregation level that contains the year, the version, and the key figure amount (ALVL_1).
Let us assume the manager enters some data (in a query defined on ALVL_1)
Year |
Version |
Amount |
2017 |
Budget |
1000 |
Now we have a look at the planner. The planner should enter amounts for the year, the profit center, and the version. Thus we need an aggregation level containing all the dimensions and the key figure (ALVL_2).
Let the planner start a query. This is what will be displayed:
Year |
Version |
ProfitCenter |
Amount |
2017 |
Budget |
# |
1000 |
We need a little explanation here why the profit center is ‘#’. The manager has entered data on an aggregation level that does not contain the profit center. But the underlying InfoProvider does contain it and so does the data base table holding the data. And in our setup each dimension is a key field in the data base. So when writing back to the data base all key fields must be filled. As the profit center is not known the data is posted against an empty (blank but not ‘null’) profit center which is displayed as ‘#’.
Now we assume the planner also displays the total and enters some data against actual profit centers:
Year |
Version |
ProfitCenter |
Amount |
2017 |
Budget |
PC1 |
200 |
2017 |
Budget |
PC2 |
500 |
2017 |
Budget |
# |
1000 |
2017 |
Budget |
TOTAL |
1700 |
As we can see the total now is above the budget (1700). Which is not what is intended in our budgeting process where we have set a fixed budget…
In order to analyze the situation a little further let us examine how the manager now sees the data on his level of detail (aggregation level ALVL_1).
Year |
Version |
Amount |
2017 |
Budget |
1700 |
In his aggregation level no profit center is used so the data is automatically added up over all profit centers – including the ‘dummy’ profit center ‘#’.
Now assume that the manager corrects the wrong total budget of 1700 and enter 1000 again. How is the data stored on the data base level (or in our case ALVL_2)?
As the manager has no influence on the single profit centers the correction should not change the data the profit center planner has entered. But the changed number must be stored somewhere. And as we again do not know against which profit center the changed data should be stored the number will be stored against the profit center ‘#’ (as above).
Now as the total is fixed to 1000 the data must look like this (also displaying the total):
Year |
Version |
ProfitCenter |
Amount |
2017 |
Budget |
PC1 |
200 |
2017 |
Budget |
PC2 |
500 |
2017 |
Budget |
# |
300 |
2017 |
Budget |
TOTAL |
1000 |
Now the correct budget is set again and the booked values on the profit center have been preserved.
In addition we see that the profit center ‘#’ can also be interpreted in another way. It holds the ‘rest’ of the budget that has not yet been distributed to the different profit centers and thus is not just a technical vehicle but also has a business meaning.
Remark: One could argue that using ‘#’ is not necessary and one could simply disaggregate the changes to the various profit centers. This is actually also possible with BPC Embedded but would be wrong in our case. The manager is NOT responsible for the single profit centers (only the profit centers planners are) and thus should NOT change the values on the profit centers – which would be the case if we just disaggregate (evenly or by some ratio) the changes.
Thus using the different aggregation levels gives us the opportunity to control where information about the changes is kept – either changes will be forced down to the lowest level in the InfoProvider (automatic disaggregation) or the changes will be kept as ‘rests’ on different level and the user can decide to either manually distribute the data (like our profit center planner) or run a defined planning function (pre-defined type distribution function) to do so.
Let us note three rules that we have learned from the scenario above:
Rule 1: Aggregation levels are used to determine on what level of detail data is displayed or entered into the system.
Rule 2: If a dimension is not contained in an aggregation level then the system aggregates the data over this dimension (in our case: profit center)
Rule 3: If data is changed in an aggregation level that does not use a certain dimension then the deltas will be posted against ‘#’.
We have explained now how two aggregation levels interact and how a budgeting process can be realized – unfortunately there is still one short coming. In the above setup the manager would have to enter the budget over and over again – any time a profit center planner has changed the value for his/her profit center and so the total budget is changed. Obviously this is not a good solution. Here is how we solve the problem:
We create two planning functions in the system:
- A planning function that is defined on the manager’s aggregation level (ALVL_1) and copies the data to a second version (say ‘saved_budget’).
- A planning function that is defined on the same aggregation level and copies the data from the second version (saved_budget) back to the main version.
All we have to make sure now is that the first planning function is run every time when the manager changes the budget. The second planning function must run every time a user changes the value for a profit center. In doing so we simulate the above procedure where the manager overtypes the budget with the original value again.
One way of making sure the planning functions run is to create a button ‘set budget’ in the application for the manager. The button runs the first copy function and saves the data.
In the planning application for the profit center planner we can have a button ‘check budget’ that runs the second planning function and saves the data. Alternatively one could run this planning function automatically on saving the data (option on the planning enabled InfoProvider).
The above scenario also can handle the case that a user enters such a high number that the budget is exceeded. In this case the value on ‘#’ would turn negative. By using a fox formula that just checks whether the value on ‘#’ is not-negative the user can be informed that he has gone over budget and if necessary the saving of the data can be forbidden.
