Modeling Bridge Tables in obiee 11g

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

Example Bridge and Associated Tables in the Physical Layer

Note that "Weight Factor" should be included as an additional column in the bridge table and calculated during ETL for efficient query processing.

Creating 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.

To create physical joins for a bridge table and its associated tables:

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

Joins Between the Example Tables in the Physical Diagram

Modeling 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.

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

To model the dimension tables associated with a bridge table in a single dimension:

1. Drag objects from the Physical layer to the Business Model and Mapping layer, except the bridge table and the associated dimension table that is not joined to the fact table. For the example described in the previous sections, you would drag all objects except for the Assignment and Employee tables.
2. In the Business Model and Mapping layer, right-click the dimension table that is joined to the fact table (Jobs in our example) 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. It is a good practice to use the bridge table name as the name of the source (for example, Assignment).
4. Click the Add button in the upper right corner of the Logical Table Source dialog. Then, select the bridge table from the Name list (Assignment in our example) and then click Select.
5. Click the Add button again and select the associated dimension table that is not joined to the fact table (Employee in our example) and then clickSelect.
6. Click OK in the Logical Table Source diaog.

Logical Table Source Dialog for Bridge Table Source

7. Drag columns from the dimension table that is not joined to the fact table (Employees in our example) from the Physical layer to the logical table source that you just created.

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

Modeling 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. To do this, create a logical join between the fact table and the dimension table that is not physically joined to the fact table, and then modify the logical table source for that same dimension table to add the other table mappings.

To model the dimension tables associated with a bridge table in separate dimensions:

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 do not 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 (Facts, Employee, and Jobs in our example). Then, right-click the objects and select Business Model Diagram, and then choose Selected Tables Only.
3. With the Business Model Diagram displayed, click New Join on the toolbar. Then, select the dimension table not currently joined to the fact table, and then select the fact table.
4. Click OK in the Logical Join dialog.

Joins Between the Example Tables in the Business Model Diagram

5. Double-click the logical table source for the logical table for which you created the logical join (Employee in our example).
6. Click the Add button in the upper right corner of the Logical Table Source dialog. Then, select the bridge table from the Name list (Assignment in our example) and then click Select.
7. Click the Add button again and select the other associated dimension table (Jobs in our example) and then click Select.
8. Click OK in the Logical Table Source diaog.

Logical Table Source Dialog for Dimension Table Source

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