In part one of this blog post series, I started talking about some of the possibilities and opportunities that SAP Profitability and Cost Management (PCM)’s automation tools provide. This is a follow-up post that talks about a few further examples.
To recap briefly, you can combine various bits of SAP PCM to side-step manual processes like roll-forward, data acquisition and hierarchy maintenance. Some of the benefits of automation are that you can reduce the risk of error, reduce key-person dependencies, implement an automatic audit trail, and save time. The saving of time is hugely important to getting business benefits from PCM, because it frees people up to actually use the data from SAP PCM, instead of spending their time nursing the system to produce that data.
And the last bit of recapping: these are the parts of SAP PCM that we’ll use in the examples:
Books: SAP PCM’s own end-user interface provides various ways to interact with a PCM model. We’re mostly using its ability to provide the user with buttons that run VB Script routines. VB Script can work with the underlying model, and can also work with the computer’s operating system to read and write files
PCM Console: SAP PCM’s scripting tool lets you define a sequence of PCM actions to be taken. You can define a sequence of commands that, for example, open PCM models, specify a log file for tracing and audit purposes, calculate models, export data/results, import data via Data Bridge, and more
Data Bridge: SAP PCM’s data acquisition tool can update a PCM model’s structure and data from input sources in a wide variety of formats.
Example – Moving data from one part of a model to another
One real-life problem that I’ve dealt with recently is where a user wanted to move model data from one version to another, within the same model. The users wanted the ability to regularly take a copy of their latest forecast, and run some scenarios on it; when they were satisfied with the scenario, they wanted to replace the original forecast with this scenario.They also didn’t want to just use a separate copy of the SAP PCM model for the scenario modelling; they needed the original forecast and the scenario to be in the same model so that they could easily compare the two.
This was done by putting a button on a book.The VB Script behind the button called PCM Console, and PCM Console ran the appropriate data out to an XML file, using an export specification (ESP) file to specify the data that was transferred. The ESP file was configured to select the Forecast version, but also to map it to the Scenario version. That way, we could simply reimport the XML back into the model, as part of the same PCM Console job and the data would automatically go into the Scenario version.
When the users had had a chance to amend the Scenario version, and they were happy with it, we just ran a similar PCM Console process, but in reverse: we exported the Scenario version to XML (mapping the version to Forecast), then imported it back into the model.
We found a problem, though. Whichever direction we were transferring data, we found that if data already existed in a given cell in the target version, but didn’t exist in the source version, then that cell wasn’t overwritten by the data being loaded. Consequently, there was a pretty good chance that our target version was going to end up not as a clean copy of the source version, but as a copy of the source version with remnants of the original data scattered around here and there.
We could have changed the process to use CSV files and Data Bridge, rather than XML, to transfer the data; Data Bridge has a useful REPLACE option that wipes model data for any Version/Period combination in the input data. But there were reasons why we couldn’t use that approach (it’s complicated but let’s just say that we didn’t actually want to wipe all the data in the target version). So instead we added to the PCM Console routine some steps that ran at the start of the process:
Export the replaceable data from the target version in CSV format
Run a Data Bridge Import to load the CSV back in, but ignore the values from the CSV and force a data value of zero instead for every row - this is done by using a REPEAT statement in the Data Bridge specification file.
This wiped the data in the target version that we wanted wiping, and left the rest untouched, so we could then run the PCM Console steps to transfer the data.
As you can see, although the initial requirement seemed simple, it turned out that managing the data was more complicated that we at first thought. Automating this process, rather than handling it manually, was definitely beneficial because any misstep in the manual process here could have led to incorrect data in the model. Worse, this was a situation that might well have remained undetected.
Example – Backup models
It’s obviously good practice to back up your models, so that when something goes wrong – which it eventually will do – there’s a way to recover a model, or even a whole system. There are several ways to do this, including:
Has the IT department automatically back up the SAP PCM database at regular intervals, e.g. overnight? They are probably doing this already (we certainly hope they are). This sort of back up is useful protection against a complete system failure, corrupt database, and so on. But recovering from a complete database backup is a long process if you’ve only overtyped a few cells of data in one model, or inadvertently changed a rule, or some other relatively small change. You have to log all the SAP PCM users off the system, restore a database from backup, reconfigure the PCM application server to use the restored database, get what you need from the backup, and then reconfigure SAP PCM to point back to the original database; then you can finally fix your broken model
Manually export the models to XML at regular intervals, or whenever appropriate, e.g. before major changes. This requires a user to remember to carry out the task. If you’re very well organised, this can work, but often it isn’t carried through with the rigour that it requires
Use SAP PCM automation to export models regularly. This is definitely worth considering. The idea is to use a scheduling tool, like Windows Task Scheduler, which allows you to run a program at a pre-determined time. So you could set it up to run PCM Console at (say) 3:00AM; PCM Console would open each important model in turn, and export it in its entirety to XML. It’s also a good idea to get Windows Task Scheduler to send an email to selected users, perhaps attaching the PCM log file, so that the users can see whether the backups are running successfully. The last thing you need, when one day you desperately need that backup, is to find that the backups haven’t been running for six months.
Note that you don’t need to just choose one of the above options. There’s no problem with having PCM Console backup models to XML and having the IT department run their database backups – in fact it’s highly recommended, because then you’re (almost) safe even if one of the backup mechanisms fails.
Example – Selective output
To carry out a one-off export of information from SAP PCM to an external file is straightforward. Using the Export option in Model Builder, you can select the tables and dimensions you want, and then click a button to send the data (or results, or model structure, or security tables) to a file.
If you save the selections to an Export Specification (ESP) file, you can repeat the same export as well. And you can call the ESP file from PCM Console, so it’s programmable too.
What’s not so easy is dynamically changing the data that you export. Say, for example, that you only want to export a particular period, or the Responsibility Centers within a selected division.
What you need to do here is dynamically build an ESP file, then run it using PCM Console.
The ESP file is a simply a text file; in fact it’s an XML file. You can open an ESP file in an XML editor, or Windows Notepad, and see what it looks like. In fact, if you’re going to get a book script to create an ESP file for you, this is the best place to start. Create an ESP file from within Model Builder, then have a look at its contents. This gives you the exact structure of file that you want your script to produce.
Here’s a sample ESP file:
So let’s suppose that you want your book button to export Activity Driver Values for a selected Responsibility Center. You might do the following:
Add a dimension tree object to a book, and link it to the Responsibility Center dimension. The dimension tree now always lists the current members of the Responsibility Center dimension, and lets the user select one of them
Add a button to the book, and write a script for the button so that, when clicked, the script gets the name of the item selected in the dimension tree. It then builds an ESP file in the format shown above, but replacing the Responsibility Center shown with the selected item from the dimension tree. (A potential gotcha here is that you need to save the file with Unicode encoding)
Add an extra step to the button’s script so that it calls a PCM Console job. Set up the PCM Console job so that it logs in to PCM, opens the model, and then exports data, using the dynamically-generated ESP above.
So there are a few examples of what’s possible with PCM automation, to get you thinking. There are many more possibilities than I’ve shown you here, but the tools are very powerful so almost anything you might want to automate is possible. If I had one wish it would be that some of these features were exposed via a proper API, but maybe that’s something that could be added by SAP in the future!