Kill Bill Volume 1: Memory Consumption and the White Screen of Death

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.