Skip to Content
-1

Allocations - Reference a Dimension property dynamically in the USING section

Dec 02, 2016 at 06:14 AM

91

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

9 Answers

avatar image
Former Member Dec 04, 2016 at 03:20 AM
-1
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

excel.jpg (85.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 02, 2016 at 06:19 AM
0
scn.txt

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


scn.txt (1.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Dec 02, 2016 at 07:50 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
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
0
Vadim Kalinin Dec 04, 2016 at 08:42 AM
0

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!

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 11, 2017 at 12:54 AM
0

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


ujkt-tester.png (365.9 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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"

0
avatar image
Former Member Jan 11, 2017 at 10:06 PM
0
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
Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0
avatar image
Former Member Jan 13, 2017 at 12:28 AM
0

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


scn-num.jpg (135.3 kB)
scn-formula.jpg (150.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 13, 2017 at 01:13 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 17, 2017 at 12:20 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Please Accept the correct answer!

0