Welcome to Part 1 of a multi-part series detailing the creation of some out-of-the-ordinary processes leveraging Hyperion Planning command-line utilities, new (and old) Essbase CDFs and Hyperion Planning back-end repository tables. In this series, we will look at a requirement that arose at a recent client requiring the ability to update security within a Planning application real-time upon the execution of a calculation rule. Boiled down, the requirement consisted of the ability to update security on a selected entity member from read/write access for all groups with (write) access to read-only for these same security groups. My first thought was to use the Planning ImportSecurity.sh command line utility in some form or fashion to update the security for the Planning application. First hurdle to overcome? How to execute the Planning utility from a calculation rule in Hyperion Planning. If you will recall, Planning calculation rules run on the Essbase server. No problem you may think and that would normally be the case if Essbase and Planning were installed on the same server. In this environment though, Hyperion Planning and Essbase were installed on two separate machines. In this first Part 1 episode, we discover how to enable the use of Hyperion Planning command line utilities on a server (Essbase server in this case) that does not contain a Hyperion Planning installation.
To begin our journey into the innards of a Hyperion installation, we first must find all the locations where Hyperion Planning libraries and related files are stored. Searching on the server where Hyperion Planning is installed, we will find the following (fairly) obvious locations related to the Planning install. I had, at a previous client, done this process of enabling Planning utilities on a non-Planning server before and remembered the following two locations as being key right off the bat. Those two locations include:
- /u11/oracle/Middleware/user_projects/epmsystem1/Planning
- /u11/oracle/EPMSystem11R1/products/Planning
Note that the exact path for your install may differ slightly depending on the volume (Linux) or drive (Windows) setup and how the install was performed. For our discussion, /u11/oracle is the (Linux) base path for all the Hyperion product installations. The first directory listed contains all the Planning command-line utilities that we want to use. It should be obvious that we will need this directory and its contents. So, let’s copy it from the Planning server to the Essbase server. The whole directory should be copied to the same location on the target server. For the Linux install I was working on, I used the ‘scp’ Linux command to copy from source (Planning) server to target (Essbase) server. The screenshot below shows the syntax for the scp command (shown in putty terminal).
The ‘-r’ flag indicates a recursive copy of all the contents in the source directory. The single period at the end of the command tells the ‘scp’ command to copy the directory into the current location. Note that I’m running the ‘scp’ command on the target server. In a Windows environment, simply use Windows Explorer to copy the directory from source server to target server. After copying the first directory and its contents over you should see the ‘Planning’ directory on the Essbase server as below in the screenshot.
The next directory to copy, /u11/oracle/EPMSystem11R1/products/Planning, contains Planning related Java archives and other Planning libraries essential to the executing the Planning command line utilities. Copy this directory over into the same location (/u11/oracle/EPMSystem11R1/products/) on the Essbase server. After copying the directory from the Planning server, the target directory structure should look like below. Before, only Essbase and Foundation directories existed (outside of the numerous _uninstX directories). Now it includes the Planning directory as well.
So, we’ve done the two obvious directories but if you attempt to run the utilities on the target (Essbase in this case) server, you will encounter various Java exceptions. These exceptions are due to missing Java libraries. After more troubleshooting, I discovered two other directories that must copied over from the Planning server to our target server. The final two directories include:
- /u11/oracle/Middleware/EPMSystem11R1/common/calcmgr
- /u11/oracle/Middleware/EPMSystem11R1/common/planning
These directories contain Java libraries used by the Planning command-line utilities. Copy these directories to the target server using the same paths as the source server. Now that we have all the supporting files and libraries you may think we are good to go. And for some utilities, such as the ExportSecurity.sh, you would be correct. They should work as expected. However, not all the utilities will work correctly. In this example, I was looking to use the ImportSecurity.sh command. While I did not encounter any more Java exceptions when executing the ImportSecurity.sh command, I noticed that security on my Planning application was not being updated. Time to dig even deeper into these utilities to find the source of my issues.
After some time fiddling with the utilities and supporting scripts, I discovered that the command line utilities call a script ‘setHPenv.sh’ that sets various environmental variables, CLASSPATHs, etc. before the actual Planning command executes. This script is located in the “/u11/oracle/Middleware/user_projects/epmsystem1/Planning/planning1” directory (first directory we copied over). For some reason, a section of the ‘setHPenv.sh’ script that loads libraries based on the OS in use was not working properly. This script sets the type of OS to a variable and for some reason the “CASE” statement at the end of the script was not interpolating the variable properly. In fact I was able to determine that in my situation the variable was evaluating to an empty string. Your mileage may vary, but in order to get the ImportSecurity.sh script to function properly, I had to hard-code the check in the CASE statement (inside the SetHPenv.sh) as seen below. I changed the CASE check from “case $OS in” seen the first next screenshot.
to “case Linux in” as seen below.
With the change made above to the ‘setHPenv.sh’ script, the ImportSecuriy.sh was now working properly on the Essbase server. I did not test every Planning command-line utility available on my Essbase server, but ExportSecurity.sh, ImportSecurity.sh and Refreshcube.sh all worked properly. Please note that this process of copying directories from the Planning server to a target server is only going to work for target servers that have some part of the EPM stack installed. In a typical install I come across, Essbase is often installed alone on a server with the other Web Service based products (EAS, Planning, Workspace, Shared Services, etc.) installed on a “Foundation” server. Depending on the distribution of products across servers, this process of copying Planning directories to enable the use of utilities on non-Planning servers may or may not work.
In the upcoming Part 2 of this series, I will detail the configuration and use of the new @CalcMgrDBSelect Essbase CDF recently released in a 11.1.2.4 patch set. The official Oracle documentation for this CDF is very light and leaves a lot to be desired. I will walk through configuring the required SQL drivers (Oracle) and how to use the CDF in a calc script. I will also demonstrate the use of the @CalcMgrExecuteMaxLScript CDF that enables the execution of MaxL statements from calc scripts and calc rules. In the final Part 3 of this series I will tie all these steps together into a calculation ruleset that dynamically updates security on a Planning application from execution of a Ruleset by end-users of the application. Stay tuned!
Additional Notes:
-Instead of manually copying the Planning directories from server to server, my assumption is that you could short-cut it and just install Hyperion Planning on the target server. Just make sure not to run the configuration setup after the install. This should accomplish the same task of creating the required Planning directories on the target server. Always more that one way to skin a cat so to speak.
-If you run into issues with the ImportSecurity command running and not updated security in your Planning application, make sure to check the file encoding on the Secfile.txt (security file). I had some troubles with security not updating and it appears that it was improper file encoding for the security file (Linux environment).
Whew, this post made Ragnar thirrsty!
