Creating a Basic Table of KPIs with Actuals to Export - SSM 10.1

[Former Member]

Hi all, we have SSM 10.1 with SP8 I believe. Our implementation is about 3 months old and we're still learning our way around the tables in SQL. One of our requirements is that we need to be able to export all of our KPIs with actuals so we can feed this to our homegrown budgeting software. We thought the easiest way to do this would be to use some SQL queries since we haven't been able to find a solution to export data in a machine readable format from SSM or our consultant. We've made some pretty good progress at creating our own little data dictionary, but we're struggling a bit linking CPMS_tables to the EA_tables - the EA_tables are where we think the actuals live (could be wrong though...).

I've reviewed some of the various posts here - like and also looked at some of the various screen captures like Providing SSM data to SAP BusinessObjects reporting tools - Exhausting all scenarios - Clariba I Ind... but we're still running into a brick wall.

We've done a pretty good job of defining our different tables and we've got CPMS_NODE_LIB joined to CPMS_STRAT_HIER and we're able to see the context it belongs to by joining to CPMS_CTX_HIER and we're able to see whether it's a KPI, Priority, or Goal by using LEVEL_NAME in CPMS_NODE_LIB.

We've also doing a really good job of being able to link these guys to our Initiatives that we have defined. But we haven't been able to figure out where the target and actual values are started and how to join the KPIs to those tables. We see the EA_DATA, EA_DETAILS, and EA_MASTER tables and they look like they're storing targets and actuals, but 1) I've also read those are just staging areas until the data is loaded to the PAS cube and 2) I don't see any foreign keys that tie to the CPMS_NODE_LIB or CPMS_STRAT_HIER table. Similarly, I've looked for PAS tables in our SQL database but I'm just not seeing anything. I've also tried to use the NAMED_QUERIES table to get some ideas, but it looks like a good amount of the queries are referencing old tables like KPI that are no longer in this version of SSM.

Any thoughts or guidance?

[Former Member · Accepted solution]

Frank,

The reason you aren't finding the Actual and Target values in the system database is that they aren't there! What you see when looking at the EA_ tables are the Entry and Approval staging tables from which the values are loaded into PAS. But by definition anything that is an automated load won't go through Entry and Approval and so not appear in those tables.

These values are held in PAS and you can either use the Excel Add-In or create an extract from PAS via a procedure to generate something for your budgeting software.

Regards

Colin Cooper

[Former Member · Accepted solution]

Thanks, Colin, appreciate it. Is there a place I can go to get this Excel Add-In?

[Former Member · Accepted solution]

Frank,

Yes go to Service Marketpace and within the section for SSM and in the Comprised Software Component Versions there is a link to SAP STR. MGM. WEB XLA.10.1 This is the Excel Add-In Make sure you get the one that reflects the Service Pack you are running as there is a new one for each new Service Pack.

There is a user guide for this as well but SAP are doing a good job of hiding it! Someone has forgotten to include it on the version 10.1 documentation page. So to get it you have to do the following:

1. Scroll down that page till you see the SSM links in the index on the left hand side and go to the equivalent page for SSM 10.0
2. Scroll down to the section for System admin & Maintenance information and click the link for Additional administrative guides
3. Go through the Disclaimer page when you will be aske for your S number
4. This shows all the 10.0 documentation but you need to select the 10.1 link from the index on the left
5. On the top right just above the list of documents, click the link 'Display all documents' and finally you will be able to download the Excel Add-In guide!

Regards

Colin