Define Logical Joins

Relationships between logical tables are expressed by logical joins.

Logical joins are conceptual, rather than physical, joins. Logical joins don't join to specific keys or columns. A single logical join can correspond to many possible physical joins.

A key property of a logical join is cardinality. Cardinality expresses how rows in one table are related to rows in the table to which it's joined. A one-to-many cardinality means that for every row in the first logical dimension table, there are 0, 1, or many rows in the second logical table. The Model Administration Tool considers a table to be a logical fact table if it's at the Many end of all logical joins that connect it to other logical tables.

Specifying the logical table joins is required so that the Oracle BI Server can have the necessary metadata to translate a logical request against the business model to SQL queries against the physical data sources. The logical join information provides the Oracle BI Server with the many‐to‐one relationships between the logical tables. This logical join information is used when the Oracle BI Server generates queries against the underlying databases.

You don't need to create logical joins in the Business Model and Mapping layer if both of the following statements are true:

You create the logical tables by simultaneously dragging and dropping all required physical tables to the Business Model and Mapping layer.
The logical joins are the same as the joins in the Physical layer.

You might need to create some logical joins in the Business Model and Mapping layer, because you can't drag and drop all physical tables simultaneously except in very simple models.

You can create logical joins using either the Joins Manager or the Business Model Diagram. When you create a complex join in the Physical layer, you can specify expressions and the specific columns on which to create the join. When you create a logical join in the Business Model and Mapping layer, you can't specify expressions or columns on which to create the join. The existence of a join in the Physical layer doesn't require a matching join in the Business Model and Mapping layer.

It's recommended that you don't have foreign keys for logical tables. However, for backward compatibility, you can create logical foreign key joins using the Joins Manager if you select Allow logical foreign key join creation in the Options dialog. Compose a logical key for a fact table using the key columns that join to the attribute tables. You might need logical foreign key joins if the Oracle BI Server is used as an ODBC data source for certain third-party query and reporting tools.

This section contains the following topics:

Define Logical Joins with the Business Model Diagram

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

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 join_new_2.gif

You can use the Business Model Diagram to define logical joins between tables. See Specify a Driving Table.

This driving table 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. Don't select a driving table unless multi-database joins are going to occur.

Important:

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.

In the Model Administration Tool, right-click a business model and select Business Model Diagram, then select Whole Diagram.
From the Diagram menu, click the New Join button on the Model Administration Tool toolbar.
In the Business Model Diagram, click to select the first table in the join, the table representing many in the one‐to‐many join.
Move the cursor to the table to use for the join, the table representing one in the one‐to‐many join, and then click the second table to select it.
Optional: To specify a driving table for the key, select a table from the Driving table list, and an applicable cardinality.
Select the join type from the Type list, or keep the default value.
Set the Cardinality for each side of the join, or keep the default values.
Click OK.

Define Logical Joins with the Joins Manager

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 isn't recommended.

This section contains the following topics:

Create Logical Joins with the Joins Manager

Logical joins are recommended over logical foreign key joins in the Business Model and Mapping layer.

Use the driving option 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. Don't select a driving table unless multi-database joins are going to occur.

Note:

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, that is, less than 1000 rows. Choosing a driving table incorrectly can lead to severe performance degradation.

See Specify a Driving Table.

In the Model Administration Tool, select Manage, then select Joins.
In the Joins Manager, select Action, select New , and then select Logical Join.
In the Logical Join dialog, type a name for the logical join.
In the Table lists on the left and right side of the dialog, select the tables that the logical join references.
Optional: To specify a driving table for the key, select a table from the Driving list, and an applicable cardinality.
Select the join type from the Type list, or keep the default value.
Set the Cardinality for each side of the join, or keep the default values.
Click OK.

Create Logical Foreign Key Joins with the Joins Manager

You might need logical foreign key joins if you plan to use the Oracle BI Server as an ODBC data source for certain third-party query and reporting tools.

You shouldn't create logical foreign keys. See Specify a Driving Table.

The driving table 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. Don't select a driving table unless multi-database joins are going to occur.

Important:

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, that is, less than 1000 rows. Choosing a driving table incorrectly can lead to severe performance degradation.

In the Model Administration Tool, select Tools, then select Options.
In the General tab of the Options dialog, select Allow logical foreign key join creation.
Click OK.
Select Manage, then select Joins to display the Joins Manager.
Select Action, select New, and then select Logical Foreign Key.
In the Browse dialog, double-click a table.
In the Logical Foreign Key dialog, type a name for the foreign key.
In the Table list on the left side of the dialog, select the table that the foreign key references.
Select the columns in the left table that the foreign key references.
Select the columns in the right table that make up the foreign key columns.
(Optional) To specify a driving table for the key, select a table from the Driving list, and an applicable cardinality.
Select the join type from the Type list, or keep the default value.
Set the Cardinality for each side of the join, or keep the default values.
In Expression Builder, type an expression for the join.
Click OK to save your work.

