INTRODUCTION
Power BI is one of the most widely used business intelligence tools out there. One of the most valuable skills for a data analyst is the ability to transform raw data into actionable insights.
In practical financial institutions like banks, SACCOs, and fintech firms, loan-related data is produced on a daily basis. This encompasses details about borrowers, disbursed amounts, repayment histories, and indicators of loan performance. Left unanalyzed, all of this information sits dormant and serves no purpose.
In this tutorial, we will develop a Loan Performance Dashboard by integrating PostgreSQL with Power BI. This project illustrates:
- How to store structured data within PostgreSQL.
- Establish a connection between PostgreSQL and Power BI
- Prepare data for reporting purposes, construct essential loan performance metrics, and design an interactive dashboard.
Why Use PostgreSQL Together with Power BI?
PostgreSQL ranks among the most widely used relational databases in the field of data analytics. It enables analysts to store structured datasets in an organized manner and carry out transformations through SQL. Power BI, on the other hand, is a business intelligence tool that empowers analysts to build dashboards and interactive reports.
When combined, the two form a robust workflow: PostgreSQL handles data storage, SQL manages data preparation, and Power BI takes care of visualization and insight generation. This workflow finds broad application across financial analytics, credit risk analysis, fintech analytics, and business intelligence reporting.
Project Overview
I want to build a dashboard that answers questions such as:
- How many loans have been issued?
- How much money has been disbursed?
- How much has been repaid?
- How much is still outstanding?
- Which counties have the highest borrowing levels?
- What percentage of loans are defaulted?
- How does loan activity change over time?
To accomplish this, I will set up a simple relational database consisting of three tables — borrowers, loans, and repayments.
I will work with a small sample dataset to keep the project straightforward and easy to follow. This dataset represents a simplified loan portfolio containing borrower information alongside repayment activity.
Step 1: Create Tables in PostgreSQL
Create the borrowers table
CREATE TABLE borrowers (
borrower_id INT PRIMARY KEY,
borrower_name VARCHAR(100),
gender VARCHAR(20),
county VARCHAR(50),
employment_status VARCHAR(50)
);
Create the loans table
CREATE TABLE loans (
loan_id INT PRIMARY KEY,
borrower_id INT,
loan_amount NUMERIC(12,2),
interest_rate NUMERIC(5,2),
loan_term_months INT,
issue_date DATE,
loan_status VARCHAR(20),
FOREIGN KEY (borrower_id) REFERENCES borrowers(borrower_id)
);
Create the repayments table
CREATE TABLE repayments (
repayment_id INT PRIMARY KEY,
loan_id INT,
payment_amount NUMERIC(12,2),
payment_date DATE,
FOREIGN KEY (loan_id) REFERENCES loans(loan_id)
);
Step 2: Load Data Into PostgreSQL
- Import the CSV files into PostgreSQL using DBeaver or pgAdmin. Alternatively, insert the data manually using SQL INSERT statements.
Step 3: Creating a Reporting View
Rather than connecting Power BI directly to raw tables, we build a view that brings together borrower, loan, and repayment data in one place. This makes the reporting process in Power BI significantly more straightforward.
CREATE OR REPLACE VIEW vw_loan_dashboard AS
SELECT
l.loan_id,
b.borrower_name,
b.gender,
b.county,
b.employment_status,
l.loan_amount,
l.interest_rate,
l.loan_term_months,
l.issue_date,
l.loan_status,
COALESCE(SUM(r.payment_amount), 0) AS total_paid,
l.loan_amount - COALESCE(SUM(r.payment_amount), 0) AS outstanding_balance
FROM loans l
JOIN borrowers b
ON l.borrower_id = b.borrower_id
LEFT JOIN repayments r
ON l.loan_id = r.loan_id
GROUP BY
l.loan_id,
b.borrower_name,
b.gender,
b.county,
b.employment_status,
l.loan_amount,
l.interest_rate,
l.loan_term_months,
l.issue_date,
l.loan_status;
This view provides a clean dataset ready for Power BI.
The
vw_loan_dashboardview serves as the reporting layer for this project. It joins theloans,borrowers, andrepaymentstables into a single clean structure, pulling in borrower details alongside each loan record. A LEFT JOIN is used with the repayments table to ensure that loans with no repayment activity are still captured. From there, two calculated fields are derived —total_paid, which sums all repayments per loan, andoutstanding_balance, which subtracts the total paid from the original loan amount. This consolidated view makes it straightforward to build meaningful metrics and visualizations in Power BI without managing complex joins on the reporting side.
Step 4: Connect PostgreSQL to Power BI
Launch Power BI Desktop and navigate to Home, then Get Data, and select PostgreSQL Database. Enter your server name along with your database name, then load the following:
- borrowers
- loans
- repayments
- vw_loan_dashboard
Step 5: Create Power BI measures
In this step we create the following DAX measures
Total Loans
Total_Loans = COUNT('loans vw_loan_dashboard'[loan_id])
Total Disbursed Amount
Total_Disbursed_Amount = SUM('loans vw_loan_dashboard'[loan_amount])
Total Amount Paid
Total_Amount_Paid = SUM('loans vw_loan_dashboard'[total_paid])
Total Outstanding Balance
Total_Outstanding_Balance = SUM('loans vw_loan_dashboard'[outstanding_balance])
Average Loan Size
Average_Loan_Size = AVERAGE('loans vw_loan_dashboard'[loan_amount])
Defaulted Loans
Defaulted_Loans = CALCULATE(COUNT('loans vw_loan_dashboard'[loan_id]),'loans vw_loan_dashboard'[loan_status]="Defaulted")
Default Rate
Default_Rate = DIVIDE([Defaulted_Loans], [Total_Loans], 0) * 100
Expected Interest Revenue
Expected_Interest_Revenue = SUMX('loans vw_loan_dashboard','loans vw_loan_dashboard'[loan_amount] * 'loans vw_loan_dashboard'[interest_rate])
Step 6: Build The Dashboard
Dashboard Layout Example
Conclusion
Connecting PostgreSQL with Power BI allows analysts to transform structured financial data into meaningful insights.
Loan performance dashboards help organizations understand portfolio health, monitor repayment behavior, and identify potential risks.

Top comments (0)