Hyperion Financial Reporting (HFR) 11.1.1.3 – Reporting on Relational Sources – XOLAP

Hyperion Financial Reporting (HFR) is one of the simplest and very robust reporting tools that is currently part of the Oracle BI EE Plus bundle. It has excellent multi-dimensional reporting capabilities and scales very well unlike Web Analysis. Part of the reason for this is, there is not much metadata management that is required for HFR(only reporting metadata). Its also probably one of the tools that Oracle has which supports XBRL report publishing natively. It has extensive formatting capabilities (similar to BI Publisher) and also supports multi-dimensional hierarchical drills, hierarchical prompts etc. The major drawback of HFR up until 9.3 release was the fact that it supported only 3 main data sources i.e Essbase, Planning and HFM. It did not support reporting directly on relational sources. But in the EPM 11 release, the introduction of XOLAP makes it possible to report on relational sources directly using HFR. In fact, based on my testing so far this works much better than the existing BI EE – Essbase connectivity.

The major advantage of using XOLAP and HFR (when compared with BI EE 10g) is this provides 3 main distinct advantages

1. HFR provides contextual drills as opposed to normal drills that BI EE 10g provides.
2. HFR provides and supports hierarchical prompts which BI EE does
3. HFR provides extensive formatting capabilities (like conditional formatting on Pivot tables etc) which BI EE itself natively lacks

Of course, BI EE 11g would provide all of these capabilities. But until then, for customers who absolutely need the 3 points mentioned above, HFR can be used to supplement BI EE as it is part of the BI EE Plus bundle.

To get this to work, lets start with a simple XOLAP cube. If you are not sure about what XOLAP offers and its advantages, refer my previous blog entry on this here. For this i shall be using the GLOBAL schema that can be downloaded here.

XOLAP basically is based on a ASO cube format. All the design considerations in terms of outline structure etc would have to be adhered to even in XOLAP. So we start with importing the required database tables from within Essbase studio

After importing the required tables, we establish the relationship across the tables using the normal SQL Joins

After establishing the relationships, we need to define the Hierarchies and Measure Hierarchies in Essbase Studio. In the XOLAP cube that i will be building, we will have 3 normal dimensions Time, Product and Channel. There will be one Accounts dimension which will have all the relational measures.

After defining the hierarchies, we need to create a Cube Schema and Essbase Model so that we can designate the target cube to be deployed as a XOLAP cube.

Deploying this to Essbase should create a new application and database. The only difference is, this cube will always return data from relational sources at run-time. There will not be any pre-aggregated data stored in this cube.

So, we basically have an outline that is directly dependent on relational source. Any MDX query fired to this cube will be converted to the corresponding SQLs by Essbase. So, all the complex multi-dimensional reporting MDX that we use on normal ASO/BSO cubes can be used on this as well. Of course, not every MDX function is supported (As there is no corresponding equivalents ones in the relational source).

Now that the XOLAP cube is built, lets see how we can use this from Hyperion Financial Reporting. To begin with i start with defining this XOLAP cube as the source for HFR.

Then i create a very simple grid in HFR with the following layout.

The idea is, i will enable the drills on the Product and Time dimensions. Both Channel and Measure dimensions will act as USER POVs. The POVs will act as hierarchical prompts just for this example.

If you notice, we can now leverage XBRL directly here. After defining this report, if you look at the report from EPM Workspace, you will notice that the HFR provides more capabilities (at-least in some aspects) than BI EE 10g.

In the report, you can see that the drills are context sensitive. Also, if we click on a User POV, we will see that the prompts show the hierarchy as well

For multi-selects we can use USER PROMPTs of HFR. Even they are hierarchy aware. Now that we have demonstrated the basic advantage of using HFR on XOLAP, lets try some complex reporting functionality on this. For example, lets add a calculation which will basically show the value of  “Hardware” - “Deluxe Mouse”. Hardware is one level above the Deluxe Mouse member. But this kind of calculations are quite common when dealing with Essbase sources

As you see, custom financial reporting functions work quite well. This is actually quite exciting (Atleast for me) to see a web based reporting solution(which is in the strategic direction of Oracle) from Oracle that can do multi-dimensional aware reporting on relational sources. All the complex formatting that we are so used in HFR will all work here as well.

From my perspective, part of the reason why XOLAP was introduced is to make HFR work with relational sources. Now, we can make Horizontal & Vertical Fragmentation & Federation work easily with XOLAP by using Essbase transparent partitions on XOLAP. Atleast until 11g comes out this can quite easily serve the reporting needs of multi-dimension aware reporting on relational sources. And of course, we can use HFR as a sort of comparison to see how 11g behaves when compared with features of HFR.

Next in line is in comparing how XOLAP converts MDX to SQL. I will cover that as a separate blog entry. The first part of the XOLAP MDX to SQL conversion is available here. The 2nd part will cover what SQL queries get fired for multi-level MDX reporting.