Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Enriching Essbase reports with relational attributes

If you had attended our training days event or one of our Open World sessions, you will have noticed that we had covered the various aspects of integration between Essbase and relational sources using BI EE. The presentations are available here. One of the scenarios that we had not covered was the ability to display relational attributes along with Essbase data. It is very similar to the Horizontal Fragmentation technique we had covered in the training. But there are significant differences which i thought made sense to cover in a separate blog entry.

For example, lets assume that we have the entire SH schema loaded into Essbase. In many cases, not all the attributes of a dimension are loaded into Essbase for a couple of reasons.

1. Load & Retrieval Performance
2. Cube size

If you look at the Essbase outline below, for the product dimension we have just loaded the primary hierarchy.

As you see this outline does not have all the Product attributes like Product Price, Product Pack Size etc. In many cases, such attributes might be maintained in relational sources but may never be loaded inside an Essbase outline. But from a reporting standpoint, we might still need to display these attributes in some cases as shown below

If you notice, Quantity is an Essbase measure and PROD_LIST_PRICE is a relational attribute of a product. The question is how do we model this in the repository in such a way that there is least impact on the queries generated. There are couple of approaches that we can take to model this. I will cover one such approach today.

This modeling technique leverages the concept of conforming dimensions. We start first by including the relational product source table in the Essbase Product dimension as shown below

Then we create another custom logical table called Product Attributes. In this logical table we we can include all those attributes that do not exist in Essbase. This logical table will be modeled as a separate dimension.

Ensure that the source of this new logical table has the same relational table source as the source that we added in the Product dimension. Now create a dummy Fact logical table called Fact-Products which will enforce the conforming dimension join.

The source for this fact logical table will be the same Products relational source. Now, create complex joins as shown below.

For each fact measure in the Essbase source map it to the total level of the Product – Attributes dimension as shown below. This will ensure that BI Server will combine both the sources together in its own memory.

Now while creating a report we need to ensure that we bring in measures from Essbase as well as our dummy fact. The dummy fact enforces BI Server level join at the product level.

If you look at the SQL queries, you will notice that BI Server will fire 2 separate queries. But the joins across the sources will be through the conforming product dimension.

   T2273.PROD_CATEGORY as c2,
     T2273.PROD_SUBCATEGORY as c3,
     T2273.PROD_NAME as c4,
     T2273.PROD_LIST_PRICE as c5,
     sum(1) as c6
from
     PRODUCTS T2273
where  ( T2273.PROD_SUBCATEGORY = 'Accessories' )
group by T2273.PROD_CATEGORY, T2273.PROD_LIST_PRICE, T2273.PROD_NAME, T2273.PROD_SUBCATEGORY, T2273.PROD_TOTAL
order by c1, c2, c3, c4

With
  set [Product4]  as '{[Product].[Accessories]}'
  set [Product5]  as 'Generate({[Product4]}, Descendants([Product].currentmember, [Product].Generations(5),SELF), ALL)'
select
  { [Measures].[Quantity]
  } on columns,
  NON EMPTY {{[Product5]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [SH.SH]

This shows the capability of BI Server in modeling multiple data source scenarios. If you require other dimensions to be part of the report then the dummy fact will have to assigned to the Total level of all the other dimensions (effectively Total levels need to be created in each dimension). For example, the Promotion dimension is shown below