Healthcare Data Analysis using SQL

Healthcare data analysis plays a vital role in enhancing patient care, improving hospital efficiency and managing financial operations. By utilizing Power BI, healthcare professionals and administrators can gain valuable insights into patient demographics, medical conditions, hospital performance, and revenue trends.

In this article, we will explore a healthcare dataset and outline the step-by-step process of creating a data analysis using SQL then create a Power BI dashboard that provides essential healthcare insights. The dashboard includes key performance indicators (KPIs), stacked column charts, a donut chart, and interactive slicers to help stakeholders to effectively analyze and interpret healthcare data.

Healthcare Dataset

The dataset used for this analysis consists of 30,000 patient records containing detailed information on hospital admissions, treatments, and billing. The following key attributes are included in the dataset:

1. Patient Information

• Patient_ID – A unique identifier assigned to each patient.
• Name – The full name of the patient.
• Age – The patient's age at the time of hospital admission.
• Gender – The patient's gender, categorized as Male, Female, or Other.

2. Medical Information

• Diagnosis – The medical condition diagnosed by the doctor.
• Treatment – The prescribed treatment or procedure for the patient’s condition.
• Doctor – The name of the consulting doctor treating the patient.

3. Hospital Information

• Hospital_Name – The name of the hospital where the patient was treated.
• Admission_Date – The date on which the patient was admitted to the hospital.
• Discharge_Date – The date on which the patient was discharged from the hospital.

4. Financial Information

• Bill_Amount – The total cost of treatment for the patient.

This dataset comprehensively overviews patient care, medical conditions, and hospital performance. It allows stakeholders to track trends and make data-driven decisions in the healthcare sector.

Exploratory Data Analysis Using SQL

Let's perform some SQL queries to get quick insights are defined below:

1. Get all patients with Kidney Disease

Retrieve the details of all patients diagnosed with Kidney Disease.

SELECT Patient_ID, Name, Age, Gender, Treatment, Doctor, Bill_Amount
FROM healthcare_data
WHERE Diagnosis = 'Kidney Disease';

Output:

Explanation: This query selects the relevant details of patients diagnosed with 'Kidney Disease' by filtering the rows where the Diagnosis column matches 'Kidney Disease'. The selected columns include patient ID, name, age, gender, treatment, doctor, and the bill amount.

2. Total bill amount by hospital

Calculate the total bill amount generated by each hospital.

SELECT Hospital_Name, SUM(Bill_Amount) AS Total_Bill
FROM healthcare_data
GROUP BY Hospital_Name;

Output:

Explanation: This query groups the data by Hospital_Name and calculates the total bill amount (SUM(Bill_Amount)) for each hospital. The result provides the sum of bill amounts for every hospital in the dataset.

3. Patients discharged after a specific date

List the patients who were discharged after January 1, 2024.

SELECT Patient_ID, Name, Diagnosis, Discharge_Date
FROM healthcare_data
WHERE Discharge_Date > '2024-01-01';

Output:

Explanation: This query filters the data to retrieve patients whose Discharge_Date is later than January 1, 2024. The output includes patient ID, name, diagnosis, and discharge date for each relevant patient.

4. Average age of patients by treatment

Calculate the average age of patients receiving each type of treatment.

SELECT Treatment, AVG(Age) AS Average_Age
FROM healthcare_data
GROUP BY Treatment;

Output:

Explanation: This query groups the data by Treatment and calculates the average age (AVG(Age)) of patients receiving each type of treatment. It helps in understanding the age distribution for each treatment type.

5. Patients with the highest bill amount

Find the top 5 patients with the highest bill amounts.

SELECT Patient_ID, Name, Bill_Amount
FROM healthcare_data
ORDER BY Bill_Amount DESC
LIMIT 5;

Output:

Explanation: This query sorts the data by Bill_Amount in descending order (DESC), and limits the result to the top 5 records. This helps identify the patients who have the highest treatment costs.

Advance Data Analysis With Dashboard Creation

A well-designed Power BI dashboard helps visualize and interpret healthcare data efficiently. The dashboard consists of KPIs, stacked column charts, a donut chart, and slicers that provide actionable insights.

1. Key Performance Indicators (KPIs)

