Methods, mediums, and systems for data harmonization and data harmonization and data mapping in specified domains

• US 10,095,716 B1
• Filed: 04/02/2018
• Issued: 10/09/2018
• Est. Priority Date: 04/02/2017
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Juneja, Sandeep Rajendra, Asselstine, Nathan John, Parikh, Preetesh Vijay, Emerton, Eric, Alderton, Kevin Ian, Bocchicchio, Benedict Edward
• Primary Examiner(s): Arjomandi, Noosha
• Application Number:
  US15/943,235
• Publication Number:
  US 20180285391A1
• Time in Patent Office: 190 Days
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 40/242 Dictionaries

Abstract

The techniques described herein automatically and programmatically harmonize data, and map variable names from a dataset to standards of domains for data in the dataset. Each variable may be stored in a table which holds related groups of variables. The variables may be named by defining mappings, each mapping including two mapping rules. A first mapping rule maps a domain of the standard to the table, while a second mapping rule maps a variable within the table to a variable within the domain. When a mapping rule exists that provides an exact match between a variable name and a standard, an auto-mapping feature may be applied that automatically maps the variable name to the standard. If no exact match exists, then an analysis is performed to determine the most likely mapping candidate.

Claims

1. A non-transitory computer-readable medium storing instructions that, when executed by one or more processors, cause the one or more processors to:

receive an identification of a standard that defines one or more domains, the standard representing a collection of recommended names for domain variables pertaining to data associated with each respective domain;

associate the standard with a collection of data, the collection of data comprising one or more tables, each of the one or more tables comprising one or more data variables having respective variable names;

read the collection of data into a memory;

canonicalize the collection of data, the canonicalizing comprising one or more of removing empty records, converting the collection of data to a same letter case, combining columns in the collection of data, or filtering categories in the collection of data to remove categories having observation counts below a predetermined minimum threshold;

determine a first mapping of a selected table from the one or more tables to a specified domain of the one or more domains, and determining a second mapping of a selected data variable name within the selected table to a specified domain variable name within the specified domain, wherein determining the first mapping and the second mapping comprises;

analyzing a name of the selected table and the selected data variable name to generate a plurality of n-grams of the selected table name and the selected data variable name,

storing the plurality of n-grams in a dictionary,

performing an analysis on the plurality of n-grams to re-scale the dictionary,

evaluating a plurality of models to determine which of the plurality of models produces a highest weighted accuracy in mapping terms of the dictionary to terms of the standard,

selecting one of the plurality of models based on the weighted accuracy, and

identifying the first mapping and the second mapping in the selected model;

defining a mapping rule based on the first mapping and the second mapping;

receive a new data set comprising one or more new tables, the one or more new tables each comprising one or more new data variables having respective new variable names; and

use the mapping rule to predict that a selected one of the new data variables should be mapped to the specified domain variable name within the specified domain based at least in part on;

the new variable name of the selected one of the new data variables being identical to the selected domain variable name, or the new variable name matching the selected domain variable name within a predetermined threshold closeness value.

2. The medium of claim 1, further storing instructions for:

storing the mapping rule as metadata in a library; and

accessing mapping rules in the library when predicting that the selected one of the new variables should be mapped to the specified variable name within the specified domain.

3. The medium of claim 1, further storing instructions for displaying an auto mapping interface, the auto mapping interface displaying the respective variable name, the name of the selected table, the specified domain variable name, and the specified domain, and a selection element that, upon selection, confirms that the mapping rule applying the first mapping and the second mapping should be applied to the respective variable name and the name of the selected table.

4. The medium of claim 1, wherein the analysis of the plurality of n-grams is a term-frequency/inverse-document-frequency (TF-IDF) analysis, and the n-gram analysis applies a value of n=2.

5. The medium of claim 1, wherein the weighted accuracy is determined by:

determining a mapping defined by the selected model, the mapping matching a variable name of the model to the selected data variable name;

determining, based on the n-gram analysis, a mapping score that describes how closely the variable name of the model matches the selected data variable name;

determining a number of studies using the mapping; and

weighing the mapping score based on the number of studies.

6. The medium of claim 1, further storing instructions for automatically generating code for programmatically applying the mapping rule to the new data set, the automatically generating comprising defining a script that replaces the selected data variable name with the specified domain variable name.

7. The medium of claim 1, wherein the mapping rule is created by a first user, and further storing instructions for:

receiving the new collection of data from a second user; and

automatically applying the mapping rule to map data in the new collection to the standard when one or more variable names in the new collection exactly match one or more variable names defined in the mapping rule.

