System and method of data cleansing using rule based formatting

• US 8,150,814 B2
• Filed: 04/07/2009
• Issued: 04/03/2012
• Est. Priority Date: 04/07/2009
• Status: Active Grant

• Current Assignee: Business Objects Incorporated (SAP SE)
• Original Assignee: Business Objects Incorporated (SAP SE)
• Inventors: Felsheim, Steven E.
• Primary Examiner(s): Vy, Hung T
• Assistant Examiner(s): Cao, Phuong Thao
• Application Number:
  US12/419,811
• Publication Number:
  US 20100257145A1
• Time in Patent Office: 1,092 Days
• Field of Search: 707/999.2, 707/602, 707/692, 707/693
• US Class Current: 707/692
• 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/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

In one embodiment the present invention includes a computer-implemented method for data cleansing using rule based formatting. The method includes tokenizing and parsing a first input data and a second input data. The method further includes including a first token in a first output data if a first formatting rule component in a formatting rule is a first valid index to said first tokenized input data. The method further includes including a second token in a second output data if said first formatting rule component in the formatting rule is a second valid index to said second tokenized input data. The method further includes formatting said first output data and said second output data according to the formatting rule.

Claims

1. A computer-implemented method for data cleansing using rule based formatting, comprising:

obtaining a first input data from a first data source and a second input data from a second data source, wherein said first input data is tokenized according to a data dictionary, wherein said second input data is tokenized according to said data dictionary;

parsing, by a rule-based parsing module implemented by a hardware server, said first input data and said second input data using a predefined parsing rule including an option operator, wherein the option operator indicates that a particular index defined in the predefined parsing rule is optional;

obtaining a formatting rule, wherein said formatting rule includes one or more formatting rule components including at least one conditional format operator, wherein the at least one conditional format operator indicates whether to include a particular string literal in an output data based on the existence of a particular token;

including a first token in a first output data if a first formatting rule component in the formatting rule is a first valid index to said first tokenized input data, wherein said first token is associated with said first valid index, and including a first string literal in said first output data if said first formatting rule component in the formatting rule is a string literal;

including a second token in a second output data if said first formatting rule component in the formatting rule is a second valid index to said second tokenized input data, wherein said second token is associated with said second valid index and including a second string literal in said second output data if said first formatting rule component in the formatting rule is the string literal;

formatting, by a formatting module implemented by the hardware server, said first output data and said second output data according to the formatting rule; and

outputting said first output data and said second output data having been formatted.

2. The method of claim 1 further comprising storing, in a target data source, said first output data and said second output data having been outputted.

3. The method of claim 1 wherein said first string literal is included in said first output data if a token associated with a second formatting rule component to the immediate left of said first formatting rule component exists.

4. The method of claim 1 wherein said first string literal is included in said first output data if a token associated with a second formatting rule component to the immediate right of said first formatting rule component exists.

5. The method of claim 1 wherein a first token included in said first output data is associated with an index, wherein a second token included in said first output data is associated with the index.

6. The method of claim 5 wherein said first token included in said first output data and said second token included in said first output data are separated by a predetermined string.

7. A non-transitory computer-readable medium containing instructions for controlling a computer system to perform a method for data cleansing using rule based formatting, the method comprising:

obtaining a first input data and a second input data, wherein said first input data is tokenized according to a data dictionary, wherein said second input data is tokenized according to the data dictionary;

parsing said first input data and said second input data using a predefined parsing rule including an option operator, wherein the option operator indicates that a particular index defined in the predefined parsing rule is optional;

obtaining a formatting rule, wherein said formatting rule includes one or more formatting rule components including at least one conditional format operator, wherein the at least one conditional format operator indicates whether to include a particular string literal in an output data based on the existence of a particular token;

including a first token in a first output data if a first formatting rule component in the formatting rule is a first valid index to said first tokenized input data, wherein said first token is associated with said first valid index, and including a first string literal in said first output data if said first formatting rule component in the formatting rule is a string literal;

including a second token in a second output data if said first formatting rule component in the formatting rule is a second valid index to said second tokenized input data, wherein said second token is associated with said second valid index and including a second string literal in said second output data if said first formatting rule component in the formatting rule is the string literal; and

formatting said first output data and said second output data according to the formatting rule.

