Evaluate calculated KPIs in BW IP
I m new in BW Integrated planning and I have already worked on SAP BPC 10...So I have just some questions regarding BW-IP...
I m wondering what is the fonctionnality in BW-IP that allows to calculate values for calculated KPIs?
Let me explain.
Let's say I have an agregated level that contains KPI1, KPI2 and KPI3. The KPI3 = KPI1/KPI2. Through the BO analysis/BW Analzer the user specifies the value for KPI1 and KPI2. While saving the data I want to call some function to calculate the value for the KPI3 for every records...How can I acheive that with BW-IP ?
My second question is when I need some value from other InfoCubes to calculate the value for KPI3 while saving data in my InfoCube?
Thank's a lot form your help.
Gregor Dieckmann replied
I think your first point is a very 'formal' argument. To store redundant data causes a data consistency problem, i.e. in your example you have to ensure that for all DB records KPI3 = KPI1 * KPI2 is valid. This effort is much higher compared to the few CPU cycles needed for these type of on the fly calculations.
Your example in addition indicates that to have KPI3 on the DB has the disadvantage that in most cases the value of KPI3 has to be calculated after aggregation anyway. So having the values on the DB level at granularity of DIM1, DIM2, DIM3 has not advantage at all if you are interested in KPI3 values at granularity of DIM1 only. You have to aggregate KPI1, KPI2 with respect to DIM2, DIM3 and then calculate KPI3 = KPI1 * KPI2 (from much less records!).
So if you are just interested as an end user using Excel as a client how KPI3 changes based on KPI1, KPI2 at any granularity simply create a BW query where KPI3 is a formula. Everything then runs out of the box, you need no planning functions for this. You can even make KPI3 input ready, change the value for KPI3 and calculate back either to KPI1 or KPI2.
If you still have other use cases where you need a redundant KPI on the DB level for 'multiplicative' KPIs (contain multiplication, division) it only makes sense to compute these KPIs for storage on the lowest level. A DataStore-Object (DSO) to store such a KPI should be used, it has after image logic not delta logic as in the InfoCube case. A planning enabled DSO also supports non-aggregatable key figures (prices are an example). You may use a planning function to do this calculation. If you don't want to do the computation twice please be aware of the fact that the function can be run at any time, so the function should do the right thing when executed several times. In the user session you can 'feed' the function based on the changed records, but this only solves this problem in the user session. You can also run a planning function 'before save'.
But as I said, in most cases you don't need this since the BW query gives you all features you need out of the box.