Oracle BI EE 11g - Enriching Essbase reports with Relational Attributes - Lookups

As mentioned in my blog posts here and here, ability to do lookups in the repository opens up a lot of possibilities to solve different reporting use cases that were otherwise not possible to solve in BI EE 10g. One such use case is the ability to easily add relational attributes to an otherwise pure Essbase report. In 10g, one had to primarily use the BI EE repository modeling to achieve such use cases. I have blogged about it before here. But 11g makes it a lot easier now.

To illustrate this, lets take a very simple example of reporting on the Sample > Basic essbase cube. Lets assume that in addition to the essbase database, we also have a relational table providing Product related attributes for the Product Dimension. One such product attribute is Product Min Price. The requirement is to have an ability to include the Product Min Price attribute to an Essbase report as shown below

To achieve this, we start of with importing the Essbase cube and the relational table into the physical layer.

After the import, we need to basically convert the Product hierarchy in the Essbase cube to a value based hierarchy. The main reason for this is, as BI EE 11g fires separate MDX queries for each level (As you navigate in a hierarchical column), converting the hierarchy type to a value based hierarchy ensures that the lookup function does not alter the grain of the report.

Create the Business Model for the Essbase cube. Create a new logical table for the relational attribute table and then mark it as a lookup table.

In this case, we shall be using the BMM lookup. So, it is necessary for the logical table to have a primary key (Product ID).

Create a new logical column in the Product Logical table. Make this column to refer to the Product Min Price attribute by using the SPARSE lookup function as shown below

LOOKUP
(  SPARSE
   "Sample - Relational Product Attributes". "Product Attributes". "Product Min Price",
' ',
"Sample - Relational Product Attributes". "Product". "Product SKU - Member Key")

Now create a report containing the product dimension, the relational attribute and a measure.

As you see, we now have the relational attributes displayed along with the Essbase data. And if we look at the queries generated, you will notice separate queries for Essbase and the relational sources. BI Server will basically do an in-memory lookup. Remember this lookup is very similar to the Driving table option that i had shown before here. So, ensure that the number of relational attributes that you are displaying in the report is less. This is not an ideal method for federation. So, if you have measures coming in for relational sources, then its best to handle them in the repository through the conforming dimension join. But this method can be really useful in scenarios like displaying Smartlists for Planning cubes where it is necessary to quickly show a small set of attributes from a relational source without doing a lot of repository modeling.

select T84512.PRODUCT_MIN_PRICE as c1,
     T84512.PRODUCT as c2
from
     PRODUCT_ATTRIBUTE T84512
where  ( T84512.PRODUCT in
(:PARAM1, :PARAM2, :PARAM3, :PARAM4, :PARAM5,
:PARAM6, :PARAM7, :PARAM8, :PARAM9, :PARAM10,
:PARAM11, :PARAM12, :PARAM13, :PARAM14, :PARAM15,
:PARAM16, :PARAM17, :PARAM18, :PARAM19, :PARAM20) )
order by c2

With
  set [_Product2]  as '{Distinct({[100]})}'
  set [_Product3]  as
'Filter(Generate({[_Product2]},[Product].currentmember.children), (IsValid([Product].CurrentMember.[MEMBER_UNIQUE_NAME])))'
select
  { [Attribute Calculations]
  } on columns,
  NON EMPTY {{[_Product3]}}
properties GEN_NUMBER, [Product].[LEVEL_NUMBER],
[Product].[Ancestor_Names],
[Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor] on rows
from [Sample.Basic]