The Mystery of OBIEE Bridge Tables

The Mystery of OBIEE Bridge Tables

August 28th, 2008 by Mark Rittman

Source : link

If you’ve played around with the Oracle BI Administrator tool for a while, you may have noticed a box you can tick in the Logical Table Properties dialog, called “Bridge Table”. If you’re familiar with Ralph Kimball and some of this dimensional modeling ideas, you’ve probably heard of this concept before, but it’s not all that clear how you use bridge tables in OBIEE and a quick search around the internet and the OTN forums doesn’t really come up with an examples on how it’s used.

Bridge tables are a solution to what’s called the “multi-valued dimension” problem. For most dimensional models you generally want to link one sale, for example, to one product, one customer, one time period and so on, and this translates into a simple dimensional model where your fact table contains a single key value per dimension for each row that’s been stored. In some circumstances though, say where you are recording the diagnoses for a patient or the claim elements in a claim, you might need to record more than one key value for a particular dimension in each fact table row. In entity relationship modeling terms, you’ve got a many-to-many relationship between patient admissions and diagnoses, like this:

and the usual way you resolve these many to many relationships is to use an intersection table, with the key from the patient admissions table and the key from the diagnoses table copied across to form the intersection, and usually with a weighting column that adds up to 1, so that you can properly add up all the diagnoses and not over-count them.

As I said, this is not exactly new stuff and bridge tables, of which diagnosis group above is one of them, are a fairly common dimensional modeling construct. The problem you hit though when starting to use this feature is that the documentation on it is pretty minimal, and only really talks about setting this feature on the bridge table itself and doesn’t really mention what to do with the dimension table that hangs off of it. What I’ll do in this posting then is set out how I use it, explain my rationale and thereafter invite some feedback, so if someone else has come up with a better idea then we can work with that instead.

Going in to BI Administrator and looking at the physical model for the data set above, it looks like this:

with the key thing here being that the fact table we’ve got the bridge table joining to both the fact and diagnosis dim tables to form an intersection. If you imported this model into the logical business layer as is, the BI Administrator would think the intersection table is the fact table as the other tables join to it.

So what you do now is go into the properties for the diagnosis table and indicate that it’s a bridge table, like this:

Now when you look at the logical model the fact table is identified correctly.

If you try and validate the model now though, you get a warning because the diagnoses dimension table doesn’t link through to the fact table, as it goes through the bridge table instead.

Now not linking through to the fact table is sometimes allowed, basically in situations where you’ve snowflaked your logical model and the dimension table is actually a higher level in the same dimension, but in this case if we try and use this model and bring in the diagnoses information into a query, we’ll get a metadata consistency error.

To solve this, what I would do is remove the diagnosis dimension from the logical model, and instead add it to the logical table source for the bridge table, like this:

Then I’d add any columns that I needed from the diagnosis dimension physical table into the bridge table, which I can do now as I’ve added the dimension table to the bridge table LTS, so that this bridge table now becomes my diagnosis dimension, like this:

So what I’ve done here is take the dimension table, which before linked to the bridge table and thereafter caused the metadata inconsistency, and instead added it to the bridge table logical table source and added it’s columns to the bridge table logical table.

If I run a report now, the data comes out as you would expect, with multiple diagnoses per patient and the weighting applied correctly.

However I’m conscious that to me, getting rid of the logical dimension table seems a bit wrong, and others have said that they don’t use the bridge table feature at all, and instead just combine the various tables into a single logical table source. If anyone’s got any other way of using bridge tables, add a comment to this post and we’ll see what the consensus is.