OBIEE 11g: Outer join in the Physical Layer now possible

Tweet

We know that we can perform outer joins in OBIEE in the Logical Layer. The problem with this mechanism is that it is not possible for one logical table sources (LTS) to join to a fact by an inner join and the other to join to the fact by an outer join. Let us try to solve this problem here.


While my original requirement was complicated, i have tried to simplify it in the following use case.

Let's say that we had an order_fact and a customer_dim with the following table structures.

CREATE TABLE order_fact (
 customer_wid number
 ,order_quantity number
 );

CREATE TABLE customer_dim (
 row_wid number
 ,customer_name varchar2(50)
 ,contract_number number
 );

Let's say that our requirement is to list all the customers with contracts if the user picked the contract_number in the analysis (read Left outer join) and customers with order_quantity if the user did NOT pick the contract_number (read inner join).

Before i begin the implementation of this use case, let me first insert some data in these tables.

insert into order_fact (customer_wid,order_quantity) values (1,500); 

insert into order_fact (customer_wid,order_quantity) values (2,50); 

insert into order_fact (customer_wid,order_quantity) values (3,1000); 

insert into order_fact (customer_wid,order_quantity) values (3,700); 

insert into order_fact (customer_wid,order_quantity) values (5,80); 

insert into customer_dim (row_wid,customer_name,contract_number) values (1,'Tom',234132); 

insert into customer_dim (row_wid,customer_name,contract_number) values (2,'Harry',454520); 

insert into customer_dim (row_wid,customer_name,contract_number) values (3,'Susan',745653); 

insert into customer_dim (row_wid,customer_name,contract_number) values (4,'Steve',342534); 

insert into customer_dim (row_wid,customer_name,contract_number) values (5,'Jim',965467); 

insert into customer_dim (row_wid,customer_name,contract_number) values (6,'Larry',null);

So we expect to see Tom, Harry, Susan, Steve and Jim when the user selects the contract number because these customers have contract numbers. Note that Larry does not have a contract number.

We expect to see Tom, Harry, Susan and Jim when the user does not select the contract number because these are the only customers which have order quantity against them (compare the customer_wid and the row_wid).

Let us now see the implementation of this use case.

1. This step is the most important step. Bring the tables in the physical layer. Create a new column in the physical  layer in order_fact and name it customer_wid(+). Keep the datatype of the new column the same as that of customer_wid. Note that this column does not exist in the database.

2.  Create 1 alias of the fact and 2 aliases of the dimension.

3.  Join these 3 aliases in the following way.

The following are the 2 joins.

"DW".""."D_DW"."Dim_Customers_Without_Contracts"."ROW_WID" = "DW".""."D_DW"."Fact_Orders"."CUSTOMER_WID"

"DW".""."D_DW"."Dim_Customers_With_Contracts"."ROW_WID" = "DW".""."D_DW"."Fact_Orders"."CUSTOMER_WID(+)"

Note that one joins on customer_wid and the other joins on customer_wid(+).

4. Drag Dim_Customers_With_Contracts and Dim_Customers_Without_Contracts as 2 LTS of a logical table and order Dim_Customers_Without_Contracts to be 1st LTS.

5. Unmap the Contract number column from the Dim_Customers_Without_Contracts LTS.

6. Add "DW".""."D_DW"."Dim_Customers_With_Contracts"."CONTRACT_NUMBER" is not null as the content level filter in Dim_Customers_With_Contracts LTS.

7. Drag the fact table alias into another Logical table. Set the aggregation rule as Sum on the Order Quantity column and join the 2 Logical tables using an inner join.

8. Rename the columns to InitCap (optional) and move the tables to the presentation table.

9. Deploy the RPD and select the Customer name and Order quantity. You will get the following result.

The SQL for this analysis is shared below.

WITH 
SAWITH0 AS (select sum(T20.ORDER_QUANTITY) as c1,
     T24.CUSTOMER_NAME as c2
from 
     CUSTOMER_DIM T24 /* Dim_Customers_Without_Contracts */ ,
     ORDER_FACT T20 /* Fact_Orders */ 
where  ( T20.CUSTOMER_WID = T24.ROW_WID ) 
group by T24.CUSTOMER_NAME)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 65001

10. Now add the contract number to this analysis. The result changes to the following.

The SQL for this analysis is shared below.

WITH 
SAWITH0 AS (select sum(T20.ORDER_QUANTITY) as c1,
     T28.CONTRACT_NUMBER as c2,
     T28.CUSTOMER_NAME as c3
from 
     CUSTOMER_DIM T28 /* Dim_Customers_With_Contracts */ ,
     ORDER_FACT T20 /* Fact_Orders */ 
where  ( T20.CUSTOMER_WID(+) = T28.ROW_WID and T28.CONTRACT_NUMBER is not null ) 
group by T28.CONTRACT_NUMBER, T28.CUSTOMER_NAME)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select distinct 0 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c1 as c4
from 
     SAWITH0 D1
order by c3, c2 ) D1 where rownum <= 65001

Logic:

customer_wid(+) does not physcially exist in the database. However, we know that in SQL, an outer join is denoted with a (+) next to the column name in the where clause. We leverage this feature of SQL and create a column with a (+) in the physical layer of the RPD and then perform a regular equi join in the RPD.

OBIEE considers customer_wid(+) as just another column and generates a physical equi join with it but because of the (+), oracle database considers this to be an outer join and returns the data set accordingly which finally gets displayed on the analysis.
The physical join we are talking about is T20.CUSTOMER_WID(+) = T28.ROW_WID in the above query.

We unmap the contract_number column from Dim_Customers_Without_Contracts so that OBIEE has to switch to Dim_Customers_With_Contracts LTS when contract_number is pulled in the analysis.

We order Dim_Customers_Without_Contracts as the 1st LTS so that OBIEE uses Dim_Customers_Without_Contracts (hence equi join and not outer join) when the user does NOT pull the contract_number in the analysis.

We put a content level filter on Dim_Customers_With_Contracts LTS of "DW".""."D_DW"."Dim_Customers_With_Contracts"."CONTRACT_NUMBER" is not null is not null so that OBIEE only display the customers with contracts and not all customers when the user pulls contract_number in the analysis.

Till next time ..