Defining Physical Joins in the Physical Diagram

You can define foreign keys and complex joins between tables, whether or not the tables are in the same database. If you click the Physical diagram icon on the toolbar, the Physical Table Diagram window opens and only the selected objects appear. If you right-click a physical object, several options are available. For more information about these options, refer to Table 15.

To display the Physical Table Diagram

1. In the Administration Tool, in the Physical layer, right-click a table and choose Physical Diagram.
2. In the shortcut menu, choose an option from Table 15.
3. To add another table to the Physical Table Diagram window, perform the following steps:
   1. Leave the Physical Table Diagram window open.
   2. Right-click to select a table you want to add and choose one of the Physical Diagram options described in Table 15.
      Repeat this process until all the tables you need appear in the Physical Table Diagram window.

Table 15. Physical Diagram Shortcut Menu Options

Physical Diagram Menu	Description
Object(s) and all joins	Displays the selected objects, as well as each object that is related directly or indirectly to the selected object through some join path. If all the objects in a schema are related, then using this option diagrams every table, even if you only select one table.
Object(s) and direct joins	Displays the selected objects and any tables that join to the objects that you select.
Object(s) and direct joins in the business model	Option Not available at this time.
Selected object(s) only	Displays the selected objects only. Joins display only if they exist between the objects that you select.

To define a foreign key join or a complex join

1. In the Physical layer of the Administration Tool, select one or more tables and execute one of the Physical Diagram commands from the right-click menu.
2. Click one of the following icons on the Administration Tool toolbar:
   • New foreign key
   • New complex join
3. With this icon selected, in the Physical Table Diagram window, left-click the first table in the join (the table representing one 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 many in the one-to-many join), and then left-click the second table to select it.
   The Physical Foreign Key or Physical Join dialog box appears.
5. Select the joining columns from the left and the right tables.
   The SQL join conditions appear in the expression pane of the window.
   NOTE:  The driving table is shown on this window, but it is not available for selection because the Oracle BI Server implements driving tables only in the Business Model and Mapping layer. For more information about driving tables, refer to Specifying a Driving Table.
6. If appropriate, specify a database hint.
   For more information, refer to Using Database Hints.
7. To open the Expression Builder, click the button to the right of the Expression pane.
   The expression displays in the Expression pane.
8. Click OK to apply the selections