Oracle BI EE 10.1.3.4.1 – Modeling Measure Dimensions on Relational Sources – Essbase Style

Venkatakrishnan J -

One of the biggest differences between Essbase and most other reporting/OLAP tools like BI EE/Oracle OLAP etc is in the way Measures are treated(comparison only in terms of data modeling and reporting and not on physical implementation). In Essbase, every data/value(cell) is attributed by a set of dimensions. Even a measure is considered as an attribute of another dimension. But in the case of Oracle OLAP, BI EE etc, one requires to specifically identify a set of measures and load/report against them. The problem with this approach is the fact that in many cases while doing the reporting we would like to treat the measures like a normal dimension and vice versa. Just to illustrate this point, the most commonly used component in BI EE Answers is the Pivot View component. A Pivot View basically converts a set of dimension attributes as a set of measures(typical analysis) by spreading all the dimension attributes in the column section.

Similarly, in many cases, we want to treat the measures as a set of dimension attributes as shown below

Again pivot table component can be used to achieve this. Both SQL and MDX can swap the attributes to Columns and Rows pretty efficiently(basically one can manually handcraft SUM, FIRST etc kind of aggregation on any valid column). But once we designate a set of static measures within the reporting tool, the model can become constrained to an extent. Of course, there are ways around it like using Pivot tables, Answers based aggregation, UNIONs etc. The main drawback with a measure based model is the fact that one cannot efficiently drill on Measure Hierarchies. The other drawback is since our database related data structures are more oriented towards static measure modeling (facts and dimensions), it is very difficult to switch the measure hierarchy for reporting.

For example, if you had gone through or attended our training days event last year, we had shown how to go about switching measure hierarchies in BI EE for an Essbase source. Its a very common requirement when you have Essbase as a data source. Many users might choose Accounts dimension as a measure dimension for reporting. Some users might choose Scenario as the measure dimension for reporting. Essbase provides that flexibility and the same can be leveraged from BI EE as well. But in the case of relational sources, it can be quite difficult to achieve this. There are a couple of reasons for this

  1. If we model a separate measure dimension in our warehouse, the fact tables will contain only one measure. The size of the fact table will grow multiplied by the number of measures in the measure dimension.

  2. Its not practical(& to an extent does not make sense) to model it this way for relational data models at the database level.

Though we might stick with a fact/dimensional model in the data warehouse, i think the reporting tools should provide the capability to switch the measure dimensions even for relational sources(PIVOT and UNPIVOT operations should be supported from the RPD). Currently we do not have this capability in BI EE (from a repository standpoint). Though BI EE does not support this currently, the BI Server repository can be tweaked to an extent to achieve this using Conforming Dimensions. Lets look at a couple of scenarios. One is modeling the measure dimension for relational sources and the other is in switching the measure dimension itself.

Measure Dimension:

Lets understand this through our usual example SH schema that gets shipped with oracle database. Most common way of designing a repository for this SH schema is given below

Its a very simple repository containing a Sales measure, a cost measure and all the dimensions. If you look at this, UNIT_COST and AMOUNT_SOLD are designated as measures(which is how it exists in the physical database). Lets assume that a requirement is to treat both these measures as attributes of a measure column i.e we want to be in a position to treat both the measures as part of a single dimension. The report below basically explains what is needed

The above report has a column called Measure which will basically un-pivot the measure values. To achieve this, we start with creating a new custom dimension within the RPD called as Measure Dim. This will basically have one column called as Measure which will be fed through 2 logical table sources (one is Sales and the other is Costs). This is to signify that each LTS will be used to serve each measure

Then we create 2 custom fact tables (one for sales measure and the other for cost measure) and then force the conforming dimension join as shown below

To combine both the measures through a common single fact, we create another logical fact table with one logical column. This will be fed through a logical calculation of UNIT_COST and AMOUNT_SOLD as shown below

This will provide an extra dimension for your reporting (measure dimension). Remember, this will work as long as you have measures from different physical tables. If you have measures from the same physical table then you will have to alias the table as many times as the number of measures (which is not recommended). Also, currently we cannot make BI EE to push the PIVOT and UNPIVOT operators directly from the BMM.

