05. Creating Relationships Between Business Tables

Once you have all of your business tables created, you will need to create relationships between the tables, so that the query generators and SQL generators that work with Pentaho metadata can create the data queries correctly.

This is very much like drawing a relational diagram to show primary and foreign key relationships. Although relational links are not the only relationships that can be modeled. You can create a relationship between any two tables, link any two columns between them and dictate what the relationship is (one to many, many to many , etc.).

So the important pieces of information to know before you try to create a relationship is:

1. what two business tables would you like to associate with this relationship,
2. what columns in the business tables identify the relationship,
3. and what kind of relationship is it - one to one, one to many, many to one, etc.

Tree Navigator: Creating a New Relationship

To create a new relationship between business tables using the Tree Navigator, first make sure that the model you want to add this relationship to is selected, and the Relationships node is visible (lives under the business model name node).

1. Right-click (or ALT-click) on the Relationships branch in the Navigator Tree.
2. Select the New Relationship... option from the popup menu.
3. The *Relationship Properties" dialog displays.
   
   
   
4. Select from the From Table / Field list the business table that you would like to start the relationship from.
5. Select from the To Table / Field list the business table that you would like the relationship to go to.
6. You must also specify the business columns (from the adjacent lists) from each business table that identify this relationship. An alternative, if the business column names are similar, is to click the Guess Matching Fields button, and let the dialog attempt to determine the columns for you.
7. Next step, define the relationship from the Relationship drop down list.
8. If the relationship requires a complex join, select the complex join checkbox, and enter a formula in the text box provided.
9. Click OK to close the dialog.
10. You should see a new relationship line drawn between the two tables on the Editor Graph, and the relationship represented in the tree.
    

    A Special Note on Complex Joins
    Note that complex joins appear in the WHERE clause of the SQL statement, so currently any joining that takes place in the FROM clause of the SQL statement is not supported.  An example of a complex join might be AND([BIZ_TABLE_A.BIZ_COL_A]=[BIZ_TABLE_B.BIZ_COL_A];[BIZ_TABLE_A.BIZ_COL_B]=[BIZ_TABLE_B.BIZ_COL_B]).  This represents a join of two tables based on two key columns vs. a single join column.
    Also note, the complex join expression provided must utilize the names of the business tables and business columns, not phyiscal tables and phyiscal column names.

Editor Graph: Creating a New Relationship

In the Editor Graph, creating a new relationship is simplified a bit, because you select the two business tables on the canvas, and the Relationship Properties dialog is pre-populated with your selections.

To begin, make sure that the model you want to add this relationship to is selected, and the business tables are displayed in the Editor Graph.

1. Select the two business tables you want to include in the new relationship, either by click and dragging a marquee around the tables, or by holding the SHIFT+CTRL keys, then clicking on the tables.
2. Once your business tables are selected in the Graph, right-click (or CTRL-click) on the selection. Click the New Relationship... option from the popup menu.
   Follow the instructions from step 4 above to fill in the relationship properties. Note that when the Relationship Properties dialog displays, the To and From tables are selected for you.
   
   
   0%

   TODO

Relationship Definitions

The relationships that can be chosen are defined with examples: 

Link: A 0:0 optional relationship basically states that a person can occupy one parking space, that I don't need a person to have a space and I don't need a space to have a person.
 
SubType: A 1:0 relationship; optional only on one side. This would indicate that a person might be a programmer, but a programmer must be a person. It is assumed that the mandatory side of the relationship is the dominant.
 
Physical Segment: A 1:1 mandatory relationship and demonstrates a segmentation denormalization. A person must have one and only one DNA pattern and that pattern must apply to one and only one person.
 
Possession: A 0:N (zero to many) optional relationship indicating that a person might have no phone, one phone or lots of phones, and that a phone might be un-owned, but can only be owned by a maximum of one person.
 
Child: A 1:N mandatory relationship, the most common one seen in databases. A person might be a member or might not, but could be found multiple times (if the member entity represents membership in multiple clubs, for instance).
 
Characteristic: A 0:N relationship that is mandatory on the many side. It indicates that a person must have at least one name, but possibly many names, and that a name might be assigned to a person (might not) but at most to one person.
 
Paradox: A 1:N relationship mandatory on both sides. The "Chicken and the Egg" is involved since you have to have a person to have citizenship, but citizenship to have a person.
 
Association: A N:N (many to many) optional relationship. Conceptually, it means that a person might or might not work for an employer, but could certainly moonlight for multiple companies. An employer might have no employees, but could have any number of them. Again, not hard to visualize, but hard to implement. Most solutions of this situation involve creating a third "Associative Entity" to resolve the M:M into two 0:M relationships. This might be an entity called employee because it does link the person to the employer the person works for.