Wednesday, 17 June 2015

HFM 11.1.2.4 as a data source in OBIEE

Continuing on the theme of EPM data sources in OBIEE I thought I would just put together a post of about using the HFM thin ADM driver, recently I have asked a few times about the correct process for getting up and running with a HFM 11.1.2.4 as a data source in OBIEE.

The OBIEE documentation has not yet been updated to include HFM 11.1.2.4 even in the recent 11.1.1.9 release and to be honest it what exists in the documentation is a little confusing anyway.

Prior to 11.1.2.3 you would use the ADM native driver to connect OBIEE to HFM but from 11.1.2.3 there was the option to use thin drivers allowing the configuration to be also run on Linux.

With all the changes to HFM in 11.1.2.4 support for the native ADM driver has gone and the only way to connect is using the thin driver.

Component Supported Version
Financial Management ADM Driver
  • Release 11.1.2.1.xxx (native drivers)
  • Release 11.1.2.2.xxx (native drivers)
  • Release 11.1.2.3.xxx
    (This release supports native and thin drivers)
  • Release 11.1.2.4.000
  • (This release supports only thin drivers)


I am going to concentrate on the process to get running with HFM 11.1.2.4 as this is the first version where the thin driver has to be used and will be in the future, I will be using OBIEE 11.1.1.9 though the process should still be relatively similar when using the thin driver in HFM 11.1.2.3 or OBIEE 11.1.1.7

The first thing to do is to get the required HFM drivers (basically a set of jar files) installed on the OBIEE server, I am not quite sure why they have not been automatically deployed in OBIEE 11.1.1.9 for HFM when they are for Essbase and Planning.

There is no separate installer just to drop the ADM related files so you will need to use the full EPM system installer which really does seem like an overkill for what is actually required.

After firing up the EPM installer keep the middleware home separate to OBIEE unless you want to destroy your OBIEE deployment.

In 11.1.2.4 there is no option to install the FM ADM driver like in 11.1.2.3 so to get the required files laid down select the SDK.


You will notice that by default WebLogic application server will be installed and there is no way of getting out of this, it can be easily be uninstalled at a later stage though.

There are a whole raft of components that will get installed as they get laid down no matter what EPM products you are installing.


In the end there are around 4GB of files that have been installed which unfortunately if you are sticking with the supported route then it is something you have to live with at the moment.


Now the next step in the process I do have questions whether it is required which I will get on to later but for now let’s stick with it.

Run the EPM configurator and set a new EPM instance name.


Configure to point to the EPM Shared Services / Registry database.

There is the option to configure FM but I don’t believe this is actually required and it is your choice to whether you feel it is needed.


Next we need to configure OBIEE to be able to pick up the EPM related jar files so we can start to use HFM as a data source.

This requires updates to the OPMN configuration file located in:

<OBIEE_INSTANCE>/config/OPMN/opmn/opmn.xml


Two additional variables are required under the Java Host component section which will be based on where the EPM components were installed too and the EPM instance location which was configured.

For example:

<variable id="EPM_ORACLE_HOME" value="E:/Oracle/Middleware/EPMSystem11R1"/>
<variable id="EPM_ORACLE_INSTANCE" value="E:/Oracle/Middleware/user_projects/obiee"/>


Now the location of the required EPM jar files need adding to the classpath parameter


Instead of going through adding lots of jars I have found it is best to add the following:

$EPM_ORACLE_HOME/common/jlib/11.1.2.0/epm_hfm_web.jar$:$EPM_ORACLE_HOME/common/jlib/11.1.2.0/epm_j2se.jar$:

The above jar files contain a manifest which includes an additional classpath to all the jar files which will be required.


Once the two additional jars have been added to the classpath then OBIEE can be restarted and should be good to start importing the metadata within the administration tool.

When you import the metadata and select Hyperion ADM connection type the URL for FM will be based on the native driver which is not supported.


The format to use the thin driver is:

adm:thin:com.oracle.hfm.HsvADMDriver:CLUSTERNAME:APPNAME


If everything has been configured correctly then OBIEE should be able to connect to the HFM application so the metadata can be imported.


If there is anything wrong with the configuration then you will probably get errors relating to java classes not being found or ADM errors, for example:


If you do get errors then double check the OPMN file, also make sure the ADM files jar files adm.jar and ap.jar are correctly being picked up.

Also problems can occur if there is an overlap of jar files which I have seen in 11.1.1.7 so it is worth deleting all files except admimport.jar and admintegration.jar in:

<MIDDLEWARE_HOME>/Oracle_BI1/bifoundation/javahost/lib/obisintegration/adm


If there are no problem then HFM dimensions and associated properties should be imported.


Once everything has been done in the physical, BMM and presentation layers and OBIEE restarted you should be good start developing reports.


If you are using the administration tool on a client machine then you may receive the following error:


If so then update the OBIEE NQSConfig.ini file on the client machine to include the server name and port of the OBIEE server running the JavaHost.


So everything is working now so all good but I just want to return to the step where the EPM configurator is run as I am questioning whether it is required or not.

Running the EPM config utility will add information into the EPM System registry about the OBIEE instance which probably does not need to be there.

The important part is that running config creates a reg.properties file which contains connection information to EPM registry database.




Adding the EPM_ORACLE_INSTANCE variable to the OPMN xml file means the reg.properties file and the EPM environment can be accessed.


To test this theory I did the following on an OBIEE instance which had not had the EPM configurator run, please note I am not suggesting you do this.

Created a folder which will be used as the EPM_ORACLE_INSTANCE variable value.


Created the folder structure and copied the reg.properties file from the EPM environment.


Set the EPM_ORACLE_INSTANCE variable to path I created.


Did it work? Yes it did and I didn’t see any issues, so is the EPM configuration actually required?

I also wanted to know if all that EPM installation was actually required so I removed the EPM installation and copied two directories from an EPM environment to the OBIEE one.


The combined total for these directories is around 16MB.

Updated EPM_ORACLE_HOME variable to point to the OBIEE home location.


As far as I could work out everything seemed to be working which meant a saving of the EPM installation process and 4GB of additional files.

I am stressing though do not try this as I just wanted to prove that Oracle can overcomplicate things sometimes and surely it would be easily if they had included the files in the 11.1.1.9 installation like the other EPM related files.

This method would only really work with 11.1.2.4 and OBIEE 11.1.1.9 because most of the EPM files in OBIEE 11.1.1.9 are the 11.1.2.4 version, once patches arrive this all changes though and you have to take into consideration that OBIEE and EPM will be running on different middleware versions.

No comments: