Trying to avoid FOR/NEXT loop with RUNALLOCATION in script logic

[Guillaume_P]

Hello Experts.

System versions :

CPMBPC8010018SAPK-80118INCPMBPCCPM Business Planning and Consolidation

SAP_BW7310013SAPKW73113SAP Business Warehouse

Current engine is JAVASCRIPT.

The DM package is called with parameters : MYPHASE = LA10 / MYYEAR=2019 / MANAGERIAL = UK2010

In our BUDGET model we have 12 dimensions, in this issue we will just need to understand the hierarchy for the PRODUCT one (screenshot). Our need is to perform (during a forecast process, forecast called here 'LA10') an allocation of an amount stored against "BRANDTYPE_I" products (the _I means 'Input' to allow input on fake 'parents').

The allocation key to the below level of product (CPL_I) is the weight of product (account T1100) available in BUDGET version.

We do have a working piece of code, but with really bad performance. I'm pretty sure this is due to the *FOR loop.

I've tried to setup something with the *DIM_NONAGGR keyword but member count of WHAT and WHERE is different in that case.

Please kindly advise. Thanks in advance.

(maybe go for a step by step process with WHEN / ENDWHEN syntax, calculate and store an allocation percentage on a tech account, and so on ... ?)

// Allocation to CPL from brandtype

*SELECT(%PROD_BTI%,ID,PRODUCT,LEVEL = BRANDTYPE_I)
*SELECT(%PROD_BT%,ID,PRODUCT,LEVEL = BRANDTYPE)
*SELECT(%PROD_CPLI%,ID,PRODUCT,LEVEL = CPL_I)
*SELECT(%COUNTRYSET%,ID,COUNTRY,CALC = N)

*XDIM_MEMBERSET AUDITTRAIL = INPUT
*XDIM_MEMBERSET CURRENCY = EUROGROUP
*XDIM_MEMBERSET MANAGERIAL = %MANAGERIAL_SET%
*XDIM_MEMBERSET METRICS = VALUE
*XDIM_MEMBERSET PHASE = $MYPHASE$
*XDIM_MEMBERSET TIME = $MYYEAR$.01,$MYYEAR$.02,$MYYEAR$.03,$MYYEAR$.04,$MYYEAR$.05,$MYYEAR$.06,$MYYEAR$.07,$MYYEAR$.08,$MYYEAR$.09,$MYYEAR$.10,$MYYEAR$.11,$MYYEAR$.12
*XDIM_MEMBERSET SOURCING = SOURCINGND
*XDIM_MEMBERSET ENTITY = E999
*XDIM_MEMBERSET FLOW = CLOSING
*XDIM_MEMBERSET ACCOUNT = BAS(R3000) //R2450
*XDIM_MEMBERSET COUNTRY = %COUNTRYSET% //GB

*FOR %loop_BT% = %PROD_BT% //000LABA
	
	*RUNALLOCATION
	*FACTOR = USING/TOTAL
	*DIM_NONAGGR ACCOUNT       WHAT = BAS(R3000) ;    WHERE = <<< ;            USING = T1100 ;     TOTAL = T1100
	*DIM         AUDITTRAIL    WHAT = INPUT ;         WHERE = CALCULATED ;     USING = UPLOADED ;  TOTAL = <<<
	*DIM         PHASE         WHAT = $MYPHASE$ ;     WHERE = <<< ;            USING = <<< ;       TOTAL = <<<
	*DIM         PRODUCT       WHAT = %loop_BT%_I ;   WHERE = BAS(%loop_BT%) ; USING = <<< ;       TOTAL = <<<
	*DIM         CURRENCY      WHAT = EUROGROUP ;     WHERE = <<< ;            USING = LC ;        TOTAL = <<<
	*ENDALLOCATION
*NEXT
*COMMIT

[former_member186338]

Trying to understand what you are doing!

First removed useless lines from your code (if dimension is defined inside RUNALLOCATION - then no XDIM_MEMBERSET and other)

*SELECT(%PROD_BT%,ID,PRODUCT,LEVEL = BRANDTYPE)
*SELECT(%COUNTRYSET%,ID,COUNTRY,CALC = N)

