System and Method of Data Cleansing using Rule Based Formatting

• US 20100257145A1
• Filed: 04/07/2009
• Published: 10/07/2010
• 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.
• Granted Patent: US 8,150,814 B2
• Time in Patent Office: Days
• 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;

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.

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 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 left of said first formatting rule component both exist.

6. The method of claim 5 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.

7. 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.

8. The method of claim 7 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.

9. A 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;

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; and

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

10. The computer-readable medium of claim 9 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.

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

12. The computer-readable medium of claim 9 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.

13. The computer-readable medium of claim 9 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.

14. The computer-readable medium of claim 9 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 left of said first formatting rule component both exist.

15. The method of claim 9 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.

16. The method of claim 15 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.

17. 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;

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,

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.

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

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

20. The system of claim 17 further comprising a data dictionary repository that stores said data dictionary.