__STYLES__

Maven - Hospital Challenge

Tools used in this project
Maven - Hospital Challenge

Maven - Hospital Challenge

About this project

Project Goal

Analyze and build reports around patient, encounter, and procedure data to provide KPI and operational performance reports, and to uncover deeper insights from the dataset.

Key Steps

1. Data Exploration

Goals achieved in this step:

  • Broke down the analysis into non-admission encounters, Admission and Readmission encounters, and Procedures.
  • Identified key metrics and attributes for each analysis section:
    • Attributes from patient information, including key demographics such as Gender, Age, Age Group, Race, and Marital Status.
    • Attributes from encounters and procedures, including encounter class, encounter description, procedure description, and procedure reasons.
    • General metrics for encounters and procedures, including counts, costs, rates, Length of Stay (LOS), and averages.
    • Special metrics for analyzing readmissions and mortalities, including the admission range, dates between the current visit and previous discharge, and dates between death and each admission.

2. Data Preparation

Goal: Prepare the data for an effective data model for analysis.

Key steps taken:

  • In Power Query:
    • Built dimension tables, including encounter class, age, age group, and date.
    • Merged encounter and patient tables to get the age at encounter and the date differences between death and each encounter.
    • Extracted the hour of each encounter visit.
    • Grouped procedure table to get procedure line cost totals for each encounter, then merged this into the encounter table.
  • In Power BI Desktop:
    • Built a calculated table from the encounters table for admissions and readmissions, obtaining the previous discharge date for each admission record using the Offset function in DAX.
    • Calculated LOS by minutes for non-admission encounters, and by days for admission encounters. Then built LOS bins for different minute ranges.
    • Built an admission type calculated column to identify initial admissions and readmissions.
    • Built a mortality indicator calculated column for mortality rate calculation.
    • Built all parameter tables for dynamic analysis.

3. Data Model

With the previous steps and preparations, the data model can be established for dashboard building.

Special consideration was given to the procedures table. Although there are possible links to dimension tables, the only relationship set is to the encounter table to ensure the correct linkage between encounters and procedures.

4. Logical Considerations While Building the Dashboard

Two major considerations were made when building the logic into the analysis:

  • Year range selection:

The dashboard is using fiscal year as the years range instead of calendar year.

  • Admissions:

By definition, only the Inpatient class includes overnight visits, which should be identified as admissions. However, data showed that visits under different encounter classes also had overnight stays, with some showing hundreds of days of stay. Thus, admissions and non-admissions were built by looking at the days stayed in the hospital rather than only the encounter classes.

  • Initial Admissions and Readmissions:

For readmission rate calculation, initial admissions and readmissions need to be identified. Initially, only these two types were considered based on visit dates. However, many visits were regular daily visits over consecutive days and should not be counted as initial admissions or readmissions. After careful review, these were categorized as a single admission for readmission rate calculation, while still counted as individual visits in the general admission settings.

Additional project images

Discussion and feedback(14 comments)
comment-1663-avatar
Jevon Williams
Jevon Williams
2 months ago
Awesome!

comment-1673-avatar
CALEB CHEMLANY
CALEB CHEMLANY
2 months ago
Hello Mark, I like your report. It is so detailed. Do you mind if you can share with me the .pbix file. This is my email: chemlany@outlook.com . Thank you.

comment-1686-avatar
Naveen Kumar Kuchani
Naveen Kumar Kuchani
2 months ago
Hello Mark, Loved your Dashboard ♥️. Could you please send me the PBIX file to my email id, naveenkuchani3697@gmail.com Thanks in advance.

comment-1739-avatar
Sameh Mahmoud
Sameh Mahmoud
about 2 months ago
Very Insightful

comment-1741-avatar
Hiqmat Abdusalam-Olajire
about 2 months ago
Hi Mark! This is awesome. A well detailed report. I am new in data analytics field. I've got few questions pls: How did you calculate the admissions and readmissions count? Do you mind sharing your pbix file? My email ID is abdusalamhiqmat@gmail.com. Thanks in anticipation.

comment-1758-avatar
Elena Genova
Elena Genova
about 2 months ago
WOw!!

comment-1861-avatar
Danish Bashir
about 1 month ago
Hello Mark, I like your report. It is so detailed. Do you mind if you can share with me the .pbix file. This is my email: imdanishbashir@gmail.com . Thank you.

comment-1900-avatar
Ayushi Dadhich.
Ayushi Dadhich.
24 days ago
Hi Mark ! This is so detailed and creative work. I am also learning Power Bi through Maven's Challenges. Can you review my dashboards as well and suggest me some tips to improve?
2000 characters remaining
Cookie SettingsWe use cookies to enhance your experience, analyze site traffic and deliver personalized content. Read our Privacy Policy.