Using Essbase as an Aggregation Layer for Federated OBIEE Models

Mark Rittman

Mar 5, 2009 • 9 min read

Now that the 11.1 release of Essbase is out, together with Essbase Studio, an option that's now open to us is to use Essbase as an aggregation layer for OBIEE. You can build a single logical dimensional model over your data using the Oracle BI Administrator tool, source your data from a number of relational databases using the "federated query" feature, load a summarized version of it into Essbase and then plug the Essbase cube back into OBIEE to provide aggregations. In theory it's quite a neat feature, and one that Oracle plan to include in the next release of OBIEE as an automated feature, but how does it work now and are there any areas to watch out for, particularly when we have complex federated logical models in OBIEE that have measures of differing granularity?

To take an example, I've got a logical OBIEE model that takes its data from relational and excel data sources. This is the model I went through in this article and use for most of my demonstrations, together with this additional data source that maps quota data in at a different level of granularity.

Essb Aggr1

If you run a query against the combined data set where both data sources can provide values, you can see quotas and quantity sold broken down by the common dimensions, like this:

Essb Aggr4

Where it gets a little interesting is when you try and include a dimension, or a level of aggregation that isn't supported by one of the data sources. When this happens, the second data source gets CAST as NULL so that you don't see values for it, which would make sense as you can't break the second data source's values down by a dimension or level that's not present in the data.

Essb Aggr5

So let's say now that we want to use Essbase to provide aggregations for this data set. If you had just the one data source, this would be a simple job and would involve just importing the OBIEE presentation model into Essbase Studio, creating the joins, picking a level in the source data's hierarchies to create the cube at and then loading the cube. In our case it's a bit more complicated though, as our two measures are effectively held at different levels of granularity, with the quantity measure sourced from just the Oracle database but at the lowest level of detail for all three dimensions, whilst the quota measure comes from the excel data source and is valid at the product category and month level only, with no breakdown by the customer dimension. As we've seen from the Answers report, the only time that you can include them into a single report is when you leave out the customer dimension and report on just products from category upwards, and on time from month upwards. All of this is fine within OBIEE, but how do we design an Essbase cube to accomodate these differences?

Essbase, unlike Oracle OLAP, has the concept of "hypercubes" where every measure within a cube is dimensioned by every dimension. This is similar to how a relational fact table works, but is different to Oracle OLAP which uses Analytic Workspaces, collections of measures and dimensions where each measure can be associated with any arbitrary selection of dimensions. The nearest equivalent to an Essbase hypercube in Oracle OLAP is an Oracle OLAP "cube", which is actually where you bring dimensions and measures together and is used mainly be legacy Oracle OLAP query tools such as Discoverer for OLAP. But overall, Oracle OLAP is a bit more flexible when it comes to measure dimensionality, and so if we're going to use Essbase then we need to think about how we're going to design this.

In fact, there are two main approaches we can take:

If we want a single Essbase cube, then we can only do what we've done in Answers and create the cube where the dimensions and granularity are common between the two data sources; that is, we create two measures, quota and quantity, and dimension them by product (going from category up to all products) and time (going from month up to quarter, year and all times). This isn't bad, but it doesn't provide any aggregation for the quantity measure when we analyze it by the customer dimension.
We can create a separate Essbase cube (or "database") for each measure, we can then dimension each of these as per the original data source, but then we are going to have two Essbase data sources for OBIEE and it's potentially going to get a bit complicated.

Having said all of this, if your OBIEE model has just a single data source then things will be a lot simpler, you'll just use Essbase Studio to create an aggegated cube in Essbase and then plug it back into OBIEE. But to start off, let's look at the first scenario, where we do indeed stick with a single Essbase cube and just use it to aggregate the OBIEE data where there is shared dimensionality.

Bringing the OBIEE "tables" into Essbase Studio and creating an initial MiniSchema diagram, you can see how the four tables are joined and that they are joined on the lowest level of detail:

Essb Aggr6

Now we could just create a single Essbase database that has just the quantity measure in it, and we could then bring across all the other dimensions and the lowest level of detail, create our cube and then load it into OBIEE. But what we're going to do is to create an Essbase cube that includes both measures, at the lowest level of detail that both measures are valid. To do this I create my candidate dimension data sources as normal, and then create candidate hierarchies that contain levels that are valid for both measures' level of granularity.

Essb Aggr7-1

Once the cube schema is created, you can then use this to generate the Essbase model (equivalent to the old Essbase Metaoutline that you used to get with 9.3.x) and then use this to generate the Essbase cube. Once the cube is put together, you can view the outline in Essbase Administration Services, like this:

Essb Aggr10

You can also check the numbers to make sure they match back to the Answers request you created earlier.

Essb Aggr11

So far so good. Now if you want to use this Essbase cube to provide aggregations for your OBIEE logical model, the next step is to import the cube metadata into the physical layer of OBIEE metadata so that it's ready then to map into your logical model. For a cube looking as ours did, the OBIEE physical model would look like this:

Essb Aggr12

Now it's a case of mapping in the Essbase keys into the relevant columns in the logical model, taking the Gen4,TimeHier key from the Essbase source and mapping it into the Month column in the logical dimension table, and mapping the quantity and quota Essbase measures on top of the existing measures in the logical fact table.

