BPC KPI

[Former Member]

Required help to Generate KPI formulas using script logic .

Please share me logic to build following scenarios .

Example .

1. Commissions/ Pf opex =     ([costcenter] : BAS(Account1) / ([costcenter] : BAS(account2))

2.  opex % revenue =    ([costcenter] : BAS(Account4) / ([costcenter.ALL_DEPT] : BAS(account6))

[Former Member · Accepted solution]

Dimension member formula's are best option to create KPI's . created around 70 KpI's  and we did not see any issue with performance in 10.1 NW version . Please follow with new thread for dimension member formulas creation . http://scn.sap.com/thread/3845653

[Former Member]

Hi Satya, is recommended to use script for 70 KPIs, but remember: 1. Perform in financial closing after validations of financial control 2. Before to execute a summarized script to calculate a kpi (as you previously wrote) must use XMEMBER properly in order to use correct cross-section data. 3. If you try to use a calculated member formula in your script logic, do not expect values. 4. Define the base members of any KPI hope this help, let me know, daniel

[former_member186338]

And how you will avoid incorrect KPI sum on parent members?

[Former Member]

Using a KPI tree or structure different to usual

[former_member186338]

Sorry, you don't understand me!

Example:

KPI - NetIncome/NetSales

You calculate it using script for each month:

Jan   Feb ...  Dec  TOTAL

15% 17% ... 14%   160%

Strange TOTAL! Same for any parent...

Vadim

[Former Member]

Vadim, in these case you are talking about KPI monthly basis? if that's the case the TOTAL is OK for each month and must named as monthly. If you are referring to a Total Period KPI, then must use directly TOTAL_PERIOD as base member (annual/3 years/etc). If you are mixing monthly basis with Total basis, then KPI Avg by month use a global var and assign to KPI account from JAN to DEC Note KPIs are not simple ratios in structure, must be created with a logical structure base behind Hope can help, daniel

[former_member186338]

Sorry, Daniel!

I do not understand what "magic" structures you are talking about, what do you mean by "monthly" KPI's.

I am talking about absolutely simple report with accounts in rows and periods in columns. And periods includes Total of the year.

In this report some accounts are normal accounts like NetIncome or NetSales. And finance people also want to have KPI's in the same column. If you calculate and store KPI's using script logic or badi you will see the correct values only on base members for all dimensions and useless figures in Total and other parents.

Hope it's clear.

Vadim

[Former Member]

Hi Daniel .

I try to write a script  as I mentioned above , but BAS is not accepting  when I try to use nodes.

Is there any way we can call Nodes ? my main gold is to generate KPI's for monthly basis for different hierarchy nodes.

please share me if you have any sample's.

Thanks,

Bala.

[former_member186338]

You can't write KPI's to nodes!

[Former Member]

Hi SATYA, sorry for delay (work). I will try to explain the best I can:

1. BAS is better performer than MDX (member formulas) but is not a very high performance method and is not good to maintenance.

2. In a structure (ex. account tree), in any node can not be stored some value because BPC uses to calculate child nodes with SUM, AVG,..as was defined.Look picture.

3. KPI's can be defined as simple (a/b) or complex (avg(a,12) x a/end(b)). If you want to use a simple KPI, and the variables of it (a,b) are in the main structure (look pict) then is better to use member formulas because is a simple reporting level method. Notes than member formulas are not good choice for complex KPI's because affects performance .

4. When you try to make a very simple KPI for only one report, you can use Excel EPM reporting formulas. (Ex. Only local members or excel formulas). This can be used for % variances or any other as Fx variance.

Conclusion

In your case, I would use member formula (MDX). For example: B2=B1/D2, depending on cube structure. There are more ways to work with but I think this can help you. Let me see your structure if you need more help.

regards!

Daniel

[Former Member]

Hi Daniel/ Vadim ,

I will agree with you suggestions but i have a requirement  to complete KPI's using Script logic .

I am working on KPI with script logic  following approach . 

step 1. All node level values are pushing into Baselevel  ( Stat Account )

step2. using STAT accounts KPI's are caluculated.

hope this will be an good idea to generate KPIs on baselevels.

thanks

[former_member186338]

Ups...

"BAS is better performer than MDX" - what do you mean by BAS in this case

"BPC uses to calculate child nodes with SUM, AVG" - BPC is not using AVG to calculate nodes, aggregation is alway done as SUM and standard measure formulas use some other function, but not AVG.

There is no big difference between complex and simple KPI's, both are slow . You have to test always to see if the particular report performance is acceptable or not. Sometimes to calculate KPI you will need to create extra members calculated with script or badi like here:

And yes, Excel formulas is always an option

Vadim

[former_member186338]

Sorry, but you are going in the wrong direction. It's absolutely not a good idea to generate KPIs on baselevels. But I can't help...

[Former Member]

check this sample and try to use it for your requirement...

Sub Test(monthh)

*XDIM_MEMBERSET Account= Account1,Account2, Commissions/ Pf opex

*XDIM_MEMBERSET Category= %CATEGORY_SET%

*XDIM_MEMBERSET DATASRC=INPUT            

*XDIM_MEMBERSET RPTCURRENCY=USD  

And any other dimension..

*WHEN TIME.PERIOD     

*IS Monthh

*REC(Account=" Commissions/ Pf opex ",EXPRESSION=(%VALUE%*GET(Account ="Account1"))/GET(Account="Account2"))

*ENDWHEN

*COMMIT

End sub

you can use %FOR NEXT loop too regards!

[Former Member]

sorry for late... Vadlim, a. BAS as badi, b. Chield nodes are sum of (end of periods or averages or any other as units) c. for sure are slow, depends of requirement d. agree regards and try help me to help Satya. I put an example. daniel

[Former Member]

Hi Vadim/ Daniel ,

I try to convince here to use dimension member formulas instead  of using Script logic.

they were in feeling, dimension formulas will be causing performance issue.

May i know what are all pro and cons of using dimension formulas instead of script logic ? 

Is there any performance improvement for latest version 10.1 to use dimension formulas ?

Thanks

[Former Member]

Hi Daniel ,

When i try to use GET function is not working , GET will work in NW version ?  I am using NW 10.1 

Thanks,

[former_member186338]

GET is from BPC MS...

[Former Member]

HI Vadim ,

Below I am illustrated my requirements to generate KPI's with combination of account and costcenter.

could be please guide me how to setup dimension formulas for following  requirements.

Challenging here is how to populate denominator with cost center Hierarchy ALL_DEPT ? 

COSTCENTER / ACCOUNT Hierarchies

ACCOUNT HIER	COSTCENTER HIER
BONUS	ALL_DEPT
AC1	CC1
AC2	CC2
REV
AC3
AC4

Transaction Data.

Account	Costcenter	TIME	CATEGORY	AMOUNT
AC1	CC1	2015.01	ACTUAL	100
AC2	CC2	2015.01	ACTUAL	200
AC3	CC1	2015.01	ACTUAL	300
AC4	CC2	2015.01	ACTUAL	400

KPI's list.

RESULT
KPI_1	ACCOUNT.BONUS,COSTCENTER.CC1/ACCOUNT.REV.COSTCENTER.CC1	100/300	0.333333333
KPI_2	ACCOUNT.BONUS,COSTCENTER.CC2/ACCOUNT.REV.COSTCENTER.CC2	200/400	0.5
KPI_3	ACCOUNT.BONUS,COSTCENTER.CC1/ACCOUNT.REV.COSTCENTER.ALL_DEPT	100/700	0.142857143
KPI_4	ACCOUNT.BONUS,COSTCENTER.CC2/ACCOUNT.REV.COSTCENTER.ALL_DEPT	200/700	0.285714286

[Former Member]

Happy new year all! Here the sentence for NW: *REC(Expression=%value% *account1/account2, account="Commissions/ Pf opex") test it and adjust, dj

[former_member186338]

KPI are calculated using dimension member formulas, not by script logic...

Vadim

[Former Member]

Hi Vadim ,

Main issue here is we have list of 70 KPI required to generate .  If we use dimension member formulas that will kill performance. same ACCOUNT dimensions using  in different Models.

so we want to go with script logic for STAT accounts.

Can I use formula like this .

PF123456 : account hierarchy nodes

PF876453 : account Hierarchy nodes.

*REC(EXPRESSION= ([ACCOUNT].[PFZ123456] / [ACCOUNT].[PF876453] ), ACCOUNT=BENEFITS_PCT)

Thanks,

Satya

[former_member186338]

First - dimension member formulas affect the performance only when used in some report!

Second - KPI's are not summable - you will get incorrect results on nodes with values calculated by script logic for base members.

Vadim