ODI Series – Patch Update – Essbase data load issue resolved

Well it certainly been a long time in the making but at last the patch has been released to tackle the essbase data issue, the problem manifests itself when performing a data load and a rejected record is hit e.g. an unknown member, the load reverts into loading record by record instead of the bulk commit size set in the KM options.

If you want to find more information on the bug, have a read of one of my earlier blogs here.

The patch is available on metalink and here is all the information you need.

ORACLE DATA INTEGRATOR 10.1.3.5.2_02 ONE-OFF PATCH

Patch id - 8785893

8589752: IKM SQL to Essbase Data - Load in bulk mode instead of row by row processing when an error occurs during load

The patch has to be installed on a version 10.1.3.5.0 and greater

The patch itself is only 59KB, it is just a replacement jar - odihapp_essbase.jar

To install the patch shut down any ODI processes such Designer, Topology Manager, Security Manager, Operator or Agent.

Rename the existing odihapp_essbase.jar that is in oracledi\drivers and extract the patch jar to the same location.

Then you can restart everything back up.

So does it work, well lets have a quick go. First all what you need to be aware of is that if you don’t use an essbase load rule the problem will still exist, the fix only works with the use of a load rule.



Back to my trusty quick example that loads data from a database table into the sample basic essbase database.





I have put a deliberate error for Market to trip up the data load

Here is example of the log file before applying the patch



And here is the log file after patching



And an error file is produced with the rejected records, so looks like it is a success, I didn’t have a chance to test a large set of data but it looks to be using the same method as a standard essbase data load using a rules file so performance should be the same. Good news at last!!!

ODI Series – Executing Hyperion Business Rules Part 2

In the last part I went through a couple of methods on how to execute business rules from ODI, today I just want to quickly show how you can incorporate this into a Knowledge module.

Incorporating this into a KM will allow you to say do an essbase dataload and then execute a specified business rule using one interface, this method will also allow you to pick up the connection information so you don’t have to specify it again. It can be incorporated into any KM I am going to use “IKM SQL to Hyperion Essbase (DATA)” in my example.

I am going to assume you went through the last part and understand the concept of using procedures to execute a rule. I am also going to use the Java method but you can easily use the OS execution method.

First duplicate the KM - “IKM SQL to Hyperion Essbase (DATA)” and give it a name



We need to be able to allow whether a rule will be run or not, this is done with the use of KM options.

Right click the KM and select “Insert Option”



I set the default to No but it is up to you, it just defines what is shown in the options of the KM.
The position value defines where the option is placed in the list of options.

Next an option is required for the name of the business rule that is going to be run.



You can add as many options as you like but I am going to stick with these two for now.

Now edit the KM and the details tab and add a new command



The command is exactly the same as using a procedure; the only difference is that the source and target depends on your interface source/target.

Select “Jython” as the technology, the command section is pretty much the same as what was used when I created the procedure in the first part of this blog, the difference being is that I can use some of the variables that have already been defined in the KM.

If you look at the command “Prepare for loading” in the KM you will see some of the variables that have already been defined.



These variables are valid throughout the interface.

If you look back to the original procedure I created then you will see I set it up like :-



I don’t have to hardcode the server name; username and password as these variables have already been defined, I can pick up the rule name from the KM options.

I do have to change the command used to define the variable file, this is because when I originally created the procedure I used the “Command on Source” to set the technology and logical schema.



This time I will use a different ODI API command - getSchemaName(), this command lets you pass in the name of the logical schema and will return name of the data schema, so in this case by passing the in the logical schema name for the file it will return the directory location, it will automatically pick up the context that it is being executed on.



As you can see I have used the variables (serverName,userName,password) that have defined in the “Prepare for loading” command

I have used the API command getOption() to retrieve the value of the option HBR_NAME

If you select Options tab you will see everything is selected and the “Always Execute” option is checked, we only want the command to be run if the option “RUN_HBR” is set to yes, so uncheck “Always Execute” and check “RUN_HBR”



This means the command will only be run if the RUN_HBR option is set to “Yes”

