Adding Essbase Measures to Relational OBIEE Models

I'm running an especially extended version of our OBIEE developer course in early January, and the client in question wants us to include a bit more content on Essbase integration. Whilst bringing Essbase cubes into OBIEE and then reporting on them in their own subject area is good, in reality the most likely OBIEE use cases for Essbase are where Essbase multidimensional measures are added to an existing relationally-sourced logical model. Earlier on in the year I blogged about using Essbase and Microsoft Analysis Services as an aggregation layer for OBIEE in a form of "Hybrid OLAP", this time though I want to look at adding a budget measure coming from Essbase into an existing OBIEE logical model.

In this instance I'm working with the physical and logical models that I generally use for our OBIEE courses, where we take a normalized schema and then use the mapping features in OBIEE Administrator to construct a "virtual" star schema. In this case, the logical model contains quantity and price measures, and I want to now bring in some budget data from an Essbase cube and display it in the same fact table as the other measures. The original measures are dimensioned by customers (at the customer level), product (at the product level) and time (at the day level), whereas the Essbase budget data is dimensioned by only products (at the product category level) and time (at the month level). So how do you fit it all together?

The first step is to bring the Essbase cube into the BI Administrator tool, so that your physical layer looks like this:

Now if you were going to create a new logical model and subject area just for this Essbase data, you would drag and drop the whole Essbase cube into the business model and mapping layer, and OBIEE Administrator would create all the logical tables, columns and dimensions for you, as I detailed in this OTN article on OBIEE and Hyperion System 9. In this instance though, I want to include the budget measure from the Essbase cube into an existing logical model, and to do this I need to associate the dimension level keys brought across from the Essbase cube with the existing data in the logical model.

To do this I locate in the first instance, the Product Category key from the PROD_CAT_DIM Essbase dimension, and drag and drop it onto the corresponding logical column in the logical model. This creates a join between the two data items and adds the Essbase cube as a new logical table source for the logical model.

I then go and edit the new logical table source and tell OBIEE only to use it at the Product Category level and above. This means that budget data will only now be displayed when the data in the report has the required level of aggregation.

I then repeat this for the Month dimension in the Essbase cube, mapping it to the Month column in the logical model and setting the level at which the logical table source can be used. Finally, I drag and drop the Budget measure across from the Essbase cube and add it to the logical fact table, afterwards editing it's new logical table source so that it only gets used at the Product Category and Month Level.

So now I'm in the situation where my new Budget measure is displayed alongside the Quantity and Price measures in my logical fact table.

If I move over to Oracle BI Answers now, I can run queries that include both the Essbase-sourced and relationally-sourced data, and as long as I query data that's at least aggregated to the Product Category and Month level, I can see all the measures together.

If you take a look at the query log, you can see the MDX and SQL queries being generated, so that BI Server can perform the required federated query.

-------------------- Sending query to database named
Customer Orders (id: <<47359>>):
select T848.CATEGORY as c1,

T856.YEAR_YYYY as c2,

sum(T832.QUANTITY) as c3

from

CUST_ORDER_HISTORY.TIMES T856,

CUST_ORDER_HISTORY.PRODUCT T848,

CUST_ORDER_HISTORY.ITEMS T832,

CUST_ORDER_HISTORY.ORDERS T841

where  ( T832.ORDID = T841.ORDID and T832.PRODID = T848.PRODID and T841.ORDERDATE = T856.DAY_ID )

group by T848.CATEGORY, T856.YEAR_YYYY

order by c1, c2
+++Administrator:2e0000:2e000c:----2008/12/19 10:25:29
-------------------- Sending query to database named

w2k3vm (id: <<47428>>):

With

set [MONTH_DIM2]  as '[MONTH_DIM].Generations(2).members'

set [PROD_CAT_DIM2]  as '[PROD_CAT_DIM].Generations(2).members'

select

{ [Accounts].[BUDGET]

} on columns,

NON EMPTY {crossjoin ({[MONTH_DIM2]},{[PROD_CAT_DIM2]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows

from [Customer.Budget]