USING A DATA MINING ALGORITHM TO GENERATE FORMAT RULES USED TO VALIDATE DATA SETS

• US 20090006283A1
• Filed: 06/27/2007
• Published: 01/01/2009
• Est. Priority Date: 06/27/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Meeks, David Thomas, Roth, Mary Ann, Saillet, Yannick, Labrie, Jacques Joseph
• Granted Patent: US 8,166,000 B2
• Time in Patent Office: Days
• US Class Current: 706/12
• 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: G06N 5/025 Extracting rules from data

Abstract

Provided are a method, system, and article of manufacture for using a data mining algorithm to generate format rules used to validate data sets. A data set has a plurality of columns and records providing data for each of the columns. Selection is received of at least one format column for which format rules are to be generated and selection is received of at least one predictor column. A format mask column is generated for each selected format column. For records in the data set, a value in the at least one format column is converted to a format mask representing a format of the value in the format column and storing the format mask in the format mask column in the record for which the format mask was generated. The at least one predictor column and the at least one format mask column are processed to generate at least one format rule. Each format rule specifies a format mask associated with at least one condition in the at least one predictor column.

Claims

1. An article of manufacture having code for causing operations to be performed, the operations comprising:

processing a data set having a plurality of columns and records providing data for each of the columns;

receiving selection of at least one format column for which format rules are to be generated;

receiving selection of at least one predictor column;

generating a format mask column for each selected format column;

for records in the data set, converting a value in the at least one format column to a format mask representing a format of the value in the format column and storing the format mask in the format mask column in the record for which the format mask was generated; and

processing the at least one predictor column and the at least one format mask column to generate at least one format rule, wherein each format rule specifies a format mask associated with at least one condition in the at least one predictor column.

2. The article of manufacture of claim 1, wherein the conditions in the at least one predictor column comprises a discrete value, a range of values or an expression defining a set of values.

3. The article of manufacture of claim 1, wherein an association rules algorithm is used to generate an association rules model to detect all possible associations between the values in the at least one predictor column and the format masks in the at least one format column.

4. The article of manufacture of claim 1, wherein the operations further comprise:

determining a first format rule predicting a same format mask for a same format column as a second format rule;

determining whether the second format rule includes a simpler condition which is always true when the condition of the first format rule is verified; and

deleting the first format rule in response to determining that second the format rule includes the simpler condition which is always true when the condition of the first format rule is verified.

5. The article of manufacture of claim 1, wherein the operations further comprise:

determining format rules having same predictor conditions and different format masks for the same format mask column; and

merging the determined format rules into a merged format rule.

6. The article of manufacture of claim 5, wherein the merged format rule has the same predictor conditions and a value for the format mask defining a set including the format masks for the determined format rules.

7. The article of manufacture of claim 5, wherein format rules are generated having a low minimum confidence level that is below a minimum confidence level specified by a user, wherein the operations further comprise:

determining whether a sum of the confidence levels for the determined format rules having the same condition for the predictor column exceeds the minimum confidence level, wherein the determined format rules are merged into the merged format rule in response to determining that the sum of the confidence levels exceeds the minimum confidence level.

8. The article of manufacture of claim 7, wherein the operations further comprise:

deleting the determined format rules having the same predictor conditions in response to determining that the sum of the confidence levels does not exceed the minimum confidence.

9. The article of manufacture of claim 1, wherein the operations further comprise:

providing the generated format rules to a user interface to enable a user to edit the generated format rules; and

storing the format rules in a rule repository to be available to use to validate data sets.

10. The article of manufacture of claim 1, wherein converting the value in the format column to the format mask comprises replacing each character of a character type with a representation of the character type.

11. The article of manufacture of claim 1, wherein the operations further comprise:

creating a copy of the data set including the selected at least one format column and the at least one predictor column, wherein the at least one format mask column is included in the copy of the data set.

12. The article of manufacture of claim 1, wherein the format mask for one format mask rule comprises a regular expression indicating a format.

13. A system in communication with a data source including a data set having a plurality of columns and records providing data for each of the columns, comprising:

a processor;

a computer readable medium having a rule engine executed by the processor to perform operations, the operations comprising;

processing a data set having a plurality of columns and records providing data for each of the columns;

