TekLink helps streamline ETL process with increased performance and improved data integrity
About the Client:
Our client is a leading global CPG manufacturer.
To simplify reporting and improve decision making capabilities on Promotional Programs and Growth Management data, the client wanted to re-platform the processing of the datasets from SAP Business Warehouse to Microsoft SQL server.
This necessitated an optimal process utilizing both SAP BusinessObjects Data Services (BODS) and Microsoft SQL to archive and update datasets containing tens of millions of records in a timely manner each morning. Client wanted daily updates to these large datasets so that they could accurately report on the most recent datasets since the data was only being refreshed weekly. In addition, it was desired to acheive performance improvements and a reduction of disk storage space usage.
Solution Offered by TekLink:
TekLink project team partnered with our client to deliver the following:
- A complete revamp of how the data was stored and indexed to ensure an optimal execution plan.
- Temporary tables were used to cut down on permanent storage footprints and clustered indexes were created on pertinent tables to ensure for quicker look-ups on large tables.
- Using these stored procedures, a single BODS job was created to allow for an optimal extraction of data from SAP Business Warehouse and to ensure the data flow called the correct Stored Procedures in parallel where possible.
- Re-architectured extract, transformation and load (ETL) design to leverage each platform’s strengths and weaknesses.
- The original process leaned heavily on BODS and all business logic was run through a collection of 10 BODS jobs and countless data flows.
- TekLink project team took the business logic included in these BODS jobs, cleansed them of improper mapping/joins and re-wrote the different process’ into Microsoft SQL Stored Procedures
Outcomes and Benefits:
Our client realized the following outcomes and benefits:
- Dramatic process/storage improvements: TekLink team took an existing BODS process that consisted of 10 BODS jobs, 3 Terabytes of data and took up to 40 hours to complete and re-engineered it to find an optimal solution. Now, the new process takes less than 30 minutes each morning, consists of a data size of 700 GB and is simplified to one BODS job that allows for easier maintenance and support.
- Improved Data Integrity: TekLink project team identified and fixed errors on program logic, provided accurate documentation of the data creation process and optimized data code streamlining the entire data services process.
- Improved Decision Making: The TekLink project team designed and implemented a “daily delta” that updated the final reporting datasets each morning based on the previous night’s changes in SAP Business Warehouse. The improved design and optimal run time allowed the client to report on the most up-to-date data each day instead of updating the data once a week. This process improvement allows the data that drives their business decisions to be as close to real-time data as possible.
Contact Us to know more about this experience.