Using Driving table in OBIEE Complex Join

In OBIEE we can join tables present in different Schema and Connection Pool. However there is a possibility of performance issue in such case. Using Driving Table can be solution for that. 

In this blog I am going to demonstrate how we can use Driving Table, and what is the impact of it.

To start with I am using the fact SAMP_REVENUE_F from BISAMPLE schema. 

I am going to join it with SAMP_OFFICE_D in the same schema

And EMP_D which is present in a different schema. 

The concept of Driving table is useful specifically if we are joining 2 tables which are in different schema and one of them has less number of rows.

I am using 2 dimensions one present in the same schema and another in different schema to showcase the difference in join.

Next I have imported all 3 into the physical layer and joined them.

Next I create a BMM and put them into it and create logical fact and dimension tables. 

The Logical / Complex join between Fact - Revenue and Dim - Office is like a usual Logical / Complex join in BMM.

To showcase the difference in query with / without Driving table first I am doing the Complex join between Fact - Revenue and Dim - Employee is like a usual Logical / Complex join in BMM. 

Let me create a simple report to test it out.

In the session log we can see 2 different SQL queries have been fired for 2 different schema which have no connection in between.

Now let me define the Driving Table in the complex join. As in this case EMP_D has less number of rows, I am using it as the Driving Table.

And in the back end SQL query we can find a additional in statement using parameters.

And this makes the difference in performance.