Work with Bridge Tables

This topic provides information about when to use bridge tables and how to model them.

Topics:

• About Bridge Tables

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

• Model the Associated Dimension Tables in a Single Dimension

• Model the Associated Dimension Tables in Separate Dimensions

About Bridge Tables

Use a bridge table (or intermediate table) to resolve many-to-many relationships between tables.

For example, suppose there is an Employee table that contains information about employees, and a Jobs table that contains information about the jobs the employees perform. An organization's employees can have multiple jobs, and the same job can be performed by multiple employees. This results in a many-to-many relationship between the Employees table and the Jobs table.

For this scenario, you create a bridge table named Assignments to resolve the many-to-many relationship. 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 performed 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

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

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.

After you've completed creating joins in the physical layer, you then add the needed associated dimension tables to the logical layer and model them in either a single dimension or separate dimensions.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, browse for and Ctrl click the fact, bridge, and associated dimension tables.
5. Right-click, hover over Show Physical Diagram, and click Selected Tables Only.
6. From the bridge table, click and drag to draw a join line to a dimension table.
7. Add joins from the bridge table to the other associated dimension tables.
8. Confirm that one of the associated dimension tables is joined to the fact table.
9. Click Save.

Model the Associated Dimension Tables in a Single Dimension

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

Before you perform this task, you need to create the required physical joins. See Create Joins in the Physical Layer for Bridge and Associated Dimension Tables.

To model the associated dimension tables in a single dimension, create a second logical table source that maps to the bridge table and to the other dimension table, and then add columns from the other dimension table. Don't add the bridge table and the associated dimension table that isn't joined to the fact table to the logical layer. For the example described in About Bridge Tables, you add the Jobs table (dimension table joined to the fact table), but not the Assignment table (bridge table) and Employee table (dimension table not joined to the fact table).

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.

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

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Logical Layer.
4. In the Logical Layer pane, click Create and then Create Logical Table to create the needed logical dimension table. Repeat this step to create the logical fact table.
5. In the logical layer, double-click the dimension table that is joined to the fact table and in the table, click the Sources tab.
6. Click Add Physical Table and then Create Logical Table Source and in the table source's Name field, enter the name of the bridge table that you created in the physical layer.
7. Click Add Physical Table and then Create Logical Table Source and in the table source's Name field, enter the name of the associated dimension table that isn't joined to the fact table.
8. Click the Columns tab to navigate to the table's column list.
9. Click Add Column and then click Create New Column.
10. In the new column's Name field, enter the name of a column from the dimension table that isn't joined to the fact table. Repeat this step to add the required columns.
11. Click Save.

Model the Associated Dimension Tables in Separate Dimensions

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

Before you perform this task, you need to create the required physical joins. See Create Joins in the Physical Layer for Bridge and Associated Dimension Tables.

To model the associated dimension tables 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 dimension table's logical table source to add the other table mappings. Don't add the bridge table to the logical layer, but add all dimension tables associated with the bridge table. For the example described in About Bridge Tables, you add the Jobs table (dimension table joined to the fact table) and Employee table (dimension table not joined to the fact table), but you won't add the Assignment table (bridge table).

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

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Logical Layer.
4. In the Logical Layer pane, click Create and then Create Logical Table to create a logical dimension table. Repeat this step to create the required logical dimension tables and to create the logical fact table.
5. In the Logical Layer pane, Crtl click the fact table and associated dimension tables.
6. Right-click, hover over Show Logical Diagram, and click Selected Tables Only.
7. From the fact table, click and drag to draw a join line to the dimension table not joined to the fact table.
8. In the Logical layer pane, double-click the dimension table you joined to the fact table and in the table, click the Sources tab.
9. Click Add Physical Table and then Create Logical Table Source and in the new table source's Name field, enter the name of the bridge table that you created in the physical layer.
10. Click Add Physical Table and then Create Logical Table Source and in the new table source's Name field, enter the name of the associated dimension table. Repeat this step to add other dimension table.
11. Click Save.