I was recently working at an organisation where the BPC implementation had developed over time with input
from a number of third party sources. There were no problems with functionality but, like most clients, they would appreciate any changes which could produce an improvement in performance. Clearly, a variety of skillsets, approaches, and quality standards had produced a challenging situation for this client, and they asked our team to make suggestions.
I was adding two new controls (validation accounts) and this led me to a small logic file which needed modifying.
As I reviewed the logic I realised that there were a number of modifications which would improve the performance, and would be simple to implement and easy to subsequently modify.
The original logic, simplified to remove some unnecessary detail, was:
*WHEN ACCOUNT
*IS FLAG_1200
*REC(ACCOUNT=FLAG_1210)
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS FLAG_1200
*REC(ACCOUNT=FLAG_1220)
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS FLAG_1300
*REC(ACCOUNT=FLAG_1330)
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS FLAG_1300
*REC(ACCOUNT=FLAG_1340)
*ENDWHEN
*COMMIT
The first performance hit would come from the four *COMMIT statements. When a Commit happens the data is written back to the underlying database. Consequently, we should always strive to minimise the number of Commits in a logic. We only need to Commit data when the results are required as input to subsequent logic statements. Even in these cases there are alternatives such as employing the *GO statement, or redeveloping the logic flow to reduce constituent dependencies.
In this case we only need one *COMMIT at the end of the logic.
There are also four *WHEN loops, each of which requires a scan of the source data records. This can be reduced to a single *WHEN loop.
At this stage the logic looks like this.
*WHEN ACCOUNT
*IS FLAG_1200
*REC(ACCOUNT=FLAG_1210)
*REC(ACCOUNT=FLAG_1220)
*IS FLAG_1300
*REC(ACCOUNT=FLAG_1330)
*REC(ACCOUNT=FLAG_1340)
*ENDWHEN
*COMMIT
We can now look at the scoping of the logic. A *WHEN statement will loop through all data records which are in context. We can improve the performance by filtering the input records which are scanned. We know that only records for FLAG_1200 and FLAG_1300 are of interest and we probably only want to run this logic on Local Currency Data.
To limit the records which are scanned we would add these lines to the logic.
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ACCOUNT=FLAG_1200, FLAG_1300
These simple changes, which had an additional benefit of producing a logic which is easier to maintain, reduced the execution time by over 50%. Pleased with this improvement by refining the logic to be more elegant and less verbose, the client has engaged us to review the other logics.
Want more Logic Training? Learn more about our upcoming SAP BPC Logic Boot Camp!
At another organisation I was recently reviewing report performance and the impact of Dimension Member 
Formulas. These are formulas, attached to a particular dimension member, which run when data is retrieved and return values based on the report context. This allows calculations to take into account parent values and various measures. For example, Gross Profit as a Percentage of Sales can be calculated on demand easily in a Dimension Member Formula as [GP] / [SALES] * 100. If we wanted to calculate and store this, it would have to be calculated for every dimension intersection and consolidation node; creating an impractical volume of data. When ratios are required Dimension Member Formulas are the appropriate tool to employ. They do have an overhead when called from a report but for ratios this is justified. However, when used for other calculations they may represent an unnecessary overhead. Knowing when to employ which method is not necessarily a hard and fast rule, factors such as model design, data volumes, report design and other influences may govern which method to apply.
My review identified a number of ratios which could be improved by breaking up the tasks into two distinct groups: calculating and storing the components best suited for this treatment, and then just performing the ratio calculation in the Dimension Member Formula for the aspects of the formula that will perform well with that method. Often we see one or the other method being employed for the whole statement, and overall performance suffers as a result.
Related Content: Performance Tuning for SAP BPC 10.0 for NetWeaver
An example of this is the calculation of debtor days based on the last 3 months’ sales. By calculating and storing as records in the database the prior 3 months’ sales by applying a Script Logic or a BADI, the Dimension Member formula can be [DEBTORS] / [PREV_3_M_SALES] *90 which minimises the Dimension Member Formula overhead.
When choosing what logic method (of the several available) to employ, the range of choices can be intimidating. If you’ve ever referred to your logic code as a “black box” or something “only our consultant can touch”…you may be well served by making such a vital part of your solution more transparent. Feeling beholden to the author of the logic need not be the case, and we encourage you to contact us to explore logic training options, or to commission a review of your logic. It is a best practice to revisit these methods from time to time as either your business needs or technology may have advanced and there are efficiencies to be gained.
If you think your implementation could benefit from an independent review, then talk to Column5 about an Application Review or a more comprehensive Assessment where we can help you develop your EPM roadmap.
Contact Column5 Consulting at info@column5.com.
BPC Logic Boot Camp
December 6-8
Column5’s BPC Logic Boot Camp focuses entirely on interacting with logic itself over the course of 3 days. We teach attendees how to make sense of the basic and more advanced logic principals, write their own logic and troubleshoot existing logic. This course takes place at the Column5 headquarters in beautiful Scottsdale, AZ.
More Related Content:
Customer Roundtable: Best in Class EPM Standards
Planning and Forecasting: Category Set-Up for SAP BPC
SAP BPC Staffing and Support Models
Author Bio:
Martin Lloyd is the Implementation Director for the UK (EMEA team). A solutions architect and Chartered Accountant with over 20 years' industry experience with Enterprise Performance Management. He is an internationally recognized expert in the design and implementation of Enterprise Performance Management (EPM) solutions. Certified in SAP BPC 10 and HANA.