KPIs are essential for measuring hospital performance and financial health. The two primary KPIs in this dashboard are:

Total Sales Amount

• This KPI represents the total revenue generated by all hospitals.
• It is calculated by summing up the Bill_Amount across all patients.
• Monitoring total revenue helps hospital administrators track financial performance and identify revenue growth trends over time.

Total Patients

• This KPI shows the total number of patients treated.
• It is determined by counting the unique Patient_IDs in the dataset.
• Understanding patient volume is critical for resource allocation, hospital staffing, and facility expansion planning.

2. Bill Amount by Hospital Name (Stacked Column Chart)

• This visualization displays the total billing amount per hospital in a stacked column chart format.
• The X-axis represents different hospitals.
• The Y-axis shows the total revenue (sum of Bill_Amount) for each hospital.
• Each bar in the chart represents a hospital, with the height indicating the revenue generated.

This chart helps in:

• Identifying high-revenue hospitals and comparing them with lower-performing facilities.
• Understanding how different hospitals contribute to overall revenue.
• Making strategic financial decisions regarding hospital funding and operational improvements.

3. Patients by Diagnosis (Stacked Column Chart)

• This visualization shows the number of patients diagnosed with different medical conditions.
• The X-axis represents various diagnoses.
• The Y-axis shows the count of patients diagnosed with each condition.
• Each bar represents a specific medical condition, and its height indicates the number of patients affected.

This chart helps in:

• Identifying the most common medical conditions in a hospital or across multiple hospitals.
• Understanding which illnesses require more resources, specialized treatments, and trained professionals.
• Assisting in disease trend analysis and outbreak prediction.
• For example, if Hypertension and Diabetes have the highest number of patients, hospitals may focus on preventive healthcare programs to manage these conditions effectively.

4. Patients by Hospital Name (Stacked Column Chart)

• This visualization displays the distribution of patients across different hospitals using a stacked column chart.
• The X-axis represents different hospitals.
• The Y-axis shows the count of patients treated at each hospital.
• Each bar represents a hospital, and its height indicates the number of patients admitted.

This chart helps in:

• Analyzing hospital capacity and patient distribution trends.
• Identifying hospitals that treat the most patients and understanding why they attract more admissions.
• Assisting in resource allocation and infrastructure development for high-demand hospitals.
• If one hospital consistently has the highest patient count, it may need more staff, beds, and facilities to manage the workload efficiently.

5. Gender Distribution (Donut Chart)

• A donut chart is used to represent the gender distribution of patients in the dataset.
• The chart is divided into three segments: Male, Female, and Other.
• Each segment represents the percentage of total patients in each gender category.

This visualization helps in:

• Understanding gender-based healthcare trends.
• Identifying whether certain medical conditions are more common in a specific gender.
• Evaluating gender inclusivity in healthcare services.
• For example, if more females are admitted for specific diagnoses, hospitals can implement targeted screening and awareness programs to address these conditions.

6. Slicers (Filters for Dynamic Analysis)

Slicers allow users to interactively filter data and explore specific insights in the dashboard. The two main slicers included are:

1. Hospital Name Slicer

• Enables users to filter the dashboard by a specific hospital.
• Helps in analyzing hospital-specific trends, financial performance, and patient demographics.

2. Admission Date Slicer

• Allows users to filter data for a specific time range.
• Useful for tracking seasonal trends in patient admissions, disease outbreaks, and financial performance.
• By using slicers, stakeholders can drill down into specific time periods or hospitals to gain granular insights into patient care and hospital operations.

Conclusion

Overall, Power BI offers a robust platform for analyzing healthcare data by enabling stakeholders to effectively visualize key metrics and trends. The healthcare analytics dashboard discussed in this article helps hospitals, administrators, and policymakers monitor financial performance through KPIs like Total Sales Amount and Total Patients, identify high-revenue hospitals and common medical conditions using stacked column charts, track patient distribution across hospitals to optimize resource allocation, and understand gender-based healthcare patterns through a donut chart. Interactive features like slicers allow users to filter data by hospital name and admission date for deeper analysis. These insights empower healthcare professionals to enhance hospital operations, improve patient care, and drive better overall healthcare outcomes.