Everything Oracle

Introduction

Just as the attributes of a standard dimension, say “Products”, can be arranged in a hierarchy; for example:

Product Category => Product Subcategory => Product

sometimes a similar hierarchical arrangement can exist between measures, “Accounts” for example:

Profit => Total Expenses => Payroll

Essbase supports the concept of measure hierarchies, but OBIEE does not. Once an Essbase cube is imported into OBIEE the hierarchical relationships between measures are lost.

However, by editing the OBIEE repository it’s possible to reconstruct the measure hierarchy within OBIEE, and then to use this hierarchy for drill-down reporting in the normal manner.

Default Reporting on Imported Measures

Let’s start off by looking at what OBIEE does by default. If we import the Essbase “Demo” application into the OBIEE repository, then the physical layer looks as follows:

Imported Essbase Cube

We have four standard dimensions, “Year”, “Market”, “Product”, and “Scenario”. The “Accounts” dimension – a measure dimension – is not shown. Instead, its hierarchy of measures has been flattened into a list: “COGS”, “Margin”... and “Total_Expenses”. All the measures in the “Accounts” hierarchy are present, but the relationships between the measures have been lost in the import process.

The best we can do by way of default reporting to show some aspects of the hierarchical relationships between the measures is to colour code the report column headings:

Default Report

Here, generations two, three, and four of the “Accounts” dimension are represented by the colours “orange”, “yellow”, and “grey” respectively. However, much of the detail is still lost; for example, this representation does not reveal that “Sales” is a detail of “Margin”, and that “Marketing” is a detail of “Total_Expenses”.

Swapping Dimensions

If we right-click on the “Basic” cube, select “Properties”, then the “Hierarchy” tab, we can edit the properties of each dimension. The standard dimensions have a “Dimension Type” of “Other”, apart from “Year”, which has a “Dimension Type” of “Time”. The “Accounts” dimension has a “Dimension Type” of “Measure Dimension”.

From the “Hierarchies” tab edit the “Accounts” dimension and change the value of “Dimension Type” from “Measure Dimension” to “Other”:

Changing the Accounts Dimension Type

From the “Hierarchies” tab edit the “Scenario” dimension, and change the value of “Dimension Type” from “Other” to “Measure Dimension”:

Changing the Scenario Dimension Type

So now the dimension types of the “Scenario” and “Accounts” dimensions have been swapped.

If we examine the physical layer we can see that the “Scenario” dimension has disappeared – now it’s a measure dimension – and it has been replaced by the “Accounts” dimension:

Physical Layer with Accounts Dimension

But we still have the physical cube columns corresponding to the “Accounts” dimension, when what we want is the physical cube columns corresponding to the “Scenario” dimension. So first we delete the existing physical cube columns:

Deleted Physical Cube Columns

Then we create physical cube columns for the attributes of the “Scenario” dimension: “Actual”, “Budget”, and “Variance”. Right-click on the “Basic” cube and select “New Object => Physical Cube Column”. In the properties window, select a “Type” of “DOUBLE”, check the “Nullable” check box, and select an “Aggregation rule” of “Aggr_External” for each “Scenario” attribute in turn. For example, for the “Actual” physical cube column we have:

Setting Physical Cube Column Properties

With the new physical cube columns in place the physical layer becomes:

Added Physical Cube Columns

If we drag the cube across to the business layer to create a logical model, and then drag the “Scenario” and “Accounts” logical columns across to create the presentation layer, we can create a report that correctly reflects the measure hierarchy:

Report showing Reconstituted Measure Hierarchy

In this report we can see that “Sales” is a detail of “Margin” and that “Marketing” is a detail of “Total_Expenses”.

Summary

While swapping dimensions is not too onerous a task, we can but wonder why Oracle hasn’t provided us with a “Swap Dimensions” list box, one in which we select the pair of dimensions that we want to swap, and then, when we press a button, OBIEE does all the hard work for us!

Everything Oracle

Home

Everything Oracle