Healthcare Analytics Dashboard in Power BI Using PostgreSQL

Nicholus Rana — Mon, 08 Dec 2025 17:29:08 +0000

This project demonstrates how to build a complete Healthcare Analytics Dashboard in Power BI using PostgreSQL (local or Aiven). It covers database connection, data import, cleaning, modeling, measures, dashboard design, and scheduled refresh.

1. Overview

The solution provides an end-to-end analytics workflow for healthcare operations, financials, and patient insights. It uses three curated PostgreSQL staging views and a clean Power BI star-schema model.

2. Prerequisites

Tools Required:

Power BI Desktop
PostgreSQL (Local or Aiven)
Npgsql PostgreSQL Connector
SQL client (pgAdmin or DBeaver)

Data Source:

Appointments
Patients
Doctors
Admissions
Bills

3. Connecting Power BI to PostgreSQL

Local PostgreSQL

Server: localhost:5432
Database: your database name
Authentication: Database
Encryption: optional

Power BI Steps:

Get Data → PostgreSQL
Enter server and database credentials → Connect

Aiven PostgreSQL

Download and install the Aiven CA certificate into Windows Trusted Root.
Power BI connection settings:
- Server: <yourhost>.aivencloud.com:<port>
- Database: defaultdb
- Encryption: on
- Authentication: Database

4. PostgreSQL Staging Views

Views Imported into Power BI:

1. Appointments_Enriched

Contains:

AppointmentID
PatientID
DoctorID
AppointmentDate
Normalized Status
Patient Name
Doctor Name
Specialization
AppointmentDateOnly (Date extracted)

2. Patient_Balances

Aggregates per patient:

TotalBilled
TotalPaid
TotalOutstanding

3. Doctor_Monthly_Metrics

For each doctor per month:

TotalAppointments
CancelledAppointments
CancellationRate

5. Data Cleaning in Power Query

Steps:

Rename columns for readability
Set correct data types (Date, Decimal, Text)
Trim and clean text fields
Replace blanks with nulls
Add helper columns: Year, Month, Year-Month

Close & Apply changes.

6. Data Modeling

Fact Tables

Appointments_Enriched
Doctor_Monthly_Metrics
Patient_Balances

Dimensions

DimDate (generated in Power BI)
Patients
Doctors

Key Relationships

Appointments_Enriched → DimDate (AppointmentDateOnly → Date)
Appointments_Enriched → Patient_Balances (PatientID → PatientID)
Appointments_Enriched → Doctor_Monthly_Metrics (DoctorID + MonthStart)

DimDate is marked as the official Date Table.

7. Dashboard Pages

Page 1 — Executive Overview

KPIs: Total Appointments, Cancellation Rate, Total Billed, Total Outstanding
Trend chart: Appointments by Month
Bar chart: Top 10 Specializations
Filters: Year, Month

Page 2 — Operations & Scheduling

Upcoming Appointments Table
Heatmap: Hour of Day × Weekday
Bar chart: Cancellation Rate by Doctor

Page 3 — Financials

KPIs: Total Billed, Total Paid, Total Outstanding
Clustered Column + Line Chart:
- Columns: Billed, Paid
- Line: Outstanding
- Axis: Year-Month
Top 20 Patients by Outstanding Balance

Page 4 — Doctor Performance

KPI: Avg Appointments per Doctor
Doctor Monthly Metrics Table
Scatter Plot: Appointments vs Cancellation Rate

Page 5 — Patient Insights

Visit Distribution Histogram
Demographic Slicers (Insurance, Segment, Gender, Region)
Patient Balances Table with drillthrough to appointments

8. Publishing and Refresh

Publishing:

Power BI Desktop → Publish → Select workspace

Scheduled Refresh:

Power BI Service → Dataset Settings → Enter credentials → Configure refresh frequency
Aiven: Ensure firewall allows Power BI IPs
Local PostgreSQL: Install Personal Gateway

Is Excel Still Relevant in the Era of Power BI and Python?

Nicholus Rana — Sun, 05 Oct 2025 09:58:12 +0000

📊 Is Excel Still Relevant in the Era of Power BI and Python?

In today’s data-driven world, tools like Power BI and Python have revolutionized how we analyze, visualize, and automate data workflows. But does that mean Excel — the long-standing favorite — is becoming obsolete?

Let’s find out.

💼 Excel: The Classic Powerhouse

For decades, Microsoft Excel has been the go-to tool for data entry, analysis, and reporting. Its simplicity, flexibility, and wide availability have made it a global standard in business and education alike.

Even today, millions of professionals rely on Excel for:

Budgeting and forecasting
Data cleaning and manipulation
Quick visualizations and dashboards
Reporting and data tracking

Excel’s familiar interface and low learning curve make it unbeatable for quick, everyday tasks.

🚀 The Rise of Power BI

Power BI, Microsoft’s advanced analytics and visualization tool, builds upon Excel’s foundations but takes things to a whole new level. It allows users to connect to multiple data sources, automate refreshes, and create interactive dashboards in minutes.

Key advantages of Power BI:

Real-time data connections
Automated updates
Interactive and shareable reports
AI-driven insights

For organizations managing large datasets and requiring collaboration, Power BI offers scalability and sophistication Excel alone cannot match.

🐍 Python: The Analyst’s Secret Weapon

Python has emerged as the go-to language for data scientists, analysts, and developers. With libraries like Pandas, NumPy, and Matplotlib, Python brings automation and advanced analytics capabilities that Excel can’t handle natively.

Why Python is powerful:

Handles millions of records efficiently
Enables automation through scripts
Integrates with machine learning and AI
Open-source and highly customizable

While Excel is great for manual exploration, Python excels in reproducibility, scalability, and automation.

⚖️ Excel vs Power BI vs Python

Feature	Excel	Power BI	Python
Ease of Use	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐
Automation	⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Visualization	⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Scalability	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Cost	💰	💰💰	Free

Each tool shines in its own space. Excel remains unmatched in accessibility, Power BI excels in visualization, and Python dominates in automation and scalability.

🧩 The Perfect Combo

Rather than replacing Excel, Power BI and Python complement it. In fact, Excel now integrates with both:

Power BI datasets can be analyzed directly in Excel.
Python scripts can run inside Excel (as of 2024 updates in Microsoft 365).

This synergy means you can clean your data with Python, visualize it with Power BI, and share quick insights in Excel — all within the same workflow.

🧠 Final Thoughts

Excel isn’t going anywhere. It’s evolving.

While Power BI and Python dominate advanced analytics, Excel remains the universal language of business — now smarter, more connected, and more powerful than ever.

In short:

🔹 Use Excel for simplicity

🔹 Use Power BI for insights

🔹 Use Python for automation

Together, they form a modern analyst’s ultimate toolkit.

💡 Written by Rana Nicholus — Tech Enthusiast & Data Professional passionate about digital transformation and analytics.

Forem: Nicholus Rana