BPC 10.x EPM Excel Add-In Observations

Hello all

This blog is to share my observations regarding EPM Excel Addin for SAP BPC NW BW. I will share my observations under several categories. I am not saying that they are valid in every scenario or project and it could be different in your case.  I also understand that all or most of the features can be achieved by some customization or work around. The intent is to have these features available out-of-the-box. I do not undermine the people involved in development of this product in any manner but respect them a lot.

EPM Ribbon(s) Layout:

• There are two different tabs provided. The EPM tab and the Data Manager tab. I may be lazy and find it annoying at times to switch between tabs when performing various activities. A single tab would have been better.
• Under EPM tab, Option –> User Options –> Display tab, following options should be sheet and user specific rather than being only user specific:
  • Navigation
    • Enable Double Click
    • Automatic Refresh on Context Changes
    • Refresh Charts Automatically
  • Context Bar and Pane
    • Display EPM Context Bar
    • Display EPM Pane
  • Display
    • Display Context inside EPM Pane : Some teams do not want to see context at all
    • Display Warning when Saving Data or Comment: Sometimes, we allow customization to allow user enter value at a member not at the base level. Let us say base level is month and we allow user to input at year level  and distribute at month level for faster data input. At the time of saving data, it throws error message which is logically correct but it would be great if it could be suppressed for a given template as users get worried on a failure message. The data gets saved at month level and only throws error for year bucket which is alright as on refresh the year value will be recalculated.
    • Display Warning when Process takes Time

EPM Report and Report Editor:

• Option to get headers(title) for the dimensions in the output
• Ability to allow variable as selection – say display current month -12 to current month + 12. I understand it can be managed using a property but then that property has to be updated every month. The variable capability should be out of the box.
• Ability to display data
  • as combinations of the members selected(which is the only way it performs today)
  • for the data available in the model only (only data loaded and/or saved – no extra combinations) – We can use “Remove Empty” and “Remove Zero and Empty” to achieve this currently however a better description would be good.
• When a value is derived using EPM formula, it should stay when the template is taken offline. If a user performs an action like filtering data, those cells change to “#Error, No connection found”. Once in the output, it should stay even when offline
• Option to display hierarchy levels into separate columns rather than in the hierarchical format only. This will take less number of rows in the output
• SAP BPC’s comment functionality is great but if that is used, user will only see the comment after it has been refreshed. Plus, user has to click on button in the EPM tab if he wants to enter priority and keywords. These features should have been incorporated somehow in standard excel comment functionality. I understand that normal excel comment functionality can be used but it will be without priority.

EPM Formatting:

• The Changed Member formatting via EPM formatting works only when the template is online. In offline mode, when user changes a value, the cell color does not change I could not fathom a reason to not to provide this functionality in offline mode
• When locking is performed and password is to be provided, this locking locks the entire sheet. It should only lock the cells that qualify for that EPM formatting. I understand that the default nature of excel is to protect entire sheet and EPM is leveraging excel functionality for it features. At this point, this is a far-fetched wish and depends if Microsoft or SAP do something about it.
• The column auto-fit works only at the time of refresh if EPM sheet options are used. It should allow to activate it on cell input
• EPM formatting sheet should allow to edit the existing entries. If you have to make a change, you have to remove it and then enter a new one.

Connection Names:

• When using Single Sign-on, if you launch the EPM Excel Addin from BPC Web Client, the default connection names show up which is a string having Environment ID and Model ID concatenated. The Web Client or back end configuration must allow a feature to provide a custom connection name instead of system generated names. This will allow a central control of the connection names.

Data Manager:

• Ability to load data using selection options – Include/Exclude single value/ranges, wild card input etc.
• The only key-figure is a big challenge. The absolute account modeling for BPC is very rigid. The primary purpose of the tool is financial planning and reporting and account modeling disrespects some financial aspects. In many scenarios, we need price and cost. The single key-figure does not allow to control decimal places for different key-figure types – Quantity, Volume, Amount (though via EPM formatting, you can display as desired) plus the load behavior. The Data Manager Package (DMP) will aggregate the price if the number of characteristics in the source are more than in BPC model which is incorrect and there is no way to handle it in BPC. The second issue is when a BPC report or template displays months and years and the price aggregation happens again at year level which is incorrect.
• Conversion file and Transformation file should allow excel formula for mapping rather than java script. BPC system can convert it back as desired on validation. This will make them much powerful.
• Ability to perform selective deletion via DMP. Currently only Clear Package is available which only zeroes out the values and Lite Optimization/Zero Suppression cannot be performed selectively. This will be a welcome feature for BPC Admins.
  

Work Status:

• Equivalent of Data Slice in BW Integrated planning but not that flexible or effective. It should allow based on variables rather than member values which makes everything so hard coded.

EPM Addin does allow us to make up for most of these shortcomings using customization in BW and in excel using EPM formulae and VBA via provided APIs.