DEV Community

John Wakaba
John Wakaba

Posted on

CONNECTING POSTGRESQL WITH POWERBI (FOR A LOAN PERFORMANCE DASHBOARD)

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:

  1. How many loans have been issued?
  2. How much money has been disbursed?
  3. How much has been repaid?
  4. How much is still outstanding?
  5. Which counties have the highest borrowing levels?
  6. What percentage of loans are defaulted?
  7. 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)
);
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

This view provides a clean dataset ready for Power BI.

The vw_loan_dashboard view serves as the reporting layer for this project. It joins the loans, borrowers, and repayments tables 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, and outstanding_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:

  1. borrowers
  2. loans
  3. repayments
  4. 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

Sample Layout

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)