Model Bridge Tables

A bridge table enables you to resolve many-to-many relationships between tables.

For example, you might hold information about employees in an Employees table, and information about the jobs they do in a Jobs table. However, an organization's employees can have multiple jobs, and the same job can be performed by multiple employees. This situation would result in a many-to-many relationship between the Employees table and the Jobs table.

To resolve the many-to-many relationship, you can create a bridge table or intermediate table called Assignments. Each row in the Assignments table is unique, representing one employee doing one job. If an employee has several jobs, there are several rows in the Assignments table for that employee. If a job is done by several employees, there are several rows in the Assignments table for that job. The primary key of the Assignments table is a composite key, made up of a column containing the employee ID and a column containing the job ID.

By acting as a bridge table between the Job and Employee tables, the Assignments table enables you to resolve the many-to-many relationship between Employees and Jobs into:

• A one-to-many relationship between Employees and Assignments

• A one-to-many relationship between Assignments and Jobs

The image shows a Physical layer view of the example bridge and associated dimension tables.

Description of the illustration bridge1.gif

You should include Weight Factor as an additional column in the bridge table, and to calculating during ETL for efficient query processing.

The following sections explain how to model bridge tables in the Physical and Business Model and Mapping layers:

• Create Joins in the Physical Layer for Bridge and Associated Dimension Tables

• Model the Associated Dimension Tables in Separate Dimensions

• Model the Associated Dimension Tables in a Single Dimension

Create Joins in the Physical Layer for Bridge and Associated Dimension Tables

To model bridge tables in the Physical layer, create joins between the bridge table and the associated dimension tables.

1. In the Model Administration Tool, in the Physical layer, select the fact, bridge, and associated dimension tables.
2. Right-click the objects, select Physical Diagram, and then choose Selected Object(s) Only.
3. Click New Join, select the bridge table, and then select one of the dimension tables.
4. Click OK in the Physical Foreign Key dialog.
5. Repeat steps 2 and 3 for the other associated dimension table.
6. Ensure that one of the associated dimension tables is joined to the fact table.

Model the Associated Dimension Tables in a Single Dimension

In the Business Model and Mapping layer, you can choose to model the two dimension tables associated with a bridge table in a single dimension, or in two separate dimensions.

To model the associated dimension tables in one dimension, create a second logical table source that maps to the bridge table and the other dimension table, and then add columns from the other dimension table. Don't drag the bridge table and the associated dimension table that isn't joined to the fact table. For the example described in the previous sections, you'd drag all objects except for the Assignment and Employee tables.

Providing two separate logical table sources makes queries more efficient, because it ensures that queries against a single dimension table don't involve the bridge table.

It's a good practice to use the bridge table name as the name of the source.

1. Drag objects from the Physical layer to the Business Model and Mapping layer.
2. In the Business Model and Mapping layer, right-click the dimension table that's joined to the fact table, and select New Object, then select Logical Table Source.
3. In the Logical Table Source dialog, provide a name for the new bridge table source.
4. Click the Add button in the upper right corner of the Logical Table Source dialog.
5. Select the bridge table from the Name list, and then click Select.
6. Click Add, select the associated dimension table that isn't joined to the fact table, and then click Select.
7. In the Logical Table Source dialog, click OK.
8. Drag columns from the dimension table that isn't joined to the fact table, Employees in this example, from the Physical layer to the logical table source that you just created.

You can create dimensions based on your logical tables, including the logical table with the bridge table source.

Model the Associated Dimension Tables in Separate Dimensions

As an alternative to modeling the two dimension tables associated with a bridge table in a single dimension, you can choose to model them in separate dimensions.

Create a logical join between the fact table and the dimension table that isn't physically joined to the fact table, and then modify the logical table source for that same dimension table to add the other table mappings.

1. Drag objects from the Physical layer to the Business Model and Mapping layer.
   Because you want to model the dimension tables in separate dimensions, drag both of the dimension tables associated with the bridge table. You don't need to drag and drop the bridge table object.
2. In the Business Model and Mapping layer, select the fact table and the two dimension tables that are associated with the bridge table.
3. Right-click the objects and select Business Model Diagram, and then choose Selected Tables Only.
4. With the Business Model Diagram displayed, click New Join on the toolbar.
5. Select the fact table, and then select the dimension table not currently joined to the fact table.
6. Click OK in the Logical Join dialog.
7. Double-click the logical table source for the logical table for which you created the logical join.
8. In the Logical Table Source dialog, click Add.
9. Select the bridge table from the Name list, and click Select.
10. Click the Add button again and select the other associated dimension table and then click Select.
11. In the Logical Table Source dialog, click OK.

You can create dimensions based on your logical tables, including both logical tables associated with the bridge table.