Defining Logical Joins

Relationships between logical tables are expressed by logical joins.

Logical joins are conceptual, rather than physical, joins. In other words, they do not join to particular 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 is 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 Administration Tool considers a table to be a logical fact table if it is 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 do not 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.

However, you will probably have to create some logical joins in the Business Model and Mapping layer, because you will rarely 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 cannot specify expressions or columns on which to create the join. The existence of a join in the Physical layer does not require a matching join in the Business Model and Mapping layer.

Note:

It is recommended that you do not 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.

A logical key for a fact table must be made up of the key columns that join to the attribute tables. Logical foreign key joins may be needed if the Oracle BI Server is to be used as an ODBC data source for certain third-party query and reporting tools.

This section contains the following topics:

Defining Logical Joins with the Business Model Diagram

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

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

In the Administration Tool, right-click a business model and select Business Model Diagram, then select Whole Diagram.
Click the New Join button on the Administration Tool toolbar.
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.
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.
(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.
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.

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

Defining 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 is not recommended.

This section contains the following topics:

Creating Logical Joins with the Joins Manager

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

See Specifying a Driving Table.

In the Administration Tool, select Manage, then select Joins.
In the Joins Manager, select Action > New > 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.

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

Creating 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 should not create logical foreign keys. See Specifying a Driving Table.

In the 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 > New > Logical Foreign Key.
In the Browse dialog, double-click a table to display 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.

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, that is, less than 1000 rows. Choosing a driving table incorrectly can lead to severe performance degradation.
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.
Enter an expression for the join, or click the Expression Builder button to define the expression in Expression Builder.
Click OK to save your work.

Specifying 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 is being joined.

Caution:

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 it if the query plan determines that its use will 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.

Caution:

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.

In general, driving tables can be used 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 are not used for full outer joins. See Defining 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. In general, the larger its value, the fewer parameterized queries need to be 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 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.

Identifying 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 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.