Maven Hospital Record Analysis

Understanding our patients' demographics and geographical distribution is crucial, as it provides insights into their needs and challenges. Equally important as analyzing seasonal trends in disease patterns within the medical domain, which helps us anticipate and prepare for peak periods of specific illnesses.

The objective is to provide a high-level KPI report for executives based on patient records regarding recent performance.

My analysis focuses on KPI performance compared to the previous year and addresses the following questions, accompanied by a self-explanatory dashboard:

• I have drafted following questions to lead my analysis
• What are the trends in the current and previous years?
• Where are our customers coming from, and who are they, including their encounter types?
• What is the trend in readmitted patients and their encounter types?
• What are the trends in encounter frequency and encounter duration?
• What is the average number of encounters per patient?
• How do procedures compare to the previous year in terms of trends?
• What are the reasons for encounters and procedures?
• What are our busiest hours and days based on encounters and procedures?
• What is the trend in insurance claim amounts?
• How is procedures coverage trending?
• What is the breakdown of insurance claim amounts and percentages by payers?
• What are the common reasons for insurance claims?

This report aims to provide clear insights into healthcare operations and performance metrics, supporting data-driven decision-making and continuous improvement strategies.

The Dasboard provides Low to No Interactivity.

Technical Part:

It took me some time to understand the keywords since this is my first project in the Medical Domain. Forming relations between the tables to create the semantic model was crucial.

Data Transformation:

The dataset provided with correct data types, requiring minimal cleaning. However, I removed the "Stop" column from Encounters and Procedures after obtaining the "Start Time" and "Duration".

Details of the Model:

I created a dynamic DAX date table spanning from the minimum date to the maximum date from the Encounter tables to accommodate future refreshes (1/1/2011 - 31/12/2022).

The initial model setup was based on the provided tables.

To analyze the frequency of patient visits per year (Encounters in a year), I created summarized and several disconnected tables during the analytical process.

The final model is structured to facilitate filtering by year. To ensure clarity, I created a year table using unique years up until the last completed year (if the current year's last month number is 12, return the current year; otherwise, return the previous year).

To categorize visit times effectively, I created the EncounterTimes&Order table to establish custom bins based on Encounter times. This is the final result :