Oracle BI EE 11g - Reporting on Hyperion Financial Management (HFM) - Part 1

This has been my second longest sabbatical since i started blogging 4.5 years back. More than a month since my last post. Rather than going into the reasons of why(& sounding cliched) i thought i will kick start my postings again with one another new BI EE 11g feature i.e. HFM integration. Recently i have been doing some interesting work on Hyperion Planning and HFM. As part of that i was testing the HFM connectivity that was introduced in BI EE 11.1.1.3. In this post i shall be covering the basics of how the integration works in general. I will follow this post up with a more in-depth walk through of the connectivity in the next part. Before we get into the details, couple of points to keep in mind. BI EE 11.1.1.3 is certified only on HFM 11.1.2(though will work with the older releases). If you are on HFM 11.1.1.3 or HFM 9.3.3, you need to upgrade BI EE to 11.1.1.5.

BI EE connects to HFM through JavaHost. So, the setup for configuring the connectivity is to basically make the EPM environment variables available for access to BI EE. Before making the changes, there are a couple of things to be aware of

1. Ensure that DCOM is enabled on the BI Server machine. This is needed for HFM client to work/connect properly with HFM Server.

2. Ensure that HFM Client is installed.

The above assumes EPM 11.1.2.x version. If you are on Hyperion 9.3.3, then you need to have the ADM drivers installed manually. All of this is documented clearly. So, rather than re-iterating that i will just point to the link here. Ensure that you follow all the steps mentioned in the docs else you will have issues in the connectivity. Once all the pre-requisite steps are done, we start off with the metadata import process.

The import process will basically reverse engineer all the HFM hierarchies. In addition, interestingly it also brings in attributes like CellText, CurrencyType etc. So, not only does this provide pure data reporting capabilities it can also report on supporting documents within HFM. A clear screenshot of the physical layer post import is given below

Like Essbase & Oracle OLAP sources, creation of metadata for other layers like BMM and Presentation layer is just a matter of drag & drop.

But the interesting thing to note here is the way hierarchies are imported. If you notice below, all the major dimension hierarchies are imported as parent child hierarchies.

This makes sense as HFM hierarchies rarely are balanced. Let's now try creating a report containing just the Entity hierarchical column and then look at the query generated.

Interestingly similar to SQL sources (HFM also has a Relational data store - But BI EE uses the ADM driver to extract the data), it does generate separate queries for each level. Also the Queries themselves don't look to be SQL. Let's now fire a query to extract the data from HFM.

This looks good. And it does seem to generate the right queries as well. But what are the queries that get generated? They are not normal SQL queries. Also, how does BI Server generate them. Does it delegate all the data extraction to the ADM driver natively or does it let JavaHost extract the data. If it is the latter, it will be very interesting as this is the first time i am noticing JavaHost being used for data extraction from within BI Server. Perhaps this is how the planned BI EE 11g-Planning integration will work in the future. Anyway to answer these queries, i will follow this post with another one with more details.