Skip to Content
avatar image
-1
Former Member

Allocations - Reference a Dimension property dynamically in the USING section

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

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
    
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 02, 2016 at 06:19 AM
    scn.txt

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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 02, 2016 at 07:50 AM

    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.

    Add comment
    10|10000 characters needed 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
  • Dec 04, 2016 at 08:42 AM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 11, 2017 at 12:54 AM

    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
    
    
    Add comment
    10|10000 characters needed 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"

  • avatar image
    Former Member
    Jan 11, 2017 at 10:06 PM
    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
    Add comment
    10|10000 characters needed characters exceeded

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

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 13, 2017 at 01:13 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 17, 2017 at 12:20 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded