Script logics performance improvements - help with parallelization

[former_member228128]

Hi experts,

I have been browsing the HTG and help documents for BPC 10.1 in NW in order to find out how to improve some of my script logic performance.

I found out that one improvement could be the use of parallelization.

I am a bit confused with which solution to use :

- using RUNLOGIC_PH (http://scn.sap.com/docs/DOC-35152) for having calculations done in parallel

- using *XDIM_PACKAGEBY in the script logic (http://help.sap.com/saphelp_bopacnw101/helpdata/en/f0/5b716f5507458eabc2f57b029a5066/content.htm?fra...)

From what I understand, both solutions will allow the script logics to run the calculation on datasets in parallel. Am I right?

What would be the benefits of one or the other solution? Can they be used together?

Also when using parallel execution (with any of the 2 solutions above), is there any interest of setting XDIM_MAXMEMBERS too ?

Finally I was wondering if the Parallelization set up described in the HTG Enable Parallelization for Datamanager packages and Consolidation (http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0255ebe-8a1b-3110-de89-dd2b81b9c...would have any impact on performance of script logics called through the DEFAULT_FORMULAS datamanager package.

Thanks for your advices!

Alexandre

[former_member186338 · Accepted solution]

I prefer to use RUNLOGIC_PH due to full control of parallelization. Please test it!

Vadim

[former_member186338 · Accepted solution]

P.S. But please show the sample of slow script - in many cases it's due to bad design...

[former_member228128 · Accepted solution]

Vadim,

Actually I have many different scripts I want to improve performance of. Some are quite complex MDX calculations with nested IIF, I know MDX has poor performance but I couldn't find another way to achieve my need.

Some scripts other are using WHEN/ENDWHEN and are slow too.

Here is one small script that is taking a lot of time to execute because of amount of data.

*SELECT(%C_GRP%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]='Y'")

*SELECT(%HP2%,"[ID]","MATERIAL_DI50","[HP]='HP2'")

*SELECT(%SEASON_%,"[SEASON_DI50]","TIME_DI50","[ID]='%TIME_DI50_SET%'")

*SELECT(%Y_%,"[YEAR]","TIME_DI50","[ID]='%TIME_DI50_SET%'")

*SELECT(%MOIS_%,"[ID]","TIME_DI50","[SEASON_DI50]='%SEASON_%'")

*FOR %CUST_GRP% = %C_GRP%

*FOR %FAM% = %HP2%

*XDIM_MEMBERSET COMPANY = E_0500

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET ACCOUNT=ACC_6

*XDIM_MEMBERSET PHASE=%PHASE_SET%

*XDIM_MEMBERSET TIME_DI50 = %MOIS_%

//*XDIM_FILTER  CUST_PREV_DI50 = [CUST_PREV_DI50].PROPERTIES("GROUPE_DE_CLIENT") = "%CUST_GRP%"

*XDIM_MEMBERSET CUST_PREV_DI50 = BAS(ALL_CUST)

*XDIM_MEMBERSET  MATERIAL_DI50 = BAS(%FAM%)

*WHEN CUST_PREV_DI50.GROUPE_DE_CLIENT

*IS %CUST_GRP%

  *REC(EXPRESSION=[PHASE].[C_113] * (1 + ([MATERIAL_DI50].[%FAM%.INP],[CUST_PREV_DI50].[%CUST_GRP%],[ACCOUNT].[ACC_62],[PHASE].[C_101],[TIME_DI50].[%Y_%.12])))

*ENDWHEN

*NEXT

*NEXT

A little parenthesis which would require another post.

You can note that there is a commented out "XDIM_FILTER". Actually it was uworkign fine in BPC 7.5, since I have migrated to BPC 10.1,I got a running error (UJK_VALIDATION_EXCEPTION:No members found in dimension "ENTITY" for condition "CALC" = "N"). I had then to use the *WHEN/*IS on my property in order to achieve the same result but I defintely saw the performance decreasing (which is normal since now for each loop of my internal FOR/NEXT the WHEN/ENDWHEN go through all base members of dimension "CUST_PREV_DI50").

I have investigated and found out that on BPC 10.1 if the XDIM_FILTER retrieve no members, the script crashes, which was not the case in BPC 7.5. I have open a SAP Incident about this)

Alexandre

[former_member186338 · Accepted solution]

The script is slow due to multiple FOR/NEXT. And the logic of REC is a bit strange...

To remove empty for XDIM_FILTER use:

*SELECT(%C_GRPALL%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]='Y'")

*SELECT(%C_GRP%,"[GROUPE_DE_CLIENT]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]=%C_GRPALL%")

%C_GRP% will contain only non empty!

For the rest please describe your requirements in line with:

Vadim

[former_member228128 · Accepted solution]

Thanks for your help.

But if I use your code I will have not unique values.

If I run following in UJKT:

*SELECT(%C_GRPALL%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]='Y'")

*SELECT(%C_GRP%,"[GROUPE_DE_CLIENT]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]=%C_GRPALL%")

*XDIM_MEMBERSET CUST_PREV_DI50 = %C_GRP%

I get:

*XDIM_MEMBERSET CUST_PREV_DI50 = MASS,MASS,MASS,MASS,MASS,MASS,MASS,MASS,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,TRAD,INTERCO,EXPORT,TRAD

So my loop with be run many more times that I would need (it should run only 4 times because I have only 4 values for GROUPE_DE_CLIENT: MASS, TRAD, EXPORT, INTERCO).

Is there anyway to return only unique values with the SELECT statement?

I will open a new post in line with your post about the rest of the problem because it doesn't concern m original post here.

Alexandre

[former_member186338 · Accepted solution]

Unfortunately I have no info how your dimension is organized...

Then:

*SELECT(%C_GRPALL%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]='Y'")

//%C_GRPALL% - unique list but some are empty

*SELECT(%C_GRPNOTEMPTY%,"[GROUPE_DE_CLIENT]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]=%C_GRPALL%")

//%C_GRPNOTEMPTY% - not empty but duplicates

*SELECT(%C_GRP%,"[ID]","CUST_PREV_DI50",[ID]=%C_GRPNOTEMPTY%")

//%C_GRP% - unique...

Vadim

[former_member186338 · Accepted solution]

Other strange lines:

*SELECT(%SEASON_%,"[SEASON_DI50]","TIME_DI50","[ID]='%TIME_DI50_SET%'")

*SELECT(%MOIS_%,"[ID]","TIME_DI50","[SEASON_DI50]='%SEASON_%'")

First you get property SEASON_DI50 of the selected TIME member (single?)

Then you get member ID by this property - same member as selected by user?

In REC I don't see %VALUE% - then I do not understand the logic... You are trying to write data to the current record in the loop, but if you have no records? No loop...

Vadim

[former_member228128 · Accepted solution]

Thanks a lot the SELECT to get unique not empty values works well!

[former_member186338 · Accepted solution]

If the answer is helpful - mark it helpful

Please answer other questions!

[former_member228128 · Accepted solution]

Vadim Kalinin wrote:

 *SELECT(%SEASON_%,"[SEASON_DI50]","TIME_DI50","[ID]='%TIME_DI50_SET%'")
*SELECT(%MOIS_%,"[ID]","TIME_DI50","[SEASON_DI50]='%SEASON_%'")

First you get property SEASON_DI50 of the selected TIME member (single?)
Then you get member ID by this property - same member as selected by user?

I want to get all member ID of TIME that have the same propery SEASON_DI50 as the member selected by user (this is just allow user to select 1 TIME member instead of 6)

 In REC I don't see %VALUE% - then I do not understand the logic... You are trying to write data to the current record in the loop, but if you have no records? No loop...

You are right, there was something wrong in the REC (even though it was achieveing expected results because before there is an initialization script that makes sure you have records for the loop)

I rewrote it to be cleaner:

*REC(EXPRESSION=%VALUE% * (1 + ([MATERIAL_DI50].[%FAM%.INP],[CUST_PREV_DI50].[%CUST_GRP%],[ACCOUNT].[ACC_62],[PHASE].[C_101],[TIME_DI50].[%Y_%.12])), PHASE=%PHASE_SET%)

Unfortunately the script is still very long. I really have to try with the parallel execution.

Alexandre

[former_member186338 · Accepted solution]

"I want to get all member ID of TIME that have the same propery SEASON_DI50 as the member selected by user (this is just allow user to select 1 TIME member instead of 6)" may be it's better to select SEASON property from PROMPT(COMBOBOX... if you have more or less fixed list of seasons? Read:

Initialization is a useless thing! The script have to be something like:

*SELECT(%C_GRPALL%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]='Y'")

*SELECT(%C_GRPNOTEMPTY%,"[GROUPE_DE_CLIENT]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]=%C_GRPALL%")

*SELECT(%C_GRP%,"[ID]","CUST_PREV_DI50",[ID]=%C_GRPNOTEMPTY%")

*SELECT(%HP2%,"[ID]","MATERIAL_DI50","[HP]='HP2'")

*SELECT(%Y_%,"[YEAR]","TIME_DI50","[ID]='%TIME_DI50_SET%'")

*SELECT(%SEASON_%,"[SEASON_DI50]","TIME_DI50","[ID]='%TIME_DI50_SET%'")

*SELECT(%MOIS_%,"[ID]","TIME_DI50","[SEASON_DI50]='%SEASON_%'")

// May be *SELECT(%MOIS_%,"[ID]","TIME_DI50","[SEASON_DI50]=$SEASON$") where $SEASON$ - from DM variable

//Constant scope settings outside FOR?NEXT

*XDIM_MEMBERSET COMPANY = E_0500

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET ACCOUNT=ACC_6

*XDIM_MEMBERSET PHASE=C_113

*XDIM_MEMBERSET TIME_DI50 = %MOIS_%

*FOR %CUST_GRP% = %C_GRP%

*FOR %FAM% = %HP2%

*XDIM_FILTER  CUST_PREV_DI50 = [CUST_PREV_DI50].PROPERTIES("GROUPE_DE_CLIENT") = "%CUST_GRP%"

*XDIM_MEMBERSET  MATERIAL_DI50 = BAS(%FAM%)

*WHEN CUST_PREV_DI50

*IS *

  *REC(EXPRESSION=%VALUE% * (1 + ([MATERIAL_DI50].[%FAM%.INP],[CUST_PREV_DI50].[%CUST_GRP%],[ACCOUNT].[ACC_62],[PHASE].[C_101],[TIME_DI50].[%Y_%.12])),PHASE=%PHASE_SET%)

*ENDWHEN

*NEXT

*NEXT

For other improvements I have to understand the logic...

Vadim

[former_member186338 · Accepted solution]

P.S. How many members do you have in %C_GRP% and in %HP2%?

[former_member228128 · Accepted solution]

%C_GRP% = 4 members

%HP2% = 103 members

The logic purpose is the following:

The goal is to apply global % of increase for a budget planning.

User have entered a % of increase on each HP2 (product family) / C_GRP (customer group).

I want to apply the corresponding % to ACCOUNT ACC_6/PHASE C_113 for each base member of MATERIAL_DI50 (products) and base member of CUST_PREV_DI50 (customers).

HP2 values are nodes of dimension MATERIAL, base members of CUST_PREV_DI50 have a property GROUPE_DE_CLIENT that contain their customer group.

I couldn't find another way to write the logic.

Hope this is clear enough.

[former_member186338 · Accepted solution]

Well, 412 iterations resulting in a long list of code is time consuming (just processing of lines..). And looks like it can't be improved by allocation - not possible to eliminate FOR/NEXT (by the way - FOR/NEXT is just text processing , not a real loop)

But the calculation itself is not very complex.

My recommendation - in this case create a simple custom logic badi that will run fast even without parallelization.

By the way how long it takes to execute my last code?

[former_member186338 · Accepted solution]

Another option is to maintain extra property:

MATERIAL_DI50 - PERCM - containing member id of corresponding %FAM%.INP

The you can use:

*LOOKUP SameCube

*DIM PERC:MATERIAL_DI50=MATERIAL_DI50.PERCM

*DIM CUST_PREV_DI50=CUST_PREV_DI50.GROUPE_DE_CLIENT

*DIM TIME_DI50="%Y_%.12"

*DIM ACCOUNT="ACC_62"

*DIM PHASE="C_101"

*ENDLOOKUP

Select all required base members and

*WHEN ...

*IS *

*REC(EXPRESSION=%VALUE%*(1+LOOKUP(PERC)), PHASE=%PHASE_SET%)

*ENDWHEN

Without FOR/NEXT but extra property to maintain.

Vadim

[former_member228128 · Accepted solution]

Unfortunately I don't have ABAP skills so the BAdi is not the easiest solution for me...

I will try with the LOOKUP on same cube solution, this seems perfect. It is not a problem to maintain an extra property. I didn't think of using LOOKUP this way, it is very smart!

Thanks Vadim!

(We have a bit derived from my initial post, for which I still need answers to understand clearly the parallelization but you have solved one of my problems!)

[former_member228128 · Accepted solution]

Your last code (before the LOOKUP solution) takes 20min..

[former_member186338 · Accepted solution]

If you are ready to maintain extra property then lookup will work fine!

Just:

*SELECT(%C_GRPBASE%,"[ID]","CUST_PREV_DI50","[GROUPE_DE_CLIENT]=%C_GRPALL%")

//%C_GRPBASE% - all base clients

*SELECT(%C_MATBASE%,"[ID]","MATERIAL_DI50","[PERCM]<>''")

//%C_MATBASE% - all base materials with non empty PERCM property

*XDIM_MEMBERSET CUST_PREV_DI50=%C_GRPBASE%

*XDIM_MEMBERSET MATERIAL_DI50=%C_MATBASE%

*WHEN...

Parallelization is the next step if the optimized script is still slow. Please read the document about RUNLOGIC_PH

Vadim

[former_member228128 · Accepted solution]

Thanks.

All my base members will have that property anyway.

For RUNLOGIC_PH, I have read the document but the transport files are not attached to it (I have posted it in the HTG too) so I cannot implement it for now.

[former_member228128 · Accepted solution]

It dropped to 40s with the LOOKUP... this is great.

[former_member186338 · Accepted solution]

I think you don't need any parallel processing here