receiving selection of at least one format column for which format rules are to be generated;

receiving selection of at least one predictor column;

generating a format mask column for each selected format column;

for records in the data set, converting a value in the at least one format column to a format mask representing a format of the value in the format column and storing the format mask in the format mask column in the record for which the format mask was generated; and

processing the at least one predictor column and the at least one format mask column to generate at least one format rule, wherein each format rule specifies a format mask associated with at least one condition in the at least one predictor column.

14. The system of claim 13, wherein the conditions in the at least one predictor column comprises a discrete value, a range of values or an expression defining a set of values.

15. The system of claim 13, wherein an association rules algorithm is used to generate an association rules model to detect all possible associations between the values in the at least one predictor column and the format masks in the at least one format column.

16. The system of claim 13, wherein the rule engine operations further comprise:

determining a first format rule predicting a same format mask for a same format column as a second format rule;

determining whether the second format rule includes a simpler condition which is always true when the condition of the first format rule is verified; and

deleting the first format rule in response to determining that second the format rule includes the simpler condition which is always true when the condition of the first format rule is verified.

17. The system of claim 13, wherein the rule engine operations further comprise:

determining format rules having same predictor conditions and different format masks for the same format mask column; and

merging the determined format rules into a merged format rule.

18. The system of claim 17, wherein the merged format rule has the same predictor conditions and a value for the format mask defining a set including the format masks for the determined format rules.

19. The system of claim 17, wherein format rules are generated having a low minimum confidence level that is below a minimum confidence level specified by a user, and wherein the rule engine operations further comprise:

determining whether a sum of the confidence levels for the determined format rules having the same condition for the predictor column exceeds the minimum confidence level, wherein the determined format rules are merged into the merged format rule in response to determining that the sum of the confidence levels exceeds the minimum confidence level.

20. The system of claim 19, wherein the rule engine operations further comprise:

deleting the determined format rules having the same predictor conditions in response to determining that the sum of the confidence levels does not exceed the minimum confidence.

21. The system of claim 13, wherein converting the value in the format column to the format mask comprises replacing each character of a character type with a representation of the character type.

22. A method, comprising:

processing a data set having a plurality of columns and records providing data for each of the columns;

receiving selection of at least one format column for which format rules are to be generated;

receiving selection of at least one predictor column;

generating a format mask column for each selected format column;

for records in the data set, converting a value in the at least one format column to a format mask representing a format of the value in the format column and storing the format mask in the format mask column in the record for which the format mask was generated; and

processing the at least one predictor column and the at least one format mask column to generate at least one format rule, wherein each format rule specifies a format mask associated with at least one condition in the at least one predictor column.

23. The method of claim 22, wherein the conditions in the at least one predictor column comprises a discrete value, a range of values or an expression defining a set of values.

24. The method of claim 22, wherein an association rules algorithm is used to generate an association rules model to detect all possible associations between the values in the at least one predictor column and the format masks in the at least one format column.

25. The method of claim 22, further comprising:

determining a first format rule predicting a same format mask for a same format column as a second format rule;

determining whether the second format rule includes a simpler condition which is always true when the condition of the first format rule is verified; and

deleting the first format rule in response to determining that second the format rule includes the simpler condition which is always true when the condition of the first format rule is verified.

26. The method of claim 22, further comprising:

determining format rules having same predictor conditions and different format masks for the same format mask column; and

merging the determined format rules into a merged format rule.

27. The method of claim 26, wherein the merged format rule has the same predictor conditions and a value for the format mask defining a set including the format masks for the determined format rules.

28. The method of claim 26, wherein format rules are generated having a low minimum confidence level that is below a minimum confidence level specified by a user, further comprising:

determining whether a sum of the confidence levels for the determined format rules having the same condition for the predictor column exceeds the minimum confidence level, wherein the determined format rules are merged into the merged format rule in response to determining that the sum of the confidence levels exceeds the minimum confidence level.

29. The method of claim 28, further comprising:

deleting the determined format rules having the same predictor conditions in response to determining that the sum of the confidence levels does not exceed the minimum confidence.

30. The method of claim 22, wherein converting the value in the format column to the format mask comprises replacing each character of a character type with a representation of the character type.