Why should you use Business Rules (Table Based Logic) instead of Script Logic in the SAP BPC?

[james_lim]

NOTE: BPC 10 Microsoft Platform shows different behavior due to design change so if you are using it, please read below URL.

http://scn.sap.com/community/epm/planning-and-consolidation-for-microsoft/blog/2013/07/31/bpc-10-mic...

Recently I had a chance to visit a customer site. They said they had a performance issue with currency conversion. I found that they were using script logic for currency conversion so I converted thier script logic to business rule. The performance improved more than 10 times even though new business rule processed more records. Please check below graph.

As you can see, it took under 150 seconds to convert 1.14 million records.

Then you may ask a question why the performance is SO MUCH different.

The answer is... 'Business rule is the logic module that is based on the stored procedure of data base server but script logic is based on the component of application server'.

Script logic reads data from the Database server and calculates it. After it finishes calculations, Logic components will post result data through BPC sending engine.

Most of customer has multi server environment so Application server and Database server is separated physically. It means script logic will make communication traffic between Application server and database server. Usually it doesn't matter when data size is small but like this customer case, if logic creates a lot of records, it will make a performance issue. In addition, sending engine performance also will be affected its amount of data.

In conclusion, we should use business rule when logic needs to process a lot of data. Of course business rule can't handle all cases but if we use it properly, we can get best result.

I attached two scripts for comparing.

*CLEAR_DESTINATION
*DESTINATION RPTCURRENCY=USD,EUR
*LOOKUP RATE
*DIM RATESRC="RATECALC"
*DIM RATE=ACCOUNTHC.RATETYPE
*DIM SOURCECURR:INPUTCURRENCY=ENTITY.CURRENCY
*DIM USD:INPUTCURRENCY="USD"
*DIM EUR:INPUTCURRENCY="EUR"
*ENDLOOKUP
*WHEN ACCOUNTHC.RATETYPE
*IS "NOTRANS"
*IS AVG,END,BOY,HISTIIS,HISTCS,HISTDIV,HISTADJ
*REC(FACTOR=LOOKUP(SOURCECURR)/LOOKUP(USD),RPTCURRENCY="USD")
*REC(FACTOR=LOOKUP(SOURCECURR)/LOOKUP(EUR),RPTCURRENCY="EUR")
*ELSE
*REC(RPTCURRENCY="USD")
*REC(RPTCURRENCY="EUR")
*ENDWHEN
[RPTCURRENCY].[#USD]=NULL
[RPTCURRENCY].[#EUR]=NULL

*COMMIT

*CLEAR_DESTINATION
*DESTINATION RPTCURRENCY=USD,EUR

*RUN_STORED_PROCEDURE=SPRUNCONVERSION('%APP%','%CATEGORY_SET%','','RATECALC','%SCOPETABLE%','%LOGTABLE%')

Note: Please refer below URL. You can find a document how to convert currency using business rule.

http://scn.sap.com/docs/DOC-4204