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.