OBIEE 10G - How to define a Many-to-Many relationship with a Bridge table ?

Bi Server Architecture With Client

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)

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.

Many To Many Relationship

Bridge table rules : A bridge table must :

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

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.

Obiee Bridge Table Logical

The primary key is the combination of the columns in a unique record.

The data model

To understand how a bridge table 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.

Warning

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.

Modeling

You can reach your goal and resolve this issue by using the join property of a Logical Table Source.

Obiee Lts Join Rules Rules Columns

In the picture below, we have create only one logical table with the two previous physical table and we obtain this business model.

Obiee Bridge Table Bm Version1

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).

Obiee Report Many To Many

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.

Reference





Discover More
Bi Server Architecture With Client
OBIEE - How to model the different relationships ?

The relationships modeling in OBIEE. 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...
Obiee True Star Schema
OBIEE - Identifying a Dimension Table

Dimension tables contain attributes that describe business entities. For example, Customer Name, Region, Address, Country and so on. Dimension tables also contain primary keys that identify each member....
Star Schema
OBIEE - Identifying a Fact Table

A fact table is a table with measures. Measures need to be defined in a logical fact; any column with an aggregation rule is a measure. Oracle BI Server understands that any table in a repository that...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...



Share this page:
Follow us:
Task Runner