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.
Chapter One: The Excel Client
First a brief look at Excel. One of the most commonly asked questions by companies is what is the best Excel version to use with the EPM Add-in? There are multiple variables in play to answer that question and in many cases the version of Office/Excel is a company-wide decision. It then become more important to understand the differences between each version and what that means to the EPM Add-in. Let’s look at memory consumption.
From Microsoft Support:
“Although improvements in Office 2013/2016 did not significantly affect system requirements, Office 2013/2016 does use more available system resources than Office 2010 did. The limit of virtual address space for 32-bit editions of Windows-based applications is 2 gigabytes (GB). For Excel, this space is shared by the Excel application itself together with any Add-ins (COM etc.)* that run in the same process. ....”
While there are multiple takeaways a couple that immediately come to mind are the need for companies to reassess large legacy workbooks and to consider the impact of multiple COM Add-ins when it comes to memory thresholds. Research suggest having multiple COM Add-ins can push memory consumption to the threshold and having three almost assures a memory issue at some point. In addition, it is always important to look at what other applications are running on the same machine – there are other memory hogs out there that can and do affect overall available memory.
Chapter Two: Project Symptoms and Issues
Our customer recently upgraded from Excel 2010 to 2013 and experienced issues with Excel before the EPM Add-in was installed. We saw the following symptoms and issues during Build Realization:
- EPM Add-in Nested reports refresh failures due to out-of-memory
- Excessive Refresh times (3 minutes +) on small record counts
- Unresponsive Excel ribbons dropdown menus using mouse clicks
- Rendering Issues in Excel Worksheets especially with objects like header bars and buttons
- Need to perform mouse clicks (on a cell, scroll, or move to another tab) to see refresh results
Our strategy was to perform an EPM Performance Trace and to ensure the proper use of Best Practices in the EPM Tool, Excel, and the overall Design.
Chapter Three: SAP Best Practices and EPM Options
First we looked at the impact of enabling the following EPM options:
- Enabling the ‘Force Symmetric Refresh for Large Asymmetric Axis’ improved report performance
- Enabling the option for ‘Use Excel References’ improved report performance
- Enabling the ‘Disable Hardware Graphics Acceleration’ helped memory consumption (also possible to enable at the Application level)
Understanding the options available and impact in these areas are potentially quick wins in front-end performance.
Our next steps were to look at SAP Best Practices and find alternative in the following areas:
- EPM Functions known to drag performance
- Extensive use of EPM comments
- Conditional formatting
- Use of Volatile or ‘Volatile Like’ Functions
- Custom VBA
For items 1-4, the focus was to determine whether our use was an enhancement or workaround due to a project flaw. When they were used due to a flaw we wanted take a closer look at alternative methods including design or source change that would eliminate the need for the workaround.
Chapter Four: The strategy to reduce dimension combinations
One of our primary areas of focus when it came to design was to reduce the number of dimension member combinations.
SAP Note 1763572 (EPM Add-in Limitation):
When the Force Symmetric Refresh for Large Asymmetric Axis option is enabled (discussed above), the number of members in each nested dimension in a report axis, when multiplied by themselves and before applying the remove empty or zero values option, cannot exceed a certain number of combinations.
Our main planning model had 2 large dimensions (25,000+ members) and 2 relatively large dimensions (4,500+ members). Additionally, certain dimensions had specific nodes used for the specific plan area. Account, for instance, had separate base level and structure for each plan area. There was an opportunity to break away each of the planning processes from the main model, evaluate data activity needs on a case-by-case basis for planning, and remove any dimensions that were not required for reporting purposes. This strategy allowed us to make the following types of changes:
- Reduce 2 dimensions by approximately 75% to focus on just the planning requirement for each planning processes
- Reduce dimension based on data activity (i.e. dimension sparsity) include 1 from approximately 30,000 to 6,500 members
- Remove 2 dimensions with 4,500 members each
We could remove 2 dimensions completely for a few reasons:
- The dimensions were flat in nature without any structure
- There wasn’t a reporting need to aggregate even in total
- We could source both dimensions as properties through another dimension for filtering needs
Final Chapter: Final Resolution
To find and resolve the root cause(s) for memory takes the functional and technical team to ensure the issue is properly assessed, evaluated, and resolved. The key is determining the root cause (or causes) so they don’t continue to surface. We would also suggest employing Best Practices around what an implementation team should look like here and the technical best practices here in order to get in front of these issues before they even begin. Taking a proactive approach along with upfront collaboration is always the preferred method.
*Component Object Model (COM) Add-ins present third party developers with a way to extend the functionality of Excel for custom tasks. COM Add-ins are typically used to automate Excel in response to a click of a CommandBar button, a form or dialog box, or some other event specific to Excel such as opening or closing workbooks or entering data on worksheets.
Interested in maximizing your EPM ROI?
Check out our "How to make BPC knowledge stick!" webinar!