I am working on designing a healthcare related data model. It has to do with sepsis patients in the inpatient hospital setting.
My question is related to the design -- specifically how to break up the fact tables. I realize that fact tables and dimension tables are one in the same in tabular data models, but for the purpose of my model, fact tables are ones that have data concerning actions.
I will have several dimension tables for things like Department, Location, and Discharge Date.
For the fact tables, this is where it gets tricky. There are some patients who are suspected of having sepsis who may or may not get sepsis but there is data that we track for these patients (performance data). There are patients who get sepsis that we may or may not have suspected and there is data that we track for these patients (outcome data). In addition, there are scores we track for all patients and alerts we track for patients who the software suspects have sepsis (predictive).
What I'm thinking of doing is making the patients table ("Encounters") a dimension table that sits between the other dimensions and the fact tables. Then, there would be a "Suspect Sepsis" fact table that contains all of the performance data (1 row for each patient and columns for whether they met or didn't meet the performance initiatives). The second fact table would be "Diagnose Sepsis" and these would be all the patients who had an actual sepsis diagnosis and would be one row per patient and would have columns for whether each outcome was met. The "Scores" fact table would be 1 row per score (there are over 8,000,000 scores in my dataset). I would not use this fact table directly for reporting. It would need some summarization via a calculated table(s). Lastly, the "Alerts" fact table would have 1 row for each alert (currently around 2,000,000 alerts in the dataset). Again, this table isn't super useful without some summarization.
The types of questions / reports that we want to try to use this data model for are things like:
(1) How do the performance initiatives in the Suspect Sepsis data impact the outcomes in the Diagnose Sepsis data?
(2) For patients who didn’t have an alert, how did their outcomes compare with those that did have an alert?
(3) What were the score(s) at or around a particular event in the Encounter?
I want to be cognizant of best pratices around granularity and I want the data model to meet the reporting needs. Is my fact table strategy appropriate? I feel like maybe it is deseigned more for "normalization" (in the relational data sense of the word) than reporting. I suspect I will end up with quite a few calculated tables to bring this data together for a more holistic patient-centered view, but not entirely sure. Am I on the right track? Is it appropriate to layer the dimensions (by puting Encounters between the other dimensions and the facts)?
