Skip to Content
Author's profile photo James Lim

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

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-microsoft-platform-business-rule-vs-script-logic

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.

image

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

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Badrish Shriniwas
      Badrish Shriniwas
      It is really good to know that the business rules makes such a big differnce. Thanks James for compiling and publishing this.

      "In god we trust for rest we need data" 🙂

      Author's profile photo Roberto Vidotti
      Roberto Vidotti

      Hi James,

      good job, only a warning the link to the doc at the end doesn't work (even the file exists)

      Kind regards

      Author's profile photo James Lim
      James Lim
      Blog Post Author

      Thank you Roberto.

      I just updated it, it was an old link because I moved it from my space to here yesterday because I just noticed SCN structure was changed.

      Thank you anyway and let me know if you find any other errors !

      Regards,

      James Lim

      Author's profile photo Former Member
      Former Member

      Hi James,

      Thanks for the blog, its really informative. I see that the blogs which you have wrote on Script logic for Dummies no longer exits, is it possible to restore the same.

      Regards,

      Raghu

      Author's profile photo James Lim
      James Lim
      Blog Post Author

      I put it into the BPC NW space.  you can find it in below link and all other posts are there.

      http://scn.sap.com/community/epm/businessobjects-planning-and-consolidations-for-netweaver/blog/2011/05/24/bpc-script-logic-for-dummies-part-1

      Thank you!

      Regards,

      James Lim

      Author's profile photo Former Member
      Former Member

      Hi James,

      Thanks for the link, I think due to the migration to the new SCN the logic code in the 1st, 2nd and 3rd blogs are broken.

      Regards,

      Raghu

      Author's profile photo James Lim
      James Lim
      Blog Post Author

      Hi Raghu,

      I updated it using my backup  so  please review it and let me know if you find any mistake or problem.

      Thank you for finding this for all of us.

      If you need any other topics for BPC like this series, please let me know, I will prepare and will post not only for you but also for others!

      Regards,

      James Lim.

      Author's profile photo Former Member
      Former Member

      Hi James,

      Thanks a lot, sure I will.

      Regards,

      Raghu

      Author's profile photo Former Member
      Former Member

      <moved to EPM - 'EVDRE: Insert Member'>

      Thanks James

      Author's profile photo James Lim
      James Lim
      Blog Post Author

      HI Ian,

      Please move your questions to Discussion area so that others can share it.

      Thanks.

      James

      Author's profile photo Former Member
      Former Member

      James

      Does it seem possible to you to use  Business Rules for FX where there are 2 possible dimensions holding the currency (eg.  if Project=NoProject use CostCentre Currency, Else use Project Currency)

      I currently have this working in Script logic but it is not as quick as I would like.

      If BRs can work with the conditional switch of dimensions it would be very useful...

      Cheers

      !i!i!i!i