Insurance Company Improves Data Quality

Industry: Insurance

Berkley Specialty Underwriting Managers (BSUM) wanted to improve its data analytics processes by transitioning from self-service to organizational BI. Prologika implemented a classic organizational solution, consisting of a data warehouse, ETL, and a cube that allows users to gain insights with Excel ad hoc reports. ETL processes extractssource data, transforms it, and loads the data warehouse.

Business Needs

BSUM understood that data analytics can help management better understand the company business and be more competitive in the crowded insurance space. Previously, the company attempted to use self-service BI tools but found that the complexity of analytical requirements surpassed the skillset of business users and capabilities of self-service BI tools. Management realized that an organizational BI solution is preferable because it can deliver a single version of truth. This required implementing ETL processes to transform the raw data into a star schema.

BSUM has multiple operational systems for creating policies and processing claims. Over the years, different versions of SQL Server Integration Services (SSIS) were used, ranging from DTS 2000 to SQL Server 2012. Management wanted to standardize all ETL packages on SQL Server 2012 to reduce developer and maintenance effort.

Solution

Prologika designed and implemented an organizational BI solution, consisting of a data warehouse, cube, and ETL. Prologika used its home-grown ETL framework that supports configurable degree of parallelism when running packages in parallel. Depending on the target ETL server, BSUM can configure the framework to paralyze the package execution over a certain number of threads. As a result, the ETL processing window is greatly reduced to a few hours for a full historical load and about 15 minutes for the daily load. The framework also supports incremental extraction for the daily load.

Prologika consultants worked on migrating the BSUM legacy packages to SSIS 2012 with the goal of having all packages converted to SSIS 2012. Not only were the packages converted, but also they use the SSIS 2012 project deployment mode that greatly simplifies ETL development and monitoring.

Benefits

ETL takes 60-80% of the effort for implementing an organizational BI solution. Therefore, it’s important to follow best practices so you don’t end up with an intangible mess of ETL code and you don’t exceed your ETL processing windows. For more information about ETL best practices, read our “Is ETL (E)ating (T)hou (L)ive?” newsletter.

Reduced ETL window

Data is rapidly growing nowadays while ETL processing windows are shrinking. You must do more with less. And, ETL usually becomes a performance bottleneck that stands in the way of your current and future BI initiatives. One way to reduce ETL windows is to run packages in parallel.

Many ETL tasks, such as ODS loads, loading dimensions and independent fact tables, can benefit greatly from parallel execution. The Prologika framework supports a configurable number of parallelism and distributes the packages across parallel flows.

Reduced maintenance effort

Because all packages target SSIS 2012, BSUM standardized its ETL processes. Monitoring and troubleshooting ETL is simplified because the SSIS 2012 project deployment supports task-level performance analysis in addition to easier development.