Bridge table in OBIEE 11g, with example

Generally, OBIEE only supports one-to-many relationships but in some rare cases we have to use many-to-many relationships in our data model. To achieve this, we use the bridge table.

Consider a scenario where there is a sales representative who is participating in many deals that pay commission. Additionally, each deal may include many sales representatives so that each sales representative receives a percentage of the commission. In such case, we must model this many-to-many relationship in the repository using a bridge table concept.

Suppose F_COMMISSION fact table with commissions paid per invoice. D_SALESREP dimension table will have data for sales representative. D_CUSTOMER contains the data for each customer.

Now we will design bridge table COMMISION_BRIDGE in such a way that it will create a many-to-many relationship between the F_COMMISSION fact table and the D_SALESREP dimension table. COMMISION_BRIDGE should have a weight factor to calculate the weighted distribution of commissions among sales teams.
A rough physical diagram for the above scenario will look like as below,

Hope this helps, happy blogging..!! :-)