Bridge table for patient diagnosis

Hi ngalemmo or fourm members,

Can I get advice on how to create a bridge table for patient diagnosis? To keep it simple, I have a patient, facilities, and doctor dimensions and one fact table. For a specific patient, I need to show all diagnosis in a facility by a doctor.Since a patient can have multiple diagnoses on a claim, I have to use a bridge table to tie to the fact table.

In our DW, we normalized the data and store each patient’s diagnosis in a row. Here is an example on 2 differnet patients:
A)
Claim_id, patient_id, Diagnosis
1,100, flu
1,100, diabetes
1,100, muscle ach

B)
Claim_id, patient_id, Diagnosis
2, 500, flu
2, 500, muscle ach

In his dicussion with pzajkowski on bridge, ngalemmo mentioned that “First, the claim has an FK to a diagnosis group table which contains every unique combination of diagnoses found on claims. If multiple claims have the same combination of diagnoses, they would all reference the same group. You then have a bridge table containing diagnosis group key and diagnosis key. The latter references a traditional diagnosis dimension table.”

Based on above statement, I can find unique diagnoses on a calim and create a bridge table, but your second statement puzzled me. Not every patient has same diagnosis on a claim. For example, paitent in example ‘A’ has diabetes, but patient in example ‘B’ does not. It is really hard to find different patient with same group of diagnosis in the above structure. I have 18 million rows in patient diagnosis table. The sql I wrote to get group of diagnosis is something like:

Select * from patient_diag a1
Where exists
(select 1 from patient_diag b1
Where a1. Claim_id <> b1. Claim_id
And a1. Diagnosis = b1. Diagnosis)

By using the above example, the result is:
Flu and muscle ach.

If I store above flu and muscle ach values as a group in the bridge table, and try to get all the diagnosis for patient ’A’, it will not show diabetes.

So the questions I have:

1) How can I create diagnosis bridge table using above table structure in the examples?
2) Please advice how to tie a diagnose group to a patient in the fact table? I have to use patient's
diagnosis table from DW and try to find a match in the bridge table, right? I am not sure how would I join patient's diagnosis table with diagnosis bridge table. We have SQL server 2005 RDBMS.

Thanks.

To define groups, you need a table to keep track of the groups you encounter. It usually has 3 columns, a PK (group key), a natural key, and, optionally a count (number of diagnosis in the group).

The natural key is a string made up of a concatenation of the diagnosis codes. If order of the codes doesn't matter, the codes should be arranged in ascending sequence to avoid creating too many combinations.

Assuming the diagnosis have the following codes:

flu: 290.9
diabetes: 342.34
muscle ache: 045.0

The group table would look something like this:
Key, Natural Key, Count
11, 045.0|290.9|342.34, 3
12, 045.0|290.9, 2

The fact table would reference the group key

Claim, Patient, Group
1, 100, 11
2, 500, 12

The group table itself is only used to support ETL. It doesn't participate in queries. A bridge table, between the fact and the diagnosis table uses the group key to associate with the fact:

Assuming a diagnosis dimension such as:
Key, Diag code, description
20, 290.9, Flu
21, 342.34, Diabetes
22, 045.0, Muscle ache

The bridge would look like:
group key, diag key, diag count
11, 20, 3
11, 21, 3
11, 22, 3
12, 20, 2
12, 22, 2

While is may seem that the group table will get big... it will... but the bridge, in practice, will be much smaller that a bridge based on claim and diagnosis. For the simple reason that diagnoses cluster and repeat across patients for most conditions. Sure, there is the occasional outlier, but, for the most part, Doctors, like everyone else, are creatures of habit.

Thank you so much for your reply. Have you been into a situation where you have to create diag bridge table on your own ( would pls share how you did it)? I am not getting any help from the business user to create diag bridge table. That is why I was thinking about using the patient diag table and grouping diag as per a patient's every encounter and assigning a group PK... i-e

Group key, diag key, patient id
11, 20, 100
11,21,100
11,22,100
12,20, 500
12,22,500

The issue with this approch is that I would get same number of rows in the bridge table as orignal patient diag table (~18 million rows)

The business user isn't going to be much help... the bridge is created and maintained as part of the ETL process based on the data you receive. It's pretty much transparent to the end user.

As far as the number of rows in the bridge, you missed the point. If patient 123 has an encounter where the diagnoses are flu and muscle ache, they would also be assigned to diagnosis group 12. Since group 12 already exists in the bridge, you do not need to add more rows to the bridge.

The group table is like a junk dimension... you add to it as you encounter a new combination of diagnoses. If a particular combination already exists, you don't add it to the group table or the bridge. You just set the FK in the fact table.

Group tables and bridges implemented this way will grow very fast initially, but quickly stabilize once the bulk of common combinations have been encountered. You can expect a reduction in the number of rows by 85% or more over not grouping.

where there could be multiple Icd 9 surgical procedures for a single claim?

Not usually. A claim usually has charges and each charge is tied to a single procedure. You would usually have one row per charge per procedure (plus all the other dimensions).

You may also have a claim aggregate, one row per claim, but such a table would not include a procedure dimension... analysis of charges by procedure would use the detailed fact.

Let us suppose we have 2 claims
CLAIMS TABLE
-------------------
C1 P1 G1 $100
C2 P2 G2 $ 50

GROUP TABLE
-----------------
G1 D1||D2||D3 3
G2 D1||D3 2

BRIDGE TABLE
-----------------
G1 D1 3
G1 D2 3
G1 D3 3
G2 D1 2
G2 D3 2

