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

data loading

in th joint team

In a record time of 17 months, insurance companies Kooperativa and ČPP built a new data warehouse with Adastra.

While maintaining business continuity, they migrated their data over to it from the previous solution (over 5,000 tables). The time necessary for data updates in the DWH is now 6 times shorter.

The new data warehouse in a nutshell: fast availability of quality information, reduced operation and maintenance costs

After 10 years of operating their DWH (data warehouse), Kooperativa and ČPP decided to replace their existing solution due to their business departments’ growing demands for information quality and update speed. They built a DWH/BI (Business Intelligence) solution on a Microsoft platform, and migrated over 5,000 tables to two instances of the new data warehouse.

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.

“When we requested, more than a year ago, that we shorten the deadline for replacing the DWH by one year, we meant it, in part, as an overstatement. But the team took the new deadline as a challenge, and met it. It might sound trite, but the work was successful mainly due to the fact that the participants did not play their own games, but put literally all their energy and skills into achieving a common end. As a result, we have taken a significant step forward in the key area of BI/DWH, and can effectively support the business of both insurance companies.”
Filip Král, Member of the Kooperativa Board of Directors

Business support for both insurance companies: data update time in DWH reduced by 6 times

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.

New data warehouse operational in 17 months

Thanks to the new solution, the time necessary for data updates in the DWH is now 6 times shorter. More than 60 internal employees of both insurance companies and 20 Adastra consultants participated in building the new DWH, which took 1 year and 5 months. It was put into operation one year earlier than originally planned.

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.

Who participated in the project

David Kozelka
Finance Division Director

Get inspired on our blog

Observability platform vs. observability tools 

Complex information systems fail in unexpected ways. That’s why IT teams need both observability tools and an observability platform. To understand the distinction between...

Read more

What is observability? Not just logs, metrics and traces

As dynamic systems architectures increase in complexity and scale, IT teams face mounting pressure to track and respond to conditions and issues across their...

Read more

Good data management and the gap between IT and business are the most burning issues for large Czech companies 

Large Czech companies consider the biggest challenge they will face in the next three years to be good data management and business’ inability to...

Read more