Defining Logical Joins

The Business Model Diagram shows logical tables and any defined joins between them.

1. In the Administration Tool, right-click a business model and select Business Model Diagram, then select Whole Diagram.
2. Click the New Join button on the Administration Tool toolbar.
3. In the Business Model Diagram, left-click the first table in the join, the table representing many in the one‐to‐many join, to select it.
4. Move the cursor to the table to which you want to join, the table representing one in the one‐to‐many join, and then left-click the second table to select it.
5. (Optional) To specify a driving table for the key, select a table from the Driving table list, and an applicable cardinality.

   This option is useful for optimizing the manner in which the Oracle BI Server processes multi-database inner joins when one table is very small and the other table is very large. Do not select a driving table unless multi-database joins are going to occur.

   Caution:

   Use extreme caution in deciding whether to specify a driving table. Driving tables are used for query optimization only under rare circumstances and when the driving table is extremely small (fewer than 1000 rows). Choosing a driving table incorrectly can lead to severe performance degradation.

6. Select the join type from the Type list, or keep the default value.
7. Set the Cardinality for each side of the join, or keep the default values.
8. Click OK.

   In the Business Model Diagram, the join is represented by a line between the two selected tables, with an arrow at the one end of the join. The image shows a join in the Business Model Diagram.

   
   Description of the illustration GUID-3166F8C9-DD81-469A-B3E6-251553024226-default.gif

You can use the Joins Manager to view logical join relationships and to create logical joins.

You can also use the Joins Manager to create logical foreign key joins if you select Allow logical foreign key join creation in the Options dialog, although this is not recommended.

This section contains the following topics:

• Creating Logical Joins with the Joins Manager

• Creating Logical Foreign Key Joins with the Joins Manager

Driving tables are useful for optimizing how the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large.

When determining which joins can be trimmed from a physical query, the Oracle BI Server considers the factors described in this section.

The following join trimming rules are enforced for tables within a logical table source:

• Join Outerness (Inner, Left Outer, Right Outer, or Full Outer).

• Join Cardinality ({0..1, 1, N, Unknown} to {0..1, 1, N, Unknown}; for example, 0..1 to N represents a zero or one to many join). There are nine join cardinality combinations excluding those with Unknown cardinality on at least one side of the join.

• Whether the logical table source contains a WHERE clause filter.

• Whether the physical join is a complex join or a foreign key join.

For the Oracle BI Server to trim a join, the following criteria must be met.

• The trimmed table must not be referenced anywhere in the query, such as in the projected list of columns or in the WHERE clause.

• The trimmed table must not cause the cardinality of the result set to change. If removing a join could potentially change the number of rows selected, then the Oracle BI Server does not trim it.

  A join is considered to have the potential to change the number of rows in the result set if any of the following conditions are true. If any of these conditions are true, then the join will not be trimmed from the query:

  • The join is a full outer join, only inner joins, left outer joins, and right outer joins are candidates for trimming

  • The join cardinality is unknown on either side

  • The table to be trimmed is on the many side of a join, in other words, the detail table can never be trimmed in a master-detail relationship

  • The table to be trimmed has a 0..1 cardinality and the join is an inner join. 0..1 cardinality implies that there might or might not be a matching row in the table. So, a join with 0..1 cardinality on one side is effectively like a filter. Therefore, it cannot be trimmed without changing the number of rows selected.

  • The table to be trimmed is on the left side of a left outer join or on the right side of a right outer join (in other words, the row-preserving table is never trimmed). There is an exception to this rule for queries that select only attributes in which a DISTINCT clause is added to the query. Because of the DISTINCT clause, trimming the row-preserving table does not affect the number of rows returned from the null-supplying table. So, in the special case of distinct queries on attributes, the row-preserving table from an outer join can be trimmed.

The following table provides examples of when the Oracle BI Server can trim joins from the query.

Scenario	Result
Employee INNER JOIN Department	Department can be trimmed because it is on the "one" side of an inner join. Employee cannot be trimmed because it is on the many side of an inner join.
Employee LEFT OUTER JOIN Department	Department can be trimmed because it is on the "one" side of the join and it is on the right side of a LEFT OUTER JOIN (in other words, the null supplying table). Employee cannot be trimmed because it is on the many side, and because it is on the left side of a LEFT OUTER JOIN (in other words, the row preserving table).
Employee RIGHT OUTER JOIN Department	Department cannot be trimmed because it is on the right side of a RIGHT OUTER JOIN (in other words, the row preserving table). Employee cannot be trimmed because it is on the many side of the join.
Employee INNER JOIN EmployeeInfo	Either side can be trimmed because both tables are on the "one" side of an inner join.
Employee LEFT OUTER JOIN EmployeeInfo	EmployeeInfo can be trimmed since it is on the "one" side of the join, and it is on the right side of a LEFT OUTER JOIN (in other words, the null supplying table). Employee cannot be trimmed because it is on the left side of a LEFT OUTER JOIN (in other words, the row preserving table).
Employee RIGHT OUTER JOIN EmployeeInfo	EmployeeInfo cannot be trimmed because it is on the right side of a RIGHT OUTER JOIN (in other words, the row preserving table.) Employee can be trimmed because it is on the "one" side of the join, and it is on the left side of a RIGHT OUTER JOIN (in other words, the null supplying table).
Employee INNER JOIN Department	Department cannot be trimmed because it is on the 0..1 side of an inner join. Employee cannot be trimmed because it is on the many side of an inner join.
Employee LEFT OUTER JOIN Department	Department can be trimmed because it is on the 0..1 side of an outer join, and it is on the right side of a LEFT OUTER JOIN (in other words, the null supplying table). The Oracle BI Server allows the null supplying table on the 0..1 side of an outer join to be trimmed, because in this case, trimming Department from the query would not change the number of rows selected from the Employee table. Employee cannot be trimmed since it is on the many side of an outer join.
Employee FULL OUTER JOIN Department	Neither side can be trimmed because the join is a FULL OUTER JOIN.
Employee MANY TO MANY Project	Neither side can be trimmed because the join is many to many.
Employee UNKNOWN Department	Neither side can be trimmed because the join has unknown cardinality.

The Physical Diagram shows the physical tables that map to the selected logical object and the physical joins between each table.

1. In the Administration Tool Business Model and Mapping layer, right-click a business model, logical table, or logical table source.
2. Select Physical Diagram and then one of the joins options.
3. Click and drag any object to more clearly view the relationship lines such as one-to-many.