cancel
Showing results for 
Search instead for 
Did you mean: 

Evaluate calculated KPIs in BW IP

Former Member
0 Kudos

Hi experts,

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.

Abdess,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Gregor,

Thank's a lot for your response.

Since the storage hardware has become less expensive than what it was, I think that storing maximum of information will decrease the requests execution time, Also that increasse the server performance because the requests execution uses less RAM space ...This is the first point.

The second point is that in the most cases we agregate KPIs and then we evaluate the calculated KPI. , but sometimes we have to evaluate a calculated KPI before doing agregation, in this case we must store the calculated KPI.

Let me explain, suppose we have 3 dimensions: Dim1, Dim2 and Dim3 and 3 KPIs: KPI1 and KPI2 and KPI3= KPI1 * KPI2

Dim1     Dim2     Dim3     KPI1     KPI2     KPI3

A1       B1          C1          10          5          50

A1       B2          C2          20          15        300

A1       B3          C3          30          25         750

If we calculate the KPI3 on the fly, using the Dim1=A1 we will get: (10+20+30)*(5+15+25) = 60*45=2700

But if we store in the InfoCube, we will have: 50+300+750 = 1100. It's defferent!

Now, I created a FOX to calculate a KPI, The issue I have is that when we execute the FOX, it is executed on the data stored in the DB...if this is true, so, each time I execute the FOX, it will traits all rows stored in the InfoCube, even those that are already traited (We can use filters select the data to be manipulated by the Fox ...)

What I m looking for is, when an end user writes/modifies some rows in the Excel sheet and clicks on the button save, the data being in the Excel gets manipulated by the Fox before it is stored in the target....This is what I dont understand how it can be done through BW IP fonctionnalities...

Thank's a lot for your help

Abdess,

0 Kudos

Hi Abdess,

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.

Regards,

Gregor

Former Member
0 Kudos

Thank's a lot Gregor ^^

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

About your first question, IP has a set of pre defined planning functions such as copy, delete etc.

In addition there is a FOX formula in IP where you can manipulate the data as you want (A+B/C*D etc.).

About your second question, aggregation level can be built over multi cube, which contains planning data as well as other cube with any other data, so you can access to such "side" data in planning function.

BR,

Former Member
0 Kudos

Hey Andery, thank's for your help.

For the second question it's clear.

But for the first question, I know that planning functions can be executed on the real time infoCube data to apply changes. what I look for is a fonctionnality that allow me to calculate a value for a given KPI when the user is saving data ....A function that can be executed on the data being saved into the target InfoCube

Like On SAP BPC, I can write some script logic on the InfoCube so that the data being saved are manipulated before they get into the target InfoCube ...

Thank's a lot for your help....

Former Member
0 Kudos

Hi,

That's exactly what planning functions do - manipulate the data before writing to the target real time cube.

You can create a sequence of manipulations, for example step 1 take the data from "side" cube and nultiply by 10, step 2 take the data from real time and divide by 10, step 3 add first dataset and second dataset, step 4 run over unified dataset and do something, step 5 save the data.

BR,

Former Member
0 Kudos

Hi Andrey,

I really have spent much time on that and I did not undestand how to manipulate data before it's stored in the real time InfoCube.

I went through all standard planning function and I did understand their fonctionnalities...And I executed them through an Excel workbook. but when I execute a planning function, it is executed on the data stored in the infoCube.

For example I created a formula function that evaluates a calculated KPI: Amount = Quantity * Price. When I executed it through Excel workbokk (Add planning function and use planning function ..), the function was applied on the data stored in the cube !

What I look for is to execute a planning function/Planning sequence that manipulates the data being writen in the excel workBook and save the result in the infoCube ...

A planning Sequence is a group of planning function that are executed sequentially, but thier manipulate the data that is already stored In the InfoCube ...

Could you giude me on that ?

Thank's a lot for your help.

Abdess,

0 Kudos

Hi Abdess,

why do you want to store KPIs on the DB? That something can be calculated indicates that this information is redundant and thus should not be stored on the DB. In addition, almost all non-trivial KPIs can not be aggregated, so storing these KPIs as basic key figures will lead to garbage if the corresponding delta records stored in an InfoCube will be aggregated.

If you come from BPC the concept comparison

http://scn.sap.com/docs/DOC-58899

might be helpful.

Just to calculate KPIs to have the values in a report do this in a BW query. For the 'Price' examples you can use inverse formulas: this allows you to change 'Prices' (formuals), but only the base key figures will be stored on the DB.

If you have some use cases where you need to store KPIs on the DB you should use a DSO; you can use FOX to calculate these KPIs. But by definition of 'store KPIs' you will get the values from the DB but you can recalculate the values in FOX. But here we come back to my initial statement, why to store redundant data if you can calculate the values on the fly?

Regards,

Gregor