airajena opened a new pull request, #20:
URL: https://github.com/apache/fineract-business-intelligence/pull/20
## Summary
##### This PR introduces the complete **Portfolio Health Dashboard** and its
underlying dbt transformation layer. It delivers points-in-time stock metrics
(GLP, active loan counts, borrower counts, average loan size, and NPA/PAR
exposure and ratios) and daily flow metrics (disbursements, collections,
write-offs, net cash flows) to track overall growth and credit risk across
branch offices and loan products.
---
## Architecture
```
PostgreSQL Warehouse (analytics DB)
│
├── analytics.* ← dbt marts (queried by Superset virtual datasets)
│ ├── mart_portfolio_health
│ └── mart_delinquency_par
│
└── Superset (Port 8088)
├── Datasets (2) ← Virtual datasets with Jinja2 RLS SQL
│ ├── portfolio_health_secure_all_dates
│ └── portfolio_health_secure_latest
│
└── Dashboards (1)
└── Portfolio Health Dashboard ← 15 charts
```
---
## Files Added & Modified
### dbt Modeling
| File | Change Type | Description |
|------|-------------|-------------|
|
[mart_portfolio_health.sql](file:///c:/Users/airaj/OneDrive/Desktop/cool_stuffs/fineract-business-intelligence/dbt/models/marts/presentations/mart_portfolio_health.sql)
| **[NEW]** | Combines stock metrics (GLP, loan/borrower counts) with daily
transaction flows (disbursed, collected, written-off) at the daily branch,
product, and currency grain. |
|
[_marts.yml](file:///c:/Users/airaj/OneDrive/Desktop/cool_stuffs/fineract-business-intelligence/dbt/models/marts/_marts.yml)
| **[MODIFY]** | Adds validation schema tests and definitions for
`mart_portfolio_health`. |
---
## Row-Level Security (RLS)
The `portfolio_health_secure_latest` and `portfolio_health_secure_all_dates`
virtual datasets enforce branch security via dynamic Jinja2 SQL mapping
(matching `current_username()` against `meta.user_office_mapping.username` and
filtering records by matching `office_id`).
---
## Detailed Chart & Metric Configuration
### Row 1 : KPI Cards
All cards use `portfolio_health_secure_latest` with no additional filters.
<img width="1881" height="187" alt="image"
src="https://github.com/user-attachments/assets/88d326b7-d58f-4e6d-a16c-a034d6a57b75";
/>
#### Chart 1 : Gross Loan Portfolio KPI
* **Type**: Big Number
* **Dataset**: `portfolio_health_secure_latest`
* **Formula**:
```sql
SUM(gross_loan_portfolio)
```
* **Interpretation**: The total active outstanding principal balance.
* **Format**: `$,.0f`
<img width="531" height="160"
alt="gross-loan-portfolio-kpi-2026-06-15T09-37-43 914Z"
src="https://github.com/user-attachments/assets/59a13145-a819-446b-9c44-f3185e167cc8";
/>
#### Chart 2 : Active Loans KPI
* **Type**: Big Number
* **Dataset**: `portfolio_health_secure_latest`
* **Formula**:
```sql
SUM(active_loan_count)
```
* **Interpretation**: Total count of active loan accounts.
* **Format**: `,d`
<img width="257" height="160" alt="active-loans-kpi-2026-06-15T09-44-07
932Z"
src="https://github.com/user-attachments/assets/5ab04fbe-e2d2-44f4-a657-2151ba5b151d";
/>
#### Chart 3 : Active Borrowers KPI
* **Type**: Big Number
* **Dataset**: `portfolio_health_secure_latest`
* **Formula**:
```sql
SUM(active_borrower_count)
```
* **Interpretation**: Total count of unique client entities holding an
active loan.
* **Format**: `,d`
<img width="257" height="160" alt="active-borrowers-kpi-2026-06-15T09-44-11
278Z"
src="https://github.com/user-attachments/assets/f905ab35-a2e3-418e-be33-e6b83efd9b9e";
/>
#### Chart 4 : NPA Ratio KPI
* **Type**: Big Number
* **Dataset**: `portfolio_health_secure_latest`
* **Formula**:
```sql
(SUM(npa_outstanding_amount) * 100) / NULLIF(SUM(gross_loan_portfolio),
0)
```
* **Interpretation**: Percentage of portfolio outstanding classified as
non-performing assets (defaulted/90+ DPD).
* **Format**: `,.2f`
<img width="257" height="160" alt="npa-ratio-kpi-2026-06-15T09-44-14 865Z"
src="https://github.com/user-attachments/assets/f9295405-ba49-4547-b2a4-55642371e4ae";
/>
#### Chart 5 : Average Loan Size KPI
* **Type**: Big Number
* **Dataset**: `portfolio_health_secure_latest`
* **Formula**:
```sql
SUM(gross_loan_portfolio) / NULLIF(SUM(active_loan_count), 0)
```
* **Interpretation**: The average outstanding balance per active loan
account.
* **Format**: `$,.0f`
<img width="257" height="160" alt="average-loan-size-kpi-2026-06-15T09-44-18
217Z"
src="https://github.com/user-attachments/assets/1970b39a-a0de-44be-9098-edcb3bd74e7f";
/>
---
### Row 2 : Trend Analysis (Historical Stock)
#### Chart 6 : Portfolio Composition Trend
* **Type**: Stacked Area Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis Stack**:
* `performing_outstanding_amount` (Performing: 0 DPD)
* `par_outstanding_amount` (At Risk: 30–89 DPD)
* `npa_outstanding_amount` (NPA: 90+ DPD)
* **Interpretation**: Displays the active portfolio outstanding balance
segmented by risk/health category over time.
* **Format**: `$,.0f`
*
<img width="804" height="336"
alt="portfolio-composition-trend-2026-06-15T10-35-17 121Z"
src="https://github.com/user-attachments/assets/4c61f20c-e52e-40fe-9170-c491da7deddc";
/>
#### Chart 7 : Gross Loan Portfolio Trend
* **Type**: Line Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis**:
* `gross_loan_portfolio` (Principal Balance)
* `total_outstanding_amount` (Principal + Interest + Fees)
* **Interpretation**: Tracks the total money lent vs the total client debt
burden over time.
* **Format**: `$,.0f`
<img width="804" height="336"
alt="gross-loan-portfolio-trend-2026-06-15T10-35-22 055Z"
src="https://github.com/user-attachments/assets/ea20f4f2-3732-48d4-9dc2-89f0d25bacfc";
/>
---
### Row 3 : Client & Interest Trends
#### Chart 8 : Active Borrowers & Loans Trend
* **Type**: Line Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis**:
* `active_borrower_count` (Borrowers count)
* `active_loan_count` (Loan accounts count)
* **Interpretation**: Compares client headcount growth vs loan accounts
growth over time.
* **Format**: `,d`
<img width="804" height="336"
alt="active-borrowers-loans-trend-2026-06-15T10-35-27 225Z"
src="https://github.com/user-attachments/assets/56c0e666-8601-437e-95f7-8f0656de46b5";
/>
#### Chart 9 : Principal vs Interest Outstanding
* **Type**: Stacked Area Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis Stack**:
* `gross_loan_portfolio` (Principal)
* `interest_outstanding_amount` (Accrued Unpaid Interest)
* **Interpretation**: Shows what share of total debt consists of principal
vs unpaid interest.
* **Format**: `$,.0f`
<img width="804" height="336"
alt="principal-vs-interest-outstanding-2026-06-15T10-35-32 117Z"
src="https://github.com/user-attachments/assets/fb0f871b-d964-456f-b334-443c9ea96143";
/>
---
### Row 4 : Transaction Flow Analysis
#### Chart 10 : Disbursement vs Collection Trend
* **Type**: Line Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis**:
* `disbursed_amount_on_date` (Capital Outflow)
* `collected_amount_on_date` (Capital Inflow)
* **Interpretation**: Tracks daily lending outflows vs collection inflows.
* **Format**: `$,.0f`
<img width="804" height="336"
alt="disbursement-vs-collection-trend-2026-06-15T10-35-39 168Z"
src="https://github.com/user-attachments/assets/a1999c31-d2e4-4de0-b122-073e8737533f";
/>
#### Chart 11 : Net Portfolio Flow
* **Type**: Bar Chart
* **Dataset**: `portfolio_health_secure_all_dates`
* **X-Axis**: `snapshot_date`
* **Y-Axis**:
```sql
SUM(disbursed_amount_on_date) - SUM(collected_amount_on_date)
```
* **Format**: `$,.0f`
<img width="804" height="336" alt="net-portfolio-flow-2026-06-15T10-35-44
443Z"
src="https://github.com/user-attachments/assets/518bdae9-c111-4209-9e1b-e9f85d56061f";
/>
---
### Row 5 : Business Breakdown (Branch & Product)
#### Chart 12 : Portfolio by Branch
* **Type**: Bar Chart
* **Dataset**: `portfolio_health_secure_latest`
* **Group by**: `office_name`
* **Y-Axis**:
* `gross_loan_portfolio` (GLP)
* `npa_outstanding_amount` (NPA)
* **Format**: `$,.0f`
<img width="804" height="336" alt="portfolio-by-branch-2026-06-15T10-35-50
288Z"
src="https://github.com/user-attachments/assets/2ff8282c-9130-4723-bc1e-e4f1dc964c5f";
/>
#### Chart 13 : Portfolio by Product
* **Type**: Bar Chart
* **Dataset**: `portfolio_health_secure_latest`
* **Group by**: `product_name`
* **Y-Axis**:
* `gross_loan_portfolio` (GLP)
* `npa_outstanding_amount` (NPA)
* **Format**: `$,.0f`
<img width="804" height="336" alt="portfolio-by-product-2026-06-15T10-35-55
556Z"
src="https://github.com/user-attachments/assets/6846b461-9943-4c72-af56-cf32a5356ecb";
/>
---
### Row 6 : Concentration & Summary
#### Chart 14 : Portfolio Concentration by Branch
* **Type**: Pie Chart
* **Dataset**: `portfolio_health_secure_latest`
* **Group by**: `office_name`
* **Metric**: `SUM(gross_loan_portfolio)`
* **Interpretation**: Shows each branch's share of total Gross Loan
Portfolio.
* **Format**: `$,.0f`
<img width="804" height="320"
alt="portfolio-concentration-by-branch-2026-06-15T10-35-59 910Z"
src="https://github.com/user-attachments/assets/c25675f8-2014-4832-a0e6-f6b58c6bfba5";
/>
#### Chart 15 : Portfolio Health Summary Table
* **Type**: Table Chart
* **Dataset**: `portfolio_health_secure_latest`
* **Group by**: `office_name`, `product_name`
* **Metrics**:
* GLP: `SUM(gross_loan_portfolio)`
* Active Loans: `SUM(active_loan_count)`
* Borrowers: `SUM(active_borrower_count)`
* Avg Size:
`SUM(gross_loan_portfolio)/NULLIF(SUM(active_loan_count),0)`
* NPA Loans: `SUM(npa_loan_count)`
* NPA Ratio:
`SUM(npa_outstanding_amount)/NULLIF(SUM(gross_loan_portfolio),0)`
* **Interpretation**: Grid view for deep operational audits per office and
product.
* **Format**: Multi-column custom formats.
<img width="804" height="320"
alt="portfolio-health-summary-table-2026-06-15T10-36-03 666Z"
src="https://github.com/user-attachments/assets/4fa0a3dc-303f-4124-898d-195779af5e91";
/>
## Portfolio Health Dashboard
<img width="1657" height="1952"
alt="portfolio-health-dashboard-2026-06-15T10-42-58 423Z"
src="https://github.com/user-attachments/assets/5023a847-64b7-4e7b-885f-da95089d301d";
/>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
