Configurating outer joins using LTS mapping content

About outer join, although it is to be avoided if can be, it can't entirely. There are always needs for the query to return results from both tables even if one side doesn't always match the other side.. Therefore, OBIEE allows us to configure outer joins in BMM layer.

The most simple way of doing it is by changing the complex join setting from "inner" to other types from the drop down. There is another way of doing it. It is by defining the logical table source of the chosen table and map it to multiple physical tables by joining them. Think of it as creating a view that joins multiple physical tables in BMM Layer.

Now, the difference between doing it this way vs changing the complex join to outer join will covered a different time. So right now, I am just going to point out that, by doing it this way, the query will always include an outer join in the 'from' clause. Because what we are doing it by converting a physical table into a logical view that uses outer join across 2 tables..

This may not be desire that, what if I am running a query that only uses 1 column from table A? However, the query will still be fired against the result set of table A outer joins to table B? This can cause performance issues unnecessarily as indicated below:

The query generated when selecting LSP Name, History from LSP table will give the following SQL statement even if nothing from the other table is needed:

select distinct T103846.LSP_NAME as c1,

     T103846.HISTORY as c2

from 

     (

          IS_SMB_LSP_DIM T103846 /* A1_IS_SMB_LSP_DIM */  left outer join IS_SMB_NODE_DIM T106742 /* A2_IS_SMB_NODE_DIM */  On T103846.FROM_NODE_KEY = T106742.NODE_KEY) left outer join IS_SMB_NODE_DIM T106752 /* A3_IS_SMB_NODE_DIM */  On T103846.TO_NODE_KEY = T106752.NODE_KEY

order by c1, c2

So what's the way around that?

Well, to get around that, we need to break up the LTS into 2 different sources. We will keep the first LSP as it as by mapping it to join all of the physical tables as intended. From there, we create an additional LTS called 'LSP'. This LTS, we map all of the columns from the physical tables that it belongs without the columns from the other table. So in other words, all of the columns that comes from LSP table will be mapped accordingly, all of the tables from Node Interface Dimension will be unmapped. So this LTS will be used when users only select columns from LSP tables for their report.

The original LTS will be left as it is as it still outer joins to node interface dimension. However in there, we only map the columns that comes from Node interface dimension, but not the columns from LSP table. So it is the other way around from the 'LSP' LTS mappings:

So the idea is that, when users select only LSP columns into their report, 'LSP' LTS will be used in the query because LSP Columns are only mapped here. And in 'LSP' LTS, there is not outer joins being configured in the mapping. When users select node interface columns, then the original LTS will be used because node interface columns are only mapped here.

So now, let's go back to the answers and run the same report again and see what query it is generated:

As we can see, for the same report, the query generated this time doesn't have an outer join in the 'From' clause:

select distinct T103846.LSP_NAME as c1,

     T103846.HISTORY as c2

from 

     IS_SMB_LSP_DIM T103846 /* A1_IS_SMB_LSP_DIM */ 

order by c1, c2

Now, when I want the outer join to be used, i simply add the columns from node interface dimension into my report and you will see that the query changes:

The query is as the following:

select distinct T103846.LSP_NAME as c1,

     T103846.HISTORY as c2,

     T106742.NODE_NAME as c3  --------- This is the new column from node interface dimension

from 

     (

          IS_SMB_LSP_DIM T103846 /* A1_IS_SMB_LSP_DIM */  left outer join IS_SMB_NODE_DIM T106742 /* A2_IS_SMB_NODE_DIM */  On T103846.FROM_NODE_KEY = T106742.NODE_KEY) left outer join IS_SMB_NODE_DIM T106752 /* A3_IS_SMB_NODE_DIM */  On T103846.TO_NODE_KEY = T106752.NODE_KEY

order by c1, c2, c3

Until next time

Thank you