OBIEE 10G/11G - How to model a bridge table (Many-to-Many relationship) with the joins property of a Logical 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
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.
To perform this design, you create a bridge table (assignment in 11g) and use the join property of the Logical Table Source.
2 - Articles Related
3 - Steps
3.1 - The data model
To understand how a bridge table modelling 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.
3.2 - Configuration of the Logical Table Source Property
Then click on the Add button.
The bridge table must always be integrated in the “many” table. For instance, if the bridge table was directly joined to the fact table, you have to integrate it in the fact table. In this case, the physical sql generated is beter
A browse windows appears. You see and you can select only the table that have a One-to-many (Not a many-to-one) relationship. You see then :
- almost always the fact table
- an other table that you have define in the physical layer with the same One-to-many relationship that with the fact table.
In your case, the table “rules” have two one-to-many relationship with two tables :
Once this is done, just set the type of join and you will have the windows below.
3.3 - What joins are performed by the BI Server
Then we can think than when when we ask in a report a column from the physical table “columns”, OBIEE perform all the joins above but no. It know where the column come from and perform a statement against only this one.
Example with a attributes from the physical table “COLUMN”.
SELECT DISTINCT T5567.CONSENSUS_DATATYPE AS c1 FROM QS_ODS.D_COLUMN T5567 /* COLUMNS */ ORDER BY c1
4 - Documentation / Reference
- Map to these tables is also used to design Snowflakes schema