The Mystery of OBIEE Bridge Tables
August 28th, 2008 by Mark Rittman
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.
August 29th, 2008 at 12:52 am
Thanks Mark for the great post. I have spent the last year relearning my Discoverer skills on OBI and have appreciated yoru posts. Looking forward to OpenWorld. I wonder if you have tried starting with a view that prejoins the bridge table and the dimension table. Then use the view as a source in OBIEE. How does OBI handle this type of situation. Curious how the SQL comes out. This is how I normally solve the bridge table for users anyway. I find would import the view in discoverer to hide the complexity of the bridge from the end users. What are your thoughts?
September 2nd, 2008 at 9:15 pm
Hi Mark, Thanks for the post. I think you do not need to delete the dimension table. By just adding the DIM table also to LTS of the bridge but leave the dim table in the logical model, you resolve the inconsistency and leave the model intuitive and intact. However, the pure bridge table situation in a DWH star schema is slightly different: It is a dimension joining to a fact, say SALES_REP_DIM to COMPENSATION_FACT which joins via a bridge to SALES_TEAM. In this case you can not add the SALES_REP dimension to the bridge because it does not join to the fact and hence does not solve the problem. THe other way around is not allowed since the granularity of the bridge is higher than it satellite dimensions. I have, until today, no solution for this problem. I am keen to discover what the effect is in the OBIEE server engine, of checking the Bridge Table box. To be continued …
Cheers,
Gerard
September 3rd, 2008 at 10:13 am
HI Gerard, Mike
Mike – thanks for the comments. In terms of defining a view outside of OBIEE, as you say this probably makes things a lot easier, however for this investigation I was trying to see how OBIEE used its bridge table feature hence giving it a go. As you say though, resolving the issue in the database layer prior to manipulating it via BI Administrator is often the simplest approach.
Gerard – I think I did try adding the dim to the bridge table LTS and I got the same error. I’ll try this again when I get a moment and see if the problem still occurs. Thanks for the heads up on the other bridge table scenario, if you get a solution then let me know, I’ll keep an eye open as well for this.
September 11th, 2008 at 6:18 pm
These are great posts. Very clear and very valuable. In general, this post describes how I have used bridge tables in other environments — End users just see a single view combining the bridge and the bridged dimension.
My question: are there any front end query tools smart enough to automatically apply the weighting factor to referenced fact amounts?
October 24th, 2008 at 9:16 am
I think the “multi-valued dimension”solution like the loop solution in BO. Maybe that need a very mature and complex solution supply.
November 20th, 2008 at 8:25 am
Hi Mark,
This is the way I modeled it and it worked fine for me.Instead of using the Bridge table to join to the fact the way I did i in the BMM layer is i Joined the Dimension(Diagnoses) to the Fact table and inside the LTS of dimension I mapped the bridge table as the other source and it works fine for me and it givs me correct result.
Please advice.
Regards,
San
March 12th, 2009 at 4:38 pm
Hello Mark,
Thanks for all the posts and insights. They are extremely helpful in the design and development.
I have a question. Would this approach work with 2 fact tables , even if there is a confirmed dimension between the 2.
The fact groups are joined using the the hierarchies and defining content level for the facts , dimensions and measures.But the queries are lengthy and long running.
Thanks,
Parag
July 3rd, 2009 at 5:00 am
Hi Mark,
I think having a bridge will be more benefical if there are two or more multivalued dimensions. If its only one then its better to have both the dimensions in fact itself and have the weight factor or the pro-rata factor maintained in the fact. This will reduce complexity while auditing the results and end queries.
Regards
Bibudesh
August 11th, 2010 at 9:13 am
Hi Mark,
This is a very good post and helpful. Can you please give an example of Patient Admission and Diagnoses with some records and show how they look like when Diagnoses Group is created, too? I am not able to understand how to move the keys from those two tables to the birdge table to convert many-to-many relationship into two one-to-many relationships.
Regards,
Roy.
September 1st, 2010 at 10:08 am
Can you please share RPD for The Mystery of OBIEE Bridge Tables.
September 1st, 2010 at 10:10 am
Hi Mark,
This article is really nice. if possible can you please send me The Mystery of OBIEE Bridge Tables rpd at rafiansari2002@gmail.com I will be thankfull to you.
Regards
Mohammad Rafi Ansari
91-9213626363
September 1st, 2010 at 1:59 pm
Hi Mohammad,
I’m afraid I’ve not got it available now, the posting is >2 years old now, sorry.
Mark
September 3rd, 2010 at 5:42 am
Hi Mark,
Thanks for ur reply.
Its ok if RPD is not available now I will try to do it by myself. Well I need some more help from your side. Actually I am planning for OBIEE Certification but i dont know what will be the pattern for that. So I need you precious help, if you can arrange/provide me any soft material or any sample paper of oracle for study. So that i can score more in paper.
Regards
Mohammad Rafi Ansari
Email: rafiansari2002@gmail.com
Contact: 91-9213626363
September 19th, 2010 at 6:52 am
Hi Mark,
Useful article that shows one more way how to bridge tables.
I was performing an upgrade from Siebel analytics to OBIEE 10.1.3 and we had lots of consistency errorspost upgrade which were not visible in analytics.
They seemed to be arising from bridge tables(although the errors were on BMM objects not even related to the bridges). Our Oracle consultant just deleted the bridge tables and all the errors vanished.
I was also surprised to see that all the bridges having the source as fact tables. Can you please share your thougts on if and when you consider a fact table to behave as a bridge?
October 6th, 2010 at 7:28 pm
Thank you for sharing your expertise – this article saved me quite a bit of frustration. I subscribe to and am impressed by your rss feed, as well. Rock on!
October 14th, 2010 at 3:08 pm
The bridge table option is not available anymore with OBIEE 11g.
It has been changed with an interesting lookup table option which of course is not the same fonctionnality.
Regards
January 17th, 2012 at 12:04 pm
awesome…