8. The non-transitory computer-readable medium of claim 7 wherein the first tokenized input data is obtained from a first data source, and the second tokenized input data is obtained from a second data source.

9. The non-transitory computer-readable medium of claim 7 further comprising storing said first output data in a target data source, and storing said second output data in said target data source.

10. The non-transitory computer-readable medium of claim 7 wherein said first string literal is included in said first output data if a token associated with a second formatting rule component to the immediate left of said first formatting rule component exists.

11. The non-transitory computer-readable medium of claim 7 wherein said first string literal is included in said first output data if a token associated with a second formatting rule component to the immediate right of said first formatting rule component exists.

12. The non-transitory computer-readable medium of claim 7 wherein said first string literal is included in said first output data if a first token associated with a second formatting rule component to the immediate left of said first formatting rule component and a second token associated with a third formatting rule component to the immediate right of said first formatting rule component both exist.

13. The non-transitory computer-readable medium of claim 7 wherein a first token included in said first output data is associated with an index, wherein a second token included in said first output data is associated with the index.

14. The non-transitory computer-readable medium of claim 13 wherein said first token included in said first output data and said second token included in said first output data are separated by a predetermined string.

15. A system for data cleansing using rule based formatting, comprising:

a hardware server that implements the system;

a tokenizing module, implemented by the hardware server, that tokenizes a first input data according to a data dictionary, and tokenizes a second input data according to said data dictionary;

a rule-based parsing module, implemented by the hardware server, that parses said first input data and said second tokenized input data using a predefined parsing rule including an option operator, wherein the option operator indicates that a particular index defined in the predefined parsing rule is optional;

a formatting module, implemented by the hardware server, that receives the said first tokenized input data and said second tokenized input data,

wherein a first token is included in a first output data if a first formatting rule component in a formatting rule is a first valid index to said first tokenized input data, wherein said first token is associated with said first valid index, wherein a first string literal is included in said first output data if said first formatting rule component in the formatting rule is a string literal, and wherein said formatting rule includes an immediate at least one conditional format operator, wherein the at least one conditional format operator indicates whether to include a particular string literal in an output data based on the existence of a particular token,

wherein a second token is included in a second output data if said first formatting rule component in the formatting rule is a second valid index to said second tokenized input data, wherein said second token is associated with said second valid index, wherein a second string literal is included in said second output data if said first formatting rule component in the formatting rule is a string literal, and

wherein said first output data and said second output data are formatted according to the formatting rule;

a first data source that stores said first input data;

a second data source that stores said second input data; and

a third data source that stores said first output data and said second output data.

16. The system of claim 15 further comprising a rule repository that stores said formatting rule and said predefined parsing rule.

17. The system of claim 16, further comprising a memory that stores at least one of said formatting rule and said predefined parsing rule.

18. The system of claim 15 further comprising a data dictionary repository that stores said data dictionary.

19. A computer-implemented method for data cleansing using rule based formatting, comprising:

obtaining a first input data from a first data source and a second input data from a second data source, wherein said first input data is tokenized according to a data dictionary, wherein said second input data is tokenized according to said data dictionary;

parsing, by a rule-based parsing module implemented by a hardware server, said first input data and said second input data using a predefined parsing rule;

obtaining a formatting rule, wherein said formatting rule includes one or more formatting rule components;

including a first token in a first output data if a first formatting rule component in the formatting rule is a first valid index to said first tokenized input data, wherein said first token is associated with said first valid index, and including a first string literal in said first output data if said first formatting rule component in the formatting rule is a string literal;

including a second token in a second output data if said first formatting rule component in the formatting rule is a second valid index to said second tokenized input data, wherein said second token is associated with said second valid index and including a second string literal in said second output data if said first formatting rule component in the formatting rule is the string literal;

formatting, by a formatting module implemented by the hardware server, said first output data and said second output data according to the formatting rule; and

outputting said first output data and said second output data having been formatted,

wherein said first string literal is included in said first output data if a first token associated with a second formatting rule component to the immediate left of said first formatting rule component and a second token associated with a third formatting rule component to the immediate right of said first formatting rule component both exist, and

wherein said second formatting rule component corresponds to a left-facing conditional format operator, and wherein said third formatting rule component corresponds to a right-facing conditional format operator.