WRITE_TO_FAC2 Question

[Former Member]

I have a logic script that runs very slowly when more than 100 records are sent to the cube. In order to improve performance I'm testing using the Write_to_FAC2/Process_FAC2 instructions. When I add these instructions the sending is fast, but only the first time period is processed. Do I need a For/Next loop or is the placement of the instrucitons the problem? Below is my script:

// <Driver 3. % of Current Period Sales by Team/SubTeam>

*XDIM_MEMBERSET CATEGORY=Forecast

*XDIM_MEMBERSET ACCOUNT = %DriverAccounts%

*XDIM_ADDMEMBERSET ACCOUNT=%SalesAccounts%

*XDIM_MEMBERSET DATASRC=INPUT,AUTO_CALC,DRIVER_METHOD,DRIVER_AMOUNT

*XDIM_ADDMEMBERSET DATASRC=FLASHSRC,ESSBASE,HCM,SUPP,PS

*XDIM_MEMBERSET RPTCURRENCY=LC_FOREIGN,USD_US

*XDIM_ADDMEMBERSET TEAM=%TEAMLIST%

*XDIM_ADDMEMBERSET SUBTEAM=%SUBTEAMLIST%

*LOOKUP FINANCE

*DIM ENTITY=ENTITY

*DIM ACCOUNT=ACCOUNT

*DIM RECALC_SETTING: DATASRC="AUTO_CALC"

*DIM DRIVER_CHOSEN: DATASRC="DRIVER_METHOD"

*DIM DRIVER_AMOUNT: DATASRC="DRIVER_AMT"

*ENDLOOKUP

*CALC_DUMMY_ORG

*ORG ACCOUNT=PARENTH1

*ENDCALC

*CALC_DUMMY_ORG

*ORG TEAM=PARENTH1

*ENDCALC

*CALC_DUMMY_ORG

*ORG SUBTEAM=PARENTH1

*ENDCALC

*CALC_DUMMY_ORG

*ORG DATASRC=PARENTH1

*ENDCALC

*WRITE_TO_FAC2

*PROCESS_FAC2

*WHEN DATASRC

*IS AUTO_CALC

*WHEN LOOKUP(RECALC_SETTING) // Turned on?

*IS = 2

*WHEN LOOKUP(DRIVER_CHOSEN) // check the method

*IS = 3

REC(Expression=(LOOKUP(DRIVER_AMOUNT)get(ACCOUNT="#ALL_SALES",DATASRC="#ALLSOURCES")*-1), DATASRC ="INPUT")

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

[Former Member]

Joost,

Thanks for your reply. I'll review my calc_dummy_org instructions.

[Former Member]

Sorin,

Thanks for the info on Write_to_Fac2. I will review the logic log file to see how I might improve performance.

Thanks,

Don

[sorin_radulescu]

The performances of logic it can not be improved with instruction

WRITE_TO_FAC2...in fact will slow down the performances because it is necessary to be done one more step:PROCESS_FAC2 which is processing FAC2 partition.

To improve the performances you have to check the log of logic and to see what selection or what is the action which is taking the most of the time.

In this way you will find out what you can improve.

Any way Write_TO_FAC2 is not an instruction to improve performances and it should be used when you know that your logic is adding more 50k records.

Otherwise writing in wb table will be always faster.

Attention you have to keep under control the number of records from wb table using lite optimize process. Otherwise is true that you can have bad performances inserting records into a table with more than 50k and if fac2 will be empty it can provide you the impresion it is faster to use Write_To_Fac2.

I hope these suggestion it will help you to understand how to improve performances for script logic, when you have to use WRITE_TO_FAC2 and to use lite optimize for good performance for queries and script logic.

Regards

Sorin Radulescu

[Former Member]

I agree with Sorin. Write_to_fac2 won't improve performance much. If you want to improve the performance of this logic I would rather look at the amount of calc_dummy_org instructions you use. Are they really needed, because I think you can improve most on this part, since this requires a lot of calculations at runtime of each send.Ffor example I don't see any calculated members from your TEAM and SUBTEAM dimension being used in this logic, while you do calculate the hierarchy every time when you runlogic.