DEV Community

Nicholus Rana
Nicholus Rana

Posted on

Healthcare Analytics Dashboard in Power BI Using PostgreSQL

#analytics #postgres #tutorial #datascience

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:

  1. Get Data → PostgreSQL
  2. Enter server and database credentials → Connect

Database Connection
Database Connection

Aiven PostgreSQL

  1. Download and install the Aiven CA certificate into Windows Trusted Root.
  2. 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.

Database Connection

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

Database Connection

Page 2 — Operations & Scheduling

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

Database Connection

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

Top comments (0)