Oracle BI EE 10.1.3.4.1 – OLTP Reporting – Conforming Dimensions and Multi-Grain Fact measures

In the last blog entry here, i had shown a very simple transactional reporting use case wherein i made the BI Server to switch tables based on the measures chosen. Basically switching of tables would result in hitting a specific table to get the desired measure. But in that case we were reporting on measures of the same grain. Today we shall see the use case 2 wherein we have 2 measures at different grains. This is again a classical OLTP reporting use case as in most cases we would typically encounter measures at different grains.

For example, lets take the same Order Entry schema. We have 2 tables ORDERS and ORDER_ITEMS. ORDERS has the Order Quantity measure sold against every order. ORDER_ITEMS has the Order Sale measure for every product sold as part of an order.

ORDER_TOTAL (or Order Quantity) measure is at a higher grain than the UNIT_PRICE (or Sale) measure. A reporting end user would typically be interested in reports containing both the measures like the 2 reports shown below.

As you see, the first report is at the Orders grain requesting ORDER_TOTAL and UNIT_PRICE measure. The second is at ORDER_ITEMS or Products grain requesting both ORDER_TOTAL and UNIT PRICE measure. Both the reports are very valid but require completely different queries.

For example, for the first report we cannot join on ORDER_ID between ORDERS and ORDER_ITEMS as that will bump up the ORDER_TOTAL value completely. But for the second one we can join on the ORDER_ID as we can afford to have the same ORDER_TOTAL value to repeat itself for every product that was bought as part of the order (some people prefer to leave these as nulls which again can be easily done). In normal cases, this would be tricky to achieve as we need to generate completely different queries depending on the grain of the report. But BI Server makes it easier with conforming dimensions and level based measures.

The idea is to model different granular measures as different fact logical tables in the Business Model Layer. In our case, we need 2 different facts, one for ORDER_TOTAL and the other for UNIT_PRICE as shown below

Ensure that the lower granular table is inner joined with the higher granular table for the logical fact table having lower granular measures (ORDER_ITEMS would be inner joined with ORDERS in the logical table source of Facts Sale Grain logical table)

Remember we need to make the BI Server to produce 2 different queries when we are at the Orders grain. In order to do that, make the ORDER_TOTAL measure to be a Total Level Based measure on the Products dimension.

Now, if you generate the first report, you would notice that BI Servers fires 2 different queries as shown below

select D1.c2 as c1,
     D1.c3 as c2,
     D1.c1 as c3
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3
          from
               (select sum(T6775.ORDER_TOTAL) as c1,
                         T6775.ORDER_ID as c2,
                         T6775.ORDER_DATE as c3,
                         ROW_NUMBER() OVER (PARTITION BY T6775.ORDER_ID ORDER BY T6775.ORDER_ID ASC) as c4
                    from
                         ORDERS T6775
                    group by T6775.ORDER_DATE, T6775.ORDER_ID
               ) D1
          where  ( D1.c4 = 1 )
     ) D1
order by c1

select D2.c2 as c1,
     D2.c3 as c2,
     D2.c1 as c3
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3
          from
               (select sum(T6785.UNIT_PRICE) as c1,
                         T6775.ORDER_ID as c2,
                         T6775.ORDER_DATE as c3,
                         ROW_NUMBER() OVER (PARTITION BY T6775.ORDER_ID ORDER BY T6775.ORDER_ID ASC) as c4
                    from
                         ORDERS T6775,
                         ORDER_ITEMS T6785
                    where  ( T6775.ORDER_ID = T6785.ORDER_ID )
                    group by T6775.ORDER_DATE, T6775.ORDER_ID
               ) D1
          where  ( D1.c4 = 1 )
     ) D2
order by c1

After producing both the queries above, BI Server will make an in-memory stitch join to produce the desired results (based on conforming dimensions). Same would be case for the 2nd report as well. But the queries generated would be different.

select D1.c3 as c1,
     D1.c4 as c2,
     D1.c2 as c3,
     D1.c1 as c4
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3,
               D1.c4 as c4
          from
               (select sum(T6785.UNIT_PRICE) as c1,
                         T6798.PRODUCT_NAME as c2,
                         T6775.ORDER_ID as c3,
                         T6775.ORDER_DATE as c4,
                         ROW_NUMBER() OVER (PARTITION BY T6775.ORDER_ID, T6798.PRODUCT_NAME ORDER BY T6775.ORDER_ID ASC, T6798.PRODUCT_NAME ASC) as c5
                    from
                         PRODUCT_INFORMATION T6798,
                         ORDERS T6775,
                         ORDER_ITEMS T6785
                    where  ( T6775.ORDER_ID = T6785.ORDER_ID and T6785.PRODUCT_ID = T6798.PRODUCT_ID )
                    group by T6775.ORDER_DATE, T6775.ORDER_ID, T6798.PRODUCT_NAME
               ) D1
          where  ( D1.c5 = 1 )
     ) D1
order by c1

select D2.c2 as c1,
     D2.c3 as c2,
     D2.c1 as c3
from
     (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3
          from
               (select sum(T6775.ORDER_TOTAL) as c1,
                         T6775.ORDER_ID as c2,
                         T6775.ORDER_DATE as c3,
                         ROW_NUMBER() OVER (PARTITION BY T6775.ORDER_ID ORDER BY T6775.ORDER_ID ASC) as c4
                    from
                         ORDERS T6775
                    group by T6775.ORDER_DATE, T6775.ORDER_ID
               ) D1
          where  ( D1.c4 = 1 )
     ) D2
order by c1

There are also a couple of other approaches that we can use to model such measures. One is done in Answers (which i generally do not recommend since that forces an end user to know them) and then the other is by using a count column in a logical table source. Both can be valuable in some cases. I will try to cover them as well in the coming weeks.