8. The medium of claim 1, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and further storing instructions for:

determining a degree of similarity between the new variable name and the variable name defined in the mapping rule; and

applying the mapping rule to the new variable name based on the determined degree of similarity.

9. The medium of claim 1, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and further storing instructions for:

determining a frequency with which the mapping rule has been applied to map the new variable name to the variable name defined in the mapping rule; and

applying the mapping rule to the new variable name based on the determined frequency.

10. The medium of claim 1, further storing instructions for presenting a user interface, the user interface configured to:

present two or more options for specified variable names from the specified domain to which the selected variable name may be mapped;

receive a selection of one of the two or more options; and

specify the second mapping based on the selection.

11. A method performed by an electronic device, the method comprising:

receiving an identification of a standard that defines one or more domains, the standard representing a collection of recommended names for domain variables pertaining to data associated with each respective domain;

associating the standard with a collection of data, the collection of data comprising one or more tables, each of the one or more tables comprising one or more data variables having respective variable names;

reading the collection of data into a memory;

canonicalizing the collection of data, the canonicalizing comprising one or more of removing empty records, converting the collection of data to a same letter case, combining columns in the collection of data, or filtering categories in the collection of data to remove categories having observation counts below a predetermined minimum threshold;

determining a first mapping of a selected table from the one or more tables to a specified domain of the one or more domains, and determining a second mapping of a selected data variable name within the selected table to a specified domain variable name within the specified domain, wherein determining the first mapping and the second mapping comprises;

analyzing a name of the selected table and the selected data variable name to generate a plurality of n-grams of the selected table name and the selected data variable name,

storing the plurality of n-grams in a dictionary,

performing an analysis on the plurality of n-grams to re-scale the dictionary,

evaluating a plurality of models to determine which of the plurality of models produces a highest weighted accuracy in mapping terms of the dictionary to terms of the standard,

selecting one of the plurality of models based on the weighted accuracy, and

identifying the first mapping and the second mapping in the selected model;

defining a mapping rule based on the first mapping and the second mapping;

receiving a new data set comprising one or more new tables, the one or more new tables each comprising one or more new data variables having respective new variable names; and

using the mapping rule to predict that a selected one of the new data variables should be mapped to the specified domain variable name within the specified domain based at least in part on;

the new variable name of the selected one of the new data variables being identical to the selected domain variable name, or the new variable name matching the selected domain variable name within a predetermined threshold closeness value.

12. The method of claim 11, further comprising:

storing the mapping rule as metadata in a library; and

accessing mapping rules in the library when predicting that the selected one of the new variables should be mapped to the specified variable name within the specified domain.

13. The method of claim 11, further storing instructions for displaying an auto mapping interface, the auto mapping interface displaying the respective variable name, the name of the selected table, the specified domain variable name, and the specified domain, and a selection element that, upon selection, confirms that the mapping rule applying the first mapping and the second mapping should be applied to the respective variable name and the name of the selected table.

14. The method of claim 11, wherein the analysis of the plurality of n-grams is a term-frequency/inverse-document-frequency (TF-IDF) analysis, and the n-gram analysis applies a value of n=2.

15. The method of claim 11, wherein the weighted accuracy is determined by:

determining a mapping defined by the selected model, the mapping matching a variable name of the model to the selected data variable name;

determining, based on the n-gram analysis, a mapping score that describes how closely the variable name of the model matches the selected data variable name;

determining a number of studies using the mapping; and

weighing the mapping score based on the number of studies.

16. The method of claim 11, further comprising automatically generating code for programmatically applying the mapping rule to the new data set, the automatically generating comprising defining a script that replaces the selected data variable name with the specified domain variable name.

17. The method of claim 11, wherein the mapping rule is created by a first user, and further comprising:

receiving the new collection of data from a second user; and

automatically applying the mapping rule to map data in the new collection to the standard when one or more variable names in the new collection exactly match one or more variable names defined in the mapping rule.

18. The method of claim 11, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and further comprising:

determining a degree of similarity between the new variable name and the variable name defined in the mapping rule; and

applying the mapping rule to the new variable name based on the determined degree of similarity.

19. The method of claim 11, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and further comprising:

determining a frequency with which the mapping rule has been applied to map the new variable name to the variable name defined in the mapping rule; and

applying the mapping rule to the new variable name based on the determined frequency.

20. The method of claim 11, further comprising presenting a user interface, the user interface configured to:

present two or more options for specified variable names from the specified domain to which the selected variable name may be mapped;

receive a selection of one of the two or more options; and

