Table of Contents

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 Relational Data Modeling - Many-to-many Relationship (Two-way 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.

Steps

The data model

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

Obiee Bridge 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 (Data Quality - Metrics) and in the same time a not null (completness) rule.

Configuration of the Logical Table Source Property

Just drag and drop in the business model from the physical layer the table “Rules” and open the Logical Table Source Property.

Then click on the Add button.

Obiee Join Lts

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 :

  • VENDOR_CHECK
  • RULE_COLUMNS

Obiee Browse Join

Once this is done, just set the type of join and you will have the windows below.

Obiee Join Lts

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

Documentation / Reference