gms | German Medical Science

Introduction: Hospital Data Integration Centers face a formidable workload of manual analysis of the extensive logs generated by ETL (Extract, Transform, Load) processes to ensure data quality. The process is time-consuming and prone to errors, underscoring the urgent need for more efficient automated solutions.

State of the art: Previous studies proposed various solutions for ETL process monitoring. Ai et al. developed a CEP (complex event processing) engine for rapid and efficient data processing in active distribution networks. Seenivasan highlighted best practices for ETL processes, focusing on data quality checks and performance optimization. Trajkovska et al. emphasized the automation benefits of tools like SnapLogic in data integration. A. and M. introduced a real-time data warehouse framework leveraging Business Activity Monitoring (BAM) for up-to-date data processing.

Despite these advances, there remains a need for specialized solutions tailored to the healthcare domain. This approach introduces a real-time automated monitoring system to address this challenge.

Concept: This proposed framework focuses on improving data ingestion within the Medical Data Integration Center (MeDIC) at the University Hospital Cologne by leveraging the ELK Stack (Elasticsearch, Logstash, Kibana) to automate ETL log analysis and identify data quality issues. The architecture collects data from various primary systems, processes it through ETL mapping engines, and uses Filebeat to transfer logs for real-time analysis and visualization. Elasticsearch facilitates rapid searches and complex queries, Logstash processes and enriches data, and Kibana creates interactive visualizations. The log processing workflow involves identifying major events, configuring the ELK stack, and iteratively optimizing the log parsing algorithm based on team feedback. Continuous updates and refinements ensure that the dashboards are accurate and informative.

Implementation: The implementation of the real-time dashboard for MeDIC's ETL processes includes several visualizations that provide crucial insights into data ingestion quality. The dashboard features a line chart for success and error rates, a summary of processed and failed files, a table of success and error counts per file type, and error counts based on past experiences. Additionally, visualizations can be downloaded as CSV files.

The automated ELK architecture has been monitoring ETL processes for six months, processing around 2.46 million files, with 2.37 million successfully processed and 91.39 thousand failed. Errors accounted for 62.89 thousand failed files, with known issues including missing patient or composition resources and validation failures. This system reduces manual work, provides instant insights, and ensures that only high-quality data enters the ingestion process by allowing immediate shutdowns of problematic ETL processes until fixes are implemented.

Lessons learned: The developed monitoring solution integrates real-time data analysis and visualization specifically for medical data ingestion processes, offering stakeholders valuable insights for informed decision-making and timely interventions. The system is transparent, scalable, easy to integrate with additional ETL engines, and reduces manual intervention, enhancing the reliability, accuracy, and speed of data integration. While setting up the ELK Stack requires initial investment and technical expertise, the benefits of efficiency, data integrity, and stakeholder engagement are substantial.

The authors declare that they have no competing interests.

The authors declare that an ethics committee vote is not required.