July 2, 2015 at 7:09 am
Hi all,
I have been using lots of guidance to create my facts and dimensions with Bridge tables. For example
http://blog.leapfrogbi.com/post/2012/11/26/Bridge-Tables.aspx
http://www.kimballgroup.com/2014/05/design-tip-166-potential-bridge-table-detours/
Lots of the guidance have said you can connect the bridge directly to your fact table.
I have then tried to use Database Diagrams to create the joins (Primary. Foreign keys etc)
The 1 to 1 dimensions are fine but it’s the bridge tables causing issues. For example
Fact Table
DiagID PersonID Fact_PeopleWithDiagnosisThisYear
1 7808 1
Bridge Table
DiagID DiagParentID Weight
1 1 0.50
2 1 0.50
Diagnosis Table
DiagID DiagParentID PersonID Diagnosis MasterDiagnosis
1 1 7808 Ty89200 Ty89200
2 1 7808 PL98088 Ty89200
So basically a person has 2 diagnosis but only one row in the top level fact table
Within the Fact table I used the parent ID to set the DiagID. I still want to be able to group against both diagnosis if Necessary
I'm then a little stumped as to how to create the keys
Attempt 1
I created the Bridge table with a contraint
CONSTRAINT PK_UserGroup PRIMARY KEY NONCLUSTERED ([DiagID], [DiagParentID]))
And then dragged across from DiagID in the fact table to DiagParentID in the bridge table
Both Sides of the relationship must have the same number of columns
Which is fair enough, This isn't going to work.
Attempt 2
Create DiagParentID as the Primary Key
But you cant because it contains repeating groups.
Again fair enough.
Attempt 4
My Next thought was should I have 2 rows in my fact table. But no, the facts don't correspond to the Diagnosis codes and I don't want to create extra complexity in the table.
I'm only interested in the seperate diagnosis codes if I'm grouping them for reports.
Report 1
Diagnosis Fact
Ty89200 1
PL98088 1
So the person is counted against each Diagnosis
Attempt 3
The only think left I can think of is to set DiagID as the primary key in the Bridge table. But I don't want to connect this to the DiagID (Foreign key) in the fact table because that means you will only be able to look at the master Diagnosis codes in the Diagnosis Table because you wont be able to view the ID of 2
I'm already using the tables and if I'm querying in SQL they work really well. But Im sure I need to add the keys etc and I'm at a loss on how to do it.
I really feel like I should be joining the DiagID from Fact to the DiagParentID in the Bridge table. Then join the DiagID to DiagID Bridge table to Diagnosis dimension. But it just doesnt work.
Does anyone know of any really helpful guidance on this one? I feel like I'm banging my head against the wall with it?
Debbie
July 2, 2015 at 12:10 pm
Debbie Edwards (7/2/2015)
Fact TableDiagID PersonID Fact_PeopleWithDiagnosisThisYear
1 7808 1
Bridge Table
DiagID DiagParentID Weight
1 1 0.50
2 1 0.50
Diagnosis Table
DiagID DiagParentID PersonID Diagnosis MasterDiagnosis
1 1 7808 Ty89200 Ty89200
2 1 7808 PL98088 Ty89200
The claim/diagnosis problem is pretty common in healthcare. One key question based on your design above, is how the DiagParentID is determined. Is it some kind of a grouping, or did you purely do that because of the many-to-many relationship?
My typical approach would be something like this:
Fact Table
UniqueDiagnosisCombinationID PersonID Fact_PeopleWithDiagnosisThisYear
100 7808 1
Bridge Table
UniqueDiagnosisCombinationID DiagID Weight
100 1 0.50
100 2 0.50
Diagnosis Table
DiagID Diagnosis MasterDiagnosis
1 Ty89200 Ty89200
2 PL98088 Ty89200
The PersonID should not be in your Diagnosis dimension. There should be a separate dimension for Person/People/Patient.
The UniqueDiagnosisCombinationID field should be unique for all possible combinations of diagnosis for a claim. You could for example create a hash value from the concatenation of all diagnosis codes (ordered). I have used some other options too in the past, but for very specific reasons.
July 3, 2015 at 2:10 am
Thank you.....
In the end I went with:
Fact table (FK) to Group Table (PK) to bridge table (FK using the parent ID) to Dimension table (PK)
I just used an example (Healthcare) rather than using my actual business area.
For the parent key, I just set it against the most recent data item for this specific schema.
I do have a seperate dimension for my person details which is a one to one relationship.
Thats really useful information. That you so much. I have read that many people dont bother using foreign keys for their schemas and that seems like an easy way to do it (But a wrong way)
Thanks again
Debbie
April 18, 2016 at 9:08 am
I am dealing with similar issue, working with a Patient/Diagnose cube, need to be able to slice and dice # of patients based on diagnosis on selected dates, using both ICD9 and ICD10 codes, and as many know already there is no 1 to 1 conversion between ICD9 and ICD10.
Diag Dim
DiagID PatientID DiagCode DOS
1 12345 250.00 4/18/2015
2 12345 E11.00 2/18/2016
250.00 (icd9) and E11.00 (icd10) - (Diabetes) are one in the same, but without knowing what codes belong to what groups makes it a challenge.
Scenario above if I was to pull a report for the past two years, I only need to count one of the records not both of them, trying different things but nothing yet
April 18, 2016 at 9:32 am
RRIOS42 (4/18/2016)
I am dealing with similar issue, working with a Patient/Diagnose cube, need to be able to slice and dice # of patients based on diagnosis on selected dates, using both ICD9 and ICD10 codes, and as many know already there is no 1 to 1 conversion between ICD9 and ICD10.Diag Dim
DiagID PatientID DiagCode DOS
1 12345 250.00 4/18/2015
2 12345 E11.00 2/18/2016
250.00 (icd9) and E11.00 (icd10) - (Diabetes) are one in the same, but without knowing what codes belong to what groups makes it a challenge.
Scenario above if I was to pull a report for the past two years, I only need to count one of the records not both of them, trying different things but nothing yet
This thread is more than a year old. Please start a new thread if you have specific questions about this...
April 18, 2016 at 10:29 am
ok, will do, thanks...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply