Healthcare Semantic Model

[Clowson]

Hello all, 

I have been having some difficulty trying make the most efficient semantic model I can for my report. I'm finding a classic star schema to be challanging due to the content of my data. I'll do my best to summarize the organization of the data, in hope it can generate some ideas. 

General list of tables:

Person_MRN (MRN as PK and Person_ID as FK)

Person (Person_id as PK and wider table about the person)

Condition_occurrence (features Person_ID, Visit_occurrence_id, diagnosis vist info)

procedure_occurrence (features Person_ID, Visit_occurrence_id,  procedural vist info)

visit_occurrence (visit_occurrence_id as PK and care_site_id as FK)

care_site (Care_site_id as PK)

The design of my report is just to identify the distinct count of MRN with a filter selected condition and/or procedure, and the care site breakdown of those meeting the filter requirments. 

I am asking to map MRN -> condition_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

but I also need MRN -> procedure_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

since patients have more than one condition and/or procedure, I have to map through the  visit_occurrence_ID's in order to capture the unique visits. There could even be a case where a distinct MRN with a distinct procedure was seen at more than one care site. 

I tried to make views to coalesce the information, but I haven't found the perfect fit yet. Any thoughts are appreciated!

[lbendlin · Accepted solution]

I am asking to map MRN -> condition_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

but I also need MRN -> procedure_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

doesn't that defeat the purpose of the de-identification ?

[v-sathmakuri]

Hi @Clowson ,

I hope the information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you.

[v-sathmakuri]

Hi @Clowson ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sathmakuri]

Hi @Clowson ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @WishAskedSooner  and @lbendlin  for prompt response.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If any of the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[lbendlin]

what's a MRN? Is that an event or a property?

[Clowson]

MRN = Primary ID for a patient within a system. Patient_ID is a de-identified version for tracking throught the rest of the tables 

[lbendlin · Accepted solution]

I am asking to map MRN -> condition_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

but I also need MRN -> procedure_occurrence.person_id -> visit_occurrence.visit_occurrence_id -> Care_site.care_site_name

doesn't that defeat the purpose of the de-identification ?

[Clowson]

Nope, thats all behind the scenes for calculations where as the end user visuals are distinct counts based off of filters like what condtion and age range a population might be in. 

[Clowson]

It might be confusing that I want to even incorporate the MRN table, but the Person_ID's are imperfect and I am only interested in receiving counts for those that have a MRN.

[WishAskedSooner · Accepted solution]

Here are some guidelines to follow when designing a star schema, especially since you are dealing with healthcare data:

1. Your Fact table(s) should be normalized

2. Your Dimension tables should be de-normalized

Given the nature of healthcare data, I would almost say you absolutely need a EAV style Fact table. Relational database purists pooh-pooh EAV, but with the proper knowledge, you will come to really appreciate just how versatile they are. The number of attribute columns will be dictated by the number of Dimension tables. See below.

How many Dimension tables should you have? Six, a dozen? Well, you need to consider how users will want to slice the data and how heirachical those slices are.

The short rule is you need a Dimension table for each slicer e.g. Date, Product, Geography, etc, but you don't want seperate Dimension tables for Product Category and Product Subcategory because they live in the same slicer as a hierarchy.

The most intuitive example is a Date dimension table which has any number of hierarchies from day, week, month, quarter, etc. You de-normalize all that hierarchy into a single Date dimension table. The goal is to do the same for all your other dimension tables.

Now, it sounds like you may be dealing with many-to-many relationships (MMR) between your tables. In some contexts this is unavoidable, and knowing how to properly design a start schema for them is really important. The topic is far too complex to outline over a forum. My suggestion is you acquire a copy of The Definitive Guide to DAX. They have an entire chapter dedicated to MMRs and the various techniques available.

HTH