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.

Hyperion Planning - modifyCellNote

(click to enlarge)

The calling script then simply needs to dot source this file that contains the function.

. “C:\Powershell\XMLtest\modifyCellNote.ps1”

With the following test “Cell Notes.xml” file as input…

Hyperion Planning - Cell Notes.xml file as input

(click to enlarge)

I ran the tests for each Move, Copy and Delete.  For the move….

Hyperion planning - Move Copy Delete

(click to enlarge)

The resulting output file changed the version/scenario to “Official” “Prior Estimate”.

Hyperion Planning - output file changed

(click to enlarge)

For the Copy…

Hyperion Planning - for the copy

(click to enlarge)

The resulting xml file contains the original (“Working”, “SP”) and the copy(“Official”, “Prior Estimate”)

Hyperion Planning - resulting XML file

(click to enlarge)

And for the delete…

Hyperion Planning - for the delete

(click to enlarge)

There are no more cell notes in the xml file.

Hyperion Planning - no more cell notes in XML file

(click to enlarge)

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)…

Hyperion Planning - example Powershell script

(click to enlarge)

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:

Jon Harvey

Jon has been a part of the Oracle team at eCapital Advisors since 2009 and is passionate about educating and teaching clients and team members how to apply the technology to drive business value within organizations. An Oracle Certified Expert in both Hyperion Planning and Essbase, Jon oversees the technical aspects of EPM project delivery and managed services. In addition, he’s responsible for ensuring the continued growth of the practice’s technical capabilities and serves as the primary interface between eCapital and the Oracle user community.

Comments are closed.