BOBJ - Automatic join and cardinality detection
You have two ways to create a join automatically :
- with the Automated Detection Tools
- on table insertion
Automatic join creation with the Automated Detection Tools
You can use the Designer feature Detect join to automatically detect selected joins in the schema. universe Designer identifies column names across tables in the target database and proposes candidate joins for the tables in your schema. You can then select which, or accept all, proposed joins you want to be created.
How are joins automatically detected?
The joins are detected based on the Joins strategy that appears in the Strategies page of the Parameters dialog box (File > Parameters > Strategies tab).
A strategy is a script file that automatically extracts structural information from the database. There are a number of inbuilt strategies that are shipped with Designer. These are listed in drop-down list boxes on the Strategies
The default automatic join detection strategy detects joins based on matching column names, excluding key information. You can select which join strategy you want to apply when you use automatic join detection.
Using automatic join detection appropriately
Detecting joins automatically is useful to help you quickly create joins in your schema. However, you need to be aware of the limitations of automatic join detection when designing your schema.
Join strategies used to detect candidate joins match column names from the database. There may be instances in the target database when primary, foreign keys, and other join columns do not have the same name across different tables. Designer will not pick up these columns. You should be aware that there may be other joins necessary that have not been detected.
To create a join using automatic detection:
- Verify that the join strategy that you want to use to detect joins is selected in the Joins drop down list box on the Parameters dialog box. You can verify this as follows:
- Select File > Parameters and click the Strategies tab.
- Select the strategy that you want to use to detect joins from the Joins drop-down list box and click OK.
- Select multiple tables in the Structure pane.
You can select multiple tables by pressing SHIFT while clicking each table, or you can select all tables in a zone by clicking in an empty space, and dragging the cursor to define a rectangular zone that includes any number of tables.
- Select Tools > Automated Detection >Detect Joins or Click the Detect Joins button.
The Candidate Joins dialog box appears. It lists candidate or proposed joins for the selected tables. The candidate joins also appear as blue lines between selected tables in the Structure pane.
- Click Insert to create all candidate joins or Select one or more joins and click Insert.
You can select one or more joins by holding down CTRL and clicking individual tables, or holding down SHIFT and clicking the first and last join in a continuous block. The joins are inserted in you schema. 6. Click Close.
Automatic join creation on table insertion
You can choose to insert joins automatically in the schema at the same time as the tables that use the joins are inserted into the structure pane.
Automatic join creation is determined by two processes:
- The active join strategy determines the column information used to detect the join.
- The default creation option Extract Joins With Tables must be selected to allow the automatic creation of joins with their associated tables.
This option is on the Database page of the Options dialog box.
Limitations when inserting joins automatically
Inserting joins automatically into your schema with associated tables is a quick way to get joins into your schema, but it can lead to serious design faults with your schema. The joins are inserted based on the database structure, so columns common to more than one table that have been renamed in the database will not be picked up.
You should not use this technique to create joins in a production universe. Instead, use it for demonstration purposes, or as a quick way to build a universe, in which you will then carefully validate each join after insertion.
To create a join automatically with an associated table:
- Verify that the join strategy that you want to use to detect joins is selected on the Strategies page of the Parameters dialog box.
- Select Tools > Options.
The Options dialog box appears.
- Click the Database tab.
The Database page appears.
- Select the Extract Joins With Tables check box.
- Click OK.
Now when you insert a table that has columns referencing other columns in tables that have already been inserted into the Structure pane, the references between tables are automatically inserted as joins between appropriate tables.