Enterprise Performance Management
Page tree
Skip to end of metadata
Go to start of metadata

Purpose

To provide collaborative tips and best practice on performance and reporting using the EPM Add-in with SAP Business Planning and Consolidation (BPC), version for NetWeaver and SAP Business Planning and Consolidation, version for Microsoft. These tips are authored by various sources within SAP. 

Overview

When upgrading from a previous version of BPC to BPC 10.X using the EPM Add-In you may notice that report performance is impacted. This can be true if you are not aware of the key tips and tricks to best utilize the EPM Add-in. Please keep in that EPM is a Microsoft Excel addin and because Microsoft frequently releases office and windows updates EPM must align with those updates at times. Therefore we recommend to stay current with the SP and patches. 

Report Options

  • Create EPM Reports instead of using cell-based EPMRetrieveData functions. EPMRetrieve can be used with small volume, but for large volume EPM reports is suggested.  Keep in mind that having multiple reports in one template can also cause problems
  • EPM reports will out-perform EVDRE reports despite EVDRE having performance optimization.
  • Parallelization is activated by default with the Axis Sharing feature which can be heavy in terms of performance.
  • Try to avoid using custom measures such as MTD or WTD (process not optimized).
  • If different member ID’s have the same descriptions, ensure “Use EPMMemberID in EPM formulas” is checked (user options). A better option would be to manually create a local member mentioning the EPMMemberID function where it is necessary (without checking the User Option).
  • In case of an input template, consider using the "calculate parents in hierarchies" (sheet options) for on-the-fly calculation on parent node members.
  • Multi selection in Page Axis: the SUM is performed on the client side. The cartesian product is returned from the server and summed up on the front-end.
  • Exclude functionality changes your report from symmetric mode to asymmetric mode. Depending on the number of tuples you decide to exclude, performance could be worse.
  • Avoid large numbers of cell formulas or complex formulas that mix EPM and Excel functions, especially if these cell formulas are dependents (a formula references another formula) or are functions that include a condition (If).
  • By default, the EPM Add-in uses the "Insert Method" to write reports. To get around this, enable "Keep Formulas Static that Reference Report Cells" (Sheet Options) to clear the entire report and re-write it entirely on a refresh.
  • When using drill-down, in the user options select to "Refresh only Expanded & Inserted Members" instead of refreshing the entire report.
  • If using the "Collapse" feature, open the FPMXLClient.dll.config file in EPM Add-in installation folder and look for "Collapsewithoutqueryingserver". Set the default value from FALSE to TRUE.
  • Beware in a Citrix/Terminal Services environment, performance may be impacted by the number of macros or complex formatting.
  • Consider using Office 64 bit with EPM Add-in .NET 4.0 (Big volume) versions.
  • You cannot have more than 20 EPM Add-in reports in a single Excel sheet.

EPM add-in tries to build axis request from a set of cell requests according to 3 "smartquery" parameters that are defined in FPMXLCLient.dll.config :

  1. SmartQueryEngineThreshold-  default value is 5 : ratio of unwanted data that can be queried when using an axis request instead of a cell request.  For example : You have 5 cell based formulas, the system will try to compute an axis query that will include all 5 cells. If the number of data to retrieve with this axis query is less than 25 it is ok, otherwise the scope of the axis query will be reduced and the system will send several axis queries or 1 axis query and a cell query, depending on the next parameter.

  2. SmartQueryEngineMaxAxisRequests-  default value 1: number of Axis requests that are generated from a set of formulas. With this default value on a refresh action 1 axis request will be generated, cells outside of this axis request will be retrieved using a cell request.

  3. SmartQueryEngineMaxTupleByCellRequest- default value 20000. Maximum number of cells that could be requested at once. In some cases, reducing this amount can improve the performances.


    Setting parameters can be trial and error however here is an example to start with: 

    SmartQueryEngineThreshold" value="5"/> CHANGE THIS VALUE TO 10
    SmartQueryEngineMaxTupleByCellRequest" value="20000"/> CHANGE THIS VALUE TO 100
    SmartQueryEngineMaxAxisRequests" value="1"/> CHANGE THIS VALUE TO 5

Functions and Formulas

  • EPMInsertCellBeforeAfterBlock and EPMCopyRange has an impact on the writing time.
  • Use EPMDimensionOverride instead of manually changing the EPMOlapMemberO function for very large EPM reports.
  • Try to avoid mixing EPM Report, EPMRetrieveData, and EPMCommentFull/Partial since the EPM Add-in does a separate call to the database for each type of function (EPMOlapMemberO, EPMRetrieveData, EPMCommentFull/Partial).
  • Functions DimensionOverride & AxisOverride will impact performance when loading large reports with a lot of formulas to evaluate.
  • Never use the connection name designing a report.  Best option is to use the model name a hidden cell of your report, and reference that cell in the connection parameter of your formula.
  • Avoid volatile Excel functions like NOW, TODAY, RAND, OFFSET, INDIRECT, INFO and cascading references.
  • The higher number of InApp dimension properties, the lower the performance of the overall BPC model / application and system. See SAP KB 1851220

 Formatting

  • Limit the number of formatting rules and make sure those rules do not overlap each other.

  • Avoid using Microsoft Excel conditional formatting within an EPM add-in dynamic formatting sheet due to the evaluation of each cell.

  • Avoid overriding rules because the Add-in is formatting the cell multiple times (Pattern for example).

  • Avoid using the CONTENT override because the EPM Add-in is retrieving data twice (writing + calculation).

  • Create several EPM add-in dynamic formatting sheets instead of only one, if possible.

Visual Basic for Applications (VBA)

  • Avoid using loops (Do, While)
  • Look for existing API before creating custom code
  • VBA Best practice “acceleration” function: the initial variables (“screen updating”, “calculations”, “events”…) must be deactivated and then reactivated to their initial status in the exit of the macro.

Related Content

Related Documents

Related SAP Notes and Articles

  • No labels