Derive Foreign Key Joins Wizard

The Derive Foreign Key Joins Wizard is used to determine the joins between entities in a model version when foreign keys are not defined in the database system.
The wizard can be started manually from within a diagram using a toolbar button:

1. Click in the diagram toolbar or select an existing model version and select   Derive relations from the Workflow pane.
2. Click to specify the tables to be included or excluded using SQL wild cards and click Next.
   
3. The tables matching the criteria in the last step are displayed. Move any specific tables not to be included to the Exclude tables list and click on Next.
   
4. Click to specify the columns to be included or excluded as joining columns using SQL wild cards and click Next.
    
5. Select the data types...a column must have in order to be included and click Next.
   
6. Define exactly how columns will be matched up between tables to determine foreign keys. Make appropriate selections and click Next.
   
   

   Note

   Multiple options can be chosen to combine logic in this dialog. The individual options are:

   • Ignore tables that have the same columns - Excludes joining similar tables defined by the % of identical columns
   • Ignore columns that are not flagged as PK - A relation will be created only when one of the two columns being compared is a primary key.
   • The FK column name contains the PK table and column name - The table User with the column ID will join to tables with a column name userid, User_ID, ID_User or iduser
   • The FK column name partially contains the PK table - Column prodId will match the table Product, so tables with a column prodId are joined to the Product table
   • Match columns that have the same name - User and Customer both have a column EmployeeId, therefore there is a relation between the two tables
   • Match columns that have the same names after string manipulation- User and CUstomer have the columns EmployeeId and EmployeeKey, therefore there is a relation between the two tables.Use the edit buttons to add, modify and delete custom criteria:
     
   • I want to specify synonyms - When identical columns are found with multiple Table/Columns. Allow identical columns to become synonyms of one another.
7. The final criteria dialog allows profiling results to be used to limit table joins. For example, joins to a table could be excluded for empty tables based on profiling results. Specify profiling criteria and click Derive to continue.
   
   

   Note

   Profiling must be run for the model version for this option to be specified.

8. If a column for joining exists in three or more tables, there is a possibility of a Cartesian set of joins being created. This dialog will be displayed containing a list of such columns to allow for the table that has the column as its primary key to be specified. For each row, the suggested primary key column is displayed in the Resolution column.
   
9. For each row in the left list, it is possible to change the driving table/column from the options in the list on the right. Click Next.
   

   Note

   You can also exclude columns at this point or allow a set of Cartesian joins to be created.

10. All joins to be added are now listed. Clear the checkbox for any joins you do not want to include and click Finish.

    
    

    Note

    To mark the referenced columns for all the checked joins as primary keys check  Make referenced attributes primary keys .

    
    

11. The model version diagram is now redrawn to include the new joins.
    

    Note

    The settings are remembered, but the defaults can be obtained by selecting the revert button in the bottom left corner of the screen .