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

Why should user use Business Rule (Table Based Logic) instead of Script Logic in the SAP BPC? James Lim   Business Card Company: SAP Posted on Mar. 08, 2010 06:54 AM in Business Intelligence (BusinessObjects), Enterprise Performance Management

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.

<Script logic for currency conversion>

*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

<Script logic that will execute business rule>

*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://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/bpx-community/enterprise-performance-management/How%20to%20do%20Currency%20Translation%20for%20Financial%20Application%20using%20SAP%20BPC%207.0%20version%20for%20Microsoft%20SQL%20Server

 

 

 

 

 

 

 

 

 

James Lim   SAP RIG America - BPC MS platform architect & Customer Care

---

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/17857