Modeling Healthcare Measurements

I'm trying to figure out how to best model a DataWarehouse Star Schema structure to capture certain medical information that will answer the questions we ask.

First the questions to put in some context:
1) How many distinct patients had a BMI in the past year <=25?
2) How many distinct patients had a flu shot in the current flu season?
3) How many distinct patients had a pneumovax ever in their life?

The "Distinct Patients" part is what is killing me. I'm not sure how to design the warehouse using additive facts that makes any kind of sense. While the above questions are typically asked, there's no reason for them not to also be asked on a monthly, quarterly, yearly, etc cycle. So I'd like to also be able to answer how many distinct patients had a BMI < 25 in the past 6 months and the past 3 years. I also want to be able to answer this question as of any particular date, not just what is the story currently.

Patients will have more than one BMI recorded because they'll have one every time they come in. When determining "BMI <25", the most recent value is used in the analysis regardless of whether it was 1 month ago, 1 day ago, or 1 year ago and, of course, each patient's BMI is at a different time but counted equally.

I've been through the DW Toolkit a few times and found nothing that deals with the distinct dilemma I'm facing. If I just wanted to know how many measurements indicated <25, I'd be golden, but that's not the case.

After pondering this for a couple weeks, the best I can come up with is a monthly snapshot that doesn't roll-up. Maybe this is the best solution, but I thought this was the best place to ask before I move forward building that. What I would end up with is a row for every patient every month and all associated measures.

Distinct always makes me think dimension. I could see carrying a BMI as an attribute on a Patient dimension, but not the other information. That leaves using a fact table. Step one is identifying a grain. In this case, the grain looks like a medical procedure. This is most likely identified by a CPT code or other common insurance claim line identifier.

To count the number of patients that had a flu shot simply sum the number of fact rows with the procedure code = 'Flu Shot' for the period of time you want. If you want distinct patients, group by the patient dimension FK for type 1 dimension. Who really wants two flu shots though? For type 2 dimension, you'll need to join to the Patient dimension to identify a common identifier for grouping.

This handles items 2 and 3. BMI sounds a little different than the other procedures. If you store it in the same fact described above, I would create an additional dimension to segment BMI into the different reporting ranges (e.g. <25, 25-30, 30-35, etc). Counting distinct patients is accomplished the same way however. Sum all fact rows where procedure code = 'BMI' and BMI segment dimension = '<25' grouping by patient dim. You can further filter any of these results using the data dimension.

The one outstanding item is the idea that BMI < 25 only counts if it's the most recent procedure. So if a patient had a (BMI < 25) last week and subseqently has a BMI > 25 this week, he no longer should be considered (BMI < 25) for any time period. This could be accomplished using the MAX function.

select max(date), patient, procedure, bmi_segment
from fact
group by patient, procedure, bmi_segment

Does that help?

It certainly helps in that I'm not out in left field for what I'm thinking as you've described my same thoughts.

The last part about the BMI is my biggest stepping stone and you're right about BMI being the big gotcha versus the flu & pneumovax.

The query you presented wouldn't work straight like that and my real wonder is what is the difference for me to pull that from my warehouse versus my transactional system if i'm going to make it work that hard. The query is just as complicated and the BI tools we have won't immediately perform those functions either.

Your query would return two rows for the patient you gave an example of: Week 1 has BMI < 25 and Week 2 has BMI 25-30. This would result in something like this:

BMI_Segment has <25 (Key 1), 25-30 (Key 2), >30 (Key 3)

PatientKey	DateKey	ProcedureKey	BMI_Segment_Key
1	5	3	1
1	8	3	2

The grouping produces two rows because "BMI_Segment_Key" is different. If both measurements had been "<25" then that query would be fine.

I think perhaps the grain is my real issue here. What I'm really working with is Diabetic patients and am looking to simplify the data gathering that's going on related to those patients.

It sounds like to do this, I'll need a structure like this:

FACT
PatientKey
ServiceDateKey
MeasureKey
SegmentKey

DIM Measure
MeasureKey
MeasureName
(sample data for MeasureName: BMI, HDL, LDL, Systolic BP, HbA1c)

DIM Segment
SegmentKey
SegmentName
(sample data for SampleName: <25, 25-30, >30, <120, >80, <7)

I think this is exactly what you're saying. In this case the actual options for Segment don't line up with anything, but that's ok because my ETL processes ensure that only the "<25, 25-30, >30" segments are associated with the "BMI" measure. I get this.

I think I'm still caught up on how to actually extract the "distinct" on a setup like this using either SQL or BI Tools because the time periods are not as distinct as the flu shots. You're right, you wouldn't have 2 flu shots in the same time period, but you could have 15 BMIs in the same time period. How do you get distinct patients when that's the situation? A "group by" statement on the patient doesn't give me a distinct if the other fields in the selection aren't the same.

Yep. It looks like you'll have to pull those last records out to another snapshot table or materialized view.

I am late to the party..........

Not the most elegant solution, but would this conditional count work?

COUNT(DISTINCT
                          Case
                            When MeasureName = 'BMI' and
                                    SegmentName  '< 25'   and
                                    ServiceDate between @PeriodStart and @PeriodEnd THEN PatientKey
                          Else NULL
                          END)

????