Microsoft Europe
Background: Microsoft Europe had purchased a software application that dealt with ERP. The company had operated in 28 different countries with a database handling all purchases of the software for that country. Each country operated a similar database in structure but each had a few differences on how it handled the data due to country specific conditions.
A third party loaded all the data from these subsystems into a single database with very little data cleanse being carried out, if any. This resulted in the data being of very low quality with vast duplication of referential and customer data and proliferation of data entry errors. The licensing of products stemming from this data was to say the least horrific.
Extraction: Each country's data was loaded into a separate area for processing, essentially this required that the referential data for each country was sorted out and the all clients for that country were isolated.
Transformation: A vast rule base was built to group records by criteria into duplicate clients - i.e. to identify cases where two separate records were in fact the same record with data entry errors. Routines were built to identify similar items such as name, address, telephone etc. It was found that the accounting sub-systems were a great source of valid information for refernce due to the importance to any company of getting paid.
Load: Once the data had been categorised into various levels of duplicity, reports were created to be sent to each country, where a representative would analyse the data and indicate certain flags. These reports were then sent back and loaded.
The returned spreadsheets were then loaded to a table contained in the specific countries structure. Another batch of routines executed that used the report findings to further group the records together. These grouped records were then merged based on strict legal standards into a single record. The project as a whole was very successful with 90% + success on cleaning the DB.