Deploying Analytics & Reporting for Historical Data
A Fortune 500 insurer needed to carry out a large-scale professional indemnity (PI) data migration within a stringent timeline to comply with the audits.
Business Challenge
The organization needed to migrate voluminous historical and sensitive data from the mainframe systems to a modern infrastructure with analytics capabilities within a stringent timeline for audit compliance.
Technology Components
-
Microsoft SQL Server, SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS)
-
Microsoft .NET
-
Python
Approach
-
Developed a reusable migration assistant tool with automation features for creating source metadata, control files for source data analysis, dynamic SSIS package and end-to-end automated ETL for migrating data from several heterogeneous systems to a structured database
-
Migrated historical data from source transactional systems built on Mainframe, SAP and EDW to a unified scalable database
-
Created a robust data model by logically grouping the data for predictive analytics and on-going business needs, as well as a centralized reporting architecture to meet all types of user needs
-
Used MS SQL Server FileStream and FileTable to integrate unstructured data related to policies and claims stored as BLOBs and CLOBs in the source transactional systems to create a direct access UI for users
Transformational Effects
-
Readily available historical data for ongoing business needs such as in-force policy conversion, open claims conversion and billing conversion
-
Easy access to business information with a unified and scalable data model that supports predictive analytics, product development, UW support, claims, risk services, distribution, operations and billing
-
Immediate access to hierarchical data extracted from conventional mainframe systems with a centralized reporting architecture to meet all types of reporting needs
