Ensure continuity of data flow and integration of cloud-based medical systems

Service type:

Introduction

Our ETL experts comprehensively provided testing services, ensuring precise verification of transformation rules and accurate data sample comparisons. As a result, we created a high-quality, reliable data warehouse system that met the client’s stringent standards and supported their business analysis needs. The solution was directed towards most of the client’s stakeholders, such as healthcare workers, logisticians, and administrative staff.

Contact sales

Customer profile

A pharmaceutical company with branches in over 150 countries worldwide, specializing in drug production and clinical research in the areas of oncology, immunology, ophthalmology, infectious diseases and neurobiology.
The Goal

The project’s main goal was to create a system to globally centralize and standardize all data from different sources and systems, enabling more efficient analytics and supporting process tracking and reporting. This required the integration of databases, distributed services, and the standardization of the approach to reporting sales results, working hours, etc.

The client needed rigorous and efficient testing for their data integration and warehousing solutions. Streamlining and unifying the previously diverse and fragmented systems was also crucial. Previously, each country using the system managed it independently, making data comparisons across regions difficult, inefficient, and time-consuming. The solution was designed to address the need for a new approach to data management.
The Challenge

Due to differences in application usage, databases, reporting approaches, and terminology, the client faced challenges in comparing medical data from different countries, entities, and systems. Effective analysis and reporting of global data were practically impossible with that approach. As a result, creating reports took several weeks, causing delays and inefficiencies, leading to untimeliness in making data-driven decisions. In addition, the data was unavailable to anyone who needed it, but access to it was on demand, making the process more complex and access to the information more tedious.
The Solution

The Billennium’s dedicated project team, experienced in ETL testing, databases, and cloud platforms (AWS in this case), conducted a thorough technical assessment to create new testing processes capable of handling a cloud-based architecture. We tested the software in various scenarios to identify bugs, verify the solution’s quality, and ensure it functioned as intended. Both manual and automated tests were conducted in several stages, including integration, system, and acceptance tests. The tests mainly covered data flow across seven layers and included end-to-end testing, API testing, and manual process testing.

Initially, tests were conducted locally using SQL Developer, which involved preparing SQL queries to compare all data between the source and the target. However, this approach was time-consuming and labor-intensive. In the next phase of the project, we updated the architecture, and all data was migrated to the cloud (AWS), necessitating a new approach to data testing and integration.

Our experienced testing team developed robust testing processes that included over 3,000 test cases per region (EMEA, LATAM, APAC). Automated tests consisted of Python scripts stored in GitLab, facilitating quick and repeatable testing processes. The test sets included SQL queries prepared based on the transformations specified in the source-to-target mapping file, executed using frameworks like PySpark and Athena.

In the final stage, we prepared SQL scripts to verify the accuracy of data flows between integrated systems. Finally, we created Confluence pages to help better understand the ETL testing approach.
The Outcome

The project is now available in its production version and fully meets the client’s expectations and business needs. Our specialists utilized their extensive knowledge and technical capabilities to successfully test and deliver high-quality integrated systems. The enhanced, implemented, and published CRM service, along with all data from other integrated systems, now helps the client access all medical information in one place. This consolidation has had a significant impact on the client’s daily operations.

Thanks to integrating various data sources and the standardization of processes, the client can now generate reports and insights in a matter of minutes, which previously took weeks. The system supports a wide range of activities, from order management and visit tracking to sending offers and maintaining comprehensive dictionaries. This integrated approach has enabled the client to achieve higher operational excellence and provide better outcomes for their stakeholders.

The new system has significantly improved the speed and efficiency of operations, streamlined workflows, reduced manual effort, and minimized errors. This allows the client to quickly access comprehensive, standardized, and more reliable data. The unified system ensures consistent data management and reporting across all regions, facilitating global tracking of goals and performance metrics.
The Team
- Product Owner 
- Scrum Master 
- Business Analysts 
- ETL Testers 
- ETL Developers 
Technologies

AWS Athena / AWS Glue / AWS Redshift / PySpark / MS SQL / MySQL Workbench / Oracle / Snowflake / Hadoop / Salesforce Workbench / Python / Windows PowerShell / Agile Methodology