Have you ever had a business requirement to automate copying of supporting detail across multiple Scenario/Version combinations?
I have a requirement to not only be able to copy the comments along the Versions (Working, Official), but also the Scenarios (Annual, Current Estimate, Prior Estimate). Some of this can be automated using Version copy, but anything other than copying a version to another version isn’t easily automated out of the box.
So I was thinking about how I could create a custom solution for this. I figured that I could take the XML file from an LCM backup, it’s only text after all, and alter that.
Since most of my recent clients have been on some flavor of UNIX I feel pretty comfortable in Korn or Bash shell scripting, but on a windows system how do I want to accomplish this XML manipulation? DOS batch scripting didn’t seem like a very good choice. Then a co-worker of mine mentioned Powershell. “It is very kornshell like with the pipelines” he says. I think to myself, “Cool, I’ll give it a shot”. The thinking here is that I can perform an LCM export, clear the Planning tables, edit the text values in the XML files, and re-import with LCM.
My requirements are such that I will need a “Copy”, a “Move” and “Delete” functionality for the cell comments.
A little research has shown that the LCM export always puts the cell comments into a file named “Cell Texts.xml”, located in the <export dir>\<export_name>\HP-<app name>\resource\Relational Data\ folder. Nice, I should be able to control all the variables from the calling script.
First I wrote a script with the function “modifyCellNote”, this contains the Move, Copy , and Delete functionality.
The calling script then simply needs to dot source this file that contains the function.
With the following test “Cell Notes.xml” file as input…
I ran the tests for each Move, Copy and Delete. For the move….
The resulting output file changed the version/scenario to “Official” “Prior Estimate”.
For the Copy…
The resulting xml file contains the original (“Working”, “SP”) and the copy(“Official”, “Prior Estimate”)
And for the delete…
There are no more cell notes in the xml file.
When you LCM export and manipulate the contents of the xml file, you then to clear the Cell Notes tables in planning. This can also be accomplished with Powershell. Since these planning tables only data, the changes will be reflected right away, no bounce of Planning services required.
Here is an example of the Powershell script to delete the contents of the Planning table entries for Cell Notes (comments)…
Then you can coordinate these examples in with the script that is calling the essmsh to invoke the Maxl command that is performing the essbase DataCopy, which can also incorporate the same script variables for the “Version” and “Scenario” that need to be copied.
So there you have it. Happy XML hacking to you.
Have more questions? We can help:
- Contact us using the form here, or call us at 952.947.9300.
- Drowning in data but parched for predictive insights? Arrange a free half-day analytics health check.
- Talk to our experts face to face at one of our upcoming industry events.