Model Outer Joins

Use this information to model outer joins.

• Queries that use outer joins are usually slower. To avoid performance issues, define outer joins only when necessary. Where possible, use ETL techniques to eliminate the need for outer joins in the reporting SQL.

• Outer joins are always defined in the logical layer. Physical layer joins don’t specify inner or outer.

• You can define outer joins by using logical table joins, or in logical table sources. Which type of outer join you use is determined by whether the physical join maps to a business model join, or to a logical table source join.

• If you must define an outer join, try to create two separate dimensions: one that uses the outer join and one that doesn’t. Make sure to name the dimension with the outer join in a way that clearly identifies it, so that client users can use it as little as possible.

• Avoid using more than one outer join. Instead, to achieve the same effect as a logical outer join, Oracle recommends that the logical join be an inner join and that the analysis designer at design time selects the Include Null Value option in the corresponding analysis.