Essbase Integration with OBIEE 10.1.3.3.1
A month or so ago I posted a series of articles on getting up and running with Hyperion Essbase, and mentioned at the time that it soon would be possible to plug Essbase directly in to Oracle BI Suite Enterprise Edition. In this article, I’ll go through a preview of this connectivity and show how easy it is to bring an Essbase cube into the Oracle BI Server metadata layer, and report against it using Oracle Answers and Oracle Dashboard.
The version of Oracle BI Enterprise Edition I’ll be using is 10.1.3.3.1, whilst the version of Essbase is 9.3.1. The Provider Services tool that comes with Essbase is used to provide the interface, with Oracle BI Server talking to Essbase through its XMLA interface. Oracle BI Server already interfaces with Microsoft Analysis Services 2000 and 2005 through their XMLA interfaces, together with SAP BW, so this is a fairly well established technique that’s been supported in Oracle BI Suite Enterprise Edition for several releases. Moreover, bringing a cube like this into the metadata layer is very straightforward, with the BI Administrator tool converting the multi-dimensional dimensions, hierarchies and measures into logical tables, logical columns and dimensions for you automatically. So, how do you start the process off?
First of all, it’s worth being aware of the limitations and guidelines for this preview access. Firstly, it’s for evaluation only, so there’s no support through the regular channels - this will come with the full, 10.1.3.3.2 release. To enable Essbase connectivity, you will need to be comfortable with editing the registry on your Microsoft Windows PC (take a backup of the registry first). Using the Windows registry editor (regedit.exe), make the following changes:
- Locate the registry key HKEY_CURRENT_USER\Software\Oracle Corporation\Oracle BI\Oracle BI Administration Tool\Hidden Features
- Create a DWORD Value named Essbase and set its Data value to 1
In this initial release, whilst Essbase can place measures into a hierarchy, so that sales, for example, can be broken down into the individual component measures that make it up, Oracle BI Suite Enteprise Edition will display all the measures in the hierarchy as a flattened list. Time-Series calculations generated by Oracle BI Server will not function-ship to Essbase without using the EVALUATE function, and this function can also be used to query Essbase user-defined attributes (UDAs) and, in conjunction with the EVALUATE_AGGREGATE function, to leverage specific Essbase MDX functions. Bottom line - this is preview only, most Essbase functionality is present but you may need to use work-around steps, in this preview release, to access them. My understanding is that Oracle's direction is to address these limitations in a future release, and if there’s interest, I’ll try and cover the use of EVALUATE and EVALUATE_AGGREGATE in relation to Essbase in a future posting.
Anyway, once you have set up the registry entry, start up Oracle BI Administrator and connect to your repository.<./p>
Using the File menu, select Import > From XMLA, like this:
Now, enter the connection details for Essbase Provider Services for your Essbase cube. The normal port number for Provider Services is 13080 with the URL generally being in the format http://
Once you enter the URL details, press the Update button for Oracle BI Server to auto-complete the Data Source field. Then, type in the username and password for your Essbase server (by default, this is hypuser/essbase, make sure you don’t enter the Shared Services login which by default is admin/password), and press OK to make the connection.
You will now be presented with a list of Essbase databases (cubes) that you can import. As I had previously set up the Global Electronics cube as an Essbase cube, I select this, press Import, and then let Oracle BI Administration read in the cube details.
Now, when I take a look at the Physical model in my metadata layer, I can see the details of the Essbase cube along with all my previously imported physical relational databases.
Notice how the icons for dimensions, cubes and hierarchies are different than for physical relational tables and columns - Oracle BI Administrator handles multi-dimensional data sources as first-class, native objects and automatically understands the dimensional structure of the cube.
To create the logical model of this cube, which unlike the physical model will be expressed as logical tables and columns, I just drag the physical cube across to the logical layer and Oracle BI Administrator will automatically convert the metadata for me.
Oracle BI Administrator has automatically converted the measures in the cube into a fact table, and the dimensions into dimension tables. The relationship between the dimensions and cube have been converted into joins, whilst the hierarchies have been converted into dimensions. Notice, though, that the attribute names within the multi-dimensional dimensions have not been converted to the correct logical column names, and so I manually edit these logical columns so that the column names are correct.
Now when I look at my logical model, the column names reflect the attribute names in the Essbase cube.
As the logical model is now correct, I drag it across to the Presentation Layer to create the Presentation Model. You may have noticed in the logical model screenshot that I had left the Level 0 attributes unaltered - these are in fact not needed and if I included them in a query, the query would error. I therefore remove these from the Presentation Model so that users will not query them by mistake.
Now that all the tidying up and formatting is complete, the Essbase cube looks just like any other relational star schema in my Presentation Layer, however when it is queried Oracle BI Server will issue MDX queries against a multi-dimensional data source, rather than an SQL query against a relational star schema.
Now, after I save the changes to my repository, I can go over to Oracle BI Answers and put together a request against this new subject area. Oracle BI Server and Oracle BI Presentation Server makes the Essbase data look just like relational data, and I can build requests up using my Essbase data just like any other data source.
The most noticeable difference though, is the speed of response. Because I have pre-summmarized all of the data in my Essbase cube, views are returned lightening-fast...
...as the Oracle BI Server does not need to aggregate any measures at request run time.
Not bad, especially for a preview release. In my next article, I’ll be bringing an Oracle 11g OLAP cube into Oracle BI Server, to show how the import process works in this latest release of the Oracle database.