ODI Launching Calculation Manager rules

admin

This blog has come about due to a post of the Oracle Planning forum, basically it is addresses an issue with executing Calculation Manager rules using the command line utility CalcMgrCmdLineLauncher using ODI.  In the past I wrote a blog on how to execute Hyperion business rules using ODI but I to be perfectly honest I have never tried the same with Calculation manager rules so I thought I would give it a go.

Once again it would be nice if there was an API available to launch the rules but unfortunately there is not one available yet so we are left with the command line utility which can be restrictive in terms of logging and functionality.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wpsF0A5.tmp.jpg

First of all let’s start with a simple rule which also uses a variable with a runtime prompt for defining the scenario member.

Before even going near ODI I am going to make sure that I can get the command line utility running successfully.

The syntax for using the utility is

CalcMgrCmdLineLauncher.cmd [-f:passwordFile] /A:appname /U:username /D:database [/R:business rule name | /S:business ruleset name] /F:runtime prompts file [/validate]

The parameters are pretty much self-explanatory but if you are looking for detailed information then have a read of the section “Launching Business Rules With a Utility” in the planning admin guide.
If you are unsure on how to create an encrypted password file then have a read here

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9BC0.tmp.jpg

I created a RTP file and the format for the file is
<VARIABLE_NAME>::<MEMBER_NAME>

Password file will be : password.txt
Application name: PLANSAMP
Username: admin
Database/Plan Type name : Consol
Rule name : SIMPLE
RTP file: RTP.txt

The location of the utility is
<MIDDLEWARE_HOME>\user_projects\<instancename>\Planning\planning1\

This means the command line syntax to run the SIMPLE rule would be
E:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\CalcMgrCmdLineLauncher.cmd -f:password.txt /Afile:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9BD1.tmp.pngLANSAMP /U:admin /D:Consol /R:SIMPLE /F:RTP.txt

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9BE1.tmp.jpg

Personally I find the output from the utility to be pretty poor and the assumption is if there is no error message it ran successfully.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9BF2.tmp.jpg

It is possible to check the Job Console from within planning to see more details around the execution of the rule, this information can also be queried from the planning application relational table HSP_JOB_STATUS

If the rule is run from planning then there is additional information in calcmgrlaunch.log in
<MIDDLEWARE_HOME>\user_projects\<instancename>\diagnostics\logs\planning

The command line utility unfortunately does not write to this log.

So now the rule is running from command line it is time to transfer this logic to ODI, I am using 11g and I am going to use an ODI procedure using the ODI Tools technology, if you are using 10g the process is very similar.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C02.tmp.jpg

The ODI tool I am using is OdiOSCommand which you probably guessed invokes an OS command line shell using cmd on Windows and sh on nix.

There are parameters to split out the standard output and errors, working directory is the location the command is executed from and synchronous waits for the completion of the command.

OdiOSCommand "-OUT_FILE=E:\ODIDEMO\CalcManager\SIMPLE.log" "-ERR_FILE=E:\ODIDEMO\CalcManager\SIMPLE.err" "-FILE_APPEND=NO" "-WORKING_DIR=E:\ODIDEMO\CalcManager" "-SYNCHRONOUS=YES" E:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\CalcMgrCmdLineLauncher.cmd  -f:password.txt /Afile:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C13.tmp.pngLANSAMP /U:admin /D:Consol /R:SIMPLE /F:RTP.txt

If you have issues running the command then there is also an extra parameter "-COMMAND=" so the syntax would be

OdiOSCommand "-OUT_FILE=E:\ODIDEMO\CalcManager\SIMPLE.log" "-ERR_FILE=E:\ODIDEMO\CalcManager\SIMPLE.err" "-FILE_APPEND=NO" "-WORKING_DIR=E:\ODIDEMO\CalcManager" "-SYNCHRONOUS=YES" "-COMMAND=E:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\CalcMgrCmdLineLauncher.cmd  -f:password.txt /Afile:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C14.tmp.pngLANSAMP /U:admin /D:Consol /R:SIMPLE /F:RTP.txt"

I am not a fan of hardcoding of parameter values and prefer to use variables as much as possible.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C25.tmp.jpg

Each variable was created as text type.

CM_DIR – Working directory containing RTP, password and log files.
CM_PASSWDFILE – password file containing encrypted password for the administrator user.
CM_PLANAPP – Planning application name.
CM_PLANDIR – Path of the calculation manager command line utility.
CM_PLANTYPE – Plan type to run the calculation against.
CM_RTPFILE – Runtime prompt file
CM_RULE – Calculation Manager rule to execute.
CM_USER – Planning application administrator.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C35.tmp.jpg

These variables were then transferred to the syntax of OS command.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C46.tmp.jpg

A scenario was generated from the ODI procedure as I will be using a Load Plan to execute the command line call.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C56.tmp.jpg

It is possible to achieve the same results using a package if you prefer or are using 10g.

The scenario was added to the load plan and all the variable values were set .

The logs generated from running command line utility will be based on the name of the rule e.g. SIMPLE.log and SIMPLE.err

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C67.tmp.jpg

The standard out log includes the variable information.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C87.tmp.png

The error log includes information messages as well as any errors that were generated.

As I said earlier there is not really any information in the logs to say that executing the rule was successful.

So let’s change the RTP to an invalid member “Actual1” and see what happens when it is executed.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C88.tmp.jpg

The Operator shows a successful execution, this is because the utility has not returned an error code to the calling OS shell.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9C99.tmp.jpg

The output log now contains the invalid member in the launch variables.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9CA9.tmp.jpg

The error log does have an additional line with a failure message even though it doesn’t say it has failed and the line should have started ERROR: but unfortunately that is the best you get at the moment with the utility.

This means the logic is that if the rule completed successfully the last entry in the error log will be

INFO: Application PLANSAMP exists in Registry: {1}

Otherwise it will have failed and contained an error message.

Using ODI there are a number of different ways to handle this logic and generate a failure, one method could be to load the error log into a table and then analyse the records which I have described the process in a previous blog.

Another technique is to simply read in the error lines of the error log and check to see if the last line starts with “INFO”, this could be done using Java, Groovy or Jython which I am going to use.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9CBA.tmp.jpg

I added a step to the ODI procedure using Jython technology which will execute after calling the command line utility, the code basically reads in all the lines of the error log which is not a problem because the file is so small and then checks if the last line starts with ‘INFO’, if it doesn’t then an error is raised.

file:///C:\Users\Tina\AppData\Local\Temp\ksohtml\wps9CCB.tmp.jpg

If the load plan is run again this time it fails and generates an error with the message to why it failed so at least now any errors can be trapped and acted upon.