Kooperativa and ČPP built a new data warehouse with Adastra while maintaining business continuity

The data warehouse built 10 years ago, which was used by both insurance companies, gradually ceased to meet the requirements of their business departments. Uploading data to the DWH from the source systems took almost two days, usually at the weekend and requiring supervision of the load and intervention in case of any errors. As a result, the data in the data warehouse were updated weekly, so most departments did not have totally current information. Many employees handled their reporting with non-standard procedures, including by creating reports directly from various copies of the source systems, over which they built their own “data warehouses”. Another important incentive was also the rising cost of operating and maintaining the existing technology.

Consequently, the insurance companies’ management began to consider building a completely new, robust DWH, which would give them the opportunity to use modern BI tools. At the same time, they requested that the technology and architecture used in the solution allow for the dynamic development of the DWH and BI in both companies, taking into account the departments’ increasing demands, for at least 10 years. In the process, they also factored in the then planned merger between Kooperativa and Česká Spořitelna Insurance (PČS), which was completed on 1st January 2019.

The insurance companies approached the task responsibly and, together with Adastra, verified the important parts and procedures of the future solution in a PoC (Proof of Concept) before the project began.

In the first step, ETL transformations were substantially shortened by replacing full data loads with incremental loads. At the same time, it was necessary to ensure the data were loaded rapidly from Informix (the database of the main source systems) into SQL Server. Adastra suggested the innovative approach of loading the data from Informix into SQL Server directly, which allowed non-standard parallel loading of hundreds of tables using scaling. Together with the customer, we conducted a series of tests that confirmed the advantages of the proposed solution.

An important aspect of implementing the new DWH/BI solution was ensuring business continuity. We opted for the strategy of replacing the normalized L1 layer 1:1 with respect to the original DWH such that some of the transformations would be optimized. We created a clone of the L1 layer of the data warehouse (using Microsoft SQL Server 2016/2017), under which we simultaneously built the L0 staging layer while also developing the L2 layer and datamarts. Meanwhile, the processes for Kooperativa and ČPP were unified, including preparations for the merger with PČS.

However, this technology alone would not allow efficient and high-quality development even within a relatively large team. After thoroughly evaluating several options to speed up DWH/ETL development on the SQL Server platform, the customer chose to use the Adastra ETL Framework – a set of ready-made templates, DWH/ETL standards and projections for ETL planning, execution and monitoring. This significantly accelerated and improved the quality of the development as well as deployment to production.

The project was launched 10th April 2018, with the initial phases carried out in the form of “Fixed Time & Fixed Price”. In September 2018, the steering committee set an ambitious deadline for completion: 13th September 2019, one year earlier than originally planned. As work on development accelerated, the insurance companies and Adastra agreed to shift the project management to an agile approach and establish a joint development team. Kooperativa used Adastra’s experience on similar projects to help set up project management, development methodology and deployment to production.

Thanks to the exemplary cooperation of the joint team, the ambitious deadline was met and the original DWH was shut down at 13:13 on Friday 13th September 2019. This offered significant cost savings with regard to the annual subscription fees for the original DWH licenses. Kooperativa and ČPP acquired a unified DWH and BI solution on a modern Microsoft SQL platform with a single data model. Meanwhile, each of the insurance companies uses its own instance of the data warehouse. The data are updated daily, and partial updates can be performed several times a day as needed.

In addition to the project’s primary objectives (replacing the DWH 1:1), a number of activities were successfully discharged to create important added value for the insurance companies. These included, for example, extending the data warehouse to cover balance sheets and accounting, consolidating most of Kooperativa’s and ČPP’s previously separate reports, and creating a uniform semantic (interpretative) layer for each of the companies. This allowed the replacement of more than 200 exports to various departments, who previously generated their own outputs from the data. Instead of processing data, those departments can now focus on operational and analytical activities.

Immediately after completing the replacement of the data warehouse, we started jointly planning the further development of the new DWH and the entire BI of both companies. At the customers’ request, for instance, we subsequently added another of Adastra’s standard components – the Web Upload Utility – which makes it possible to load data files to the DWH in various formats (xls., csv., etc.) comfortably and in a way that is easy to audit. For example, we are looking forward to concluding the “data” merger of Kooperativa with PČS, meeting the requirements of IFRS 17, etc.