Introduction
Our hospital management system began with five interconnected tables storing operational data:
- Patients - Demographics and contact information
- Doctors - Provider profiles and specializations
- Appointments - Scheduling and visit records
- Admissions - Inpatient stay information
- Bills - Financial transactions and payment tracking
The Integration Challenge
Raw data rarely tells a complete story. Our appointment records contained timestamps but lacked easy date grouping. Status fields had inconsistent formatting ("cancelled" vs "Cancelled" vs "CANCELLED"). Most critically, connecting appointment data to patient demographics and doctor specializations required three separate table joins.
For financial analysis, understanding a patient's complete billing history meant traversing from patients → admissions → bills, aggregating along the way.
The Solution: Build staging views, making the data consistently accessible for all downstream analysis
View 1: Appointments_Enriched (Operational Hub)
- Combines the most frequently accessed data points
- Eliminates repetitive join logic across reports
- Maintains real-time accuracy (dynamic view updates automatically)
Key Decision: Used joins to ensure data integrity. Appointments without valid patient/doctor references are excluded, preventing corrupt data from polluting reports.
View 2: Patient_Balances (Financial Lens)
Enables quick identification of collection priorities
Supports cash flow forecasting and bad debt analysis
Key Decision: Aggregated at "PatientID" level rather than admission level.
view 3: Doctor_Monthly_Metrics (Performance Tracker)
In this post, I’ll walk through the key steps I followed connecting to data, cleaning it, modelling it, and designing a clear dashboard highlighting practical decisions that helped shape the final report.
1. Connecting to the Data
The project began by bringing multiple data sources into Power BI Desktop. These included structured tables that contained records, lookup information, and date related fields. Using Power BI’s Get Data interface, the sources were imported in Import Mode connect to PostgreSQL database.
Once the tables were loaded, I confirmed:
- Data types were detected correctly
- Column names were consistent
- Tables aligned logically (fact vs. dimension)
This initial step set the foundation for all transformations and modelling work.
2. Cleaning & Transforming the Data
Most of the data preparation happened in Power Query, where I carried out cleaning tasks before loading tables into the model.
Key cleaning steps included:
- Renaming columns to proper text.
- Merging columns, such as first and last name into full names.
- Fixing incorrect data types (e.g., numbers stored as text, date/time inconsistencies).
- Normalizing categories so that values followed a consistent naming convention.
- Filtering out invalid or missing records that could distort metrics by replacing.
- Removing duplicates to avoid inflated total.
- Trimming spaces and texts.
These cleaning procedures ensured that the dataset was accurate, consistent, and analytics ready.
3. Data Modelling Choices
I used a star schema design to keep the model simple, efficient, and easy to scale.
The model included:
- Fact tables holding transactional or event level records.
- Dimension tables for people, categories, products, locations, and calendar data.
- A dedicated Date table, enabling accurate time-based analysis.
Relationships were kept single-directional except where needed for specific behaviours, and unused columns were removed to keep the model lean.
4. Dashboard Design & Visual Layout
With the data clean and model optimized, I designed an interactive dashboard intended to provide both summaries and insights.
The dashboard included:
- KPI cards showing totals and key performance indicators
- Trend charts to show how activity changed over time
- Category comparisons using bar and column charts
- Detailed tables for users who want records
- Slicers and filters for month, doctor's name, and specialization
5. Final Dashboard
- Home screen with KPIs
- Trend charts
- Category breakdowns
- Detailed tables
- Filters panel
Dashboards
Conclusion
This Power BI project successfully transforms raw data into clear, actionable insights. Through careful data cleaning, a well structured star schema model, and thoughtfully designed visuals, the dashboard provides users with an intuitive way to explore trends and compare performance. The result is a clean, interactive, and reliable report that supports quick understanding and informed decision making.
This was done as a group.
co- authors:
1. - Hilda Chepkirui
2. - Asha Siyat
3. - Saciid Shaakaal
4. - Samuel Irungu
Top comments (0)