Sunday, 13 December 2009

ODI – Patch Update –

I have been waiting a while for a patch to resolve an issue that I logged with Oracle, the problem occurs when loading data into essbase and error records are produced, the record where the rejected record occurs is logged but not the member, instead you are given an error 3303 in the logs.

This goes back to a patch that fixed the original issue when loading data to essbase, the original problem was when an error record was hit then the adaptor would start loading record by record instead of in chunks. I documented the error in this blog , the patch to fix this was when documented in the following blog and finally the error 3303 issue was logged in this blog.

So with ODI and prior versions if your load encounters a rejection the adaptor will go into record by record load mode. ONE-OFF PATCH resolved this issue but introduced the error record 3303 issue.
Now Patch 9200535: ORACLE DATA INTEGRATOR CUMULATIVE PATCH has been released and should fix the logging issue.

The patch is cumulative patch so as long as you have it will include all patch updates up to and including

This patch actually includes a number of fixes relating to ODI Hyperion adaptors so it is well worth looking into applying.

Here are the Hyperion related fixes.

8912703 - When an Essbase member is rejected due to error "3303: Member not found in database" the log does not indicate which member caused the record to be rejected.

This fix I will test out shortly

8713986 - Consolidation attribute "^" (Never Consolidate - Added in Essbase 9.3.1) is not handled in the Essbase KM. The Extract Metadata step fails with error "No_matching_Enum_found".

I go through this and test out shortly.

9080483 - Essbase Interface fails with "ImportError: No module named hyperion" on the "Prepare for loading step" when loading ASO/BSO data into Essbase.

This issue I have only come across in the forums and I thought it was due to drivers not being picked up in the classpath, maybe it is a different issue, if anybody has this problem and it can be easily recreated then let me know I will document it.

8529169 - "Class not registered" error while loading metadata into Hyperion Financial Management classic application using an 64-Bit HFM Client.
A new 64-bit driver (HFMDriver64.dll) has been added to the /oracledi/drivers directory. To use this driver on a 64-Bit platform with a 64-Bit HFM Client installed, rename HFMDriver.dll as HFMDriver32.dll and rename HFMDriver64.dll as HFMDriver.dll.

Installing the patch is very straightforward, first make sure you

Perform a backup of the Oracle Data Integrator installation directory.
Perform a backup of the Master and Work Repositories.

Close all ODI components e.g. Topology Manager, Designer, Operator, Security Manager. Stop any agents running on the machine where you are going to install the patch.

Next uncompress the patch file and copy the content of the oracledi directory over the top of your ODI installation, overwriting any existing files.

Open the designer, go to Help > About Oracle Data Integrator. The patch version should be displayed.

The knowledge module “IKM SQL to Hyperion Essbase (DATA) has also been updated, so you will need to import the KM replacing the original one.

If you right click your original KM and select “Import Replace..”

If you just use the standard import for a KM then it will import it in duplication mode and just create another KM.

So lets have a look and see if the patch fixes the error 3303 problem.

I have created a very simple interface to load data from a text file into essbase sample.basic

In the load file I have added some members that do not exist in the essbase database.

In the market dimension there are two members in the load file “Florida1” & “Florida2” that should error out in the load.

Please not make sure you are using a load rule when loading data otherwise it will use the original method to load the data and when it encounters a rejected record it will revert to loading record by record.

Good news, in the log the 3303 error message now contains the member that was rejected.

Now on to one of the other fixes 8713986 - Consolidation attribute "^" (Never Consolidate - Added in Essbase 9.3.1) is not handled in the Essbase KM. The Extract Metadata step fails with error "No_matching_Enum_found".

Prior to this fix say the following member in your hierarchy.

The member is using the never consolidate across all dimensions operator ^
If you have an interface to extract metadata from an essbase database outline that contains the operator then the interface would fail.

The error would be “No_matching_Enum_found”
After applying the latest patch and running the same interface.

The interface runs successfully

The member is extracted with the correct consolidator operator.