Swapping Measure Dimensions:

The next requirement is, now that we have converted the measures as a set of attributes derived from a column, to convert each member of the CHANNEL_CLASS column as a measure i.e we want 3 measures Direct, Indirect and Others. To do this, replicate the measure in each fact table 3 times. Basically each measure will have a FILTER function applied to filter only on the necessary attributes. For example, the Sales and Cost fact tables are shown below

The Direct measure will have a FILTER function applied as shown below

Similar filters will be applied for Indirect and Others measures as well for both the fact tables. Then in the main fact table, we need to combine the individual measures again.

This will allow you to create a report containing the actual measures as Dimension attributes and the actual dimension attributes as measures (all of this in a normal table view)

If you look at the SQL, you will notice that there will be conforming dimension join done on the Amount Sold and Unit Price attributes and then they are combined using a main query

WITH
SAWITH0 AS (select sum(case  when T1776.CHANNEL_CLASS = 'Direct' then T1929.AMOUNT_SOLD end ) as c1,
     sum(case  when T1776.CHANNEL_CLASS = 'Indirect' then T1929.AMOUNT_SOLD end ) as c2,
     sum(case  when T1776.CHANNEL_CLASS = 'Others' then T1929.AMOUNT_SOLD end ) as c3,
     'Amount Sold' as c4,
     T1916.PROMO_NAME as c5
from
     PROMOTIONS T1916,
     CHANNELS T1776,
     SALES T1929
where  ( T1776.CHANNEL_ID = T1929.CHANNEL_ID and T1916.PROMO_ID = T1929.PROMO_ID and (T1776.CHANNEL_CLASS in ('Direct', 'Indirect', 'Others')) )
group by T1916.PROMO_NAME),
SAWITH1 AS (select sum(case  when T1776.CHANNEL_CLASS = 'Direct' then T1784.UNIT_COST end ) as c1,
     sum(case  when T1776.CHANNEL_CLASS = 'Indirect' then T1784.UNIT_COST end ) as c2,
     sum(case  when T1776.CHANNEL_CLASS = 'Others' then T1784.UNIT_COST end ) as c3,
     'Unit Price' as c4,
     T1916.PROMO_NAME as c5
from
     PROMOTIONS T1916,
     CHANNELS T1776,
     COSTS T1784
where  ( T1776.CHANNEL_ID = T1784.CHANNEL_ID and T1784.PROMO_ID = T1916.PROMO_ID and (T1776.CHANNEL_CLASS in ('Direct', 'Indirect', 'Others')) )
group by T1916.PROMO_NAME)
select distinct case  when SAWITH0.c4 is not null then SAWITH0.c4 when SAWITH1.c4 is not null then SAWITH1.c4 end  as c1,
     case  when SAWITH1.c5 is not null then SAWITH1.c5 when SAWITH0.c5 is not null then SAWITH0.c5 end  as c2,
     nvl(SAWITH1.c1 , 0) + nvl(SAWITH0.c1 , 0) as c3,
     nvl(SAWITH1.c2 , 0) + nvl(SAWITH0.c2 , 0) as c4,
     nvl(SAWITH1.c3 , 0) + nvl(SAWITH0.c3 , 0) as c5
from
     SAWITH0 full outer join SAWITH1 On SAWITH0.c4 = SAWITH1.c4 and SAWITH0.c5 = SAWITH1.c5
order by c1, c2

Though this will work in most cases, there are some drawbacks with this approach

  1. The SQLs generated will not be the best performing ones. As the number of measures grow, the number of conforming dimension joins will increase thereby producing poor performance. Unfortunately BI EE cannot push the PIVOT/UNPIVOT operators supported from Oracle 11g

  2. If there are a lot of measures from a single fact table then this requires multiple fact table aliases to force the conforming dimension join across all dimension sources (For the measure dim). This might make the RPD un-necessarily big.

You might not find this technique to be of practical use, but this demonstrates the capabilities of the BI Server to model some complex requirements. I am hoping that sometime in the near future we should see more of pure multi-dimensional modeling on relational sources to be supported by BI EE.