Allocations - Reference a Dimension property dynamically in the USING section

[Former Member]

I am trying to do a simple allocation logic based on the received %

Sending CostCentre - CS_SENDER ID POOL_DRIVER CCATEGY 67610 SKF_VEHICLES CSP Receiving CostCentre - COSTCENTRE ID CCATEGY 40180 NSP

//Scope TIME horizon *SELECT(%TIME_HOR003%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'") *SELECT(%TIME_HOR004%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'") // *SELECT(%CC_DRIV%,POOL_DRIVER,CC_SENDER,"CCATEGY = 'CSP'") *SELECT(%CC_S%,ID,CC_SENDER,"POOL_DRIVER <> ''") //Scope dimension members *XDIM_MEMBERSET VERSION = %VERSION_SET% *XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002% *XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE *XDIM_MEMBERSET POSITIONS = NO_POS *XDIM_MEMBERSET PROFIT_CTR = NO_PC *XDIM_MEMBERSET CC_SENDER = BAS(S_LINKALLCC) *FOR %CC_D% = %CC_DRIV% *RUNALLOCATION *FACTOR = 1 *DIM ACCOUNT WHAT = AC_NONE; WHERE = 700998; USING = %CC_D% *DIM CC_SENDER WHAT = [POOL_DRIVER] = "%CC_D%" AND [CCATEGY] = "CSP"; WHERE = <<<; USING = CS_NONE *DIM COSTCENTRE WHAT = CC_NONE; WHERE = >>>; USING = [CCATEGY] = "NSP" *DIM AUDITID WHAT = T4_SEND; WHERE = T4_ALLOC_1; USING = DRIVER_VEHICLES *DIM ACT_TYPE WHAT = BAS(ALL_ACTTYPE); WHERE = <<<; USING = NO_ACTTYPE *ENDALLOCATION *NEXT *COMMIT

I need to be able to allocate back to the receiving costcentres based on the Driver account ID that is stored as a property i.e. POOL_DRIVER against each sending costcentre ID.

Wondering what could be done to improve performance of this script.

This does not seem to write back any values despite having the correct intersections.

Appreciate any pointers in the right direction.

thanks

Nilesh D

[Former Member]

Hi Vadim, thank you verymuch - I should have tried this before 🙂 - Works like a charm and completes is less than a minute - have got the concept right now.

[former_member186338]

Please Accept the correct answer!

[former_member186338]

But why do you need second for/next loop? Try to change: USING = %REC% to USING = %NSP_REC% without for/next.

[Former Member]

Hi Vadim,

Have tried to explain the logic using following two images -

It has 3 sections -

1. What - this is the source data that needs to be multiplied by the % from the using section

The filter here is on CC_SEND.CCATEGY = CSP & CC_SEND.POOL_DRIVER = SKF_VEHICLES

I have highlighted the record in green which is in scope

2. Using - this is the driver section from where you fetch the receiver costcentre with a %

The filter here is CC_REC.CCATEGY =NSP

3. Where - this is the section where (1) * (2) to give you the result set

Hope this help. Appreciate all help on this since I am trying to avoid to take the BADI route and stick to a script logic solution

[Former Member]

Hi Vadim, initially I was working with a set of 40 COSTCENTRE'S (i.e. Receiver costcentres). Now the complete list of receiver costcentres has gone up to 1400.

When I hard code the following with 1 sending Cost Centre (CS_100010)and 2 receiver costcentres(CC_500000,CC_600000) the code works as expected

*FOR %VEH% = %VEHICLE%             //CS_100010
*FOR %REC% = %NSP_REC%             //CC_500000,CC_600000

[former_member186338]

Yes, a lot of members in nested for/next loops can result in script not working.

[former_member186338]

P.S. In order to get some proposals please prepare the question correctly with data samples.

[Former Member]

Hi Vadim,

Sorry I had moved on from this issues but revisiting it back again.

What I want to achieve is the following -

- We have 2 COSTCENTRE dimensions i.e. CC_SENDER(Sender costcentre) and COSTCENTRE(Receiver costcentre dimension)

- Both CC_SENDER & COSTCENTRE have a property called CCATEGY which flags the costcentres as either a CSP(Corporate service provider) or a NSP(Network service provider)

