A Fortune 500 property and casualty insurance company needed help carrying out a large-scale historical data migration. A voluminous quantity of professional indemnity insurance (PI) data was acquired from another financial services company—and the transitional service agreement mandated execution within a stringent timeline.
As part of the engagement, we migrated 30 years of PI data from multiple heterogeneous systems to a unified, scalable platform, enabling its audit, compliance and analysis. Trianz also partnered with the client to design and develop a solution for migration of historical data for NA personal lines.
THE BUSINESS CHALLENGE
Migrating 30 years of data from a newly acquired business’ multiple heterogeneous systems. The data needed to be moved to a unified scalable platform within the scheduled time before systems were shut down.
Technology Components
- Microsoft SQL Server
- Microsoft SQL Server Reporting Services (SSRS)
- Microsoft SQL Server Integration Services (SSIS)
- Microsoft .NET
- Python
THE APPROACH
- Developed a reusable migration assistant tool with features like source metadata generation, control files creation for source data analysis, and dynamic SSIS package generation. The former gives an end-to-end automated ETL solution for data migration from several heterogeneous systems to a structured database.
- Migrated historical data from the source transactional systems built on Mainframe, SAP and the EDW data to a unified scalable database. Created a robust data model by logically grouping the data needs for predictive analytics and on-going business needs with centralized reporting architecture.
- Used MS SQL Server FileStream and FileTable to integrate the documents and attachments related to policies and claims stored as BLOBs and CLOBs in the source transactional systems. Created a direct access UI for the users.
TRANSFORMATIONAL EFFECTS
- Historical data made available for ongoing business needs such as in-force policy conversion, open claims conversion, and billing conversion.
- A unified and scalable data model made available for predictive analytics. Supports actuarial, product development, UW support, claims, risk services, distribution, operations, and billing.
- All types of user needs met by centralized reporting architecture built with a tool-agnostic data platform for reporting.
