Heel is an international pharmaceutical company specializing in medicines made from natural ingredients. Founded in 1936, its headquarters and manufacturing plant are located in Baden-Baden, Germany. In Russia, the company is represented by Heel Rus LLC.
Previously, the ICL Services team implemented a project to migrate the client's analytical data warehouse. All identified issues and specifics formed the basis for a gradual plan for its modernization and optimization.At the same time, the transition to a new mechanism as part of the warehouse optimization was to be as seamless and painless as possible for system users. It was with this request that the client turned to ICL Services' expertise.
Key Challenges
- Analysis of source data
- Correction of the source database, table refinement, procedure optimization
- Development of standard SSIS ETL integration packages for loading 46 source files
- Optimization of Cislink data synchronization in the customer's database
- Development of Data Quality checks for 5 key files in the ETL integration
- Monitoring the operation of integration packages, sending statistics by email
Implemented our solution
- Project Progress
Working in phases on the project allowed us to spread out testing of the updated loading algorithm on the customer's side and reduce the risks associated with implementing the new mechanism.
It was decided that developing a comprehensive ETL system (data integration using the standard Microsoft SQL Server Integration Services engine as a replacement for custom scripts) would be the first step in comprehensively improving the warehouse.
Work on the project began with prioritizing integration adjustments for 46 source files. This prioritization resulted in dividing the project into two phases. The first phase involved loading 29 files, while the second involved loading the remaining 17 files and adding the necessary data quality checks for key fields.
The following data quality checks were developed as part of the project:
— checking for empty values in Excel files,
— checking for missing records for a specified time period.
For each check, the team configured notifications to monitor data loading and promptly resolve any issues.
Project Results
ICL Services experts seamlessly replaced the existing ETL data integration system with the standard Microsoft SQL Server Integration Services (SSIS) engine, significantly improving the system and enabling further profit growth.
Now, accurate data and timely and reliable reports enable more informed business decisions, optimize internal business processes, reduce costs, and, consequently, increase profits.
Products and technologies
- Microsoft Excel
- Microsoft SQL Server, SQL Server Integration Services
- Power BI
Results
- Using standard SQL SSIS integration service components instead of custom scripts provided a number of advantages, including simplified development, ease of use and support, increased performance, and scalability..
- The implementation of data quality checks improved the quality and reliability of data, the accuracy and reliability of business reports, and significantly reduced business risks.
- We developed the documentation describing the ETL data integration