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 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.  

Project Outcomes

Data process was remodeled and re-sequenced

ETL process was redesigned and optimized

Dashboard and report queries redesigned and optimized

Agile plan, detailed micro-milestone approach

Visible and verifiable deliverables from the first week

Corrected errors in the historical data

Report launch time reduced from ~60 seconds to 5 seconds

Data load reduced from 36 hours to 6 hours

Operational and maintenance tasks got simplified

section-03
info (1)

Reach out to our team

Schedule a conversation with our technology experts

Share your requirement with us and our team will contact you within one business day to schedule a personalized consultation.

Once you connect with our technology leaders, they will evaluate your specific business case and share a proof of concept with estimates of costs, the effort required in terms of technologies and developers, and the timeline for the process.

Request free consultation