Diagnosis Dimension Modeling

[srokes]

I am working on a dimensional model and dealing with the wonderful world of claims and diagnosis. I have come across a few solutions, but was wondering about other solutions. I have read R.Kimball and saw the bridge concept. I was not sure if anybody has implemented this or any other solutions. Any thoughts would be appreciated.


Thanks

 

[johnherman]

I have experience in this area. I denormalized most of the claim diagnosis bridge table into the claim for performance reasons. I allow up to 4 diagnosis per procedure/service and up to 4 diagnosis per claim. Then, I have a flag indicating that there are more diagnosis codes stored in the Claim-Diagnosis and/or Service-Diagnosis bridge tables. I found with our data that over 99% of all claims and services have four or less diagnosis codes.

Next, we also allowed an additional diagnosis associated with admissions, i.e. inpatient services. This is a column in the admission table identifying the admitting diagnosis associated with a patient admission. There is also an admitting physician.

But back to the point, and to recap. Our system allowed unlimited diagnosis associated with a claim and also unlimited diagnosis associated with a service (a claim line or a pre-paid encounter in the HMO world). We had four diagnosis columns in the Claim table, and four diagnosis columns in the Service table. In addition, there is a one character flag "Additional Diagnosis" Y/N whereby a Y indicated that there were more diagnosis stored in the Claim-Diagnosis bridge table. Similarly for Service and Service-Diagnosis.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[teccum]

I recently modelled a datawarehouse with Claims and Diagnosis dimension for an IPA(Independent Providers Association) model of Healthcare business. I had a Diagnosis_group dimension table and Diagnosis table which is a parent of Diagnosis_group table and a Diagnosis_cross_reference table(like a bridge table) which has the combinations of diagnosis group and diagnosis id's to handle many to many relationships with the diagnosis.

The diagnosis_Group table had two roles with Claims namely Diagnosis_Group_Master and Diagnosis_Group_Detail which I had to design through role definition.


teccum.

 

[srokes]

ok, can you tell me approximately how many records you are in your bridge table and the claim fact table?

 

[teccum]

We are in the process of loading the data into the ODS and then into the Datawarehouse. The size of the claim fact table is estimate to be around 180M records since the Management wanted 6 years of data. Well, there are also two more fact tables namely Authorization Fact and Capitation fact tables in this datawarehouse.

The bridge table (for diagnosis and diagnosis dimension) is estimated to have around 180000 records.


teccum