Oracle BI EE 10.1.3.4.1 – OLTP Reporting – Switching Tables based on chosen dimensions – Use Case 1

Though BI EE is well known for reporting on Stars, Facts and Dimensions, one of the lesser known or lesser advertised features is its extensive transactional or OLTP modeling capabilities. Steve Devine (our guest author last week) documents his experience well here. Time and again, i find that BI Server’s transactional reporting capabilities is not marketed well. For example, i was going through a classic transactional reporting requirement on Orders and somehow i thought it would make sense to blog about it as it quite clearly demonstrates the advantages of the BI Server in doing transactional reporting. To make it simple for demonstration purposes, i would use the Order Entry schema for illustration.

Consider the data model diagram of OE schema shown below

As you see its a very simple Orders and Order Items transactional schema. Whenever an order is made, an entry is made into the ORDERS table and then the order items go into the ORDER_ITEMS table. Lets consider 2 different use cases.

1. ORDERS table contains the Order ID & Order Quantity. ORDER_ITEMS table contains Order Quantity per product, the unit price per ordered product and the the Order ID

2. ORDERS table contains the Order Quantity and ORDER_ITEMS table contains just the unit price of individual products sold.

Use Case 1: So, in the first case we have Order Quantity coming from 2 different tables. One is from the ORDERS level table which gives the value at an Order ID level. The second is from the ORDER_ITEMS table which gives the value at the Product level. So whenever an end user creates a report containing any one of the Customer, Promotion dimensions and the Order Quantity measure the ORDERS table quantity measure would have to be used. But as soon as Product dimension is chosen the quantity measure should automatically switch to the ORDER_ITEMS table.

To model this, we would have to first identify the dimensions. In our case, it would be Product, Customer and Promotion. Now create a Fact logical table. This fact table would contain 2 measures. One is Order Quantity and Unit Price. Set the aggregation of both these measures to sum. Remember that Order Quantity measure needs to switch the tables depending on the dimension used in analysis. In effect we have measures at 2 grains here.

1. Orders grain – A logical fact table source would have to be created to cater to this grain. This table source will contain just the ORDERS table. It will contain mapping only to the Order Quantity measure.

2. Order Items grain – Another logical table source will be created to cater to reporting at this grain. This will contain ORDER_ITEMS table inner joined with ORDERS table. The mapping to both the measures would be from the ORDER_ITEMS table.

The ordering of the fact logical table sources is important. This will always ensure that whenever just customer, Promotion and Order quantity is chosen, it will go against the ORDERS table alone. But as soon as the Products dimension is chosen the Order Quantity will automatically switch to the ORDER_ITEMS table.

So for the reports below the queries would be as shown

select T6753.GENDER as c1,
     sum(T6775.ORDER_TOTAL) as c2
from
     CUSTOMERS T6753,
     ORDERS T6775
where  ( T6753.CUSTOMER_ID = T6775.CUSTOMER_ID )
group by T6753.GENDER
order by c1

select T6753.GENDER as c1,
     T6798.PRODUCT_STATUS as c2,
     sum(T6785.QUANTITY) as c3
from
     PRODUCT_INFORMATION T6798,
     CUSTOMERS T6753,
     ORDERS T6775,
     ORDER_ITEMS T6785
where  ( T6753.CUSTOMER_ID = T6775.CUSTOMER_ID
and T6775.ORDER_ID = T6785.ORDER_ID
and T6785.PRODUCT_ID = T6798.PRODUCT_ID )
group by T6753.GENDER, T6798.PRODUCT_STATUS
order by c1, c2

This is a very commonly used method wherein we are forcing a fact table selection based on the dimension that is getting chosen. I will follow this up with a solution for use case 2.

As a side note, if you have any such modeling questions especially when you are dealing with OLTP tables, feel free to leave them as comments here. I will try to cover them as blog posts in future.