Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Loading Bridge Table

3 posters

Go down

Loading Bridge Table Empty Loading Bridge Table

Post  abolk Mon Dec 06, 2010 5:17 pm

I started my work in the DW world about 9 years ago, but haven't done much for the past 3 years. I am starting to work on DW projects again and have a multivalued dimension situation where I require the use of a bridge table. I am new to bridge tables, but understand the concept. I have the tables modeled, but am struggling with a process to load the bridge table. I have taken a look at previous posts on here, but am still a little lost.

Source Data
Patient_Id Diag_Codes
1 '1 2 3'
2 '9 11'

Bridge Table
SKey DiagGroupKey DiagKey
1 1 1
2 1 2
3 1 3
4 2 4
4 2 5

SKey DiagKey Description
1 1 Cold
2 2 Flu
3 3 Fever
4 4 Broken hip
5 5 Broken ankle

So my question is when I get a new record coming in from the source, how do I manage the bridge table? What is the recommended practice to check to see if the group already exists or if I need to add it to the bridge table? I was thinking of adding another table in between the source data and the bridge which would store the diagnosis codes concatenated together. To me that seems to be the easiest route, otherwise you have to go through some complex logic to manage the table. So my intermediate table would be something like this.

DiagGroupKey DiagCodes
1 '1 2 3'
2 '9 11'

Then when I load a new record from source I can check this table to see if my grouping exists. If it does, then I grab the key from this table, otherwise I add to it and then add to the bridge table in the next step.

Does this method work? Is there an easier way?

Any help is appreciated.


Posts : 1
Join date : 2010-12-06

Back to top Go down

Loading Bridge Table Empty Re: Loading Bridge Table

Post  Al Wood Thu Dec 16, 2010 1:54 pm


I think I have the same problem, expressed as clearly as I can in this post:

"How to lookup a small group of records as one"

I'm not calling it a bridging table though, as it's all on the loading/staging side, and hopefully it will be maintained by a data steward.

I wonder if creating scalar functions in SQL server to concatenate, sort and de-duplicate the string of id's will enable a join to occur in an SSIS lookup step?

Al Wood

Al Wood

Posts : 46
Join date : 2010-12-08

Back to top Go down

Loading Bridge Table Empty Re: Loading Bridge Table

Post  ngalemmo Thu Dec 16, 2010 7:57 pm

The group table you describe is the way to go. Particularly with diagnosis as, you will find, the group table and bridge will eventually stabilize over time.

Not sure why you have a surrogate PK in the bridge, as it has no purpose. The group key and diagnosis key is all you need.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

Loading Bridge Table Empty Re: Loading Bridge Table

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum