When considering tradeoffs between performance, ease of maintenance, and integration with platform functionality, choosing the “language” to author business rules in BPC is a key decision. Of the many options available to BPC administrators & developers, BPC Script Logic is often selected as a reasonable compromise to access the power of database calculations without the high bar of outright code development. Promising a good balance between functionality, maintainability and performance, Script Logic is a set of functions that simplify the calculations most often needed in EPM processes.
BPC script logic provides powerful and flexible functionality, but if you don’t have previous SQL or coding experience, it may seem unapproachable. Of course, there are other methods for performing calculations in BPC: BPC’s delivered standard business rules are generally easy to configure and may accomplish some of the calculations your system needs to perform but ultimately lack the flexibility to meet all of your organization’s needs. It can also be tempting from a development standpoint to rely on member formulas and Excel to perform calculations, but often these methods result in poor performance and can be a maintenance nightmare in practice. BPC script logic has the power and flexibility to perform better and accomplish more than the aforementioned methods – if you know how to use it. Learning BPC script logic syntax can feel daunting, but the rewards – understanding and troubleshooting your existing script logic, and writing new logic when the need arises without relying on outside consultants – tend to outweigh the challenges if you keep a few tips in mind.
1. Sustain or Maintain
We often see clients who have hard coded member IDs in their script logic which worked great initially but every time something changes, they’re faced with the tedious task of pouring through their script logic files to make all of the necessary updates. A telltale sign of this deficiency is a roll to a new calendar year requiring manual intervention throughout the logic to “set up” the next year’s code. It is vital to assess and understand whether your script logic will carry your organization into a new period, a different version, or handle new accounts and businesses. Frequent editing and updating of script logic is time-consuming and increases your chances for errors as it is easy to miss a time period or grouping of accounts buried in your script that may need to be changed. If possible, think about how your script logic could be more automated and sustainable through the use properties and variables to dynamically reference member IDs, as opposed to attempting to maintain hard coded member IDs. Done properly, a few changes to configuration flags will signal to the code the necessary changes without the risk of complex manual updates.
2. Code for BPC Performance
Our performance testing has shown that SAP’s purpose built SQL script logic syntax performs significantly faster than MDX syntax. MDX might be simpler to write, but in practically all cases where MDX logic has been replaced with SQL logic, the improvements have been substantial. It’s also worth reviewing your script logic to ensure that it is written in the most compact and efficient way possible by limiting the number of *COMMIT statements, looking for places where calculations can be combined within the same *WHEN/*ENDWHEN section, or even combining instructions for calculations that behave the same.
3. Pay Attention to Scoping
Managing your query size can drastically improve BPC performance, but often those just starting out misunderstand what the scope is actually doing behind the scenes or why it matters so much when it comes to performance. There are a variety of keywords available to narrow, filter, and specify the scope, all of which can increase BPC performance when script logic is run, as well as ensure that you are running calculations on the intended members and getting the correct computational results.
4. Don’t forget the Allocation Engine
You might know that the Allocation Engine was designed to simplify allocations and tends to yield quick performance on large databases, but did you know that the Allocation Engine structure can be used for much more than just allocations? The Allocation Engine should be your first choice for writing script logic, not only for top-down allocations across multiple dimensions, but also bulk copies, data movements, and driver-based calculations.
5. The Devil is in the Details
If you have a lot of experience writing script logic, you know that the smallest errors can result in incorrect results, even if your code validates. If you’re seeing an issue, it’s worth taking few minutes to check that all of your quotations, parentheses, and brackets are in the correct places. Ensure that there aren’t any commas where there should be a period. Depending on which backend platform your system runs on, ensure that the capitalization of member, dimension and property names is correct.
Do you want to learn more about these topics and become a BPC script logic superhero? Give your mind a workout and enroll in Column5’s BPC Logic Boot Camp! This course provides you with instruction that covers all of the above topics in detail and much, much more. Over the course of three full days, this comprehensive in-person training provides you with step-by-step guidance on all the “ins and outs” and best practices for writing SQL script logic and the Allocation Engine structure, real-world examples, and hands-on exercises in a BPC environment. This course also covers worksheet logic, member formulas, and business rules.
Related Articles:
Quick Wins in BPC Logic Tuning
Useful Tips of Advanced Functionality in EPM 10 Dynamic Formatting
Using the Power of Visual Basic in EPM 10 Reporting
SAP BW-IP, SAP BPC NW 10.1 Standard and SAP BPC NW 10.1 Embedded Compared
Author Bio:
Leveraging her expertise in the SAP EPM space and her background in education, Jessica has delivered engaging and relevant EPM training in reporting, administration, and logic to clients in a variety of industries, both in person and via the EPM Academy’s virtual classroom. In her role as a curriculum developer and trainer with the Column5 training team, Jessica also develops new curriculum for the EPM Academy, creating innovative and interactive eLearning courses. She also creates custom training curriculum designed to achieve an organization’s EPM training goals.
Jessica Christophersen, EPM Curriculum Developer and Training, Column5 EPM Academy
