Introduction
Working with Integrated Planning is about supporting the planning process with a common plan database and with planning functions that accelerates the work of the planners. Until now we had many options to define planning functions and for the complex ones we could use Formula Extensions - shortly FOX - or ABAP. Starting the version SAP Netweaver 7.3 we have another tool in our toolbox, called Inverse Formula that we can define in the Query Designer.
The first time when a new tool comes into the system where I do my developments, I have enthousiasm and also many questions arise in my mind as follows:
- FOX and Inverse Formula are both tools for defining formula in Planning. Is the Inverse Formula a replacement of FOX ?
- Should I feel guilty that I still use FOX instead of Inverse Formula ?
- Can I use them both ?
- If yes, which tool should I use in which scenario ?
- ....and so on, and so on
Having such questions that I can't answer immediately, the best I can do to jump into the system, to try them both in a simple scenario and to evaluate the results. This post is about this trial and evaluation.
The simple sales example
For testing formula tools, we need a business formula. In sales planning everybody has the scenario for calculating the sales:
Sales = Average Price * Quantity
In planning we might have the following sales plan scenarios:
- we change the Price and Quantity and we want the system to calculate the Sales
- we define the target Sales and Price and we want the system to calculate the Quantity
- or we define the target Sales and Quantity and want to calculate the expected Price
A planner might want to use all of the scenarios on a planning layout.
Let's solve this problem first with FOX.
Using FOX
I have prepared a data flow for our example: we need a Real Time Infocube, a Multiprovider and an Aggregation Level.
Note: We don't need a Multiprovider in order to make this example work. I use Multiproviders because they give great flexibility if we need to change the database structures, like exchange to another infocube or to split the infocube to multiple infocubes. We can do this without rebuilding the Planning Layers from scratch.
I used the same structure for all of the structures for the sake of the simplicity. Here I only list the Key Figures needed, you can use any characteristic you want, this will not change the results.
Infoprovider Structures (minimum required key figures)
| Description |
Technical Name |
| Sales |
ZSALES |
| Quantity |
ZQUANTITY |
| Price |
ZPRICE |
In order to support the scenarios mentioned above, we need to create 3 separate FOX:
{ ZSALES } = { ZPRICE } * { ZQUANTITY } .
IF { ZPRICE } <> 0.
{ ZQUANTITY } = { ZSALES } / { ZPRICE } .
ENDIF.
IF { ZQUANTITY } <> 0.
{ ZPRICE } = { ZSALES } / { ZQUANTITY } .
ENDIF.
Summary for FOX formulas
As a summary, we need to create 3 separate key figures and place them into the structures in order that we can use them in a FOX formula. In addition, if we want to support the 3 planning scenarios, we we need to create 3 separate FOX formulas. These formulas can be used later on the user interface as different buttons.
Using Inverse Formula
Inverse formulas can be prepared in an Input Ready Query, so we need to create one on our Aggregation Level. We can use the same database with the prepared structures.
Creating an Inverse Formula is similar to create a simple formula. Now I am going to create the formula for the Price like a normal Calculated Key Figure, so we need to prepare a key figure structure with the Sales and the Quantity for our calculation.
The formula for the Price looks like on the following picture.
Here I want to mention the usage of NDIV0(). Since we divide the sales by quantity, it might happen that the quantity is zero. NDIV0() is useful for avoiding formula errors in cases of division by zero and it's necessary also for the Inverse Formulas.
We created a normal Calculated Key Figure until now, but how to prepare it as an Inverse Formula ? The prerequisite is that the operands in the formula and also the formula itself (!) should be set as Input Ready. You can achieve it easily by selecting all the Key Figures in your Key Figure Structure and by choosing the Input-Ready (Relevant For Locking) option on the Planning Tab.
As you can see, the system generates 2 empty Inverse Formulas linked to Price: Sales (Inverse formula), Quantity (Inverse formula).
We need to define these formulas because the system allows you to change the Price, the Sales and the Quantity as well in the query and needs a calculation for the other 2 operands in order the have the expected result after the change. So let's define the Sales and the Quantity.
Important Note: Both inverse formulas should use the operands that you used in the basic formula.
Polite formulas = Priority
The Price, Sales and Quantity now are part of a Formula Group.
There are some questions to answer now:
- How does the system know which formula to calculate ?
- What happens if the user changes all of the values in the query ?
- How does the system avoid errors or misbehavior ?
The answer lies in the Formula Priority. In the Query Designer, you can set the order of the calculation of the formulas with priorities. The priority is important in cases when the user changes more operands at the same time.
Let's see how to set this priority.
You need to highlight the main formula, Price in my case and you can set the priority order in the Planning Tab. As you can see, the Sales has higher priority than Quantity, but don't forget the Price! The Price has always the highest priority!
So if I change the Price, then the system is going to recalculate the Sales. If I change the Sales or the Quantity then the system will recalculate the Price since it has the highest priority.
This calculation mode is called asymmetric, because the Price is set first as a base formula. If you want to override this behavior and you want to set another priority order for the Price, you can switch to symmetric calculation mode in Query Properties, Planning Tab as you can see below.
Summary for Inverse Formulas
As a summary, we need to create only 2 separate key figures (Sales, Quantity) and place them into the structures in order that we can use them in an Inverse Formula. We need to define 3 calculations in the Query Designer as well, but we don't need to create additional functions, the Input Ready Query will support 3 planning scenarios. You can change any values in the Query, the system is going to calculate the operands based on the Formula Priority. One important thing to mention, that we can't store the basic formula, in our case the Price in the database with Inverse Formula. If you don't need the Price, just for planning simulation that's fine, if you need to store it, you will need to create FOX formula as well.
Conclusion
After I prepared this example, I had the impression that the Inverse Formula increases the flexibility in my development work, because I don't need to prepare a basic key figure in the database just for doing planning simulations.
From the user point of view, using too much formulas in an Input Ready Query, might be confusing since the Formula Calculation Priority is not obvious for the user. In this case, I would prefer using FOX and command buttons on the planning layout, because it's self explanatory. And also, if you want to store the result of the basic formula (Price) then I found easier to use a FOX calculation.
Answering my questions based on this small example, I don't think that the Inverse Formula is the replacement of the FOX, but the opposite, it's an enhancement that increases my flexibility. My responsibility as a developer is to use both tools in a way that the planning users find them rather helpful than confusing. I will definitely use them both, FOX for database operations, for defining command buttons and for efficient background processing. Inverse Formula is helpful for flexible simulations and for introducing new virtual key figures (like Price) without the need to create them always in the database.
Dear Reader, what is your opinion and experience ? Write me a comment for further discussions.