Everything Oracle

Introduction

If you’re familiar with using OBIEE to report against SQL and flat-file data sources, then the prospect of reporting against Essbase might seem to be “more of the same”. Indeed, given that OBIEE constructs a multi-dimensional model in the logical layer from its physical data sources, it might seem that reporting against a data source that is already in a multi-dimensional form would, if anything, be simpler.

However, in practice, reporting against Essbase as a data source is very different from reporting against relational and flat-file data sources. The key issue is that the multi-dimensional model used by Essbase is much more sophisticated than that constructed by OBIEE. And this disparity between the models has two consequences:

End user reporting expectations; and a

Lack of in-built OBIEE support for complex reporting requirements.

End users familiar with Essbase reports constructed using Hyperion reporting tools will expect reports that reflect the complexity of the Essbase multi-dimensional model – the fact that these reports are now being produced using OBIEE will not change end user expectations.

As a consequence of the simplistic multi-dimensional model used by OBIEE it is impossible to define many reports using in-built OBIEE functionality and then have the BI Server automatically function-ship the equivalent MDX expressions to Essbase. Instead, OBIEE developers are forced to work directly with MDX and Essbase database outlines using calls to “Evaluate”. OBIEE effectively becomes no more than a container into which the results of native Essbase calculations are “dumped” for the purposes of display. The implication of this mismatch between the two products is that as an OBIEE developer you’ll need to have a good understanding of MDX and Essbase database outlines in order to construct reports that will meet end-user expectations. Reporting against Essbase is far from the seamless experience that’s customary with relational back-ends – if you’re not familiar with Essbase or another MDX-based multi-dimensional database then expect a long learning curve.

Database Outlines

Dimensional structures within Essbase are defined using a “database outline”. The following database outline is that for the “Basic” database (cube) within the “Demo” Essbase application:

Product Dimension Members in Database Outline

This outline shows that the cube contains five dimensions:

Year,

Market,

Product,

Accounts, and

Scenario.

We’ve fully expanded the “Product” dimension to show its eight members. In terms of an OBIEE hierarchy, the “Product” dimension has three OBIEE levels: the first level contains the member “Product”, the second level contains the members “Audio” and “Visual”, and the third level contains the members “Stereo”, “Compact Disk”, “Television”, “VCR”, and “Camera”. In Essbase terminology, these OBIEE levels are referred to as “generations” – numbers one, two, and three respectively (Essbase also has a concept of levels, but Essbase levels are defined by counting upwards from leaf members, rather than by counting downwards from the topmost member of the dimension).

Where have all our Measures gone?

Essbase is not only more sophisticated than OBIEE, it also takes a more generic and logically consistent approach to organizing its metadata. In OBIEE, there is a clear division of metadata into “dimensions”, on the one hand, and “measures”, on the other. However, logically “a member that is a measure”, such as “Sales”, is not fundamentally different from “a member that is not a measure”, such as “Television”. So, Essbase treats measures as just another dimension. If we expand the “Accounts” dimension:

Accounts Dimension Members in Database Outline

we can find all the items that OBIEE would classify as measures.

From a multi-dimensional perspective, the total sales for televisions is obtained by examining the intersection between the members “Sales” and “Television”. This intersection identifies a collection of cells, corresponding to the other unspecified dimensions. Adding together the values of all these cells gives the requisite answer.

One important factor to note is that the measures in the “Accounts” dimension are arranged in a hierarchy, in much the same way that individual products are arranged within a hierarchy in the “Product” dimension. For example, “Total Expenses” is the sum of its components: “Marketing”, “Payroll”, and “Miscellaneous” expenses. Indeed, mixed methods of consolidation or aggregation are possible: the minus sign after the “Total Expenses” member indicates that “Profit” equals the difference between “Margin” and “Total Expenses”. The tildes after “Profit_%” and “Margin_%” indicate that they do not participate in the derivation of “Profit”. We can see that this method of representing measures is far more “value-added” – no pun intended – than its OBIEE equivalent. In OBIEE, the measures just “hang off” the dimensions, but have no relationships with one another.

Other Metadata

Essbase makes use of many other types of metadata that are not found within OBIEE. We can see the use of aliases, such as “Period” and “Misc”, in the database outline above. And there are many others, such as “user defined attributes”, “shared members”, and “attribute dimensions”, to add to the Essbase-style levels that we have already discussed. As these items of metadata cannot be mapped to OBIEE, default OBIEE reporting will ignore them. So, if we want to include these items in our reports we have to bypass the default OBIEE functionality and work directly with Essbase.

Importing an Essbase Cube into OBIEE

The procedure for importing the “Basic” cube into OBIEE and for creating a simple report in Answers is described in the article entitled “Essbase as an OBIEE Data Source”. In this article we’ll focus, instead, on the principles of metadata mapping rather than on its mechanics. Let’s start by importing the “Basic” cube into OBIEE:

OBIEE Physical Layer

