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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Vadim Kalinin

    Hi Gajendra!

    Number of proposals are absolutely correct, but many are simply impossible or already implemented:

    for the data available in the model only (only data loaded and/or saved – no extra combinations)” – No Data and Zero values in the report options


    “When a value is derived using EPM formula, it should stay when the template is taken offline.” – there are different options for Offline mode…


    Option to display hierarchy levels into separate columns rather than in the hierarchical format only” – can be achieved with local members using =EPMMemberOffset(; “MEMBERID”; 0; 1), where 1 is level


    The Changed Member formatting via EPM formatting works only when the template is online.” – do you want the template to contain VBA inserted by EPM??? This approach was used in BPC 7.5 – not perfect!


    When locking is performed and password is to be provided, this locking locks the entire sheet.” – Ups, simply select the whole sheet and unlock 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” – there is no automatic column auto-fit in Excel and users of Excel will be disappointed.


    B.R. Vadim



    (0) 
    1. Gajendra Moond Post author

      Hi Vadim

      Thanks for your response. Here are my comments:

      for the data available in the model only (only data loaded and/or saved – no extra combinations)” – No Data and Zero values in the report options

      I have written zero records from BW to BPC for certain formatting requirements. I cannot do that. I can choose remove empty. But I am comparing this functionality with the one provided in Integrated Planning where it gives you option to generate combinations based on Master Data or Transaction Data.


      “When a value is derived using EPM formula, it should stay when the template is taken offline.” – there are different options for Offline mode…

      I tried on my machine and various other machines. The offline mode works fine for dimensions but not for the fields derived from EPM formula – say a property. If you save and open the file, you will see the values until or unless you perform certain action like filter values using auto-filter. On that action, I get the error message for those fields. Dimensions remain fine in offline mode. I would be happy to edit my blog if it is not true.


      Option to display hierarchy levels into separate columns rather than in the hierarchical format only” – can be achieved with local members using =EPMMemberOffset(; “MEMBERID”; 0; 1), where 1 is level

      I agree to what you have suggested however, this should have been out-of-the-box. Secondly, since it is an EPM formula, it will not work in offline mode.


      The Changed Member formatting via EPM formatting works only when the template is online.” – do you want the template to contain VBA inserted by EPM??? This approach was used in BPC 7.5 – not perfect!

      I do not want that. I inserted EPM formatting for Changed Member to turn the cell to green on user input. It works perfect when online but does not work when offline. The cell does not change green.


      When locking is performed and password is to be provided, this locking locks the entire sheet.” – Ups, simply select the whole sheet and unlock it…

      I agree but when you do EPM formatting and select the option to lock it, should it not be locking only the cells that qualify for that formatting.


      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” – there is no automatic column auto-fit in Excel and users of Excel will be disappointed.

      I know this was little far fetched from my side. Currently I am using VBA to perform auto-fit on user input. However, I thought it would be great to have that functionality available as standard generally when large values are entered or calculated.

      (0) 
      1. Vadim Kalinin

        I have written zero records from BW to BPC for certain formatting requirements. I cannot do that.

        ——————————————-

        Not clear…  You can remove Emty or Zero and Empty fits your requirements



        The offline mode works fine for dimensions but not for the fields derived from EPM formula – say a property.

        ——————————————–

        For offline property values you will have formula like:

        That will correctly work with the result 2011



        The Changed Member formatting via EPM formatting works only when the template is online.” – do you want the template to contain VBA inserted by EPM??? This approach was used in BPC 7.5 – not perfect!

        I do not want that. I inserted EPM formatting for Changed Member to turn the cell to green on user input. It works perfect when online but does not work when offline. The cell does not change green.

        ——————————————–

        There is no special formatting in Excel to reflect data changed – you have to rewrite Excel 🙂

        Color change is done by EPM engine when online, and can be changed ONLY with VBA in the template when Offline (Offline mode assumes you don’t have EPM on the computer).



        When locking is performed and password is to be provided, this locking locks the entire sheet.” – Ups, simply select the whole sheet and unlock it…

        I agree but when you do EPM formatting and select the option to lock it, should it not be locking only the cells that qualify for that formatting.

        ——————————————–

        All cells in the Excel Sheet are locked by default (Excel default).

        (0) 
        1. Gajendra Moond Post author

          First of all, thanks again for your comments.

          I have edited my blog for first and last point in your latest response.

          Here are my remarks for offline functionality.

          The offline functionality is not up to the mark.

          Let us say, I click on the “Offline” button in EPM tab, save the file on desktop. I open the file again and guess what – it is protected. I cannot apply any data filters. Since I cannot perform any action other than data input, it is fine but applying filters is one of the most basic requirement.

          Second scenario – I do not click “Offline” button in EPM tab and save it locally. Now when I open the file, it is fine and unprotected. I apply data filter, select a filter value and see the error message “#Error, no current connection.” for EPM formula used for property derivation.Save Local.jpg

          In none of the two cases, I get a completely functional offline version of my template.

          Offline mode also does not assume completely that there is no EPM engine. I opened an EPM template on a machine with no EPM Addin installed. It will display as follows:

          No EPM.png

          This is not how I would define an offline functionality. Think from an end user perspective. You need to send a template to someone for review and that person does not necessarily have EPM installed.

          Also, the formatting of the changed member only when it is online is again not acceptable. Let us say I download the template and later in the day make changes. I should know what changes I made just by looking at it. The offline functionality must allow change in the color even when offline. Then only it is worthwhile to project offline functionality of BPC. How to achieve it is not something I would think of. When it comes to improving/innovating/developing something, my only questions are “What” and “When”.

          (0) 
          1. Beverly Armstrong

            Hey, Gajendra!

            You brought up some issues we have experienced as well with our client with offline mode and also the recipient not having EPM Excel Add in on their computer.

            Is the downloaded file password protected via EPM or Excel?  In our case we have to use the EPM protection because we have users who may change the report results.  Unfortunately we found that out with the legacy system – Hyperion Planning – in allowing exporting to Excel.  Often times we had to defend the integrity of the data due to users changing information and presenting it as their final numbers.

            I had a request where I needed to send an offline report to a non BPC user.  I saved the report in Offline mode and unprotected it per the EPM protection and Excel protection.  I copied > paste special > values the entire report so the client would not see #NAME, etc. when she viewed it.  I know this is not ideal and is another work-around, but has worked well for our client.

            Perhaps create an unprotected data input template to use strictly for non BPC users.  We are discussing this scenario to assist the controllers & accounting managers with gathering data for the respective departments.  The controllers & accounting managers can copy/paste special > values into the input schedules when online with BPC.  Some have used this method this year especially with account detail reports.  It worked very well and made the input move more quickly.

            We don’t really use the color coding for changes although it would be helpful in most instances.  I’ll have to look further at that as well.  As I’m typing this I’m wondering if the EPM Formatting Sheet can be helpful in Offline mode as well with the data changes.  If I stumble across anything, I’ll definitely share.

            Have a great day!

            Thanks!

            Beverly

            (0) 
            1. Gajendra Moond Post author

              Thanks for your response Beverly!

              The file gets locked automatically when the offline mode is selected. I did not put any other protection in the worksheet and the template was created unprotected. I believe it the default nature of the offline capability provided.

              EPM formatting sheet does not work in offline mode.

              The idea behind this blog is to highlight certain functionality that should work out of the box without us going for work-around for them. I am myself implemented a lot of work around of avoid these issues and I feel that some of these things should work without any manual intervention and work-around.

              (0) 
              1. Beverly Armstrong

                Hey, Gajendra!

                I’ll be sure to follow any updates or suggestions.  Since I’m fairly new to the BPC NetWeaver world , I definitely pay attention to any tips/tricks and observations from more experienced Admins and Consultants.

                I look forward to your future posts.

                Thanks!

                Beverly

                (0) 
  2. Beverly Armstrong

    Hello!

    When we set up our reports and input schedules, we followed most of the answers from Vadim’s post and they have worked well.  We implemented BPC 10.0 in 2013.

    There are some things I’d love to see change as well, but realize it’s software restrictions or unable to get the functionality to work properly.  Overall, our client has been pleased with the ease of using BPC.

    Below is how we have a work around for the Column Auto-Fit on a locked report or input schedule:

    EPM Ribbon > Options > Sheet Options > Protection

    Under Worksheet Protection, place a check mark in Format Columns.  This worked well with both reports and input schedules.

    It allows the users to format the columns to make the data values readable.

    sheet options.png

    Thanks!

    Beverly Armstrong

    NIIT Media Technologies, LLC

    (0) 
    1. Gajendra Moond Post author

      Hi Beverly

      Thanks for highlighting it. I am aware of this option and is very much identical to the options that you get when you protect a normal excel worksheet. The idea was for the system to perform auto-fit automatically when a large value is entered or calculated. The password provided here will help EPM to unlock the worksheet and perform the actions – like refresh etc along with options checked.

      (0) 

Leave a Reply