Wednesday, 8 February 2012

ODI Series – Launching Calculation Manager rules

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.


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


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 /A:PLANSAMP /U:admin /D:Consol /R:SIMPLE /F:RTP.txt


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.


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.


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 /A:PLANSAMP /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 /A:PLANSAMP /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.


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.


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


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


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


The standard out log includes the variable information.


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.


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


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


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.


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.


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.

6 comments:

Anonymous said...

Hi John!
You're becoming some kind of legend to those who start working with Oracle technology.

Since you know so much about ODI, I would like to ask you if there is any chance of execute Essbase Commands through ODI to clean cube measures before I load them.
(i.e.
FIX (fixMbrs)
CLEARBLOCK ALL;
ENDFIX
)

The options available in the ODI Interface aren't good for me because I need to delete some specific measures.

Thanks, and Keep up with the good work.

Jarbas

Martina said...

I was wondering is there a reason you didn't just make a select from HSP_JOB_STATUS in the app "metadata" to get the BR status?

John Goodwin said...

Hi,

There is nothing stopping you querying the table if you feel that is a solution that suites you.

Cheers

John

Michele said...

HI John,

I'm trying to launch a rule with ODI.
The problem is that ODI is installed on another server, so when I launch the cmd goes in error.

Could you help me?

Thanks in advance
Michele

shivank sailakwal said...

Hi Martina,

I think you need to install ODI agent on calc mgr server. So that ODI can communicate to that server and calc mngr node.

Julien said...

Hello John,

Since 11.1.2.3, I think all logs goes to PlanningCLU.log file and the ODI error output is empty...