ETL – Migration

Problem:
Prepare vendor table for migration from SQL based system to Workday
Why it mattered
System migration from to Workday required the data to be analyzed and cleaned. The plan was to reduce the number of vendors, fix errors and remove duplicates.
Plan for resolution:
Create 10 vendor categories, supplier groups, routines for address checking and write upload routines to generate XML files for upload into Workday
Solution:
Extract vendor data from existing system. Transform (fix, categorize and map). Load using Workday routines and very specific formats.
Creation of 40 categories. Setting up programs and processes to find invalid data  and correct it. Working with MS Data Quality Client, Data Quality Projects and SSIS.
Summary:
The analysis was based on various KPIs and also involved finding duplicate entries (same vendor at same location, with often slightly different names and addresses) and eliminating some vendors for the same products. The analysis looked at data over 2 time periods covering 12 and 24 months. Many iterations.