Profiling and comparison of data
A computerized method is provided for automated profiling and comparison of electronic data including in different environments and formats. Data column comparison and/or mapping can be efficiently accomplished without input of human experts through a three-step process including column name direct matching, a fuzzy string matching algorithm (token sort ratio) on unmatched columns, and finally a column data comparison for remaining unmatched columns.
1 . A computerized method for electronic data comparison, the method comprising using a column mapping engine for:
comparing column names between a first electronic data table and a second electronic data table;
defining columns having 100% column name similarities between the first and second electronic data tables as matches;
applying a fuzzy string matching algorithm (token sort ratio) to any unmatched column names from the defining step to produce a similarity score for each pair of unmatched column names between the first and second electronic data tables;
classifying columns as matches when the similarity score for the pair of unmatched column names is above a selected threshold;
selecting a number of sample records from remaining columns in the first and second electronic data tables not matched in the defining or classifying steps;
comparing the sample records from each remaining column in the first electronic data table to the sample records from each remaining column in the second electronic data table to determine a data match score;
mapping remaining columns from the first electronic data table as matches to remaining columns from the second electronic data table based on highest data match score; and
performing, with a data comparison engine, a row-by-row comparison of records between the matched columns in the first and second electronic data tables to determine a match percentage or variance percentage for each of the matched columns.
2 . The computerized method of claim 1 , further comprising generating a report comprising the column names and match or variance percentage for each of the matched columns.
3 . The computerized method of claim 2 , wherein the report further comprises a record match count for each of the matched columns.
4 . The computerized method of claim 2 , wherein the report further comprises a record difference count for each of the matched columns.
5 . The computerized method of claim 1 , wherein the selected threshold is 50% similarity.
6 . The computerized method of claim 1 , wherein the selected threshold is 60% similarity.
7 . The computerized method of claim 1 , wherein the selected threshold is 70% similarity.
8 . The computerized method of claim 1 , wherein the number of sample records is 500 or more.
9 . The computerized method of claim 1 , wherein the number of sample records is 1000 or more.
10 . A computer system for electronic data comparison, the system comprising a processor in communication with a non-transient memory and operable to perform the steps of:
using a column mapping engine to:
compare column names between a first electronic data table and a second electronic data table;
defining columns having 100% column name similarities between the first and second electronic data tables as matches;
applying a fuzzy string matching algorithm (token sort ratio) to any unmatched column names from the defining step to produce a similarity score for each pair of unmatched column names between the first and second electronic data tables;
classifying columns as matches when the similarity score for the pair of unmatched column names is above a selected threshold;
selecting a number of sample records from remaining columns in the first and second electronic data tables not matched in the defining or classifying steps;
comparing the sample records from each remaining column in the first electronic data table to the sample records from each remaining column in the second electronic data table to determine a data match score;
mapping remaining columns from the first electronic data table as matches to remaining columns from the second electronic data table based on highest data match score; and
performing, with a data comparison engine, a row-by-row comparison of records between the matched columns in the first and second electronic data tables to determine a match percentage or variance percentage for each of the matched columns.
11 . The computer system of claim 10 , further operable to generate a report comprising the column names and match or variance percentage for each of the matched columns.
12 . The computer system of claim 11 , wherein the report further comprises a record match count for each of the matched columns.
13 . The computer system of claim 11 , wherein the report further comprises a record difference count for each of the matched columns.
14 . The computer system of claim 10 , wherein the selected threshold is 50% similarity.
15 . The computer system of claim 10 , wherein the selected threshold is 60% similarity.
16 . The computer system of claim 10 , wherein the selected threshold is 70% similarity.
17 . The computer system of claim 10 , wherein the number of sample records is 500 or more.
18 . The computer system of claim 10 , wherein the number of sample records is 1000 or more.