BI DWH Optimization Solution

BI DWH optimization solution for an insurance company

A multi-line business insurance company aimed to implement a data warehouse solution for managing its claims operations. They used MS SQL server, and the source systems were a popular multi-line claims system, ERP systems, home-grown applications, AS400, and mainframe systems.  

They also had multiple feeds coming in from business sources and digitized forms. They had around 150 TB of data, and the ETL processes that extracted data from these sources took over 30-36 hours, as opposed to an overnight processing requirement.  

The extended data load time impacted operational dashboards and reports, impacting timely operational decision-making. The ETL processes were not designed well for exception handling and they lacked an efficient recovery process. This resulted in data quality and integrity issues. The client sought to optimize the data load mechanism to a 6-hour process two times a day. Nuvento’s team comprising of its lead solutions architect, SQL engineers, and ETL experts assessed the requirement. They came up with a six-month redesign and optimization plan. They proposed a new design to get the ETL time under 7 hours and optimize the data model to help the report queries to be completed in 5 seconds.  

The plan comprised of verifiable micro-milestones delivered every three days and verified with the business user representatives for data quality and performance. This micro-milestone-based agile model ensured timely completion and verification without surprises.   

The Nuvento team met all objectives of the redesign on or before schedule and brought the ETL under 7 hours. We redesigned the reports to meet the 5 second query time requirement and achieved this with the existing hardware.