Our client is an American multinational food manufacturing company focused on producing cereal, and other convenience foods, including crackers and toaster pastries. They have their manufacturing units set up in more than 15 countries, with their product being distributed worldwide.
Business Challenges and Objectives
- The client’s original process leaned heavily on BODS.
- All the business logic was run through a collection of 10 BODS jobs.
- The process further involved 3 Terabytes of data and took 40 hours for processing.
- The client looked to simplify reporting and improve the decision-making capabilities on promotional programs and growth management data.
- The client also wanted to re-platform the processing of datasets from SAP Business Warehouse to Microsoft SQL Server.
- The client required daily updates to the large datasets to accurately report on the most recent ones.
- They also wanted to achieve significant performance improvements and a reduction of disk storage space usage.
The TekLink Team provided the following solution to the client:
- Revamping the data storage and indexing to ensure an optimal execution plan.
- Temporary tables used to cut down on permanent storage footprints
- Creation of clustered index on important tables to ensure quicker lookups on large tables
- A single BODS job is created to allow an optimal extraction of data from SAP BW using stored procedures
- Re-architecting the extract, transformation, and load (ETL) design to leverage the platform’s strengths
- The TekLink Team cleansed the business logic included in the BODS jobs for any improper mapping/joins
The team also rewrote the different processes into Microsoft SQL Stored Procedures
- The new process takes less than 30 minutes to process, with only 700 GB of data size.
- The process is simplified to only one BODS job allowing easier maintenance and support
- The team has provided documentation of the data creation process
- Optimization of data code streamlining the entire data services process
- The ‘daily delta’ designed updates the final reporting datasets each morning based on the overnight changes
- The improved design ensures a daily update as compared to previous weekly updates
- The process improvement ensures the data is as close to real-time as possible, paving the way for better decision making.