Create a bridge table?

Chris Arnold

They say there is no such thing as a 'dumb question'...so here goes...

Currently, we are using 11g (but will be upgrading to 12c in approximately 1 month)

In my RPD I need to join a fact to a dimension with a many-to-many relationship.

All of my internet searching seems to point me toward the use of a bridge table for this. While I do not totally understand the concept of bridge tables and how to implement them, I figure I can try to figure it out as I go -- sort of learn from the 'hands-on' experience.

Here is where my problem is -- I don't really know where to begin. Every blog and post I see talks about modeling the bridge table, but I can't find anything that actually talks about CREATING the bridge table.

So my questions are...

(1) Can I create the bridge table in the RPD, or is the bridge table created in the database and then I import it into the RPD?

(2) If the bridge table gets created in the RPD, where might I see some instructions on how to do this? (not just the modelling, but also the creation)

Any help is much appreciated.

Joel

You should push as much as possible to the underlying data source. This means everything will be pre calculated and you should get some performance benefits from this.

This blog describes the process:

http://www.rittmanmead.com/blog/2008/08/the-mystery-of-obiee-bridge-tables/amp/

Robert Angel

See also=> https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-tech…

for a brief explanation on the principal of bridge tables.

And, for another worked example=>

Brent Greenwood's blog: Bridges

[Deleted User]

There are no "stupid questions" just "stupidly asked questions" at max

Jokes aside you got two good answers already. Whenever it comes to M:N in an analytical context the key is however: what are you trying to do / achieve?

Most M:N situations will run into double-/multi-counting issues when not thought through.

A person can have many adresses but that does not mean that there were 2 counts of client contacts for zipcode just because the person lives and works in the same zipcode. Nor that 2 people were contacted.

Robert Angel

On creation of the table this would be down to your ETL / data warehouse developers as like all things it will not stand still, the joins that the table provides will be dynamic.

Alternatively you could achieve the same by creating an opaque view, but I would not recommend this as it would not be nearly as performant.

Chris Arnold

Thanks for the replies EVERYONE. With everyone's answers, I believe I have gotten some great information -- exactly what I was looking for.

[Deleted User]

Now if every thread in here would work like this...that'd just be perfect