ODI Series - problems using pre/post maxl option in interfaces

 This problem was brought up on the ODI forum so I thought it is probably more beneficial if I write it up in case anybody experiences the same problem.

I will be running through the issue on a windows OS but the concept is the same if using *nix

If you are integrating with essbase 11.x and using either the PRE_LOAD_MAXL _SCRIPT or POST_LOAD_MAXL _SCRIPT options in any of the interface KM options

It is possible you could encounter one of the following error messages in the operator.

or the following error

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File "<string>", line 89, in <module>
at com.hyperion.odi.essbase.ODIEssbaseConnection.executeMaxl(Unknown Source)
at com.hyperion.odi.essbase.AbstractEssbaseWriter.beginLoad(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)com.hyperion.odi.essbase.ODIEssbaseException: com.hyperion.odi.essbase.ODIEssbaseException: Cannot run program "essmsh": CreateProcess error=2, The system cannot find the file specified
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)

I will just briefly explain what ODI does behind the scenes when you use any Maxl options.

Once an interface is executed the Java code used by the knowledge module checks if one of the Maxl options is populated, if it is it then uses a property file to format and form a command line statement.

essmsh = MaxL shell
-m error = Sets the level of messages returned by the shell. Valid values are: all (the default), warning, error, and fatal.
-s = server name
-l = user and password
0 = Essbase server name
1 = Essbase Port
2 = User name
3 = Password
4 = Maxl Script

An example of the command line generated is

essmsh -m error -s essServer:1423 -l admin password F:\scripts\log.mxl

The code then uses the Runtime Java class to execute the statement.

The reasons behind the errors are a combination of essmsh cannot be found as the location does not exist in the environment path variable.

In ODI this would generate the error message

Cannot run program "essmsh": CreateProcess error=2, The system cannot find the file specified

If the location of essmsh command does exist in the path variable then the following can occur

In ODI this would produce the first error I highlighted at the beginning of this blog.

From version 11 a number of environment variables are not automatically set and the use of the  StartMaxl script is the preferred method to start up a MaxL session.

If have the essbase client installed and edit the startMaxl script then you can see that the variables ESSBASEPATH, ARBORPATH and PATH are set before calling the MaxL shell.

It is similar on the essbase server install except the startMaxl script calls a setEssbaseEnv script which sets additional variables.

The simple solution to fix the issue with OD calling the Maxl Shell is to set the environment variables either at OS user, system or ODI agent level.
By ODI agent level I mean updating the scripts that start up the agent and adding in the variables.

If the ODI agent is using the essbase client then an example variables would be

ARBORPATH -  <MIDDLWARE_HOME>\EPMSystem11R1/common/EssbaseRTC-64/<VERSION>

e.g.  E:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0

The ESSBASEPATH variable should be exactly the same as the ARBORPATH variable

The following locations are required in the PATH variable.

<MIDDLEWARE_HOME> EPMSystem11R1\bin; and <MIDDLWARE_HOME>\EPMSystem11R1\common\EssbaseRTC-64\<V ERSION>\bin;

e.g.  E:\Oracle\Middleware\EPMSystem11R1\bin; E:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0\bin;

If the ODI agent is on the essbase server then just have a look at the setEssbaseEnv script to find out the values to use for the ARBORPATH,ESSBASEPATH and PATH variables.

Once the changes have been made restart all the ODI related components and possibly reboot the machine if using an older OS to make sure the variables have been applied and then the interfaces should be able to execute the MaxL scripts.