ODI Leaving – ETL Migration and Legacy Technology Decommissioning

Project Background

Česká spořitelna, as a major Czech bank, operates a comprehensive data and analytical platform, including a Data Warehouse (DWH), Data Lake, analytical data storage, and reporting solutions. Historically, this platform was developed using various technologies. The bank faced the challenge of simplifying the technological stack and reprogramming old components in outdated technologies according to the new software development methodology.

In the past, Profinit was involved in a challenging and successful project to end the use of SAS technology and migrate ETL processes developed in SAS Enterprise Guide. Additionally, Profinit contributed to the termination and migration of ETL processes related to data processing for marketing campaigns as part of the ‘BOS Decommissioning’ project.

Based on the good reputation gained from these projects, Profinit was selected for the demanding ‘ODI Leaving’ project. Its goal was to migrate a large number of ETL processes, which prepare data for the data warehouse and analytical platform, into a new modern environment and terminate the operation of the old and outdated Oracle Data Integrator technology.

Challenge

The main challenge revolved around the requirement for maximizing automation in regression testing, thereby eliminating human involvement in verifying that the migrated process functions flawlessly and provides the same results as before migration. This was closely tied to another challenge in terms of maximizing machine processing in the reverse analysis of original algorithms. Simultaneously, there was a demand for the project‘s output to be a comprehensive functional framework suitable for automating regression tests in similar data-oriented projects in the future.

Business Needs

• Licensing Optimization: Seeking to optimize licensing structures, the client aimed to gain better control over costs associated with software licenses, aligning their expenditure with actual usage.
• Performance Concerns – Limited Code Control: With a desire for greater control over the final code output, the client was motivated to address performance issues by implementing measures that would provide more oversight and management capabilities.
• ETL Development Challenges – Limited Skilled Developers: The client recognized the challenge of ETL (Extract, Transform, Load) development with a shortage of skilled developers. Motivated by the need for expertise, they embarked on the project to enhance the competency of their development team in this crucial area.
• IT Consolidation – Reduction of Tools and Systems: Driven by a strategic approach to IT management, the client aimed to consolidate their IT infrastructure. This involved reducing the number of tools and systems in use, thereby lowering the associated costs of administration and maintenance.

Solution & Results

Thanks to Profinit, a total of 130 processes and 1200 mappings were successfully migrated for 3 internal customers, resulting in a significant reduction in ETL runtime by many machine days through numerous SQL performance tuning measures. With an average team of 5 consultants, including juniors, using the ‚Team leasing‘ model, the migration was successfully completed from October 2022 to December 2023.

The developed testing framework will continue to serve for regression test automation. As a side effect, a comprehensive automation framework, ETL Test Framework (ETF), was delivered, featuring functionality for automated copies of tested data, partitioning support, data comparison with detailed logs, and artifact generation for process and mapping analysis, custom migration, and testing. The entire migration process, including workflow and mapping migration, was meticulously planned and executed using metadata from ODI, involving export, transformation, and import into Rockify.

This systematic and technologically sophisticated migration has brought significant improvements in the efficiency and reliability of the data warehouse and analytical platform.