Challenges
- The client onboards customers frequently and each customer onboarded needs a data extract as well as on-time and lead-time rules setup in their data warehouse. Each extract needed a new package to be written
- Existing ETL packages needed changes for new requirements or additions for new customers
- The current DWH processes were outdated and needed process improvements.
Solutions
- Created views, packages and shell scripts to provide data extracts for customers on a daily, weekly or monthly basis as per the requirements
- Created packages with on-time and lead-time rules and logic to estimate whether carriers are on time or late to the location
- Made changes to the existing ETL packages and created new ones wherever required
- Wrote a generic package which can be used by all extracts to save time and effort
Tools & Technologies
Oracle Database
Key benefits
- Data extracts are sent to customers without any issues or delays
- On-time and lead-time counts are made available to BI team for reporting and offer better insights
- Reduced report generation time owing to removal of redundant logic
- Improved ETL processes with new package for new ETL requirements
- Reduced development time for resources as the generic package can be used for all the extracts
