Sunday, 14 February 2010

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

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

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

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 “….

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 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 NOTE: Oracle’s Hyperion Financial Management – Fusion Edition Release 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”


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


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.


Mary said...

Hi John,

I get the error you mentionned needed a patch:

File "", line 38, in ?

com.hyperion.odi.common.ODIHAppException: Error occurred in driver while connecting to Financial Management application [ ] on [ ] using user-name [ ].

However I am working with ODI v., does the same patch need to be applied (Patch 9285774)?

John Goodwin said...

Hi Mary,

You shouldn't need to install the patch as you are already using a patch version higher than 9285774

Amit said...


I got ODI configured with HFM. However, once I click reverse, the designer closes automatically and the reverse process never works. Out of 3 steps, reset metadata completes but gets stuck on second step 'Reverse'.

What can we try?

John Goodwin said...

Hi Amit,

Have you tried reversing using different agent instead of the local agent.

Mary said...

Hi John,

One more question I was able to reverse HFM by changing the environment variables path. In fact, HFM drivers are installed in a different folder (other than ODI_Home1\oracledi\drivers). However now I can't reverse HP (is it because it's drivers in another folder?)

I get the following error:

org.apache.bsf.BSFException: exception from Jython:
Traceback (innermost last):

File "", line 32, in ?

com.hyperion.odi.common.ODIHAppException: com.hyperion.odi.planning.PlanningConnectionFactory{app.planning.factory.class=com.hyperion.odi.planning.PlanningConnectionFactory, app.essbase.factory.class=com.hyperion.odi.essbase.EssbaseConnectionFactory, app.hfm.factory.class=com.hyperion.odi.hfm.HFMConnectionFactory}

at com.hyperion.odi.connection.HypAppConnectionFactory.getFactory(Unknown Source)


Thank you

Amit said...

We got our issue fixed where HFM reverse was closing the designer.

We had ODI driver path listed in environment but HFM with ODI is strictly looking to HFM ODI driver path. Once we explicitly listed HFM driver path in variables, the issue was resolved.

Fernando Parra said...

Hi Jonh,

I have the same issue
with ODI We need another patch to solve that? Because in this post you mention ODI 10.1.3 version.


Fernando Parra. Buenos Aires, Argentina

Anonymous said...

Like your blog.. Do you have tips on how to extract full metadata from LCM on an HFM 11 environment? I'm looking for all dimensions, members, attributes and hierarchies in .ads format?

Anonymous said...

Hi John,

Thanks for the blog and info. All your blogs have been extremely helpful. I was able to get past this issue after following all your directions. I did notice that the path for the snpsagent.conf file is off as it is missing a period:

currently you have:

it should be:

It took me a few hours to figure that out as I was able to reverse the application using the Local Agent but not using the windows service agent.

sailendra T said...

Hi John,

I was wondering to extract the data for all the base members of Account or Entity dimension at a go in ODI. I know we can provide the members as comma seperated values, but say my Entity dimension has 20000 member and I need to extract data for all the members at a go, what is the best way to proceed. Please suggest.