Overview
delaPlex collaborated with a supply chain software solutions provider to develop an intuitive Data Integration and Reconciliation System. This initiative aimed to transform employee scheduling processes and optimize workforce management.
The project involved exploring and implementing strategies to seamlessly integrate data from two primary sources into a centralized data hub. The extracted information underwent processing in Azure Data Lake Factory to identify discrepancies, reconcile differences, and generate comprehensive reports. This provided the end user with valuable insights into workforce performance and scheduling efficiency, ultimately ensuring operational excellence and improved customer satisfaction.
Client Needs:
The client sought a robust system to automate the integration and reconciliation of employee scheduling data. The objective was to gain insights into workforce performance, scheduling efficiency, and areas for improvement, thereby optimizing workforce management.
Project Challenges:
The project faced challenges related to performance, scalability, and security. Key objectives included:
- Establishing secure connections to the data sources.
- Managing large volumes of diverse data.
- Ensuring real-time processing with minimal latency.
- Implementing robust error-handling mechanisms.
- Designing an automated notification system.
The delaPlex Solution
delaPlex addressed these challenges by developing an integrated Data Integration and Reconciliation System using Azure Data Factory. Leveraging Azure Key Vault for secure storage and SQL Server for data management, the solution met the client’s specific requirements and exceeded performance expectations while ensuring security and scalability.
to deliver a data solution that met our client's needs."
How We Did It
delaPlex adopted a process-driven approach and agile methodology to design and execute key components which included:
- Setting Up Azure Data Factory: Configured the Azure Data Factory, defined datasets, and established secure connections to BY and WJ data sources.
- Connection Configuration: Implemented secure connections to the data sources, integrated with Azure Key Vault for secret management.
- Fetching Schedule Data from BY Database: Designed a storage system for staging records, created Data Factory pipelines and implemented error handling for data processing.
- Fetching Employee Shift Data from WJ: Developed a storage system, organized WJ files, and implemented a process for secure transfer of CSV files to SQL Server.
- Controlling Processes Based on Time Interval: Designed a table structure for control flow and implemented a mechanism to trigger data processing at specified intervals.
- Comparing Staged Data Shifts: Implemented a process to compare data from multiple sources, designed storage for discrepancies, and created a reconciliation mechanism using SQL Server.
- Generating Discrepancy Summary: Developed a process to save discrepancy records in a CSV file, managed reconciliation status, and implemented error handling.
- Implementing Email Notifications: Designed storage for email templates, created a summary report of discrepancies with a link to the CSV file, and automated the email notification process.
Results
- Optimized workforce management using new BI insights.
- Established a responsive and adaptable operational environment to facilitate real-time data synchronization.
- Achieved hourly reconciliation within 30 minutes,
- Ability to handle 200k schedule changes per day.
- Ensure version control by storing source code in the client's GitHub repository.
- Conducted end-to-end regression testing to ensure smooth system functionality after updates or changes.
