How-To Install & Use the PCM 10.0 REST connection and enable write-back functionality

The PCM REST connection is available as of PCM 10.0 SP8.

It Introduces READ/WRITE capabilities for PCM reports via the EPM Add-In.

Note: The write-back feature does not work for “Bill of Materials” and “Objectives and Metrics” type of PCM models.

Server Requirements

• Best to use PCM 10.0 SP8 Patch 1 or higher.
  You should use at least SP10 if you have renamed PCM dimensions or used non-English PCM models.
• No options need selecting during PCM installation – it gets installed automatically when doing the update.
• A new IIS Application Pool is created “PCMExcelServicePool”.
  When using a 32bit server (the standard 32bit PCM server installation) the “Enable 32-bit” has to be set to TRUE. For 64bit web-server (the x64 PCM server installer), this must be FALSE.
  See SAP Note 2062125 – PCM – How to identify installer used for PCM
  After changing this, recycle the Application Pool by using the ‘Recycle’ command in the action pane.

Client Prerequisites

• EPM-addin must be SP16 Patch 1 or above and has to be the .NET 4.0 version (a.k.a. BIG VOLUME version)
• A client needs to have the PCM Client software installed to get this option in the EPM-addin!
  Same PCM version like the server is needed (PCM 10.0 SP8 P1 or higher).
• Three files “PCMConnectionExtension.dll”, “PCMConnectionExtension.dll.config” and “PCMConnectionExtension.pdb” are placed in the PCM Install directory and then copied to the EPM Add-In install directory. After this, close excel completely and restart.

Creating the connection

1. There are 2 kind of authentication types possible. If you use standard PCM authentication, you can skip this step.
   If you have SAP BI Platform or IPS installed and want to use BO Enterprise authentication, you first need to check if the default BO Enterprise server is present the the EPM-addin user options.
   Find this setting in the Excel ribbon under EPM > Options > User Options
   
2. Launch Excel, click EPM > Log On > Create and select the PCM REST Connection from the Connection Type drop-down list
   Connection Name: enter any name you want to give
   Web Server URL: has to be in the format of
   “http://<your_pcm_server_name>:<your IIS port>/PCMExcelService/“
   p.s. You can test this URL in your browser where you should receive a error like “403 Forbidden access”.
   When you click “Next” on the screen below, the connection will be tested as well.
   
3. Specify Login Connection
   PCM Server: PCM primary model server name (as set in PCM Configuration Wizard)
   Sign-On Type:
   • “Default” will revert to default authentication as you have setup in PCM Configuration Wizard (usually standard PCM security)
   • “Enterprise” will revert to BO Enterprise (BI Platform or IPS) authentication with the CM

     

4. Select the specific PCM model you want to connect to and click OK
   Tip: Are there no PCM models showing? Then check all the server requirements and client prerequisites above!

Designing the input schedule

• Best Practice is to include all dimensions in either row, column or page axes.
  OR you can also choose to lock the context for those important dimensions that should be fixed, for example: Measures.
  -> Just remember that another user that opens the report, might have a totally different context.
• Especially make sure you specify a specific member for ‘Measures’ and ‘Currencies’ dimensions! Otherwise no data might return.
• If you have selected a parent member, make sure the appropriate ‘Consolidation Type’ is set from the hierarchy design screen in the PCM Model builder.
• For input schedules: make sure you are sending data on base level member for each dimension and don’t forget to set the report option that enables data input (EPM > Options > “Use as input”)

• Hide irrelevant dimensions from the EPM context pane (as PCM has many dimensions!).

The default context like this:

can quickly be turned into following, by using the EPM-addin Context Options:

• For dynamically expanding reports & input schedules, make sure you use the EPMFormattingSheet functionality.
• For ad-hoc reporting, I’ve found it very useful to create a little macro that checks the MEASURE from the context, and then hide/unhide relevant/irrelevant dimensions using for example the API SetContextOptions that comes with the EPM-addin.
• Check out the EPM-addin academy on SCN & youtube for more tips to create something nice!

TroubleShooting

• HTTP 500 Internal Server Error
  • This can be caused by the ISS application pool, see Server requirements above.
• Excel crashes
  • Check your EPM-addin version, try a higher version.
• Debugging
  • See the How-To debug EPM-addin guide: http://scn.sap.com/docs/DOC-38755

  • Use the FIDDLER web debugging tool on the client to capture the error in the Excel Add-In, then look at the enhanced details in the Web View.
    It might be that you get a “401 – Unauthorized: Access is denied due to invalid credentials” error, this indicates you need to check the authentication / security settings of the PCMExcelService in IIS.

  • Use IIS Manager to locate WEB.CONFIG file associated with the PCMExcelService nod
    You can edit this file and add the field <customErrors> to the <system.web> section like this:
    <configuration>
    <system.web>
      <customErrors mode=”Off” />
    <system.web>

For more information, see the “Support for the EPM Add-in” topic in the “SAP BusinessObjects Profitability and Cost Management 10.0 Master Guide”, as well the “Prerequisite Software” section in the “SAP BusinessObjects Profitability and Cost Management 10.0 Installation Guide”.

SAP Profitability and Cost Management 10.0 – SAP Help Portal Page