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.
Articles Related
Steps
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.
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 (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.
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
Once this is done, just set the type of join and you will have the windows below.
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
- Map to these tables is also used to design Snowflakes schema