Our client, a major insurer managing asset-backed finance portfolios across 20+ banking partners, saw an opportunity to modernize their data consolidation processes. Credit line data arrived in different formats via email from multiple institutions, requiring significant manual processing. They partnered with us to build a comprehensive ETL system that automated data processing, improved accuracy, and enabled portfolio-wide analytics. The solution saves hundreds of hours monthly while providing real-time insights for portfolio management.
The Story of our Partner
Our partner is a global insurance and reinsurance company with offices around the world. The company offers specialized coverage across property, marine, aviation, cyber, energy, and political risk lines.
With a focus on disciplined underwriting and expert risk selection, our partner leverages strategic partnerships to efficiently manage a diversified global portfolio. The company's deep broker relationships enable it to capitalize quickly on favorable market opportunities while delivering tailored insurance solutions to clients worldwide.
The Challenge
Managing a multi-bank asset-backed finance portfolio requires monitoring credit line movements and exposure levels. Each of the banks our client works with would send credit line information via email attachments, in various formats, on varying schedules.
The existing process was manual. Employees downloaded attachments and used Excel workbooks to cleanse, convert, sort, and aggregate data from each bank into a consolidated view. The primary workbook had grown large and formula-heavy, which slowed down operations. This was taking a lot of time, and institutional knowledge was getting concentrated into a few employees. The insurance leader saw an opportunity to introduce strategic improvements to the process.
The Dreamix Solution
We analyzed the existing workflow and examined sample files to design a three-module ETL system.
Module 1: Data Ingestion and Cleansing
Employees upload bank files to our web-based system. The application analyzes file structure, compares it against our database of known bank formats, identifies the source institution, and maps columns to standardized fields. The cleansing engine validates data types, checks for missing fields, identifies outliers, and generates error reports.
Module 2: Standardization and Data Modeling
This module transforms bank-specific formats into a unified data model. Credit line identifiers, date formats, currency codes, and other key fields are normalized into consistent structures, then loaded into a centralized database.
Module 3: Analytics and Visualization
PowerBI dashboards provide visibility into portfolio exposure, credit line utilization trends, concentration risk, and anomaly detection. Automated reports highlight month-over-month changes and support regulatory compliance.
The infrastructure includes a scalable backend, secure file storage with version control, role-based access controls, and comprehensive audit trails.
The Results
Thanks to our work together, processing time dropped significantly. The system handles files much more efficiently, freeing staff to focus on more strategic tasks. Automated validation ensures consistency and identifies quality issues proactively through clear notifications.
The unified database enabled cross-bank, cross-sector and cross-deal group analysis and portfolio-wide trend identification. PowerBI dashboards provide real-time portfolio visibility that previously required days of manual report preparation.
The guided interface allows multiple team members to handle data processing, distributing knowledge across the organization. Training time for new employees noticeably decreased, as well. Our collaboration helped our partner modernise their data operations and gain better capabilities for informed portfolio management.
