OBIEE Reporting for HFM: Solution for Hierarchical Roll-ups

One of the many multi-dimensional data sources that OBIEE supports is Hyperion Financial Management (HFM). If you have used Essbase as a data source in OBIEE, you will realize that HFM integrates in a similar approach, but not quite the same. While a direct connectivity between OBIEE and HFM is very useful for financial users, there are currently some restrictions to this capability that I will describe in this blog. Often times you want to be aware of these intricacies as you are designing a BI solution for HFM reporting. If this is relevant to you, keep reading. This blog eventually narrows down on the OBIEE capability of drilling down on HFM dimensions and how to go about resolving the issue of missing dimension members.

To connect OBIEE 12c to HFM 11.1.2.4, there are quite a few steps that need to happen. (It’s not as straightforward as things are with connecting to Essbase.) HFM doesn’t store its data in Essbase and this may explain why things are different from a connectivity perspective. Here are the high level steps that are necessary to establish this connection:

1. Download, install and configure EPM on the OBIEE Server: You can download the software from e-delivery by looking for “Oracle Enterprise Performance Management System”. At the time of writing this blog, the latest version was 11.1.2.4.0. Refer to Oracle Doc ID 2103685.1 for details related on how to install EPM components for the purpose of establishing a connection from the OBIEE Server. (Note that even if you already have HFM running on a separate server, you will still need to install EPM on the OBIEE Server.)
2. Configure BI Admin Tool to be able to import HFM applications:
   On the local machine where the BI Administration Tool is installed, update the following file \Oracle\Middleware\Oracle_Home\domains\bi\config\fmwconfig\biconfig\OBIS\NQSConfig.INI to add the server name where the Java Host is running. In the JAVA HOST section set the following parameter: JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = “<Java Host Name>:<Java Host Port #>”;
   On the OBIEE client machine go to Oracle\Middleware\Oracle_Home12\bi\bitools\bin and edit the file admintool.cmd to add INSTANCE_NAME=%DOMAIN_HOME%
3. Copy 3 files over from the EPM Server machine to the OBIEE client machine where Admin Tool is running.
   The files are: adm.jar, ap.jar, and HspAdm.jar (where to find these files is mentioned towards the end of the same Oracle Doc referenced above). These need to be copied into the client machine under FMW_HOME\oracle_common\modules.
4. Run the Metadata Import Wizard from the OBIEE Admin Tool:
   Go to File -> Import Metadata and provide the following information. Then click the Next button:
   Connection Type: Hyperion ADM
   URL: adm:thin:com.oracle.hfm.HsvADMDriver:<EPM Cluster Name>:<HFM Application Name>
   Provider Type: Hyperion Financial Management 9

In the URL field, there are only 2 things that need to be entered based on your environment. The first is the EPM Cluster Name, which is something that is defined at the time the EPM system was installed on the Hyperion Server. (This is not the HFM server/host name or IP so you may want to consult with the Hyperion installation expert on your team to know what the cluster name is.) The second thing you need to set in the URL is the HFM Application Name that you want to import. I would like to add that if you are able to connect to the HFM application from Smartview, that should help you figure out what the Cluster and Application names are from the Smartview connection url. The username entered here should be an EPM user who has at least read-only access to the HFM application that is being imported.

As you continue the import wizard, you end up with an imported model into the Physical layer of the RPD which you may then move over to the Business and Presentation layers with no changes at all. This is unlike importing from Essbase where we have multiple options to apply changes to the default model settings. For example, with Essbase we can add in member aliases, or change dimensions from parent-child to level based dimensions. With an HFM import, we don’t have the option of changing the type of a dimension. They are imported as parent-child dimensions (also referred to as value-based) and there is no option to switch them to level-based dimensions. This is fine, but prohibits reporting on fixed level attributes in a straightforward manner.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

For instance, if you want to filter on a level of the Product Hierarchy, say Product Family. But there is no level explicitly assigned to Product Family (since there are no level columns at all, just 2 columns: parent product and child product). You may, however, bypass this problem if you are lucky to have the Product Hierarchy balanced enough to have Family on the same level of every branch of the rollup structure. In this situation, you can leverage the generation column which is imported by default for all HFM dimensions to filter on the generation number that corresponds to where the Product Family is defined. Despite doing this, you end up with just the Product Family and won’t be able to drilldown to the lower levels. An alternative would be to make use of Selection Steps and apply more dynamic filters to pull in server members and their descendants. Long story short, there are limitations to what can be done with a value-based hierarchy as opposed to a level-based one, but there are different ways, as I have seen, to make things work on the reports in one way or the other. It’s worth mentioning that value-based hierarchies have a huge advantage over level-based ones in that they are more dynamic and can seamlessly adjust to HFM rollup changes with no necessary changes on the OBIEE side. For example, a new level gets introduced into a dimension in HFM. This new level starts showing up automatically in OBIEE reports. Whereas, if we were using a level-based dimension, we would have to update the RPD metadata to be able to support the updated EPM dimension.

Reporting on Shared Dimension Members

One issue that you are likely to encounter when reporting on HFM dimensions from OBIEE is when there are multiple rollup hierarchies defined within the same dimension in HFM. This is what results in a hierarchy node with multiple parents. OBIEE is good at handling this from Essbase because Essbase integrates into OBIEE with fully-qualified members so it is able to relate each member upwards to upper levels of a hierarchy. However, the HFM driver for OBIEE doesn’t do fully-qualified so when a member is shared across multiple rollups, we have a problem. To figure out if this is something you will encounter, you can check with your HFM expert on whether the HFM application you will be using includes “shared members”. Here is an example of what I mean:

Department Dimension

1. All Departments (Americas)
   1. Northern
      1. Marketing
      2. Sales
   2. Southern
      1. Legal
      2. Finance
2. All Departments (Europe)
   1. Western
      1. Marketing
      2. Operations
   2. Eastern
      1. Legal
      2. Finance

In this example, the underlined members: Marketing, Legal and Finance are shared members because they are not unique within the Department dimension. These rollup to both All Departments (Americas) and All Departments (Europe). So when you pull in the Department hierarchy onto an OBIEE analysis, you will start noticing that the shared members are missing at times. This issue is a known Oracle enhancement and is documented in Enhancement 18338476.

Following are 2 possible workarounds for this issue:

1. Use Selection Steps to explicitly include the missing members. The disadvantage of this approach is that the selection steps will have to be updated every time new shared members are introduced to the HFM hierarchy.
2. Use the last rollup hierarchy within a dimension. In my aforementioned example, if we apply a selection step to include only “All Departments (Europe)” things work as expected and shared members get displayed. Obviously, this is great if we want to report based on the last hierarchy, but if we want to report on “All Departments (Americas)” then shared members will be missing. A possible solution, is to copy over the hierarchy of interest into a new rollup in HFM, ordered last within the same dimension, to reflect the rollup structure that is most frequently going to be used in OBIEE reporting. For the same example, we could create a third rollup called “All Departments (Reporting)” that includes all members and have OBIEE use Selection Steps to only pull in the new rollup: “All Departments (Reporting)”. In this situation, we would be able to expand All Departments (Reporting) and go down to all the descendants, but that would be the only hierarchy under the Departments dimension that works properly by default. The other ones still work but require explicitly listing out the shared members as mentioned in the first workaround above.