DATA VALIDATION

Validation is a most important phase in a  data migration project, to ensure the data integrity between the legacy and the target systems.

Below is one case study  I've been involved to help the business with the validation process.

Client: A technology-based provider of power and automation products, systems, solutions, and services in the united States

History:

The Project is an  ERP(SAP) implementation project, and currently in the Go live phases, with 19 ERP system migrating in 6 phases.

Go live 1 through 3,the Data Migration team performed multiple loads into data migration specific SAP clients/systems in an attempt to have the businesses review, validate and cleanse their data. While the upside of this was that the data was in an SAP system for the businesses to review, the down side was that the turn-around time to correct errors and reload the SAP system was too long thus reducing the productivity of the Data Migration team.  Also no one could visually see the data that was to be uploaded.

 

Prior Model of Operation:

Cransoft is the tool used in tjis

To overcome this challenge, the Raj came along with the  data migration team came up with the new strategy called VLVP (Virtual load and validation process) during Go-Live 4. VLVP means that the legacy data, once extracted and transformed into SAP ready load formats, is exported to logical Excel spreadsheet formats and Access databases, also known as SIM (Simulation Sheets).

VLVP allowed the Data Migration team to provide, to the business, snapshots of the actual data to be loaded to SAP without actually loading the SAP system. It allows the data team to respond quicker to data object load specification changes presented by the businesses and shortens the load turn around time drastically.

 

Version1:

Version 1 was a simulated Excel sheets and access databases that were  interacting with the SQL server

Period of release: Go Live 4, Feb 2009

Designers:

1. RAJKUMAR (Excel,Access)
2. Ernie Blazek of  ABB had designed major of large volume data to be show via, Access

Model Of operation

	Pros of this version
1	Validate the data that is to be loaded into SAP , prior itself,
2	Office Tools part of every PC, so new installation requirement
3	Easier methodology to validate whether proper transformations are in place
4	Improved Data Quality
5	Higher Percentage of Success in load

	Cons of this version
1	Occupies huge space and high network on the Cransoft hosted network
2	Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users
3	Limitation of 65000 record with the Microsoft excel 2003
4	Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.
5	Back up of data not possible
6	Numerous of number of process locks in SQL server

The biggest  cons from the User and Developer point of view was that  SIM sheets consisted of various Excel spreadsheets and Access databases that resided on a network drive and the division teams were provided access to them. It became a strenuous process for the data migration team to educate the division teams on how not only to access the network drive; but which SIM sheets applied to their site; what versions of the SIM sheets were relevant; which SIM sheet naming conventions were applicable; whether a SIM sheet for a particular data object was a Excel spreadsheet or an Access database and why that was.

It caused a lot of coordination at times, where the objective of the SIM sheet was to simplify the validation process.

Some of the positive client Feedbacks(With out any Edition)

• Simulation sheets (SIM) worked well with ability to re-load data when needed.
• Continue with the SIM sheets for future sites, definitely see future sites benefitting.
• The SIM helped identify mapping issues before data was loaded to SAP.
• The error log produced by the SIM helped identify areas of legacy data that needed to be cleansed prior to the actual load of data in SAP.
• This was my first Go-Live on the Power Products (PP) OsA team, and I honestly cannot imagine how difficult the cutover must have been for the previous go-lives.
• Without the SIM and error logs, I do not believe we would have been able to begin cutover 3 days earlier than schedule or for the controllers to sign off 2 days ahead of schedule.
• Excellent tool for the cutover teams to identify data issues. Examples - Do purchase parts have planned delivery time?
• Excellent tool to see if cross reference logic is working as expected.
• Excellent tool to come up with remediation rules.
• Excellent tool to check data counts and see any glaring issues.
• Excellent tool to perform analytical analysis of data and provide recommendations to business on fixing data issues.
• Eliminated the unknown of guessing if our changes are going to work or not.

Version 2:

Period of release: Go Live 4.5, May 2009

Though the Sim sheets received  a positive feedback, Raj heeded the constructive criticism and changed the way the SIM sheets are presented to the division site data validators with a new user Interface application to house all SIM sheets that is far simpler, intuitive and easier to use. There is no switching between Excel and Access. The user is free of the tool is behind the user interface

Designed by:

1. RAJKUMAR

Programmed by

1. VIJI
2. RAJKUMAR

Model Of operation

 

 

Points of comparison	excel/Access	DATA VALIDATION TOOL
Occupies huge space and high network on the Cransoft hosted network	Yes	No
Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users	Yes	Real time
Limitation of 65000 record with the Microsoft excel 2003	Yes	No limitation
Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.	Yes	No. Its on vitual
Back up of data	Not possible	Possible

Result:

We are happy to report that the data migration team has not received one criticism of the new simulation sheet (SIM) application; in fact, numerous compliments; The new  SIM sheet application has allowed the users to focus  on validating the data and eliminated distractions in the validation process.

We heard a very  very positive feedback one of the business user saying, this is beyond the obvious.

 

 

Some of the Screen Shots of DATA VALIDATION Tool

The simple Home Page designed to access GO Live 5( 6 ERP systems ) and Go live 6( 3 ERP system) data

 

The list of  ERP sites sites on the left as a tree view

individual objects can  be displayed choosing  the appropriate node, you have options as filter and sort similar to Excel to work around other options are to view the data on a full screen as below

 

And  can export the data in the grid view to txt,csv or xls format. by clicking on the Export File option on the right hand screen or on the file menu. If you are more comfortable with Excel.

 

This is the xls File saved to my desktop

 

The Additional Feature of this tool is the error summary , which reports, the business object and the volume of records that would fail to make through into SAP when it is uploaded prior to even load, and the reason why they would not make through.This makes the tool a bit of artificial intelligence one too…

 

By Clicking on the Error Summary on the Main Menu, you can view the summary of the errors simulated  site wise with count, to view which all records would fail in detail, you can click on the View report.

Our next exploration  would be on  Extraction tool , Raj has the design  in place on the walls, i ve got to figure out a schedule to support him., more on this experience  i will blog once it comes to ready to use state. One thing is for sure, it is going to an interesting assignment. SAP ---> SQL ,JDE ----> SQL,MAPICS---->SQL, MACPAC---> SQL, SYTELINE ---> SQL, .. Oops there is a  lot