Business Objective
Our client is a leading North American 3PL (third party transport and logistics) company. The client had a 12-year-old data warehouse with limited production access, performance issues, and delay in data delivery to IBO’s (Independent Business Owners) due to large volume and manual processes.
The client wanted to modernize the data warehouse to:
- Improve operational efficiency through storage optimization, better error handling, and other data processing enhancements
- Provide comprehensive data governance and automation framework
- Support advanced analytics solutions for better decision making
Challenges
- Driving adoption of the proposed new system
- Handling data inconsistencies across multiple sources
- Building a flexible system that allows seamless integration of additional data sources
Solution Methodology
- Took a two-phased approach and adopted agile methodology for development and testing.
- Phase 1 of the solution comprised of enhancing the existing process across XML Parsing, ETL Control, Data Purging, ODS view optimization, and Report Automation
- XML Parsing: Implemented parallel job execution in SQL to reduce processing time, new business logic for primary key violations and deadlock prevention measures for smoother parallel execution, handling large file sizes (>1MB)
- ETL Control: Limited ETL jobs handling IBO data to ODS, removed IBO data flow to EDW, and introduced naming patterns to identify IBO data files
- Data Purging: Purged the data having no immediate requirement and automated config-based archival using control tables
- ODS view optimization: Analyzed and validated existing views & queries. Created new tables and SSIS jobs to increase performance
- Report automation: Automated General Ledger coding and payment validation in daily booking files. Automated failure notification to Analytics team
- Phase 2 included data integration of the acquired company
- Implemented BI Use cases e.g., Freight analytics tool leveraging modern DWH leading to improved account audit, efficient cargo tracking
- Implemented ML Use cases e.g., pricing engine leveraging DWH leading to increase in gross margin by 2.5 percentage points
Business Impact
- Designed a cost-effective DWH architecture with comprehensive data governance and automation framework
- 85% reduction in the processing time of XML parsing due to proposed parallel processing framework
- 20-25% manual effort saved due to Config-based archiving and automated failure notification
- Significant improvement in performance and operational efficiency
