Everything Oracle

Introduction

Integration between Hyperion 9.3.1 and OBIEE is far from satisfactory. It is possible to use Essbase as a data source for the OBIEE BI Server. But trying to use the BI Server driver to create an ODBC connection from Hyperion’s Integration Services fails due to a non-supported function call (the initial exchange proceeds satisfactorily and the user is authenticated by OBIEE, but then a “The driver does not support this function” error is raised).

In this article we’ll have a quick look at what’s available by way of integration in Oracle Hyperion 11.1.1.

Essbase as an OBIEE Data Source

OBIEE has an impressive collection of data sources to which it can connect and retrieve data to satisfy queries, be they homogeneous or heterogeneous. As far as multi-dimensional OLAP is concerned, OBIEE can connect to Oracle OLAP (11g), Hyperion (9.3.x), SAP/BW (3.5 and 7.0), and Microsoft Analysis Services (2000 and 2005). To this we can now add Hyperion 11.1.1.

If you use Hyperion exclusively for financial analysis and forecasting and you wish to combine financial data with other corporate data in your reports, then OBIEE can come to the rescue by combining data from Essbase with that stored in other corporate databases.

OBIEE repository creation is simplified since the metadata imported into OBIEE is already in cube format. However, column names will be generic – unless generations have been named in the Essbase outline – and so will have to be renamed. If changes are made to the depth of the Essbase hierarchy, then the Essbase cube will have to be re-imported into the OBIEE repository. Considerable care needs to be taken in constructing aggregate queries in the OBIEE repository, since depending on what functionality is, or is not, function shipped to Essbase by way of MDX queries, the report results can not only be slow in coming in, but can also be totally incorrect.

OBIEE as a Data Source for Essbase

The problem with the BI Server ODBC driver and Integration Services remains in Hyperion 11.1.1. However, Oracle appears to be ditching the Integration Services in favour Essbase Studio. Essbase Studio aims to provide a more streamlined and consolidated approach to Essbase cube construction. It incorporates existing Integration Services functionality, but adds other features, such as lineage tracking. It has its own relational database, which replaces the OLAP Metadata Catalog.

From an integration perspective, Essbase Studio can connect to the OBIEE BI Server as a data source (it bypasses ODBC and connects directly to the BI Server port – 9703, by default). Folders in the OBIEE Presentation layer appear as databases to Essbase Studio.

Integration can be rather hard work. Essbase Studio will bring across tables and columns, but not hierarchies. The Presentation layer in an OBIEE repository does not usually contain keys as these are meaningless and distracting for end users creating reports with BI Answers, but keys are needed for the Essbase import. Even if keys are present there may be problems with uniqueness and illegal characters.

OBIEE metadata is imported to create a mini-schema (equivalent to an OLAP model in Integration Services). Then it’s necessary to define keys, joins, hierarchies, and aliases for key columns. A cube schema (equivalent to an OLAP metaoutline) is generated from selected dimensions, and the transform feature is used to ensure that OBIEE column keys are unique.

Hard work, and much of it could be automated, but that’s for some future release.

EPM Workspace

EPM Workspace is a component of the Hyperion Foundation Services. It serves as a portal, a convenient means of accessing various tools and applications. In 11.1.1 it’s possible to access the OBIEE components from the “Applications” menu: BI Publisher, BI Answers, BI Interactive Dashboards, BI Delivers, and BI Disconnected Analytics.

Smart Space

Smart Space has been around since 2006. It’s aim is to increase BI availability by making applications easier to use. It’s “gadgets” are mini applications that reside on the desktop and can be personalized to meet individual requirements. Data can be updated automatically, and users can collaborate through instance messaging. A development toolkit can be used to create additional gadgets that address the BI needs of individual organizations. OBIEE components can be accessed through Smart Space gadgets: BI Publisher, BI Answers, BI Interactive Dashboards, and BI Delivers.

Smart View

Smart View allows data to be imported into Microsoft Office applications: Excel, Word, and PowerPoint. As well as connecting to Essbase (9.2.x+), it can also connect to OBIEE (10.1.3+).

SQR Production Reporting

In Hyperion 11.1.1, SQR Production Reporting can use the OBIEE Presentation layer as a data source. There is also support for integration with BI Publisher. The latter is important as SQR Production Reporting is unlikely to be enhanced in the future – Oracle’s focus seems to be on BI Publisher. Reporting among Oracle’s BI products is in a state of turmoil at present, but the trend seems to be in favour of the OBIEE components instead of the Hyperion components, as far as future enhancements are concerned. So if you’re embarking on a major round of new report production, SQR Production Reporting might not be a wise choice – at the very least you should look into the future ease with which reports could be migrated to BI Publisher.

Everything Oracle

Home

Everything Oracle