specify the second mapping based on the selection.

21. An apparatus comprising:

a hardware processor circuit;

an interface executable on the processor circuit and configured to receive an identification of a standard that defines one or more domains, the standard representing a collection of recommended names for domain variables pertaining to data associated with each respective domain;

association logic executable on the processor circuit and configured to associate the standard with a collection of data, the collection of data comprising one or more tables, each of the one or more tables comprising one or more data variables having respective variable names;

storage logic executable on the processor circuit and configured to read the collection of data into a memory;

canonicalization logic executable on the processor circuit and configured to canonicalize the collection of data, the canonicalizing comprising one or more of removing empty records, converting the collection of data to a same letter case, combining columns in the collection of data, or filtering categories in the collection of data to remove categories having observation counts below a predetermined minimum threshold; and

mapping logic executable on the processor circuit and configured to determine a first mapping of a selected table from the one or more tables to a specified domain of the one or more domains, and determining a second mapping of a selected data variable name within the selected table to a specified domain variable name within the specified domain, wherein determining the first mapping and the second mapping comprises;

analyzing a name of the selected table and the selected data variable name to generate a plurality of n-grams of the selected table name and the selected data variable name,

storing the plurality of n-grams in a dictionary,

performing an analysis on the plurality of n-grams to re-scale the dictionary,

evaluating a plurality of models to determine which of the plurality of models produces a highest weighted accuracy in mapping terms of the dictionary to terms of the standard,

selecting one of the plurality of models based on the weighted accuracy, and

identifying the first mapping and the second mapping in the selected model, wherein;

the interface is further configured to receive a new data set comprising one or more new tables, the one or more new tables each comprising one or more new data variables having respective new variable names, and

the mapping logic is further configured to defining a mapping rule based on the first mapping and the second mapping, and to use the mapping rule to predict that a selected one of the new data variables should be mapped to the specified domain variable name within the specified domain based at least in part on;

the new variable name of the selected one of the new data variables being identical to the selected domain variable name, or the new variable name matching the selected domain variable name within a predetermined threshold closeness value.

22. The apparatus of claim 21, wherein the storage logic is further configured to store the mapping rule as metadata in a library, and the mapping logic is further configured to access mapping rules in the library when predicting that the selected one of the new variables should be mapped to the specified variable name within the specified domain.

23. The apparatus of claim 21, further storing instructions for displaying an auto mapping interface, the auto mapping interface displaying the respective variable name, the name of the selected table, the specified domain variable name, and the specified domain, and a selection element that, upon selection, confirms that the mapping rule applying the first mapping and the second mapping should be applied to the respective variable name and the name of the selected table.

24. The apparatus of claim 21, wherein the analysis of the plurality of n-grams is a term-frequency/inverse-document-frequency (TF-IDF) analysis, and the n-gram analysis applies a value of n=2.

25. The apparatus of claim 21, wherein the weighted accuracy is determined by:

determining a mapping defined by the selected model, the mapping matching a variable name of the model to the selected data variable name;

determining, based on the n-gram analysis, a mapping score that describes how closely the variable name of the model matches the selected data variable name;

determining a number of studies using the mapping; and

weighing the mapping score based on the number of studies.

26. The apparatus of claim 21, further comprising code generation logic executable on the processor circuit and configured to automatically generate code for programmatically applying the mapping rule to the new data set, the automatically generating comprising defining a script that replaces the selected data variable name with the specified domain variable name.

27. The apparatus of claim 21, wherein the mapping rule is created by a first user, and wherein:

the interface logic is further configured to receive the new collection of data from a second user; and

the mapping logic is further configured to automatically apply the mapping rule to map data in the new collection to the standard when one or more variable names in the new collection exactly match one or more variable names defined in the mapping rule.

28. The apparatus of claim 21, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and the mapping logic is further configured to:

determine a degree of similarity between the new variable name and the variable name defined in the mapping rule; and

apply the mapping rule to the new variable name based on the determined degree of similarity.

29. The apparatus of claim 21, wherein at least one of the new variable names is distinct from a variable name defined in the mapping rule, and the mapping logic is further configured to:

determine a frequency with which the mapping rule has been applied to map the new variable name to the variable name defined in the mapping rule; and

apply the mapping rule to the new variable name based on the determined frequency.

30. The apparatus of claim 21, further comprising user interface logic configured to display a user interface on a display device, the user interface configured to:

present two or more options for specified variable names from the specified domain to which the selected variable name may be mapped;

receive a selection of one of the two or more options; and

specify the second mapping based on the selection.