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