SAP Analytics
Thought Leadership

The EPM Add-in client is a hot topic among our customers and a subject we receive plenty of questions about. Questions usually arise during migration from the SAP BusinessObjects Planning and Consolidation (BPC) 7.x version for the Microsoft platform to SAP BPC 10.0 or 10.1, version for the Microsoft platform. This blog will cover questions about the EPM Add-in client during system upgrades. 

Google “EPM Performance or Memory issues” and you will receive millions of hits from your search results.  When it comes to performance and especially memory consumption, not only is the “magic bullet” fix rare but the resolution is potentially complex and multifaceted in nature.  On a recent project, we started to see symptoms that became issues including long refresh times.  For one user, we saw an Input Schedule consume so much memory it resulted in the “White Screen of Death (WSoD) and Excel crashing.  In this article, I wanted to look at the challenges we faced and strategies employed to resolve the issues.

When comparing BPC to other products, a big factor to consider is the user interface experience. SaaS based
EPM solutions often have significant constraints to the available options when designing the experience users will 
interact with, due to the inherent design limitations of those solutions. BPC’s robust Excel interface has an unlimited ability to meet any user experience requirement. In addition to the significant numbers of Excel functions included in the EPM Add-in that can be applied creatively to meet any need, sometimes it is necessary to leverage Visual Basic to create highly specific behavior in the interface.

If you follow best practices for SAP implementation and use, whether it be BPC, Disclosure Management, ECC,
HANA, ABAP, or many others, you will be successful in quantifying the benefits from the suite of products you choose.  Our perspective is that there are no inherently “bad” SAP products, just poor implementation that can make your product use more stressful than it needs to be.

Need to calculate a grand total dynamically?  Shifting of Local Members giving you a headache?  I previously used Indirect() functions, but I discovered with larger reports this put a drag on reporting performance.  To sum from the first report column to the last report column I was using something like this  =SUM(EPMPOSITION(1):INDIRECT(ADDRESS(ROW(),COLUMN()-1))), which is not considered best practice but I got results!

The SAP definition: “EPMALLMEMBERS is used to perform a calculation (such as sum or average) on all the members displayed in a row or column axis, whether the axis contains one or several dimensions”

HOW?  Here are some examples:

This blog is part of a series on reporting techniques to help you get more value from SAP BPC.

DROP-DOWN LISTS help make a worksheet more efficient by provided a controlled list of options that make the report or input template respond to preconfigured options.

To create the list use Excel’s Data Validation tool. We are going to take this a step further by populating the list with EPM reports on a ‘List’ tab within the workbook.  In the snapshot below we are using drop-down list for Company, Cost Centre, Program, and Account. Each drop-down list will have its own report to populate the list.

COST CENTER LIST: A list of cost centers is created using fixed members that had been input. In this example, the dropdown has a friendly label that is the result of concatenating ID & Description, but the report will only use the dimension ID’s referenced on the list tab by using Excel’s VLOOKUP function to match the concatenated friendly label to the ID from the right row.

The formatting strategy chosen for a report or input schedule is one of the most important decisions made in development because it influences performance, complexity and the “fun factor” for development.  I say this with humor because the report may take 4 hours to build and 8 hours to format!  There isn’t room enough to go into all aspects of formatting in detail, however the understanding of choices we have is critical and can certainly influence the overall life cycle (maintenance and productivity).

The EPMFormatting sheet is used to format dynamic reports and input templates.  It is an Excel sheet that can be copied and changed for multiple worksheets and reports.  Dynamic formatting means the formats are applied to levels of a hierarchy, types of members or column and page axis dimensions.

BPC owners often seek to implement automation to make manual administration the exception wherever possible, in order to lower TCO and increase uptime.  For dimension automation, new members added to source systems (accounts, product names, etc.) would be automatically inserted into the BPC master data tables.  Automating this task could save quite a bit of manual effort over time, and increase system uptime.  If your BPC deployment requires manual dimension maintenance, this solution may be of great interest to you.

This is part two of a blog on utilizing the BPC Restore Journal and Export Journal Data Manager Packages.  In the first installment we covered how to setup BPC to use the packages and a few of the reasons why a business team might utilize mass journal updates.  In the second installment, we will walk through a working example of how a team might use the packages to seed budget journals from actuals.  See part 1 for a refresher on how to setup BPC for importing and exporting journals.