OBIEE 10G - How to define a Many-to-Many relationship with a Bridge table ?
Table of Contents
1 - About
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 :
- have a direct business (in the business model) link with only ONE fact table.
- have always many-to-one relationships
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
2 - Articles Related
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 each data quality rules, there can be multiple columns.
- and for each column you can have multiples data quality rules.
5 - Warning
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 :
WARNINGS: BUSINESS MODEL MDM:  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.