Once you apply then the command will be created as the bottom of the list of the commands, depending on where you want the business rule to be executed defines where you move the command to in the list, I want it run just after the load so move it up to run after “Load data into essbase”.



If you create a standard essbase data load interface and in the flow section you should be able to select the custom IKM that has been created, it should also display the options created.



If you don’t change the RUN_HBR option then the KM will operate exactly the same as the standard essbase data IKM.



If you change the HBR option and enter the business rule name then it will execute the business rule after the data load.



As I said you don’t have to use this IKM you could just as easily customize one of the planning KMs, you could also customize it further and add in the creation of the variables HBR file.

ODI Series – Executing Hyperion Business Rules

At a conference I was attending a few weeks back a question was asked – “Is it possible to run business rules through ODI?”.
Now it is certainly true that none of the adaptors have the functionality to run business rules but I thought I would go through a couple of ways that it is possible to execute them.

This is going to be based around classic business rules and not calc manager ones. I always thought the available functionality outside EAS has been pretty poor for business rules, you have the choice of the not so great web launcher or the command line launcher, and there has never been an API that I feel would have been a lot more useful.

Anyway in your business processes you may have business rules that are run a scheduled basis and you want to integrate them with your current ODI processes.

The first option is to use the OS functionality in ODI procedures, I am going to use the command line launcher in version 11 which is pretty much the same as previous versions except you have to use an encrypted password file. I am also going to be using a windows O/S but the example could easily be modified to fit a different environment.

The documentation for the command line launcher is completely out of date but the usage should be :-

CmdLineLauncher [-p:] -Sservername -Uusername [-rBusiness Rule Name | -sSequence Name] [-fRun Time Prompts file]

The important parameter to watch out for is –fRun Time Prompts file as even if you are not using run time prompts you still need to provide information on which database you want the rule to be run against.
The run time prompts file can be generated from within EAS by right clicking on a rule and choosing “Automate Launch Variables” and this will generate a file.



The ExecDB parameter is always required, all parameters are paired together with ::
You can manually edit the file to add in more run time prompts if required.

If you are using version 11 then you will have to create an encrypted password file first, there is a utility (PasswordEncryption.bat) that is in the same directory as the command line utility (\Hyperion\products\Essbase\eas\console\bin)

So an example to run a business using the command line launcher would be



Ok, so we need to transfer this example to ODI. In the designer you will need to create a new procedure, add a new command and set it against “Operating System” technology.



To use the command shell it is good practice to start with cmd /C this means the command will be terminated after execution



If you execute the procedure as it is then it will fail.



Not the most useful error code but what you need to realise is that the command is not being executed in the directory of the utility, if you have a look at the command line launcher batch script you will see one of the reasons why it is failing



The script uses a relative path so none of the java files will be added to the classpath, I created an environment variable to point to the EAS directory and updated the batch script to use it.



It is up to you whether you want to use an environment variable or just set one up in the batch script.



You also need to be aware that when calling the command line launcher from ODI you need to specify the location of the password file and run time prompts file otherwise it will not locate the files.

Once again, this can be done by hard coding the path or using environment variables.



Another option would be use ODI variables and they could be set a run time if required. (If you want to know more about ODI variables have a read of this previous blog)



You could set a variable up for each parameter of the command line call.



This way the variables could be set when you run the integration or they could be constants or set by retrieving the information from tables or files, there are many different options to choose from.

This time when you execute the procedure it should through without any problems.


So that is one method which does what it is supposed to but is a little limited, the next option would to be use a little piece of Java to call the launcher, now unfortunately there is no HBR launcher API but that is not going to stop me and I will show you how it can be done.

Before you start you will need to use a number jar files in your classpath, most of them are displayed in the batch file but I found that it required an extra jar file to get it working (xalan.jar). I just copied them all to another directory.



You will also need the HBRCLient.propeties file as the java code looks for this file by default.



Basically you just need to pass in the server name, username, password, rule name, the RTP file location and the location of the HBRClient.properties file, this is set as a system property (HBR_HOME)

The main method is not required this is just to test the java code was functioning; it can be removed after testing.