- CC_SENDER has an additional property called POOL_DRIVER (this is populated with the member ID of the Account dimension against which a driver % is stored for performing the allocation i.e. USING section of the allocation logic)

Variable - %TIME_HOR003%,%TIME_HOR004% will have 12+12 months i.e. 24 months of members ID's populated

Logically I am looking to achieve the following -

For each CC_SENDER member(which is a CSP and has POOL_DRIVER = SKF_VEHICLE) Loop through every COSTCENTRE member which has a property CCATEGY =NSP and has the driver % stored against ACCOUNT.ID = SKF_VEHICLE

Hence I have used 2 for loops - code is as below

//Scope TIME horizon
*SELECT(%TIME_HOR003%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'")           //Jan 18 to Dec 18
*SELECT(%TIME_HOR004%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'")           //Jan 19 to Dec 19


//Scope Sending and Receiving Cost Centres based on CCATEGY and POOL_DRIVER property
*SELECT(%VEHICLE%,ID,CC_SENDER,"POOL_DRIVER = 'SKF_VEHICLES' AND CCATEGY = 'CSP'")  //CS_100010,CS_100011
*SELECT(%NSP_REC%,ID,COSTCENTRE,"CCATEGY = 'NSP'")                                  //CC_500000,CC_600000


//Scope dimension members
*XDIM_MEMBERSET VERSION = %VERSION_SET%                                             //DEMO18_V1
*XDIM_MEMBERSET TIME = %TIME_HOR003%,%TIME_HOR004%                                  //JAN 18 TO DEC 19
*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE
*XDIM_MEMBERSET POSITIONS = NO_POS
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET ACT_TYPE = BAS(ALL_ACTTYPE)

*FOR %VEH% = %VEHICLE%             //CS_100010
*FOR %REC% = %NSP_REC%             //CC_500000,CC_600000
*RUNALLOCATION
*FACTOR = 1*USING
*DIM ACCOUNT       WHAT = AC_NONE;           WHERE = 700998;                 USING = SKF_VEHICLES
*DIM CC_SENDER     WHAT = %VEH%;             WHERE = <<<;                    USING = CS_NONE
*DIM COSTCENTRE    WHAT = CC_NONE;           WHERE = >>>;                    USING = %REC%
*DIM AUDITID       WHAT = T4_SEND;           WHERE = T4_ALLOC_INTERIM;       USING = DRIVER_VEHICLES
*ENDALLOCATION
*NEXT
*NEXT

This code had worked for me in smaller data sets while testing, but with bigger data sets now the run fails to complete. Not sure if its the Database issue of the script performance can be improved. Please advice.
Below is a snapshot of my process that i received from the admin - UJKT TESTER log

[former_member186338]

Please explain: "This code had worked for me in smaller data sets while testing, but with bigger data sets now the run fails to complete." - what do you mean by "bigger data sets"

[former_member186338]

Still not clear and badly formatted 😞

You have to provide explanation for each dimensions, etc...

"3. Ideally for each Sending Cost Center with property CCATEGY = 'CSP' and POOL_DRIVER <> "", loop through all the receiving COSTCENTRE'S which have the same driver as the sending costcentre.POOL_DRIVER account ID" - unable to understand!

Something can be mentioned based on script:

*SELECT(%CC_DRIV%,POOL_DRIVER,CC_SENDER,"CCATEGY = 'CSP' AND POOL_DRIVER <> ''")

in this line %CC_DRIV% will contain multiple repeats of the same POOL_DRIVER

In order to get unique POOL_DRIVER list you have to change it to:

*SELECT(%CC_DRIVS%,POOL_DRIVER,CC_SENDER,"CCATEGY = 'CSP' AND POOL_DRIVER <> ''")
*SELECT(%CC_DRIV%,ID,ACCOUNT,ID=%CC_DRIVS%)

P.S.

What do you mean by this:

*SELECT(%TIME_HOR003%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'")
*SELECT(%TIME_HOR004%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'")

%TIME_HOR003% will be equal to %TIME_HOR004%

in the line:

*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%

variables are not defined...

Please, be accurate!

[Former Member]

Hi Vadim,

I am trying to do the following -

1. Have a sending Cost Centre with a sample transaction as seen below - S_67610
2. Sending cost centre has a property POOL_DRIVER which hold the ID of the Driver Account i.e. SKF_VEHICLES
3. Ideally for each Sending Cost Center with property CCATEGY = 'CSP' and POOL_DRIVER <> "", loop through all the
   receiving COSTCENTRE'S which have the same driver as the sending costcentre.POOL_DRIVER account ID
thank you for your help
excel.jpg

//Scope TIME horizon
*SELECT(%TIME_HOR003%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'")
*SELECT(%TIME_HOR004%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'")
//           
*SELECT(%CC_DRIV%,POOL_DRIVER,CC_SENDER,"CCATEGY = 'CSP' AND POOL_DRIVER <> ''")
*SELECT(%CC_S%,ID,CC_SENDER,"POOL_DRIVER <> ''")


//Scope dimension members
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE
*XDIM_MEMBERSET POSITIONS = NO_POS
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(S_LINKALLCC)




*FOR %CC_D% = %CC_DRIV%
	*RUNALLOCATION
		*FACTOR = 1*USING
		*DIM ACCOUNT       WHAT = AC_NONE;                                              WHERE = 700998;                 USING = %CC_D%
		*DIM CC_SENDER     WHAT = [POOL_DRIVER] = "%CC_D%" AND [CCATEGY] = "CSP";       WHERE = <<<;                    USING = CS_NONE
		*DIM COSTCENTRE    WHAT = CC_NONE;                                              WHERE = >>>;                    USING = [CCATEGY] = "NSP"
		*DIM AUDITID       WHAT = T4_SEND;                                              WHERE = T4_ALLOC_1;             USING = DRIVER_VEHICLES
		*DIM ACT_TYPE      WHAT = BAS(ALL_ACTTYPE);                                     WHERE = <<<;                    USING = NO_ACTTYPE
	*ENDALLOCATION


*COMMIT
*NEXT

[former_member186338]

Sorry, but the explanation is not clear! You have to explain the required logic in details and attach Excel screenshot of the desired results.

Also, use "CODE" button to insert code in the message.

[Former Member]

Hi Vadim,I am trying todo the following -1. Have a sending Cost Centre with a sample transactionas seen below - S_67610
2. Sending cost centre has a property POOL_DRIVER which hold the IDof the Driver Account i.e. SKF_VEHICLES
3. Ideally for each Sending Cost Centerwithproperty CCATEGY ='CSP'and POOL_DRIVER <>"", loop through all thereceiving COSTCENTRE'S which have the same driver as the sending costcentre.POOL_DRIVER account ID
thank you for your help


//Scope TIME horizon
*SELECT(%TIME_HOR003%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'")
*SELECT(%TIME_HOR004%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'")
//           
*SELECT(%CC_DRIV%,POOL_DRIVER,CC_SENDER,"CCATEGY = 'CSP' AND POOL_DRIVER <> ''")
*SELECT(%CC_S%,ID,CC_SENDER,"POOL_DRIVER <> ''")


//Scope dimension members
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET EMPLOYEE = NO_EMPLOYEE
*XDIM_MEMBERSET POSITIONS = NO_POS
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(S_LINKALLCC)




*FOR %CC_D% = %CC_DRIV%
	*RUNALLOCATION
		*FACTOR = 1*USING
		*DIM ACCOUNT       WHAT = AC_NONE;                                              WHERE = 700998;                 USING = %CC_D%
		*DIM CC_SENDER     WHAT = [POOL_DRIVER] = "%CC_D%" AND [CCATEGY] = "CSP";       WHERE = <<<;                    USING = CS_NONE
		*DIM COSTCENTRE    WHAT = CC_NONE;                                              WHERE = >>>;                    USING = [CCATEGY] = "NSP"
		*DIM AUDITID       WHAT = T4_SEND;                                              WHERE = T4_ALLOC_1;             USING = DRIVER_VEHICLES
		*DIM ACT_TYPE      WHAT = BAS(ALL_ACTTYPE);                                     WHERE = <<<;                    USING = NO_ACTTYPE
	*ENDALLOCATION


*COMMIT
*NEXT

[Former Member]

scn.txt

Sorry about the format - please find attached a more readable format of the content

scn.txt