Essb Aggr13

Then, in the same way that you have to do for any new source of a differing granularity to the logical star schema, you need to tell the BI Server at what level in the various hierarchies to use the new Essbase data.

Essb Aggr14

After that it's a case of running a report that would use the aggregated data in the Essbase cube, and checking the query log to see whether it's handed off the query to Essbase, which it has:

-------------------- Logical Request (before navigation):
RqList

Product.Category as c1 GB,

Quantity:[DAggr(Items.Quantity by [ Product.Category] )] as c2 GB

OrderBy: c1 asc
+++Administrator:6b0000:6b0006:----2009/03/05 14:43:20
-------------------- Sending query to database named w2k3vm (id: <<30675>>):
With

set [ProductHier2]  as '[ProductHier].Generations(2).members'

select

{ [MeasureHier].[Quantity]

} on columns,

NON EMPTY {{[ProductHier2]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows

from [CusApp.CubeFull]

So what happens if we run a report that includes the customer dimension in the report, or requests data at a finer level of granularity than the cube contains, such as if we drill-down on the category to show the individual products? In either case, the BI Server switches over to the relational source that provides more detailed data for the quantity measure, CASTing the quota measure as NULL so that it shows as a blank in report.

-------------------- Logical Request (before navigation):

RqList
    Product.Category as c1 GB,
    Product.Product ID as c2 GB,
    Quantity:[DAggr(Items.Quantity by [ Product.Category, Product.Product ID] )] as c3 GB,
    Quota:[DAggr(Items.Quota by [ Product.Category, Product.Product ID] )] as c4 GB
DetailFilter: Product.Category = 'Computers'
OrderBy: c1 asc, c2 asc

+++Administrator:6b0000:6b0009:----2009/03/05 14:55:50

-------------------- Sending query to database named orcl (id: >):

select distinct D1.c2 as c1,
     D1.c3 as c2,
     D1.c1 as c3,
     cast(NULL as  DOUBLE PRECISION  ) as c4
from
     (select sum(T3102.QUANTITY) as c1,
               T3118.CATEGORY as c2,
               T3118.PRODID as c3
          from
               CUST_ORDER_HISTORY.PRODUCT T3118,
               CUST_ORDER_HISTORY.ITEMS T3102
          where  ( T3102.PRODID = T3118.PRODID and T3118.CATEGORY = 'Computers' )
          group by T3118.CATEGORY, T3118.PRODID
     ) D1
order by c1, c2

But what if we want to view just quantity data aggregated, with the customer dimension included? The cube we've just built in Essbase excludes the customer dimension as the quota measure isn't dimensioned by it, so in this case the BI Server would have to revert back to aggregating the detail-level relational data for this measure. What we can do though is create another Essbase cube but this time just for the quantity measure, again loaded with aggregated data but with the full set of dimensions attached, like this:

Essb Aggr15

After then checking that it contains the same data as our relational source, which it does:

Essb Aggr16

we can then import this additional cube's metadata into OBIEE and map that in to the logical model as well, this time creating links to the customer as well as product and time dimensions, and mapping the quantity measure in the cube - the only measure - into the existing quantity measure in the logical model, at the correct level of aggregation.

Essb Aggr17

Then, when you create a request that contains just the quantity measure, and asks for the data to be aggregated, the BI Server will use this new Essbase data source, like this:

-------------------- Logical Request (before navigation):

RqList
    Product.Category as c1 GB,
    Times.Year as c2 GB,
    Quantity:[DAggr(Items.Quantity by [ Customer.State, Product.Category, Times.Year] )] as c3 GB,
    Customer.State as c4 GB
OrderBy: c1 asc, c2 asc, c4 asc

+++Administrator:850000:85000a:----2009/03/05 16:30:05

-------------------- Sending query to database named w2k3vm (id: <<36589>>):

With
  set [CustHier3]  as '[CustHier].Generations(3).members'
  set [ProductHier2]  as '[ProductHier].Generations(2).members'
  set [TimeHier2]  as '[TimeHier].Generations(2).members'
select
  { [QuantHier].[Quantity]
  } on columns,
  NON EMPTY {crossjoin ({[CustHier3]},crossjoin ({[ProductHier2]},{[TimeHier2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [CusApp2.CubeQuan]

If you then add the quota measure into the query, the BI Server still queries the same Essbase dataset but doesn't return any quota information, but if you then leave quota in but remove the dimension that only the quantity measure uses, the BI Server switches over to the Essbase cube that provides data for both of the measures.

So, the moral of the story is: it's relatively simple to take data in your OBIEE logical model, load it into an Essbase cube and then map it back into OBIEE. Essbase can therefore provide an easy-to-setup aggregation layer for OBIEE that satisfies all of your aggregations, not just the individual ones that the existing Aggregate Persistence Wizard does (Oracle OLAP does a good job on this, as well). If your logical model is a bit more complex though, and in particular features measures that contain values only at certain levels of granularity, you will need to set up multiple Essbase cubes (or "databases") and map them in to each measure appropriately. It's not a particularly big deal, and the BI Server hands-off between the different Essbase data sources well, but it something that you'll need to think about if you start using Essbase to aggregate OBIEE logical model that feature federated data sources and/or measures of differing granularity.

Sign up for more like this.