In using the java example you don’t need to provide the location of the password file you can pass it straight into the method, unfortunately you still need to provide the location of the run time prompts file. I did have a look whether you could get round this and it is possible but you would have to change the core java code and I didn’t want to head in that direction.

If you are interested then in the LaunchManager class there is HashTable that stores the run time prompt properties.



If this is changed from private to public you could probably push the RTP values into it, as it is currently private you can’t access it from outside of the class.

Once you have the code working and executing the rules then it can be compiled and put into a jar file.

To be able to use this java in ODI you need to let ODI know where all the jar files are, there are few ways of doing this, you can just copy all the files in the oracledi\drivers directory, make sure you restart any ODI GUI and agents.

Another way would be to update the classpath in odiparams file in the \oracledi\bin directory.



This will dynamically add all the jar files to the classpath from the directory you specify, in my case E:\Java\HBRlauncer

If you are running an agent as service then you will have to update a configuration file that is called when the service is started, it is snpsagent.conf and is located in \oracledi\tools\wrapper\conf



I just added a new line with location to the jar files.
One thing to watch out for with the above methods is not to include xercesImpl.jar as it causes conflicts with the other classes that ODI uses.



You can just remove the file and the code will still function correctly as it will use a class already referenced by ODI.

With the files in place you can now create a new procedure in ODI to call the java method, the procedure could use “Java BeanShell” or “Jython” technology.

I am going to use Jython the same technology that the Hyperion KMs use.



In the example I just import the class in, set up the variables and pass them into the Java method.

Once again you can set use ODI variables instead of hard coding all the parameters.



If you any of the parameters are incorrect it should raise an error.



If the rule has run successfully and you are running an agent as a service then it will be logged in \OraHome_1\oracledi\bin\agentservice.log



The hbrlaunch.log will also be populated with information about the execution just like it would if it was run from EAS or planning.

The only issue at the moment with this solution is that you have to manually edit the RTP file each time you want to make changes, well as usual there are ways around this and you can let ODI do the work and create the RTP file for you.

You could have the information stored in a db table, a file or an ODI variable and create an interface to generate the file. I am going to show how this can be done using an ODI variable that could be populated at run time.

First you need to create a File Datastore point to the directory and name of the file.



Create one column with a length that will be long enough to hold RTP information.



Now create a variable of text type to hold the RTP information



Create a new interface, set the staging area (I am using SQL Server as the staging for this example) and drag the file Datastore that was just created to the target.



Next in the mapping I added the reference to ODI variable and used a replace function to replace spaces, this is because with a ODI text variable the lines are separated by a space, I replaced the space with a line break char(10).

This means it should write a new line for each line of the text variable.



I know your asking, but what if the value element of your variable has spaces in it, well if that is the case you could add something to distinguish each separate line for example.



Then use the replace as

replace('#HBR_Variable','@NEWLINE@ ',char(10))

In the flow section of the interface the IKM to use is “IKM SQL to File Append”, though it will require some changes as if you try and run the interface it will generate an error in its current state.



This is because the IKM is based on the interface having a source and we are not using a source so the SQL generated is incorrect.



As you can see there is no table name, to get around this, duplicate the “IKM SQL to File Append” and give it a new name



In the details tab, you can delete the Command “Insert column headers” as it is not required and then edit the Command “Insert new rows”

In the source tab you view the code that is generating the SQL when the IKM is used



As you can see it has from and then an ODI command to retrieve the table name, this can be updated, if you are using SQL server as the Staging Area then you can remove everything from just before from.



If you are going to be using Oracle as the staging area you would change it to “from dual”



So what this means it will ignore generating any source table name and will just run SQL to display what you have in the target mapping of the interface, in this example it will be the text from the ODI variable.

Once you have saved it you can update the IKM in the flow of the interface to use the newly updated IKM.



I have used TRUNCATE option so the file is recreated each time.



When you execute the interface and check the steps in the operator you can now see that it has generated the correct SQL.



The output file now has all the required information to be used with HBR launcher.

A package can be created to bring it all together.



If you generate a scenario from the package and integrate some of the web functionality I have shown how to create in previous blogs, you will have your own HBR web launcher with the benefit of being able to add other ODI elements into the package.