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.