Oracle BI EE 10.1.3.4.1 – Enforcing and Non-Enforcing Outer Joins

One of the flexibilities of the BI Server is its ability to fine-tune certain sql queries being fired back to the database. One such example is its ability to enforce certain outer joins when required. For example, lets consider a simple example containing the Customer dimension and the Sales fact from the SH schema. Customer dimension is snow-flaked as shown below in the database schema

The most common way of designing the customers dimension in the business model is to create a single logical table called Customers and then bring in the Countries table through an inner join as shown below

As long as it is an inner join, the join would be enforced only when a corresponding column in the country table is chosen. Assume that we have a requirement to outer join the countries to the customer table. The common approach is to convert the inner join to outer join in the logical table source itself as shown below

What this will do is, it will make sure that countries table is always outer joined to the customers table. But there is a major drawback with this approach i.e the right outer join would be enforced even when no column in the countries table is chosen, which might turn out to be an un-necessary performance bottleneck. For example, if you create a report as shown below

which does not include any column form the countries table, you would still notice an outer join to the countries dimension in the actual sql getting fired

select T17879.CUST_TOTAL as c1,
     T17879.CUST_INCOME_LEVEL as c2,
     sum(T17952.AMOUNT_SOLD) as c3
from
     CUSTOMERS T17879 left outer join COUNTRIES T17867
     On T17867.COUNTRY_ID = T17879.COUNTRY_ID,
     SALES T17952
where  ( T17879.CUST_ID = T17952.CUST_ID )
group by T17879.CUST_INCOME_LEVEL, T17879.CUST_TOTAL
order by c1, c2

This is not desired especially when you have a big snow-flaked dimension. To negate this, BI Server supports a snow-flaked business model itself wherein instead of combining Customers and Countries into a single logical table, we would model each one of them as separate logical tables in the business model layer as shown below

And in the complex join between countries and customers, just ensure that they are outer-joined

If you now create the same report again, you would notice that the outer join is not part of the sql unless a column from the customers logical table is included

select T17879.CUST_TOTAL as c1,
     T17879.CUST_INCOME_LEVEL as c2,
     sum(T17952.AMOUNT_SOLD) as c3
from
     CUSTOMERS T17879,
     SALES T17952
where  ( T17879.CUST_ID = T17952.CUST_ID )
group by T17879.CUST_INCOME_LEVEL, T17879.CUST_TOTAL
order by c1, c2

When you include a column from the Countries logical table, the sql would change as shown below

select T17879.CUST_TOTAL as c1,
     T17879.CUST_INCOME_LEVEL as c2,
     T17867.COUNTRY_REGION as c3,
     sum(T17952.AMOUNT_SOLD) as c4
from
     (
          CUSTOMERS T17879 inner join SALES T17952
          On T17879.CUST_ID = T17952.CUST_ID)
          left outer join
          COUNTRIES T17867
          On T17867.COUNTRY_ID = T17879.COUNTRY_ID
group by T17867.COUNTRY_REGION, T17879.CUST_INCOME_LEVEL, T17879.CUST_TOTAL
order by c1, c2, c3

Its very interesting to see how the behavior of BI Server changes with different modeling techniques.