*XDIM_MEMBERSET MANAGERIAL = %MANAGERIAL_SET%
*XDIM_MEMBERSET METRICS = VALUE
*XDIM_MEMBERSET PHASE = $MYPHASE$
*XDIM_MEMBERSET TIME = $MYYEAR$.01,$MYYEAR$.02,$MYYEAR$.03,$MYYEAR$.04,$MYYEAR$.05,$MYYEAR$.06,$MYYEAR$.07,$MYYEAR$.08,$MYYEAR$.09,$MYYEAR$.10,$MYYEAR$.11,$MYYEAR$.12
*XDIM_MEMBERSET SOURCING = SOURCINGND
*XDIM_MEMBERSET ENTITY = E999
*XDIM_MEMBERSET FLOW = CLOSING
*XDIM_MEMBERSET COUNTRY = %COUNTRYSET% //GB

*FOR %loop_BT% = %PROD_BT% //000LABA
    *RUNALLOCATION
    *FACTOR = USING/TOTAL
    *DIM_NONAGGR ACCOUNT       WHAT = BAS(R3000) ;    WHERE = <<< ;            USING = T1100 ;     TOTAL = T1100
    *DIM         AUDITTRAIL    WHAT = INPUT ;         WHERE = CALCULATED ;     USING = UPLOADED ;  TOTAL = <<<
    *DIM         PRODUCT       WHAT = %loop_BT%_I ;   WHERE = BAS(%loop_BT%) ; USING = <<< ;       TOTAL = <<<
    *DIM         CURRENCY      WHAT = EUROGROUP ;     WHERE = <<< ;            USING = LC ;        TOTAL = <<<
    *ENDALLOCATION
*NEXT

level of product (CPL_I) - not used in the code???

Structure of PRODUCT dimension is not clear

etc

[former_member186338 · Accepted solution]

OK, CPL_I has no effect!

In general you have:

1. Number of parent members with the LEVEL: BRANDTYPE and some base members under each parent. Like 000LABA - parent, base: 000LABA0A_I, 000LABA0L_I...

2. For each parent with LEVEL: BRANDTYPE you have corresponding base member XXXXX_I storing value to be allocated to base members under parent. For 000LABA this member is 000LABA_I

For this case the code:

*SELECT(%PROD_BT%,ID,PRODUCT,LEVEL=BRANDTYPE)
...
*FOR %loop_BT% = %PROD_BT% //000LABA
*RUNALLOCATION
*FACTOR =USING/TOTAL
...
*DIM PRODUCT WHAT =%loop_BT%_I;WHERE= BAS(%loop_BT%);USING=<<<;TOTAL =<<<
...
*ENDALLOCATION
*NEXT

is absolutely correct and you can't improve it! *DIM_NONAGGR is not applicable to this case.

WHEN/ENDWHEN will be dramatically slower.

The only way to improve performance is to write a custom logic badi...

[Guillaume_P]

Thanks a lot for your efficient and fast answers.

(we have more than 1K BRANDTYPE_I, and almost 2K CPL_I, we often have 1 brandtype_i = 1cpl_i)

I'll try to get some help from an abaper to make something with a BADI.

Regards.

[former_member186338]

I can understand your issue!

1K BRANDTYPE_I will generate a huge lgx file and will be very slow....

If it's possible to somehow select 1 brandtype_i = 1cpl_i and process it separately then you can improve performance

[Guillaume_P]

Hello Vadim.

level of product (CPL_I) - not used in the code???

Since there is records only on CPL_I products for the account used to calculate the distribution key (T1100 in USING and TOTAL) the BAS(%loop_BT%) is only posting records to CPL_I products.

This is not explicitely defined in the code.

Please find an additional screenshot for dimension PRODUCT structure :

(Members with level property at SPECIFICITY are not used in this allocation.)

For the PRODUCT line in the run allocation block I'd like to have my WHAT to be all product.level=BRANDTYPE_I and my WHERE to be descendants of each brandtype (one by one, like would do a *DIM_NONAGR)

Anything else not clear ?

Thanks & Regards.