Specify a Driving Table

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.

Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it's being joined.

Important:

To avoid problems, only specify driving tables when the driving table is extremely small - less than 1000 rows.

You can specify a driving table for logical joins from the Logical Joins window. When you specify a driving table, the Oracle BI Server uses the driving table if the query plan determines that the table’s use can optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows. The other tables, including other driving tables, are then joined together.

Important:

If large numbers of rows are being selected from the driving table, specifying a driving table could lead to significant performance degradation or, if the MAX_QUERIES_PER_DRIVE_JOIN limit is exceeded, the query terminates.

Use driving tables with inner joins, and for outer joins when the driving table is the left table for a left outer join, or the right table for a right outer join. Driving tables aren't used for full outer joins. See Define Logical Joins for instructions on specifying a driving table.

There are two entries in the database features table that control and tune driving table performance.

MAX_PARAMETERS_PER_DRIVE_JOIN

This is a performance tuning parameter. The larger its value, the fewer parameterized queries are generated. Values that are too large can result in parameterized queries that fail due to back-end database limitations. Setting the value to 0 (zero) turns off drive table joins.
MAX_QUERIES_PER_DRIVE_JOIN

This is used to prevent runaway drive table joins. If the number of parameterized queries exceeds its value, the query is terminated and an error message is returned to the user.

Factors That Determine Join Trimming

When determining which joins Oracle BI Server can trim 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, meeting the following criteria is required.

No references to the trimmed table can exists anywhere in the query such as in the projected list of columns or in the WHERE clause.
The trimmed table mustn't 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 doesn't 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 isn't 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 trim is on the many side of a join, in other words, the detail table is never trimmed in a master-detail relationship
- The table to trim has a 0..1 cardinality and the join is an inner join. 0..1 cardinality implies that a possible matching row in the table. A join with 0..1 cardinality on one side is effectively like a filter. Oracle BI Server can't trim the table without changing the number of rows selected.
- The table to trim is on the left side of a left outer join or on the right side of a right outer join, 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 doesn't affect the number of rows returned from the null-supplying table. In the special case of distinct queries on attributes, you can trim the row-preserving table from an outer join.

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

Scenario	Result
Employee INNER JOIN Department	Oracle BI Server can trim Department because it's on the one side of an inner join. Oracle BI Server can't trim Employee because it's on the many side of an inner join.
Employee LEFT OUTER JOIN Department	Oracle BI Server can trim Department because it's on the one side of the join and it's on the right side of a LEFT OUTER JOIN, the null supplying table. Oracle BI Server can't trim Employee because it's on the many side, and because it's on the left side of a LEFT OUTER JOIN, the row preserving table.
Employee RIGHT OUTER JOIN Department	Oracle BI Server can't trim Department because it's on the right side of a RIGHT OUTER JOIN, the row preserving table. Oracle BI Server can't trim Employee because it's on the many side of the join.
Employee INNER JOIN EmployeeInfo	Oracle BI Server can trim either side because both tables are on the one side of an inner join.
Employee LEFT OUTER JOIN EmployeeInfo	Oracle BI Server can trim EmployeeInfo since it's on the one side of the join, and it's on the right side of a LEFT OUTER JOIN, the null supplying table. Oracle BI Server can't trim Employee because it's on the left side of a LEFT OUTER JOIN, the row preserving table.
Employee RIGHT OUTER JOIN EmployeeInfo	Oracle BI Server can trim EmployeeInfo because it's on the right side of a RIGHT OUTER JOIN, the row preserving table. You can trim Employee because it's on the "one" side of the join, and it's on the left side of a RIGHT OUTER JOIN, the null supplying table.
Employee INNER JOIN Department	Oracle BI Server can trim Department because it's on the 0..1 side of an inner join. Oracle BI Server can trim Employee because it's on the many side of an inner join.
Employee LEFT OUTER JOIN Department	Oracle BI Server can trim Department because it's on the 0..1 side of an outer join, and it's on the right side of a LEFT OUTER JOIN, the null supplying table. The Oracle BI Server allows trimming the null supplying table on the 0..1 side of an outer join, because in this case, trimming Department from the query wouldn't change the number of rows selected from the Employee table. Oracle BI Server can trim Employee since it's on the many side of an outer join.
Employee FULL OUTER JOIN Department	Oracle BI Server can't trim either side because the join is a FULL OUTER JOIN.
Employee MANY TO MANY Project	Oracle BI Server can't trim either side because the join is many-to-many.
Employee UNKNOWN Department	Oracle BI Server can't trim either side because the join has unknown cardinality.

Identify Physical Tables That Map to Logical Objects

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

One of the joins options, Object(s) and Direct Joins within Business Model, is unique to the logical layer. It creates a physical diagram of the tables that meet both of the following conditions:

Tables in the selected objects and tables that join directly
Tables that are mapped, exist in logical table sources in the business model, in the business model

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