Run Allocation Performance

[Former Member]

Hi Experts.

I have an allocation requirement and I'm trying to solve it under Script Logic but the performance is not quite good.

The records are as follow:

Entity	Account	Condition	Version	Date	Ratio	SIGNEDDATA
A	ACC_A	NA	V1	2012.JAN	AMOUNT	1000
A	ACC_A	C1	V1	2012.JAN	PERCT	0.5

And the result should be:

Entity	Account	Condition	Version	Date	Ratio	SIGNEDDATA
A	ACC_A	C1	V1	2012.JAN	AMOUNT	500

To do this, the Script Logic I've defined is:

*XDIM_MEMBERSET RATIO = PERCT
*XDIM_MEMBERSET ENTITY = %ENTIDAD_SET%
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET DATE = %DATE_SET%
*XDIM_MEMBERSET CONDITION =  C1, C2, C3, C4
*SELECT(%VBLE_ACC%, ID, ACCOUNT, [CALC]='N')
*XDIM_MEMBERSET ACCOUNT= %VBLE_ACC%
*XDIM_MAXMEMBERS ACCOUNT = 20
*XDIM_MAXMEMBERS ENTITY = 1
*XDIM_MAXMEMBERS VERSION = 1
*FOR %ENT%=%ENTITY_SET%
*FOR %VER%=%VERSION_SET%
*FOR %ACC%=%VBLE_ACC%
*FOR %DAT%=%DATE_SET%
*FOR  %COND%= C1, C2, C3, C4
   *XDIM_FILTER ENTITY= [ENTITY].PROPERTIES("ID") = %ENT%
   *XDIM_FILTER VERSION= [VERSION].PROPERTIES("ID") = %VER%
   *XDIM_FILTER DATE= [DATE].PROPERTIES("ID") = %DAT%
   *XDIM_FILTER ACCOUNT = [ACCOUNT].PROPERTIES("ID") = %ACC%
   *XDIM_FILTER CONDITION = [CONDITION].PROPERTIES("ID") = %COND%
   *RUNALLOCATION
   *DESCRIPTION=ALLOCATION TEST1
   *FACTOR=USING*1
   *DIM CONDITION WHAT=%COND%;WHERE=<<<;USING=NA
   *DIM ACCOUNT WHAT=%ACC%;WHERE=<<<;USING=<<< 
   *DIM ENTITY WHAT=%ENT%;WHERE=<<<;USING=<<<
   *DIM DATE WHAT=%DAT%;WHERE=<<<;USING=<<<
   *DIM RATIO WHAT=PERCT; WHERE=AMOUNT;USING=AMOUNT
   *DIM VERSION WHAT=%VER%;WHERE=<<<;USING=<<<
   *ENDALLOCATION
*NEXT 
*NEXT
*NEXT
*NEXT
*NEXT
*COMMIT

The allocation works fine but the time to execute for one entity, version and 12 months is greater than 20 minutes, even if I just try to allocate 3 records.

Any idea on how can I improve the performance of the script logic?

Thanks in advance.

it0

[Former Member · Accepted solution]

Hi,

The performance issue is cauded by the number of *FOR statements.

First of all, all those FOR are really necessary?

Note that, in Allocation you can use BAS(member) in WHERE/WHAT/USING clauses, besides use more than one member.

One think I can advance to you, is the first *FOR (Entity), replace by *WHEN statement and take off the entity dimension filter inside the for.

Regards,

Lucas

[Former Member · Accepted solution]

Hi Lucas.

First of all, thanks for the answer.

The reason I do so many FOR is to iterate through all the data, since the user can do the allocation for multiple entities, versions and dates. So If the allocation is executed for two entities I have to be able to read each record.

The BAS at the allocation will aggregate the values? I can have a different  percentage and amount for each account, entity, time and version combination, so I have to read it individually.

I don't see how to replace the *FOR with a *WHEN statement, since I have to be able to read the records for a specific entity and in this case I have it on the variable %ENT%.

Regards

it0

[Former Member · Accepted solution]

I changed the script logic and now seems to be working fine. But I don't understand it

*XDIM_MEMBERSET RATIO = PERCT  
*XDIM_MEMBERSET ENTITY = %ENTIDAD_SET%  
*XDIM_MEMBERSET VERSION = %VERSION_SET%  
*XDIM_MEMBERSET DATE = %DATE_SET%  
*XDIM_MEMBERSET CONDITION =  C1, C2, C3, C4  
*SELECT(%VBLE_ACC%, ID, ACCOUNT, [CALC]='N')  
*XDIM_MEMBERSET ACCOUNT= %VBLE_ACC%  
*XDIM_MAXMEMBERS ACCOUNT = 20  
*XDIM_MAXMEMBERS ENTITY = 1  
*XDIM_MAXMEMBERS VERSION = 1  
*WHEN_REF_DATA=TRANS_DATA
*WHEN *
*IS *
   *FOR %COND%= C_CONTADO, C_30, C_60, C_90
   *FOR %ACC% = %VBLE_ACC%
      *RUNALLOCATION
         *DESCRIPTION=ALLOCATION TEST 1
         *FACTOR=USING*1
         *DIM ACCOUNT WHAT=%ACC%;WHERE=<<<;USING=<<<
         *DIM CONDITION WHAT=%COND%;WHERE=<<<;USING=NA
         *DIM RATIO WHAT=PERCT; WHERE=AMOUN;USING=AMOUNT
         *ENDALLOCATION
     *NEXT
     *NEXT
*ENDWHEN
*COMMIT

I don't understand why it's going through the different entities and date records but it's generating the result amount correctly and the performance it's much better.

The *WHEN is going for each record? Why is the allocation not aggregating the records of both entities? Is it because the MAX MEMBERS instruction?

Thanks a lot.

Regards

it0

[Former Member · Accepted solution]

Hi Ito,

Ok about the FOR, I just suggested you to analyse if it's really indispensable.

About the WHEN statement, works like loop. Going through all records fetched in scope, and the *IS statement just read specific members in case of multiple scope.

Regarding the BAS, imagine you have a hierarchy in account dimension with a rent expenses node and the children of this node some rent accounts. If is needed to allocate all these accounts at once, is allowed to used in allocation the BAS(node) instead of doing one by one account or list all accounts in the clause.

If Condition is not relevant in your allocation for example, you can take of the *FOR and insert the following statement:

*DIM CONDITION WHAT=C_CONTADO, C_30, C_60, C_90;WHERE=<<<;USING=NA 

I hope it helps.

Regards,

Lucas

[Former Member · Accepted solution]

Hi Lucas.

Thanks again for helping me.

I guess that the combination maxmember on entity and the when is doing my process to allocate all records from one entity at a time.

Regarding the BAS, imagine you have a hierarchy in account dimension with a rent expenses node and the children of this node some rent accounts. If is needed to allocate all these accounts at once, is allowed to used in allocation the BAS(node) instead of doing one by one account or list all accounts in the clause.

I can't use the BAS on accounts since I have to read the amount and percentage for echa account, entity, date, version and condition, the BAS memberset will aggregate the signeddata and the result won't be the one expected.

If Condition is not relevant in your allocation for example, you can take of the *FOR and insert the following statement:
*DIM CONDITION WHAT=C_CONTADO, C_30, C_60, C_90;WHERE=<<<;USING=NA 

Same as the account case, every percentage I need to allocate is record to a single condition, I need to allocate the value to be able to execute the allocation properly.

I have executed the logic with multiple entities and it seems to be working fine.

Again thank you for your answers.

Regards.

it0