Modeling Outer Join for OBIEE 11g

A join is a query that combines rows from two or more tables, views, or materialized views. Database performs a join whenever multiple tables appear in the FROM clause of the query.

Oracle 11g database supports following types of joins

·    Equijoin – is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

·    Self Join - is a join of the table to itself. This table appears twice in the FROM clause and is followed by table aliases that quality column names in the join condition.

·    Cartesian product – Combines each row of one table with each row of the other to return records when two tables do not have a join condition. It is useful when there is specific need to have a Cartesian product.

·    Inner Join  ( Simple Join)- is a join of two or more tables that returns only those rows that satisfy the join condition

·    Antijoins – returns rows for the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the sub query on the right side.

·    Semijoin – returns rows that match an EXISTS sub query without duplicating rows from the left side of predicate when multiple rows on the right side satisfy the criteria of the sub query.

·    Outer Join – extends the result of simple join. A outer join returns all rows that satisfy the join condition and also returns some or all those rows from one table of which no rows for the other satisfy the join condition. The outer join may be left outer, right outer or full outer join based on user’s requirement.

In my view the most fascinating join is terms of modeling for OBIEE 11g perspective is the ‘Outer Join’. This blog is an attempt to explain various types of outer join and how to model the outer join in OBIEE 10g/11g and compare the options in details in terms of performance, usability and complexity etc.

Outer Join

There are three different types of outer joins database supports  

·    To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

·    To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

·      To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

 

The outer joins can be used to fill gaps in sparse data; this outer join is called as partitioned outer join and formed using the query_partition_clause of the join_clause syntax.

 

Sparse data is data that does not have rows for all possible values of a dimension such as time or department. For example, tables of sales data typically do not have rows for products that had no sales on a given date. Filling data gaps is useful in situations where data sparsity complicates analytic computation or where some data might be missed if the sparse data is queried directly. The process of converting spare data into dense form is called as Data Densification.

 

There are three different options to implement the Outer Joins for OBIEE 10g/11g

A.      Physical Opaque Views

B.      Logical Modeling – Between Logical tables and Logical Table Sources

C.      Preserve Dimension 

 

Physical Opaque Views

 

Opaque Views in physical layer of OBIEE 10g/11g facilities creation of table by using SELECT clause. The simple way to implement Outer Join is to include the outer join within the SELECT statement. For Oracle database the portioned join operator provides an easy, elegant and efficient option  

SELECT V2.pid, V2.tid, SUM(SALES) OVER

(PARTITION BY V2.PID,V2.YEAR ORDER BY V2.TID) YTD sales

FROM SALES PARTITION BY(PID) V1 RIGHT OUTER JOIN

TIMES V2 ON (V1.TID=V2.TID)

 

Logical Modeling - Setting up Outer Join Between Logical Tables and Logical Table Sources

 

 

Preserve Dimension 
 

Both options discussed in earlier section have following disadvantages when using in conjunction with OBIEE 11g reporting

·    Outer-Joins perform slower than inner joins for the relational sources; the multidimensional databases (Essbase) do not support Outer Join.

·    Outer Joins often will return NULL values for certain columns, and in the BI Dashboards/Reports does not provide drill-down on NULL values.

·    Additional coding is required to tackle NULL values e.g. IFNULL (TYPE, ‘Y’)

·    The SQL queries being generated by OBIEE are complex and may not always work as expected.

 

There are two options to model to Preserve Dimension Values to implement the outer join for OBIEE 10g /11g requests.

 

Option A – Dashboard Only – Applicable to single dimension, the steps are as follows

• Union all query: second query adds all dimension values with measure values of 0
• Explicit aggregation sum rule to merge duplicates

 

 

 

Option B– By adding measure to preserve dimension values, it is applicable to one or more dimension
 

Preserve Months, Preserve Year and Preserve Company

Separate logical table source for each preservation measure to trigger fact-based partitioning

outer joins (see attached screen shot); each LTS maps just that preservation column

     Level-based at the All level except for dimensions to be preserved (see screen shot)

 

 

 

Apply the hidden column format as the system-wide default for these preservation
measures

 

 

       Selectively add preservation columns to reports