Ananya S

Posted on Jul 13

I Built an End-to-End Mortgage Loan Analytics Dashboard with Python & Power BI

#dataengineering #datascience #analytics #python

Business Intelligence isn't just about creating beautiful dashboards.

It's about transforming raw, messy data into meaningful business decisions.

To understand the complete analytics lifecycle, I built an end-to-end Mortgage Loan Portfolio Analytics Dashboard using Python, Power BI, Power Query, and DAX.

This project goes far beyond drag-and-drop visualizations—it covers data generation, ETL, dimensional modeling, KPI development, and executive dashboard design, closely mirroring how analytics teams work in real organizations.

Why I Built This Project

Financial institutions manage thousands of mortgage loans every year.

Business leaders constantly need answers to questions like:

Which regions have the highest loan exposure?
What is the portfolio default rate?
Which customers are at higher credit risk?
Are loan repayments improving over time?
Which loan officers manage the largest portfolios?

Without centralized reporting, these insights are scattered across multiple systems.

The goal of this project was to build a dashboard that converts raw mortgage data into actionable business intelligence.

Tech Stack

🐍 Python
Pandas
Faker
Excel
Power Query
Power BI
DAX
Git & GitHub

Each tool played a different role throughout the analytics pipeline instead of using Power BI alone.

Project Architecture

Python
      │
      ▼
Synthetic Mortgage Dataset
      │
      ▼
Excel Files
      │
      ▼
Power Query (ETL)
      │
      ▼
Star Schema Data Model
      │
      ▼
DAX Measures
      │
      ▼
Interactive Power BI Dashboard
      │
      ▼
Business Insights

This mirrors a real Business Intelligence workflow rather than simply importing a CSV into Power BI.

Step 1 — Generating Realistic Data

Instead of downloading a public dataset, I generated my own synthetic mortgage portfolio using Python.

The generated data includes:

1,000 customers
1,000 properties
1,500 mortgage loans
400,000 payment records
25 loan officers
Calendar dimension table

Creating the data myself gave me complete control over relationships and business scenarios while making the project feel closer to a production system.

Step 2 — Data Cleaning with Power Query

Real-world datasets are rarely perfect.

To simulate production data, I intentionally introduced quality issues before cleaning them.

The ETL process included:

Standardizing employment types
Standardizing province names
Removing duplicate IDs
Handling missing values
Correcting data types
Creating income bands
Creating property appreciation categories

Power Query became the primary ETL layer before any reporting began.

Step 3 — Building a Star Schema

One of the biggest improvements over beginner Power BI projects was using a proper dimensional model.

Instead of connecting tables randomly, I designed a Star Schema.

Fact tables:

Loans
Payments

Dimension tables:

Customers
Properties
Loan Officers
Calendar

This model keeps DAX simpler while improving report performance and scalability.

Step 4 — Writing Business KPIs with DAX

Dashboards are only as valuable as the metrics they expose.

Some of the measures I created include:

Portfolio KPIs

Total Portfolio
Total Loans
Total Customers
Average Loan Amount
Average Interest Rate
Average Credit Score

Risk KPIs

Default Rate
Delinquency Rate
Average Days Late

Payment KPIs

Monthly Payments
Total Payments Received
Cumulative Payments

Instead of focusing on visuals first, I focused on translating business questions into measurable KPIs.

Dashboard Pages

The report is divided into multiple business-focused dashboards.

Executive Overview

Provides leadership with an instant snapshot of:

Portfolio value
Default rate
Delinquency rate
Loan status
Monthly payment trends

Customer & Loan Analysis

Focuses on:

Income segmentation
Credit score analysis
Borrowing behavior
Loan officer performance

Risk & Payment Analysis

Shows:

Average days late
Geographic delinquency
Credit score vs risk
Property type risk

Interactive slicers and custom navigation make exploration much easier for end users. The reddish orange rectangles in the top left corner in each image are custom buttons made for navigation between dashboard pages with ease.

Business Insights

The dashboard uncovered several useful observations:

Most loans remain active, with a relatively low default rate.
Mortgage exposure is concentrated in a few provinces.
Medium-income borrowers form the largest customer segment.
Higher income generally correlates with larger loan sizes.
Payment delays exist across all regions rather than a single hotspot.
Loan officer portfolios are unevenly distributed, suggesting opportunities for workload balancing.

These are the kinds of insights executives actually use to guide lending strategy.