ODI Series - Will the real ODI-HFM blog please stand up.

I have always been meaning to write up a few blogs on using ODI with HFM adaptors but I have never got round to it. I have seen more and more questions coming up lately on problems configuring and using the adaptor so I thought it would be a good time to go through the basics of what can be done.

Yes, I have already seen a one off blog about ODI/HFM but to honest I was not impressed with the quality and depth of it so I thought it was time to put my version together, I will let you decide which one to use.

I would first like to make it clear I am not a HFM consultant so apologies if I use any incorrect terminology or don’t go about some of the methods in the correct way, no doubt I will be contacted as usual if I make any slip ups.



I am not setting out to go into too much depth just enough to get people up and running and gain a little knowledge of using the adaptors, my end game really is to be able to move metadata and data between essbase, planning and HFM but we will see how it pans out.



In this first blog I will go through installing the HFM client, configuration in ODI and some pitfalls that may be encountered and hopefully finishing up with a successful reverse of an FM application.



I have wrote a number of articles in the past using the Hyperion adaptors and I am not going to replicate the detail I have in the past so if you don’t understand you may have to read back through my earlier blogs.



I will be using a two-machine architecture, one machine hosting HFM and EPM foundation (Workspace and shared services). I have created a FM application named SAMPLE that is based on the “Simple Demo” information that can be found in “Hyperion\products\FinancialManagement\Sample Apps\Simple Demo\” of the HFM installation.

The second machine will be hosting ODI; both machines are windows based as that is a pre-requisite for HFM and also the HFM client that has to be installed on the ODI machine.

The EPM version installed is 11.1.1.3

Ok, lets start with the installation of the HFM client.
The files you will need (based on 11.1.1.3) are

Oracle Hyperion Enterprise Performance Management System Installer, Fusion Edition Release 11.1.1.3.0 Foundation
Hyperion Enterprise Performance Management System Foundation Services Release 11.1.1.3.0 Part 1
Oracle Hyperion Financial Management, Fusion Edition Release 11.1.1.3.0


Extract all the files to the same location and execute “InstallTool.cmd” to launch the installer.



Select “Choose components individually”



Uncheck all, expand Financial Management and select “Financial Management Client”



Should not take too long to install and after installation there is no need to run the configurator.



After installing the client I wanted to make sure I could connect to my HFM instance.

I opened up the FM configuration utility from the start menu > Oracle EPM System > Financial Management > Client Configuration



I added the server name of my HFM instance (HFMV11), enabled DCOM and that was all that was required to be done in the utility.

Next opened up the FM desktop to make a connection to my SAMPLE FM application.



When logging in make sure the Domain box is cleared and don’t enter the HFM server as it will not use Shared Services security and when you try and open the application it will generate a funky error.



I connected to the cluster and the SAMPLE application appeared in the list on application, clicked on Open and no errors were received so that is enough for me to believe the client is working successfully so on to ODI.

Open the Topology Manager

This is where we will define the connection to the HFM server, define the application to use and associate this with a logical schema and context.



Enter a name for the connection, the Cluster name and an account that has been provisioned with a FM admin role in Shared Services.

Do not click Test as this is only for JDBC technologies, the HFM is using an API so clicking it will just generate a driver error, I have lost count the number of times people have clicked the Test button and thought there was a problem.




Manually enter the HFM application name into the Catalog and Work Catalog boxes, make sure you enter exactly the same name into both boxes as I have also seen problems with integrations where this has not been done.



Select a Context which you want to use and associate a logical Schema, you will need to manually type the name of the logical schema you want to use.

If you don’t understand about Logical Schemas and contexts then I suggest you have a quick read here

That is all you need to do in the Topology manager for now so on to the Designer.

I have created a new project for the purpose of this HFM blog.



Once the project has been created the HFM knowledge modules will need to be imported. There are currently five HFM knowledge modules


IKM SQL to Hyperion Financial Management Data
IKM SQL to Hyperion Financial Management Dimension
LKM Hyperion Financial Management Data to SQL

LKM Hyperion Financial Management Members to SQL RKM Hyperion Financial Management

All the xml based KMs are situated in OraHome_1\oracledi\impexp

I am only going to be using the RKM today to reverse engineer the FM application into an ODI Model.

Right click the project and select Import Knowledge Modules.
Multi-select the KMs I have highlighted above and import.



In the models tab create a new Model, the model is basically going to be an ODI interpretation of the structure of the HFM application broken down into manageable Datastores.



Give the model a name and select “Hyperion Financial Management” and select the Logical Schema you have just defined, this will create the link to the physical connection information for the FM application.



In the reverse tab, select customized, select the context and logical agent you want to use to do handle the reversing, you can choose a local agent if you have not created another agent, if you want to understand how to create agents look back to one of my earlier blogs.

You will need to the select the RKM that was imported earlier, I have left the other options default for now, I will go into them in more detail at a later stage.

Click Reverse and go into the operator. This is where you may encounter some issues.



You may receive an error “Error occurred while loading driver”, this is due the agent not being able to see the ODI HFM driver (HFMDriver.dll / HFMDriver64.dll) in the \oracledi\drivers directory.

Depending on what sort of agent you are using will determine what you need to do to resolve this issue.


If you are using the “Local Agent” you will need to add the driver location to the windows environment variable (Path)



Once you have added the location make sure you restart all the ODI components to pick up the driver or you will continue to receive the error message.

If you are using an agent created as a windows service you will have to update a configuration file, when the windows service agent is started up it retrieves some of its parameter information from OraHome_1\oracledi\tools\wrapper\conf\snpsagent.conf



Edit the file and add the following line below “wrapper.java.library.path.1=….”

wrapper.java.library.path.1=./drivers



This means the DLLs in the drivers directory will be picked up.

Restart the agent once the changes have been made.



Reverse again.



If you using EPM version 11.1.1.3 or above like I am then you will encounter the error 

“Error occurecd in driver while connecting to Financial Management application [xxxxx] on [xxxxx] using user-name [xxxxx]..."

Don’t panic it is a known bug.


8725017: IKM SQL to HFM - Is not compatible with Oracle's Hyperion Financial Mangement 11.1.1.3.0. NOTE: Oracle’s Hyperion Financial Management – Fusion Edition Release 11.1.1.3 is the only release supported for this patch.

Tbere are currently a couple of solutions to fix the problem, you could download and install the following patch from “My Oracle Support”


Patch 8785892 - ORACLE DATA INTEGRATOR 10.1.3.5.2_01 ONE-OFF PATCH

Or you could install one of the following later patches as they address another issue with using HFM adaptor with ODI
9201073 - Cannot load metadata that includes shared members into HFM 11.1.1.3

Patch 9327111: ORACLE DATA INTEGRATOR 10.1.3.5.6 CUMULATIVE PATCH Or Patch 9285774: ORACLE DATA INTEGRATOR 10.1.3.5.5_01 ONE-OFF PATCH

I opted for patch 9285774, it is really simple to install, stop all ODI components and agent, extract the three files in the patch to \oracledi\drivers overwriting the existing files and start up the ODI components again.



Reverse once again and this time it should be successful.



Check the model and make sure all the Datastores have been created.



That concludes the first part and should get you up and running with the adaptor, in the next blog I will start looking in more detail at using the available FM KMs.