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.
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. 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.
Solution to a problem
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.
Insurance companies Kooperativa and ČPP used their data warehouse, based on SAS technology, for 10 years.
Business description of the solution
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.
During the project, approximately 1,600 tables in the L0 layer, around 1,600 tables in the L1 layer, more than 200 tables/views in the L2 layer, and more than 2,000 user tables in the datamarts were created or migrated.
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.
The amount of time necessary to load data from the source systems has been reduced from 36 hours to approximately 6 hours (i.e. it is 6 times quicker).
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.
It took 17 months to build a new DWH for Kooperativa and ČPP. The project was completed a year earlier than originally planned.
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.
The joint team comprised 20 specialists from Adastra and more than 60 employees from Kooperativa and ČPP.
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.
Are you interested in a similar solution? Contact us.
We will contact you as soon as possible.