SCRIPT LOGIC ON MULTIPLE PARENT MEMBERS

[Former Member]

Hi Team,

I am trying to write script logic on parent members by using FOR Loop.

We are working on BPC 11.0

Its effects the performance .

Actually the reqiremnt is

1)TOT_REV(Base member) = ACCOUNT=(2000(parent member consist of 35 child members),DATASRCE=ERP(Parent member)

+ACCOUNT=2100(parent member consist of 20 child members),DATASRCE=ERP(Parent member)

+ACCOUNT=54001(Base member),DATASRCE=INPUT)/UOA (UOA property contains ACCOUNT dimension members maintained in product dimension.So each PRODUCT have different UOA ACCOUNT Dimension members )

2)54000(Base member) = 54001(Parent member consist of 5 members),DATASRCE=ERP(Parent member)/UOA etc

Total 10 formulas there in each formula have parent members The script is write as followes

*SELECT (%PRODUCTS% , "[ID]",PRODUCT,"[CALC]='N' ")

*SELECT (%PRODUCT_UOA% , "[UOA]",PRODUCT,"[ID]='%PRODUCTS%' ")

*SELECT (%ALLACCOUNTS% , "[ID]",ACCOUNT,"[CALC]='N' ")

*XDIM_MEMBERSET ACCOUNT AS %ABC% = BAS(2000)

*XDIM_MEMBERSET ACCOUNT AS %XYZ% = BAS(2100)

*XDIM_MEMBERSET ACCOUNT AS %MNO% = BAS(54001)

*XDIM_MEMBERSET DATASRCE AS %ERP_TOTAL% = BAS(ERP)

*XDIM_MEMBERSET PRODUCT = %PRODUCTS%

*XDIM_MEMBERSET ACCOUNT = %PRODUCT_UOA% , %ABC% , %XYZ% , %MNO% , %ALLACCOUNTS%

*XDIM_MEMBERSET DATASRCE = %ERP_TOTAL%

*WHEN ACCOUNT

*IS PRODUCT.UOA

*WHEN PRODUCT

*IS %PRODUCTS%

*FOR %ACCOUNT_11% = %ABC%

*FOR %ACCOUNT_22% = %XYZ%

*FOR %ACCOUNT_33% = %MNO%

*FOR %ERP_ALL% = %ERP_TOTAL%

*REC(EXPRESSION = ([ACCOUNT].[%ACCOUNT_11%]+[ACCOUNT].[%ACCOUNT_22%]+[ACCOUNT].[54001])/%VALUE%

*REC(EXPRESSION = ([ACCOUNT].[%ACCOUNT_33%]/%VALUE%

*NEXT

*NEXT

*NEXT

*NEXT

*ENDWHEN

*ENDWHEN

In these script i use 4 FOR LOOPs to read parent members.

When i run these package its working fine. But it effects the performance.So i request to all is there any other alternative without using FOR LOOP and without effecting the performance.

[former_member186338 · Accepted solution]

If 54001 is a base member then:

*LOOKUP SameModel
*DIM UOA:ACCOUNT=PRODUCT.UOA
*ENDLOOKUP

*XDIM_MEMBERSET ACCOUNT AS %BAS2000%=BAS(2000)
*XDIM_MEMBERSET ACCOUNT AS %BAS2100%=BAS(2100)
*XDIM_MEMBERSET ACCOUNT=%BAS2000%,%BAS2100%,54001
*XDIM_MEMBERSET DATASRCE AS %ERPALL% = BAS(ERP) 
*XDIM_MEMBERSET DATASRCE=%ERPALL%,INPUT

*WHEN ACCOUNT
*IS %BAS2000%,%BAS2100%
  *WHEN DATASRCE
  *IS %ERPALL%
    *REC(EXPRESSION=%VALUE%/LOOKUP(UOA),ACCOUNT=TOT_REV,DATASRCE=CALC)
  *ENWHEN
*IS 54001 //base member
  *WHEN DATASRCE
  *IS INPUT
    *REC(EXPRESSION=%VALUE%/LOOKUP(UOA),ACCOUNT=TOT_REV,DATASRCE=CALC)
  *ENWHEN
*ENDWHEN

TOT_REV(Base member) = (ACCOUNT=2000(parent member consist of 35 child members),DATASRCE=ERP(Parent member)

+ACCOUNT=2100(parent member consist of 20 child members),DATASRCE=ERP(Parent member)

+ACCOUNT=54001(Base member),DATASRCE=INPUT)/UOA

[Former Member]

Hi Vadim,

It's working fine.

The above script is avoid the performance issue.

Thanks & regards

Uma

[former_member186338]

Try to avoid FOR/NEXT! And look on my previous answer to your question.

Also, please provide accurate info:

"+ACCOUNT=54001(Base member)"

"= 54001(Parent member consist of 5 members)"

54001 - BASE or Parent?????