Level Based Measures and Grand Totals

 Level Based Measures and  Grand Totals

One of the features that Oracle could add to BI EE, is in opening up the BI Server in the form of updatable APIs. Currently, the biggest stumbling block is if there are any lack of features we will have to completely depend on Oracle to add that feature. I thought Content Accelerator Framework or CAF that Oracle released as a free utility was a step in the right direction. It basically demonstrated how Catalog Manager can be extended to add more functionality. In fact, one can completely decompile the Catalog Manager jar files ourselves and add more features if needed. Unfortunately, in the case of BI Server, its not that straightforward. All functionality is hidden in the form of compiled DLLs or shared libraries. It is not possible to even use any standard de-compilers to understand how the queries are generated as the functionality is spread across multiple DLLs. Now only if Oracle could somehow expose a Framework within the Admin tool so that customers get an option to extend the BI Admin tool & its functionality, it will be all the more interesting.

Along similar lines(a feature that does not exist in the BI Server), I was asked recently how can we make Level based Measures to honor filters to attributes at lower levels. To expand further on the question, lets start with a very simple data model shown below

As you see, its a very simple model containing one dimension called Product and a set of Sales Measures. There is a Level Based measure called AMOUNT_SOLD_TOTAL which is assigned to the total level. Now, lets create a report containing Product Category, AMOUNT_SOLD and AMOUNT_SOLD_TOTAL.

This works as expected. Now, lets apply a filter to this report to show only product categories of certain products.

If you notice, the Level Based Measure assigned to the total level does not honor the report level filters. This is because the measure is applied to a level higher than the attribute that we are filtering on.  The Physical SQL fired is given below

WITH
SAWITH0 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from
     (select sum(T8684.AMOUNT_SOLD) as c1,
               T8626.PROD_CATEGORY as c2,
               T8626.PROD_CATEGORY_ID as c3,
               ROW_NUMBER() OVER (PARTITION BY T8626.PROD_CATEGORY_ID ORDER BY T8626.PROD_CATEGORY_ID ASC) as c4
          from
               PRODUCTS T8626,
               SALES T8684
          where  ( T8626.PROD_ID = T8684.PROD_ID and (T8626.PROD_NAME in ('1.44MB External 3.5" Diskette', '64MB Memory Card', 'Bounce', 'CD-R Mini Discs', 'Internal 6X CD-ROM')) )
          group by T8626.PROD_CATEGORY, T8626.PROD_CATEGORY_ID
     ) D1
where  ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(T8684.AMOUNT_SOLD) as c1
from
     SALES T8684),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6
from
     (select SAWITH0.c2 as c1,
               SAWITH1.c1 as c2,
               SAWITH0.c1 as c3,
               SAWITH1.c1 as c4,
               SAWITH0.c1 as c5,
               SAWITH0.c3 as c6,
               ROW_NUMBER() OVER (PARTITION BY SAWITH0.c2, SAWITH0.c3 ORDER BY SAWITH0.c2 ASC, SAWITH0.c3 ASC) as c7
          from
               SAWITH0,
               SAWITH1
     ) D1
where  ( D1.c7 = 1 ) )
select SAWITH2.c1 as c1,
     SAWITH2.c2 as c2,
     SAWITH2.c3 as c3,
     SAWITH2.c6 as c7,
     SAWITH2.c4 as c9,
     SAWITH2.c5 as c10
from
     SAWITH2
order by c1, c7

As you see in the SQL, the Product filter is not pushed into the second sub-query which calculates the level based measure. But what if we want a capability wherein we want the Level Based Measure to act like a Report Level Total i.e should honor the filters within the report for the same dimension as well. This is not possible out of the box. The behavior of Level Based Measures is completely determined by the BI Server and unfortunately we cannot customize that. This is an interesting use case. Though this report can easily be achieved using SUM BY clause directly from Answers, the question pertained to how we can achieve this using Level Based Measures.

So, to enable this(a workaround) we start with creating another logical table which will be an exact replica of the dimension that we created above

The only major difference is, we will not be assigning the measure to the total level of the new duplicate dimension. The duplicate dimension will refer to the same physical source. That is the key for this technique to work.

In the report, while applying the filter, use the column from the Duplicate Dimension. But the report will have columns from the Original Dimension. This will make the Level Based Measures to Honor the filters.

If you look at the Physical SQL now, you will notice that the filters are pushed even into the Level Based Measure Sub-query

WITH
SAWITH0 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from
     (select sum(T8684.AMOUNT_SOLD) as c1,
               T8626.PROD_CATEGORY as c2,
               T8626.PROD_NAME as c3,
               T8626.PROD_CATEGORY_ID as c4,
               T8626.PROD_ID as c5,
               ROW_NUMBER() OVER (PARTITION BY T8626.PROD_ID ORDER BY T8626.PROD_ID ASC) as c6
          from
               PRODUCTS T8626,
               SALES T8684
          where  ( T8626.PROD_ID = T8684.PROD_ID and (T8626.PROD_NAME in ('1.44MB External 3.5" Diskette', '64MB Memory Card', 'Bounce', 'CD-R Mini Discs', 'Internal 6X CD-ROM')) )
          group by T8626.PROD_CATEGORY, T8626.PROD_CATEGORY_ID, T8626.PROD_ID, T8626.PROD_NAME
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH1 AS (select sum(T8684.AMOUNT_SOLD) as c1
from
     PRODUCTS T8626,
     SALES T8684
where  ( T8626.PROD_ID = T8684.PROD_ID and (T8626.PROD_NAME in ('1.44MB External 3.5" Diskette', '64MB Memory Card', 'Bounce', 'CD-R Mini Discs', 'Internal 6X CD-ROM')) ) ),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6,
     D1.c7 as c7,
     D1.c8 as c8
from
     (select SAWITH0.c2 as c1,
               SAWITH0.c3 as c2,
               SAWITH1.c1 as c3,
               SAWITH0.c1 as c4,
               SAWITH1.c1 as c5,
               SAWITH0.c1 as c6,
               SAWITH0.c4 as c7,
               SAWITH0.c5 as c8,
               ROW_NUMBER() OVER (PARTITION BY SAWITH0.c2, SAWITH0.c3, SAWITH0.c4, SAWITH0.c5 ORDER BY SAWITH0.c2 ASC, SAWITH0.c3 ASC, SAWITH0.c4 ASC, SAWITH0.c5 ASC) as c9
          from
               SAWITH0,
               SAWITH1
     ) D1
where  ( D1.c9 = 1 ) )
select SAWITH2.c1 as c1,
     SAWITH2.c2 as c2,
     SAWITH2.c3 as c3,
     SAWITH2.c4 as c4,
     SAWITH2.c7 as c9,
     SAWITH2.c8 as c10,
     SAWITH2.c5 as c12,
     SAWITH2.c6 as c13
from
     SAWITH2
order by c1, c9, c10, c2

Not a straight forward solution but can be used if the only filters that you apply in your reports are through prompts. Remember the drill filters will not be honored as well (as the drills will automatically apply filters using the columns from the original dimension). This behavior can potentially change in future releases.