DIAGNOSIS CODE TABLE
-------------------------
D1 Flu
D2 Diabetes
D3 Muscle ache

So the bridge table will never be exposed to the users...its only use is ETL and the queries will be done on claims table along with the group table?

How can it be used in a BO universe if I have to pull some long description of the diagnosis from the code table ?

Wouldn't it multiply the claim value if i have a question like, to find claims for members who had Flu or Muscle ache in Business objects wouldnt it show $250 instead of 150

Edit: I realized the mistake , the group (junk) dimension is what is exposed to the user in BI tools and not the ETL bridge table which is only used in ETL process. Now the natural key of the group junk dimension can be used in a query to intelligently gather specific diagnosis using like %D3% or like D3%D1% etc

I don't understand the need for a bridge table in the situation being described. Aren't bridge tables usually used in many to many relationships? Diagnosis to Claim is a many to one relationship.

If the only reason for create a bridge table to a claim is to get the multiple diagnosis on the claim, wouldn't the more appropriate design be a fact table at the Diagnosis level?

Where I can see needing a bridge table is when you are trying to link the diagnosis to the treatment. A claim can have multiple treatments and multiple diagnosis. Treatment is a many to many relationship with diagnosis.

Create 2 fact tables at the diagnosis level and the treatment level. The bridge table is what is used to connect all of the possible combinations of Treatment and Diagnosis on the claim.

It is a multivalued dimension. The driving dimension in a claim fact table is the procedure. A claim has many procedures, with a charge associated with each procedure. Along with all the other dimensions, diagnosis is attached to the line. Since there may be many diagnoses, you need a bridge to associate the group of diagnoses to the row in the fact table.

From what I've seen, the diagnosis is usually on the Procedure Line, at least in the data that I've seen.

In any case, it's possible to have many diagnosis and many treatments on the same claim. I can see 2 fact tables - one for procedure and one for diagnosis with bridge table linking the 2 since many treatments can be associated with many diagnosis. I don't understand the single fact table with the bridge table. If it's Fact to Brdige to dimension, isn't that a basic snowflake?

I'm just trying to understand the bridge table.

Using a bridge is not snowflaking. A snowflake, by definition, is a relationship from one dimension table to another dimension table, used to achieve a more normalized data model. Creating a snowflake is a design choice, not a necessity (except in very few cases).

A bridge on the other hand resolves many to many relationships between a fact table and a dimension table. When such relationships exists, a bridge is necessary and is the only mechanism that will resolve it.

A claim fact, in general, has one row per procedure, with many rows for the claim. For any one row (procedure) in the claim fact, there could be multiple diagnoses associated with it. The bridge allows you to attach a list of diagnoses to a single row in the claim. You do not want to introduce diagnosis into the grain of the fact as it would create additional rows for a procedure. All measures in a claim relate to a procedure, and it does not make any sense to allocate those measures based on diagnosis. So a bridge is used to reference a group of diagnoses rather than a FK to a single diagnosis.

I have tried this exactly as described. My problem is in the load of the bridge table where I try to get the group key. To do this, you must match on the natural key in the group table (concatenation of ICD9 codes) to bring back the group key. Some of the cases have >20 ICD9 codes. This brings the transformation to a crawl (< 10 rows/s). I have tried to index the natural key, but no change. Any ideas here would be greatly appreciated.
Thanks
Jeff

jmather wrote:I have tried this exactly as described. My problem is in the load of the bridge table where I try to get the group key. To do this, you must match on the natural key in the group table (concatenation of ICD9 codes) to bring back the group key. Some of the cases have >20 ICD9 codes. This brings the transformation to a crawl (< 10 rows/s). I have tried to index the natural key, but no change. Any ideas here would be greatly appreciated.
Thanks
Jeff

Are you saying > 20 diagnosis on a claim?

The model above does seem to jive with what I have read in books, etc.

What I am really trying to see is what advantages it has over a typical OLTP many-to-many resolution table.
One disadvantage I see is that the ETL is more complicated.
The other is that it doesn't have the concept of 'primary diagnosis', 'secondary diagnosis', etc - which I think is important.

Basically, it seems to me that their is the concept of re-using groups of diag codes and allowing for a foreign_key on the fact table and lets weights be assigned.

If I were to model it like a transactional database, I would do this:

claim_fact
id, person, paid_amt, num_of_diags
c1, p1, $100, 3
c2, p1, $200, 2

diag_dim
id, code, desc
d1, v11.1, diag 1
d2, v11.2, diag 2
d3, v21.1, diag 3
d4, v22.1, diag 4

bridge
id, claim_id, diag_id, type
b1, c1, d1, primary
b2, c1, d2, secondary
b3, c1, d3, third
b4, c2, d2, primary
b4, c2, d4, secondary

I realize I am storing more records, but it is very easy to load and manage and query.
You still have the weight concept available by putting the number of diags right on the claim fact.
I know this doesn't follow most of the star schema concepts, but thought I would try to understand why this is not really proposed as a solution in these rare kind of cases.
I haven't worked with many of the BI tools recently. Is there an issue with the way they would be able to handle this?

Thanks for the work on this post all - very useful.
Paul

You can do it that way, the downside, as you mention, is the larger bridge table.

As far as the other technique goes, you can introduce precedence in the codes if you need to. The two techniques can achive exactly the same functionality. One has a (much) bigger bridge, the other, more complex ETL.

By the way, what is the point of the ID column in the bridge table?

Thanks for the reply.
I put an id column on the table just out of consistency of having primary surrogate keys on all tables.
Probably extraneous since it will never really be used.