Build This Project · Portfolio Guide
Healthcare Analytics Dashboard — Complete Project Guide
Every Monday, the operations head of a mid-sized hospital asks the same three questions: How long did patients stay last week? How many came back within a month of going home? Which department is under pressure? A healthcare analytics dashboard answers those questions on one screen. Building one is a strong portfolio project because it forces you to define metrics carefully, which is exactly what employers test in data analyst interviews.
This guide builds a hospital operations dashboard from raw admission records to a finished Power BI or Tableau report.
Step 1: Pick a data source
| Source | What you get | Access |
|---|---|---|
| Kaggle hospital and patient datasets | Many synthetic or anonymised admission tables, good for practice | Free account; check each dataset's licence and whether it is synthetic |
| CMS Provider Data (data.cms.gov) | US hospital-level quality measures, including readmission and patient-experience data | Open download |
| MIMIC-IV on PhysioNet | De-identified ICU and hospital records with real clinical detail | Credentialed: you must register, complete a human-subjects training course and sign a data use agreement |
| Synthea | Open-source generator of realistic synthetic patient records | Free; you generate the data yourself |
For a first version, use one admissions-level table (one row per hospital stay). Indian public data at patient level is rarely available because of privacy rules, so say clearly in your project that the data is synthetic or from another country. That honesty is a plus in interviews, not a weakness.
Step 2: Define each KPI before you chart it
Many portfolio dashboards look polished but use vague metrics. Write the formula for each KPI first:
| KPI | Formula | Watch out for |
|---|---|---|
| Average length of stay (ALOS) | Total inpatient days / number of discharges | Same-day discharges (0 days); very long outlier stays pull the average up, so also show the median |
| 30-day readmission rate | Discharges followed by another admission of the same patient within 30 days / all discharges | Official definitions exclude planned readmissions (for example, scheduled chemotherapy) and deaths; say which definition you used |
| Bed occupancy rate | Occupied bed-days / available bed-days × 100 | Needs bed capacity per ward, which many datasets lack |
| Admissions per day | Count of admissions grouped by date | Weekday patterns; show a 7-day moving average |
| Emergency wait time | Median minutes from arrival to first doctor assessment | Use the median, as waits are heavily skewed |
Step 3: Calculate the KPIs in SQL
Here is a small admissions table and a query that computes discharges, ALOS and the 30-day readmission rate per department. The key idea is the LEAD window function: for each stay, it fetches the same patient's next admission date.
CREATE TABLE admissions (
admission_id INTEGER PRIMARY KEY,
patient_id TEXT,
department TEXT,
admit_date TEXT,
discharge_date TEXT
);
INSERT INTO admissions VALUES
(1,'P01','Cardiology','2026-01-02','2026-01-07'),
(2,'P02','Orthopaedics','2026-01-03','2026-01-05'),
(3,'P01','Cardiology','2026-01-20','2026-01-24'),
(4,'P03','General Medicine','2026-01-05','2026-01-08'),
(5,'P04','Cardiology','2026-01-10','2026-01-12'),
(6,'P03','General Medicine','2026-03-01','2026-03-04'),
(7,'P05','Orthopaedics','2026-01-15','2026-01-21'),
(8,'P02','Orthopaedics','2026-01-25','2026-01-27');
WITH ordered AS (
SELECT *,
julianday(discharge_date) - julianday(admit_date) AS los_days,
LEAD(admit_date) OVER (PARTITION BY patient_id ORDER BY admit_date) AS next_admit
FROM admissions
)
SELECT department,
COUNT(*) AS discharges,
ROUND(AVG(los_days), 1) AS avg_los_days,
SUM(CASE WHEN next_admit IS NOT NULL
AND julianday(next_admit) - julianday(discharge_date) <= 30
THEN 1 ELSE 0 END) AS readmit_30d,
ROUND(100.0 * SUM(CASE WHEN next_admit IS NOT NULL
AND julianday(next_admit) - julianday(discharge_date) <= 30
THEN 1 ELSE 0 END) / COUNT(*), 1) AS readmit_rate_pct
FROM ordered
GROUP BY department
ORDER BY readmit_rate_pct DESC, department;Result (run in SQLite 3.45):
department | discharges | avg_los_days | readmit_30d | readmit_rate_pct
Cardiology | 3 | 3.7 | 1 | 33.3
Orthopaedics | 3 | 3.3 | 1 | 33.3
General Medicine | 2 | 3.0 | 0 | 0.0Check it by hand: patient P01 left Cardiology on 7 January and returned on 20 January, 13 days later, so that counts. P03 left on 8 January and returned on 1 March, 52 days later, so it does not. julianday is SQLite syntax; in MySQL use DATEDIFF(next_admit, discharge_date), and in SQL Server DATEDIFF(day, discharge_date, next_admit).
A design choice to note in your write-up: this query credits the readmission to the department of the first stay. Some hospitals credit the department of the second stay instead. Either is fine if you state it.
Step 4: Build the measures in Power BI
Add a calculated column or Power Query step that stores the readmission flag (1 or 0) on each row, using the same logic as the SQL. Then the measures are short:
Discharges = COUNTROWS ( Admissions )
ALOS (days) =
AVERAGEX ( Admissions,
DATEDIFF ( Admissions[admit_date], Admissions[discharge_date], DAY ) )
Readmissions 30d =
CALCULATE ( COUNTROWS ( Admissions ), Admissions[readmit_30d_flag] = 1 )
Readmission Rate =
DIVIDE ( [Readmissions 30d], [Discharges] )DIVIDE returns blank instead of an error when a filter leaves zero discharges. Format the rate as a percentage. If Power BI is new to you, start with this beginner's guide to Power BI; Tableau users can follow building advanced dashboards in Tableau.
Step 5: Lay out the dashboard
- Top row, KPI cards: discharges, ALOS, median LOS, 30-day readmission rate, bed occupancy. Show each against the previous period.
- Middle, trends: a line chart of weekly admissions with a moving average, and monthly readmission rate.
- Bottom, breakdowns: a bar chart of ALOS and readmission rate by department, and a table of the top diagnoses by readmissions.
- Filters: date range, department, admission type (emergency or planned) and payer type (for example cash, insurance, government scheme).
Keep colours meaningful: one accent colour for "needs attention", grey for everything else. A busy hospital manager should see the problem in five seconds.
Step 6: Write up the insights
A dashboard alone is not the project; the findings are. Write three or four observations from your own data, each with a suggested action. For example: "Orthopaedics has the longest ALOS on weekend admissions; check whether discharge paperwork waits for Monday rounds." Never present a pattern as a cause without evidence; say "is associated with" when that is all you know.
Also note privacy: real patient data in India falls under the Digital Personal Data Protection Act, 2023, so production dashboards show aggregated figures and restrict access to patient-level views.
Step 7: Present it in your portfolio
- A one-paragraph problem statement, the data source and its limits.
- A KPI definition table like the one in Step 2.
- Screenshots or a published link, plus the SQL and the Power BI file on GitHub.
- The insights with recommended actions.
Pair this with one project from another domain, for example the sales analytics dashboard or the HR attrition analysis, so your portfolio shows range. For background on roles in this sector, read analytics roles in the healthcare sector.
FAQs
Do I need medical knowledge for this project?
No, but learn the basic terms: admission, discharge, length of stay, readmission, OPD and IPD, and diagnosis codes such as ICD-10. Reading how hospitals define each metric is part of the work.
Power BI or Tableau for a healthcare dashboard?
Either works. Choose the tool used in the job posts you are applying to. The KPI logic and SQL are the same.
Can I use real patient data from a hospital I know?
Not without formal permission and de-identification. Use public or synthetic data for a portfolio project.