OBIEE 10G - How to define a Many-to-Many relationship with a Bridge table ?

1 - About

Dimensional Schemas (Star and snowflake schemas) work well for modeling a particular part of a business where there are one-to-many relationships between the dimension tables and the fact tables.

However, sometimes it is necessary to model many-to-many relationships between dimension tables and fact tables. For instance, an organization's employees can have multiple jobs, and the same job can be performed by multiple employees.

When you need to model many-to-many relationships between dimension tables and fact tables and because most database only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table (of bridge table) with two one-to-many relationships. More see Data Modeling - Many-to-many Relationship

In 11g, the bridge table doesn't exist any more, you have to use the join property of the Logical Table Source to model it.

Bridge table rules : A bridge table must :

This type of design can create more records in the bridge table than in the fact table. You can limit the number of records in the bridge table by predefining groups and forcing each fact record to fit in one of these predefined groups.

As the bridge table must be connected to the fact table, the easiest way to design a many-to-many relationship in OBIEE is to use fully the join property of the Logical Table Source

3 - How to declare that a logical table is a bridge table ?

In the Administration Tool, the Logical Table dialog box has an option you can select to specify that a table is a bridge table.

The primary key is the combination of the columns in a unique record.

4 - The data model

To understand how a bridge table works, consider the following portion of a sample dimension data quality schema :

In this schema, the table Vendor_check is the fact table.

The many-to-many relationship is on the dimension branch because :

For example, an referential integrity rules must have minimum two columns to design a relationship.

For instance, a column must follow a pattern (conformity) and in the same time a not null (completness) rule.

5 - Warning

This type of design is not exactly as OBIEE define a bridge table. As you can see, I have a many-to-many relationship in the dimension and thus not connected to the fact table.

If you try to design the same model in the business area and that you check the box “Bridge table” on any table that don't join with a fact table, you will receive this warning :

[39008] Logical dimension table RULE_COLUMNS has a source RULE_COLUMNS that does not join to any fact source.

In a other way, I tried to say that the table Rules and the table Rules_columns was my bridge table but it doesn't work.

6 - Modeling

You can reach your goal and resolve this issue by using the join property of a Logical Table Source.

In the picture below, we have create only one logical table with the two previous physical table and we obtain this business model.

Now all works fine as you can see in the report below :

  • You have multiple column names (ADDRESS, CITY, COUNTRY) with the same value for the fact measure “Passed” (99.63). Passed is equivalent to the check result share when the data rule is good).

7 - Bridge Table or Join Property

For the scenario above :

Fact Table – Dimension table 1 – Bridge table - Dimension table 2

I was obligated to made only one logical table of the tables Dimension table 1 and Bridge table by using eht join property of the Logical Table Source because a bridge table in OBIEE must be directly connected to the fact table.

But you can go further in this method and use it completely to model your many-to-many relationship : join property of the Logical Table Source

8 - Weight factor

As result of the many-to-many relationship, one fact rows have multiple dimension rows.

For example, a data rule can apply on several column.

The bridge table then needs to have a weight factor column in it so that all column for a single data rule add up to a value of 1.

The weight factor has to be calculated as part of the process of building the data.

9 - Reference

dat/obiee/obis/obiee_bridge_table.txt · Last modified: 2017/09/13 21:21 by gerardnico