Surfacing Data Ingestion and Integrity Issues

CLIENT SUCCESS STORY

 

Company Highlights

Region: USA

Sector: Financials

Industry: Insurance

Division: Operations

Solution Technologies: Microsoft 

Solution Products: Power BI 

Solution Service Type: Project

 

Introduction

This insurance business provides liquidity to insurance underwriters and brokers by buying their policies and owning the renewal risk. Their rapidly developed data architecture and SQL warehouse needed to be redesigned, to accommodate their next growth cycle.

Quantifying the size of their existing challenges formed the core of this project-based consulting engagement. They needed some basic reporting to continue existing business development and understand the potential degree of variance in assumed risk.

This client success story explains how issues were surfaced, quantified in order to support the follow-on redesign and development of their enterprise data architecture.

 

Highlights

Core persistency and cancelation rate KPIs

Identification of data ingestion and ETL issues

Quantify scope of re-architecture requirements

 

Challenges

Accurate and timely pricing of potential new business became increasingly challenging with the existing architecture. The client needed to consume significant transactional datasets provided by underwriters and brokers, in order to calculate a purchase price. The input was, with limited exception, always different and required cleansing and normalisation before deploying pricing and risk modelling.

The SQL based infrastructure and Excel reporting built during the start-up phase satisfied initial needs. As the company experienced
exponential growth this solution moved from an enabler to a delayer of execution. The client recognised they were experiencing more data anomalies,
and this would continue until they could build a more robust solution. With limited internal development resources and without jeopardizing their exceptional commercial growth, this business looked to a 3rd party provider to quantify their data gap.

 

Solution

To meet this client’s needs Pomerol built two Power BI reports: The first, an operational reporting dashboard provides a view of their financial position and performance of their investments. This includes modelling of the persistence of their policy portfolio and comparison of the performance against actuarial models. Additionally, forecasting of their liability over the coming year, and financial reports to meet the requirements of their auditors. The goal of the second dashboard was to validate their financial reporting and to highlight any data quality issues. Pomerol rebuilt all the calculations used for their financial reporting metrics in the Power BI data model. This created an independent control against which the data in their own database could be
validated.

 

Stakeholders

The project was initiated and approved by the CEO. Immediate business as usual support was provided by their lead SQL developer. The primary requirement was getting basic operational KPIs in play to support business decision making. The secondary ask was to size the problem in redesigning their architecture.

 

Result

The Power BI reports gave our client live visibility of their financial position and confidence in their investment models. The data validation exercise confirmed that their current data infrastructure would not meet their long-term needs and justified the investment in building an enterprise data warehouse

 

About Pomerol

We create and delivers meaningful, business-focused data solutions. We are not general technologists; we specialize in data and data-driven projects. Our team of consultants and data engineers have a depth of experience across the full analytics supply chain – from initial data extract, through transformation and modelling, to dashboarding and operational alerting, and, onward to machine learning and predictive analytics. We also integrate data into customer facing portals. We work in a wide range of industries across a wide range of business functions including sales, operations, finance, manufacturing, marketing, and, more. Our clients include the Fortune 100 to 100 employee companies. Our consultants are in the United States, United Kingdom, and Portugal.