1

We receive patient data that includes case level info (hospital id, patient id, length of stay, surgery date) as well as surveys/educational material that the patient completed as well as their responses to the surveys. We are developing a star schema data model to store this data. Below is an example of raw data that we receive in the structure we receive it: In summary there are three tables. The case data table that store high level case data, the content data that contains the different material that the patient completed. Within this table there are scores for the surveys only. And lastly the survey responses table that contains the question and answer from the survey.

There are two main use cases for this data. First is aggregating length_of_stay and app_logins to find averages, min, max, etc. Second, is aggregating scores in the content data to compare preop and postop scores. The content responses table is used to generate the scores, and some users like to see if there are patterns in responses, such as understanding whether 'Is level of pain consistent with low or high survey scores?' The content responses only contains data for surveys, not the educational material found in the content table.

enter image description here

Some of the concerns is that:

  1. we have a factless fact (FactSurveyResponses) that is mainly used for displaying questions/answers for a given patient survey. This is probably OK overall but begs the question is there a better way to store this information?
  2. Some columns, like surgery_date, are used as filters on a Power BI dashboard, so they should be available on each fact to properly filter out the data; however, this seems like it would bloat the facts on the database side because it is unlikely that someone would want to see how a patient responded on a certain date (at least no one has asked for this before).

I am hoping to get some feedback on whether this is a suitable model, or if there are any adjustments or other considerations someone would make. I attached an entity relationship diagram of the model we anticipate to build.

enter image description here

Improve this question
2
  • Do you have any idea of the volumetry of data that will be in that data-warehouse ? Do you plan to use any SSAS to hold your dimensions of analysis ?
    – Hybris95
    Dec 13, 2021 at 10:19
  • This will not be a massive DB. Likely up to 20M rows in the FactContentResponse as the largest table. No plans on using SSAS at the moment.
    – CandleWax
    Dec 13, 2021 at 15:15

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.