OBIEE - How to define a Many-to-Many relationship with a Bridge table ?
Post date: 19-Jul-2010 09:28:38
About
Star schemas 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.
When you need to model many-to-many relationships between dimension tables and fact tables and because most database only support one-to-manyrelationships, it is necessary to implement such relationships physically via a third junction table (of bridge table) with two one-to-many relationships.
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
Articles Related
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.
Example
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.
For example, an referential integrity rules must have minimum two columns to design a relationship.
and for each column you can have multiples data quality rules.
For instance, a column must follow a pattern (conformity) and in the same time a not null (completness) rule.
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 :
WARNINGS: BUSINESS MODEL MDM: [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.
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).
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
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.