Our client, a leading debt collection and restructuring company, expected cost optimization related to data analysis and report creation. At the analysis stage, it turned out that the biggest challenge is the amount of data (over five billion records), a large number of source tables (over five thousand) and dynamically changing data structures. Only business users have knowledge of the actual data configuration.
The proposed solution consisted of two elements: implementation of the Operational Data Store (ODS) layer in the data warehouse and automation of ETL processes.
In order to optimize costs, it was decided to implement ODS using PostgreSQL and created an ETL procedure generator based on parameters stored in reference data dictionaries. Automatically created ETL procedures feed ODS. The content of dictionaries containing ETL process parameters is managed by business users responsible for data using Metastudio DRM. Dictionary verification rules defined in Metastudio DRM allow you to automatically verify the correctness of ETL processes.
The goal of the project has been achieved. Analyzes and reports use data whose preparation requires minimal participation of IT experts.