Logical tables are related to each other. How they are related is expressed in logical 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.
The joins between the logical layer and the physical layer will be automatically created 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. In the Business Model and Mapping layer, you should create complex joins with one-to-many relationships and not key or foreign key joins.
You can create logical foreign keys and logical complex 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 complex 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.
CAUTION: It is recommended that you do not have foreign keys for logical tables. However, you can create logical foreign keys and logical complex joins using either the Joins Manager or the Business Model Diagram. A logical key for a fact table must be made up of the key columns that join to the attribute tables.
- Defining Logical Joins with the Joins Manager
- Defining Logical Joins with the Business Model Diagram
Defining Logical Joins with the Joins Manager
You can use the Joins Manager to view logical join relationships and to create logical foreign keys and complex joins.
Creating a Logical Foreign Key
Logical foreign key joins might be needed if the Oracle BI Server is to be used as an ODBC data source for certain third-party query and reporting tools. Typically, you should not create logical foreign keys. This capability is in the Administration Tool to provide compatibility with previous releases.
- In the Administration Tool toolbar, select Manage > Joins.
- Select Action > New > Logical Foreign Key
- In the Browse dialog box, double-click a table.
- Type a name for the foreign key.
- In the Table drop-down list on the left side of the dialog box, 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 drop-down list, and an applicable cardinality. This is for use in 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. For more information about driving tables, refer to Specifying a Driving Table.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 drop-down list.
- To open the Expression Builder, click the button to the right of the Expression pane.
- Click OK to save your work.
Creating a Logical Complex Join
- In the Administration Tool toolbar, select Manage > Joins.
- Select Action > New > Logical Complex Join.
- Type a name for the complex join.
- In the Table drop-down lists on the left and right side of the dialog box, select the tables that the complex join references.
- (Optional) To specify a driving table for the key, select a table from the Driving drop-down list, and an applicable cardinality. This is for use in 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. For more information about driving tables, refer to Specifying a Driving Table.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 drop-down list.
- Click OK.
No comments:
Post a Comment