Best Practices For Reporting Against SAP Business Planning and Consolidation (Powered by SAP HANA), utilizing the EPM Add-in for Excel
Welcome to this blog mentionning some key concepts/best practices to have in mind when designing a report/input schedule utilizing the EPM Add-in for Excel on the top of SAP Business Planning and Consolidation 10.0 (powered by SAP HANA). This document will be updated as soon as some new concepts are coming from implemention teams. But before jumping directly to best practices, here's first a quick introduction on this front-end tool.
EPM ADD-IN VERSIONS
EPM Add-in dotnet 3.5:
- Supports only Office 32 bits
- Not limited to certain function
- Performance hit when querying via ODBO connection
- Run with .NET 3.5 framework (has 1.2 GB RAM limitation)
- Can be updated with *.msp file (no need to uninstall/re-install)
- Master Data downloaded to client (No support of BIG VOLUME (BV) mode)
EPM Add-in dotnet 4.0:
- Supports Office 32 bits or Office 64 bits
- Possibility to enable the BIG VOLUME mode on BW data sources (ODBO / XMLA connections only). Standard mode or Big Volume mode are supported.
- In Standard mode, it runs the same features as the 3.5 version.
- Better performance via ODBO connection
- Can be updated with *.msp file (have to uninstall/re-install)
- Supports Office 2010 64 bits to get the most out of client-side performance (no RAM limitation)
- Supports SAP BW (INA provider) connections (for BPC 10.1 embedded model).
Big Volume (ODBO connection)
With the Big Volume mode enabled, you cannot write data back to the database, you can only render data. Additionally, certain specific BPC functions are not available (EPMMemberProperty/EVPRO, etc…), as well as some EPM add-in functionalities (Freeze data refresh, Member Selector – dimension/member properties and Data Ranking/Sorting).
The Big Volume mode changes the interface for member selection so that dimension members are displayed in pages instead of the default tree view (SP 07). This improves performance for dimensions with large numbers of members.The BV mode only loads metadata asked for, or required, not all of it.
BPC 10.1 EMBEDDED MODEL (BW INA provider)
Latest Drill-Down performance test results showed interesting improvement when activating "Refresh only Expanded and Inserted Members" option in the EPM User Options. It's recommended to use it as a default setting for BPC embedded customers.
The EPM Add-in is an add-in to Microsoft Office Excel, Microsoft Word, and Microsoft Office PowerPoint and is used to analyze data in the following applications:
- SAP Business Financial Consolidation
- SAP Business Planning and Consolidations, version for SAP NetWeaver **
- SAP Business Planning and Consolidations, version for the Microsoft platform**
- SAP Profitability and Cost Management
- SAP NetWeaver BW InfoProviders (different ODBO connector for BPC MS and BPC NW version).
** Allows write back of data via BPC Web Service connection
This connection type is for ODBO connections, (FC SSAS cubes, FC BW cubes, SSAS standard cubes, BW standard cubes, PCM ODBO provider, SSM ODBO provider, BPC MS ODBO provider and SAP BW OLE DB provider. An ODBO connection doesn’t allow data input, and is only used for data retrieval. Data Manager features are also not reachable with an ODBO connection. However, the usage of custom members (to build some complex MDX calculated members) can only be performed with an ODBO connection.
Planning & Consolidation
This type is for Web Service connections, on the top of BPC 10 MS and BPC 10 NW. A Web service connection allows data input, and the use of the Data Manager ribbon, but does not support the creation of custom members (only local members).
FUNCTIONS & FORMULAS
- EPMInsertCellBeforeAfterBlock and EPMCopyRange – big 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). And all those queries are not parallelized.
- Functions DimensionOverride and AxisOverride are costly on loading, especially when a report is huge and have lots of formulas to evaluate.
- Enter the connection name in all EPM formulas (EPMModelCubeID or a static text cell can be used as reference somwhere in a hidden place of your report).
- Avoid volatile Excel functions like Row(), Column(), and Offset(), as well as cascade references between cells.
- Create EPM Reports instead of using EPMRetrieveData function.
- Parallelization is activated by default with the Axis Sharing feature, and on the same data source/connection. Caution: the Axis Sharing feature is a bit heavy in terms of performance.
- Try not to use custom Measures such as MTD or WTD (process not optimized).
- EPM reports will outperform EVDRE reports. EVDRE does have some performance optimization but EPM10 report have 2-3 times better performance for the same layout/behavior.
- If different member ID’s have the same descriptions, ensure “Use EPMMemberID in EPM formulas” is checked in User Options. Caution: It also has an impact on performance as the EPMemberID formula is automatically inserted for each dimension member. A better option would be to manually create a local member mentionning the EPMMemberID function only where it is necessary (without checking the User Option).
- In case of input, consider using Calculate Parents in Hierarchies (Sheet Options) for on-the-fly calculation on Parent Node members. But be aware that, even it's a very useful feature, the rendering time is bigger as the EPM Add-in has to insert the SUM function based on the hierarchy definition.
- Multi selection in Page Axis: the SUM is performed on the client side. 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. But depending the number of tuples you decide to exclude, performance could be worse.
- Avoid using complex formulas mixing EPM and Microsoft Excel functions, such as functions that create a dependence (a formula references another formula; a formula creates a dependence between two reports), or 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" option in the Sheet Options to clear the entire report and re-write it entirely on a refresh.
- In case of drill-down, check option to "Refresh only Expanded and Inserted Members" in the User Options instead of refreshing the entire report.
- In case of using the "Collapse" feature, there is a new tag in the FPMClient config file in EPM add-in SP15: "Collapsewithoutqueryingserver". Its default value is set to "FALSE", but it can be turned to "TRUE" for better performance.
- Limit the number of formatting rules and make sure those rules don’t 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. For example, Report 1 displays only properties A and B, and Report 2 displays only properties C and D, you should create two different dynamic formatting sheets for better performance.
LOCAL vs CUSTOM MEMBERS
- Created for the purpose of containing a dynamic formula. An editor screen assists with the creation of the Excel-based formula
- A local member is specific to a single report
- Local Members can be made context-sensitive. This is in the options for Local Members
- Custom members are created for the purpose of containing an MDX formula
- An editor screen is presented to help create the formula
- Customer members can be used throughout a worksheet
- Only available for ODBO type connections
General note: When data is existing on the spread sheet, choose Local Members. Local Members is native Excel functionality and will always perform better.
- Look for existing API before creating custom code
- Avoid Loop (Do, While...)
- 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.
ANALYZING & DEBUGGING
Review the How to Debug the EPM Add-in document (https://scn.sap.com/docs/DOC-38755)
Determine the source of the issue (client vs. server)
- Fiddler (3rd party software) measures the network traffic
- EPM Add-in logs to measure the client traffic
- ST12 (NetWeaver) – Single Transaction Trace to measure the server traffic. UJSTAT can also be used for analyzing queries on the server.
Not sure where an issue occurs?
The Development Team may ask for a trace from the customer. To obtain one, here is the recommended process using Fiddler:
- In Excel, click ‘More’ ► ‘Clear Metadata Cache’ from the EPM toolbar.
- Log off from the EPM Add-In.
- Add the “TRACE” flag to the FPMXLClient.dll-ExcelLogConfig.xml file against the Trace logger.
- Start the Fiddler tool running to capture web events (“Capture Events [F12]”).
- Log into the EPM Add-In opening the necessary Connection and Model.
- Recreate the issue or refresh the report.
- Stop the capture and save the resulting trace log that it has produced (.SAZ file) and send it to development.
Doing this gives Development all of the data and metadata needed to reproduce an issue without the actual data and metadata files from the customer.
- EPM Add-in Academy (http://wiki.scn.sap.com/wiki/display/CPM/EPM+Add-in+Academy)
Videos and solutions of EPM Add-in functionality (Comments, Formatting, Macros, Local Members, Miscellaneous Reporting, etc.)