Oracle BI EE 10.1.3.4.1 – Drills on Union Requests – Fragmentation

One another issue with BI EE is the fact that, whenever we try to build a report with a Union All request, we would lose the capability of using repository based drills in the front-end. There was a question recently on how to overcome this limitation. This is very valid whenever we are trying to mimic a multi-dimensional report using a relational database wherein one can pick and choose the members from multiple levels. The most common way of reporting on multiple levels within a single column is to use Union-All directly from Answers. But we would lose the drills on this single column which sometimes defeats the purpose. In order to overcome that, lets look at a repository design technique wherein we can provide drills on such Union-based requests as well.

To illustrate, lets use the Product dimension. Lets assume the requirement is to have the values of PRODUCT_CATEGORY and PRODUCT_SUBCATEGORY columns in a single row as shown below.

The idea is provide drills on both the category as well as Product Sub-category values in the above report. The design that we would be using today, to an extent can help us in achieving parent-child hierarchy reporting as well(i would be covering this in a future blog entry).

To start with, the approach is to have as many aliases as the number of columns that we would be needing in our union all request. Lets say, if you have 4 levels in your hierarchy, i would create 4 aliases of that dimension and also the facts. This is not hard-coded by any means as even if you have 4 columns, you can still pick and choose the columns that you want to do Union-All using a filter.

Now, create 3 custom columns in the Product Dimension. The first column will correspond to the Level ID and the 2nd will correspond to the Level Name. The third one basically point to the hardcoded column Name. Now bring in both the Aliases of the Product Dimension as logical table sources for the product dimension.

In the first logical table source make the Level1 ID, Level 1 Name and Source columns to point to PRODUCT_CATEGORY related columns.

In the second logical table source, make the Level1 ID, Level 1 Name and Source columns to point to PRODUCT_SUBCATEGORY related columns.

Setup a hierarchy for the Product dimension as shown below.

Now ensure that both the logical table sources would be used using Fragmentation.

The above is generally done in the fact logical table sources. But i would recommend doing this in the dimension as well so that we can have more control while creating reports without facts. Also, setup the same fragmentation as above in the fact logical table sources as well. Now, lets create a report containing the Level1 Name and a fact measure. You would notice that you would have a union all request getting fired automatically.

select D1.c1 as c1,
     D1.c2 as c2
from
     (select D5.c2 as c1,
               sum(D5.c4) as c2,
               D5.c3 as c3
          from
               ((select T5226.PROD_SUBCATEGORY as c2,
                         T5226.PROD_SUBCATEGORY_ID as c3,
                         T5257.AMOUNT_SOLD as c4
                    from
                         PRODUCTS T5226 /* Products - B */ ,
                         SALES T5257 /* Sales - B */
                    where  ( T5226.PROD_ID = T5257.PROD_ID )
                    union all
                    select T5203.PROD_CATEGORY as c2,
                         T5203.PROD_CATEGORY_ID as c3,
                         T5249.AMOUNT_SOLD as c4
                    from
                         PRODUCTS T5203 /* Products - A */ ,
                         SALES T5249 /* Sales - A */
                    where  ( T5203.PROD_ID = T5249.PROD_ID ) )
               ) D5
          group by D5.c2, D5.c3
     ) D1
order by c1

We should be able to drill on Product Category as well as the subcategory.

The next question is what if we want to choose the columns (either Product Category or Product Sub Category). In such a case, just add a filter on the source column as shown below

This will restrict the report to only the Sub Categories. Drills would still be available.

This approach can be used for those dimensions where every level is treated more or less the same (like a multi-dimensional drill). The downside with this approach though is, if we need this on all the dimensions, then the number of logical table sources can sometimes become too large to manage.