In the physical layer we can see that four of the five dimensions that appear in the Essbase database outline appear as hierarchies. We can see that the “Accounts” dimension is missing. However, all the members of the “Accounts” dimension have been faithfully extracted from Essbase and listed, at the bottom, as measures in the form of “physical cube columns”.

The manner in which the OBIEE import process breaks the symmetry of an Essbase cube is as follows. OBIEE maps all Essbase dimensions, other than the “Accounts” dimension, to OBIEE dimensions. And it maps all members belonging to the “Accounts” dimension to OBIEE measures. You’ll hear frequent references in discussions of OBIEE and Essbase to “flattening the hierarchy”. This phrase refers to the fact that whereas the “Accounts” dimension in Essbase is a hierarchy, the mapped measures in OBIEE consist of a no more than a list, of what appear to be, unrelated items.

Note that whereas the Essbase aliases are not used when mapping dimensions (the alias of “Year” is “Period”, and it is not used in the mapping), they are used when mapping measures (the alias of “Miscellaneous” is “Misc”, and it is used to name the corresponding measure in the physical layer).

OBIEE Levels

The OBIEE logical and presentation layers can be obtained by dragging the cube across from the physical layer in the usual manner:

OBIEE Logical and Presentation Layers

Each Essbase generation is mapped onto an OBIEE physical column, which in turn is mapped onto the corresponding logical and presentation columns. And each logical column is mapped onto a corresponding level in the OBIEE hierarchy.

Because the emphasis in Essbase is on members and because a variety of functions exist to define various collections of members – of which “the members that belong to a particular generation” is but one – Essbase generations are not named by default. When imported into OBIEE the default names for columns and hierarchy levels take the form:

Gen<n>,<dimension name>

where “n” is the Essbase generation number.

It is possible to rename the generations after they have been imported, but in many ways it is more appropriate, given the member-driven reporting that is typically used with Essbase, to leave them unnamed.

MDX

Reporting with Answers works in much the same manner with Essbase as with any other data source. If we create a simple report:

Answers Report

then the logical SQL is sent to the BI Server and the Essbase specific MDX (multi-dimensional expression) is function-shipped to Essbase:

Function-Shipped MDX

Superficially, this expression resembles the familiar “select” statement. The “from” clause identifies the Essbase application and cube, and the “with” clause is used to tidy up lengthy expressions. The key clause is the “select” clause, which has two components, one ending in “on columns” and the other ending in “on rows”.

The “on columns” element contains measure expressions – in this case “[Accounts].[Sales]”, the “Sales” member drawn from the “Accounts” dimension.

The “on rows” element contains member expressions – in this case “[Year].Generations(2).members”, all members belonging to generation two of the dimension “Year” (you can see why generations in Essbase are not usually named, as their members can be easily referenced, when needed, by using an appropriate expression).

The properties sub-clause is used to provide OBIEE with the information needed to map Essbase members onto OBIEE levels (for an OBIEE level, say, “Gen2,Year”, “ANCESTOR_NAMES” provides the component “Year”, while “GEN_NUMBER” provides the component “2”).

The Importance of “Evaluate”

It should be clear from the discussion thus far that when we import an Essbase cube into OBIEE a great deal of the Essbase metadata is lost. And one consequence of this loss is that the type of default reporting that is possible using OBIEE is very limited compared to what is possible using reporting tools that are specifically designed to work with Essbase.

End-users accustomed to viewing Essbase derived reports will expect the same range of functionality when these reports come from within Answers and Dashboards. The only way to reproduce most of this Essbase functionality is to ignore OBIEE and to work directly with native Essbase MDX expressions by using “Evaluate” calls to function-ship native analytics to Essbase. Even when OBIEE alternatives are available, working directly with Essbase usually leads to a considerable improvement in performance.

In order to use “Evaluate” in Essbase reporting it is, of course, necessary to understand the Essbase database outline and to understand the MDX expressions needed to extract the relevant data from this outline. With another type of data source the code that is being function-shipped out the back door and the structure of the data source is usually of very little relevance to OBIEE reporting, but in the case of Essbase it is all important. So, in order to use Answers and Dashboards to report against Essbase it is just as important to understand Essbase as it is to understand OBIEE, a factor that adds considerably to the learning curve.

We hope this unsatisfactory situation will improve in 11g as OBIEE develops a more sophisticated multi-dimensional model that can be mapped automatically onto multi-dimensional data sources, but for the present working with OBIEE and Essbase basically involves constructing and debugging MDX expressions. The far from perfect mapping between the two products readily results in a variety of subtle bugs and incorrect aggregations, so a facility to debug MDX expressions becomes very important.

In order to work effectively with Essbase, you’ll need a development version of the Essbase data source with the production database outline but with a sufficiently small set of data so that you can verify the results of calculations by “hand-cranking” the numbers. You’ll also need access to the “MDX Script Editor” within the Essbase Administration Services client interface. Then you can cut and paste an MDX expression from the OBIEE query log file into the editor and run it directly against Essbase.

Everything Oracle

Home

Everything Oracle