JPH11238006A - Data cleaning processing method and apparatus, and recording medium recording data cleaning processing program - Google Patents

Abstract

(57) [Summary] [PROBLEM] To efficiently create an integrated record by removing inconsistent data from a file in which data having a non-uniform expression format is stored without the need to individually create an application program or a manual visual check process. And a recording medium storing a data cleaning processing program and a data cleaning processing program. SOLUTION: A record unification processing unit 1 creates a unified record of data items in a unified expression format for each data item of a record according to a record unification definition 4, and a record unification processing unit 2 complies with a record unification definition 5. An integrated record is created by integrating the unified records having the same data item value with respect to the unified record group, and the inter-data item constraint condition check processing unit 3 executes the inter-data item constraint condition check definition 6 according to the inter-data item constraint condition check definition 6. The constraint condition is checked, and a data item constraint condition check result record to which a data item for displaying the result is added is created.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a unified representation format in which a plurality of records are matched from one or more files storing data in which the representation format of data values is not uniform, and inconsistent data is removed. More specifically, the present invention relates to a data cleaning processing method and apparatus for creating an integrated record and a recording medium on which a data cleaning processing program is recorded, and more specifically, for example, when storing data extracted from a plurality of backbone databases in an integrated database or a data warehouse. The present invention relates to a data cleaning method and apparatus which can be applied to the data cleaning processing. The file used here is a file in which data extracted from the database is stored.

[0002]

2. Description of the Related Art When data extracted from a plurality of databases is stored in a data warehouse, the data is extracted from the plurality of databases. Therefore, (1) the data items have the same data item name but different meanings. Or
(2) Even if the data item names are different, the meaning of the data is the same, (3) the data attribute (type) is different,
(4) Data formats and character codes may differ, and (5) data inconsistency may occur.

The above problems (1) to (4) depend on differences in the schema structure of a plurality of databases, and a data distribution platform DB-STREAM for converting data into a unified schema defined as a data warehouse.
(Information Processing Society of Japan Vol.38, No.12,1997, pp.2553-256
5) has been resolved.

[0004]

The problem (5) described above is a problem depending on the difference in data values between a plurality of databases. Conventionally, detection and removal of inconsistent data (this process is called a data cleaning process) It is necessary to individually create an application program for performing this process, or to manually deal with the problem by visually checking all data, which is inefficient.

[0005] The present invention has been made in view of the above,
Its purpose is to eliminate the need for individual creation of application programs and manual visual confirmation processing.
It is an object of the present invention to provide a data cleaning method and apparatus for efficiently creating an integrated record by removing inconsistent data from a file storing data having a non-uniform expression format, and a recording medium on which a data cleaning program is recorded.

[0006]

In order to achieve the above object, according to the present invention, a plurality of records are matched from an input file storing data in which the representation format of data values is not uniform. A data cleaning processing method for creating an integrated record by removing data, a record unification definition that describes a method for unifying the data representation format of records, a record integration definition that describes a method for integrating multiple records, and an inside record A standard data unified component group that unifies the data expression format for each data item, and a standard data unified component group other than the standard data unified component group Define individual data unification parts group that enables unification specified by the user, and according to the record unification definition, Using the standard data unified parts group and the individual data unified parts group for each data item of one or more records stored in the output file, create a data item in a unified representation format and add it to each record Perform record unification processing to create a unified record
According to the record unification definition, a unified record in which unified records having the same data item value are integrated and a unified record which does not have the same data item value are added to the unified record group that is the result of the record unification process. Performs record integration processing to output records to separate files, checks the data item constraint conditions in the integrated record that is the result of the record integration processing according to the data item constraint condition check definition, and displays the result. Creates a data item constraint condition check result record with the data item added and outputs the data item constraint condition check result record to a separate file according to the data item value that displays the check result. Make a summary.

According to the first aspect of the present invention, a unified record is created by creating a unified expression format data item for each data item of the record stored in the file in accordance with the unified record definition. According to the record integration definition, create a unified record that integrates unified records that have the same data item value for the unified record group and a non-integrated record that is a unified record that does not have the same data item value, and create separate files for each. In accordance with the definition of the inter-data item constraint condition check, the inter-data item constraint condition in the integrated record is checked, and the data item for displaying the result is added to create a data item constraint condition check result record. Output to a separate file according to the data item value that displays the check result. With no need to create a data cleaning processing application program for each Le, visual confirmation work even without the need for all the data by hand,
Efficiency can be improved.

[0008] According to the present invention, a plurality of records are matched from an input file storing data in which data values are not represented in a uniform format, and inconsistent data is removed to create an integrated record. A cleaning processor that describes a record unification definition that describes how to unify the data representation format of records, a record integration definition that describes how to integrate multiple records, and a method of checking constraints between data items in a record Storage means for storing the constraint check definitions between data items, standard data unified parts group for unifying the data expression format for each data item, and unification specified by the user in addition to the standard data unified parts group A component group storage means for defining and storing individual data unified component groups that enable Using the standard data unified parts group and the individual data unified parts group for each data item of one or more records stored in the input file, create a data item in a unified expression format, and A record unification processing means for adding and creating a unified record, and integration integrating unified records having the same data item value into a unified record group as a result of the record unification processing according to the record unification definition A record integration processing means for outputting a record and a non-integrated record that is a unified record having no identical data item value to separate files, and a record integration processing result in the integrated record according to the data item constraint condition check definition. A data item that checks the constraints between data items and adds a data item that displays the result Create a constraint check result record, the check according to the result data item value for displaying, and summarized in that and a between data items constraint checking processing means for outputting the data items between constraint checking result record in a separate file.

According to the present invention, in accordance with the record unification definition, a unified record is created by creating a data item in a unified expression format for each data item of the record stored in the file. According to the record integration definition, create a unified record that integrates unified records that have the same data item value for the unified record group and a non-integrated record that is a unified record that does not have the same data item value, and create separate files for each. In accordance with the definition of the inter-data item constraint condition check, the inter-data item constraint condition in the integrated record is checked, and the data item for displaying the result is added to create a data item constraint condition check result record. Output to a separate file according to the data item value that displays the check result. With no need to create a data cleaning processing application program for each Le, visual confirmation work even without the need for all the data by hand,
Efficiency can be improved.

[0010] Further, according to the present invention, a plurality of records are matched from an input file storing data in which a data value is not expressed in a uniform format, and inconsistent data is removed to create an integrated record. A recording medium on which a cleaning processing program is recorded, wherein a record unification definition that describes a method for unifying the data representation format of the record;
A standard that unifies the data representation format for each data item by providing a record integration definition that describes how to integrate multiple records and a definition check for data item constraints that describes how to check constraints between data items in records In addition to the data unifying parts group and the standard data unifying parts group, an individual data unifying parts group that enables unification specified by the user is defined, and is stored in the input file according to the record unifying definition. Using the standard data unified parts group and the individual data unified parts group for each data item of one or more records, create a data item in a unified expression format and add it to each record to create a unified record Perform a record unification process to create a unified record group that is the result of the record unification process according to the record integration definition. Performing a record integration process of outputting an integrated record obtained by integrating unified records having the same data item value and a non-integrated record which is a unified record having no same data item value to separate files, and According to the check definition, check the constraint condition between data items in the integrated record that is the result of record integration processing, create a data item constraint condition check result record with the data item that displays the result, and display the check result The gist of the present invention is to record a data cleaning processing program for performing a data item constraint condition check process for outputting a data item constraint condition check result record to another file in accordance with a data item value on a recording medium.

According to the third aspect of the present invention, in accordance with the record unification definition, a data item in a unified expression format is created for each data item of the record stored in the file to create a unified record. According to the record integration definition, create a unified record that integrates unified records that have the same data item value for the unified record group and a non-integrated record that is a unified record that does not have the same data item value, and create separate files for each. In accordance with the definition of the inter-data item constraint condition check, the inter-data item constraint condition in the integrated record is checked, and the data item for displaying the result is added to create a data item constraint condition check result record. Data processing processing that outputs a check result to a separate file according to the data item value Since that records ram on a recording medium, using the recording medium, it is possible to enhance the flow properties.

[0012]

Embodiments of the present invention will be described below with reference to the drawings.

FIG. 1 is a block diagram showing a configuration of a data cleaning processing apparatus according to an embodiment of the present invention.
The data cleaning processing apparatus shown in FIG. 1 matches a plurality of records from an input file in which data in which data values are expressed in a non-uniform format is stored, removes inconsistent data, creates an integrated record, and outputs the integrated record to a file. In the present embodiment, as an example, data obtained by integrating records having the same telephone number and customer name from a file storing data extracted from two customer databases in which expression forms of telephone numbers and customer names are not uniform The case where the data cleaning processing apparatus of the present invention is applied to data cleaning processing when constructing a warehouse is described.

In FIG. 1, reference numeral 1 denotes a record unification processing unit, 2 denotes a record integration processing unit, 3 denotes a data item constraint condition check processing unit, 4 denotes a record unification definition, 5 denotes a record integration definition, and 6 denotes a data item. 7 is a standard data unified component group, 8 is an individual data unified component group, 9 and 10 are input files, 11 and 12 are record unification processing result files, 13 is an integrated record storage file, 14 And 15 are non-integrated record storage files,
16 is a data constraint condition check result OK file, 17
Is a data constraint condition check result NG file.

The record unification definition 4 is a definition that describes a method of unifying the data expression format of records. The record integration definition 5 is a definition that describes a method of integrating a plurality of records. Check definition 6
Is a definition describing a method of checking constraints between data items in the integrated code. Record unification definition 4, record integration definition 5, data item constraint condition check definition 6
Are, for example, stored in a memory or the like.

The standard data unifying component group 7 is a component group for unifying the data expression format for each data item.
The individual data unifying component group 8 is a component group that can be unified by the user in addition to the standard data unifying component group 7. Note that the standard data unified parts group 7 and the individual data unified parts group 8 have their information stored in a memory or the like.

According to the record unification definition 4, the record unification processing unit 1 uses the standard data unification parts group 7 and the individual data unification parts group 8 to form a unified expression format for the data items of the records in the input file 9. Are added and stored in the record unification processing result file 11. Next, similarly to the above, the record unification processing unit 1 adds a data item in a unified expression format to the data item of each record in the input file 10 and stores the data item in the record unification processing result file 12.

According to the record integration definition 5 specifying the data items to be compared, the record integration processing unit 2 records the record unification processing result file 11 and the record unification processing result file 12 which are the processing results of the record unification processing unit 1.
The records inside are matched, and records having the same data item value are stored in the integrated record storage file 13 as integrated records. The records in the record unification processing result file 11 and the record unification processing result file 12 that do not have the same data item value are the non-integrated record storage file 14 and the non-integrated record storage file 1, respectively.
5 is stored.

Data item constraint condition checking section 3
Checks whether the data item value of the integrated record in the integrated record storage file 13 satisfies the inter-data item constraint condition check definition 6, and adds the result (a value for identifying the same or different) as a data item. In accordance with the data item value, the data constraint condition check result OK file 16 or the data constraint condition check result NG file 17 is stored.

Next, details of each part will be described with reference to explanatory diagrams shown in FIGS. 2 to 4 and a flowchart showing the entire operation shown in FIG.

First, the details of the record unification processing unit 1 will be described with reference to the explanatory diagram of FIG.

In FIG. 2, 1 is a record unification processing unit, 4 is a record unification definition, 7 is a standard data unification component group, 8 is an individual data unification component group, 9 and 10 are input files, 11 and 12 Is a record unification processing result file, 18 and 19 are input records, and 20 and 21 are unified records.

The input files 9 and 10 store one or more input records 18 and 19, respectively. The input record 18 includes a plurality of data items A1 to An. Input record 19
Is composed of a plurality of data items B1 to Bm.

The record unification processing unit 1 reads the input record 18 from the input file 9 (step S in FIG. 5).
11) According to the record unification definition 4 specifying the standard data unifying parts group 7 and the individual data unifying parts group 8 used for the data items A1 to An of the input record 18, the data items A1 to Data items NA1 to NAn in the unified expression format corresponding to An are added. For example, when A1 is a data item that stores a telephone number, various expression forms (“012-345”) of the telephone number are used.
−6789 ”,“ 012 (345) 6789 ”,“ (0
12) 345-6789 ”) in a unified expression format (“ 0
123456789 ") and add it as a data item NA1. When A2 is a data item for storing a customer name, various expressions of the customer name are unified (for example," (stock) ... "," Stock)… ”,“ (stock)… ”,“… stock ”,“… (stock) ”,“… (stock), etc. are unified into “stock ....”), and added as data item NA2 to unify records. This is stored in the conversion result file 11. This is performed for all the input records 18 of the input file 9 (step S13).

Next, the record unification processing unit 1 performs the same processing as described above for all the input records 19 in the input file 10 to create a record unification processing result file 12 (step S13). .

Next, details of the record integration processing section 2 will be described with reference to FIG.

In FIG. 3, reference numeral 2 denotes a record integration processing unit;
5 is a record integration definition, 11 and 12 are record unification processing result files, 13 is an integrated record storage file, 1
4 and 15 are non-integrated record storage files, 20 and 21 are unified records, 22 is an integrated record, and 23 and 24 are non-integrated records.

In the record unification processing result file 11 and the record unification processing result file 12,
One or more unified records 20 and unified records 21 are stored. The unified record 20 includes the data item A1
To An and NA1 to NAn. The unified record 21 includes data items B1 to Bm and NB1 to NBm.

The record integration processing unit 2 performs a record integration definition 5 describing a method of integrating a plurality of records (for example, a definition when integrating by a telephone number in a unified expression format: “Unification with data item NA1 of unified record 20”). Integrate records where the value of data item NB1 of record 21 is the same ”)
From the unified records 20 and 21 in which the values of the data item NA1 and the data item NB1 match, the data item A1
~ An, NA1 ~ NAn, B1 ~ Bm, NB1 ~ NBm
Is created and stored in the integrated record storage file 13 (step S15). There is no unified record 21 having a data item NB1 that matches the value of the data item NA1.
Is stored in the non-integrated record storage file 14 as the non-integrated record 23. The unified record 21 having no unified record 20 having the data item NA1 that matches the value of the data item NB1 is stored in the non-integrated record storage file 15 as the non-integrated record 24 (step S).
17).

Next, details of the inter-data item constraint condition check processing section 3 will be described with reference to FIG.

In FIG. 4, reference numeral 3 denotes an inter-data item constraint condition check processing unit, 6 denotes a data item constraint condition check definition, 13 denotes an integrated record storage file, 16 denotes a data constraint condition check result OK file, and 17 denotes a data constraint condition. Check result NG file, 22 is integrated record, 2
Reference numeral 5 denotes a data item constraint condition check result record.

In the integrated record storage file 13, 1
One or more integrated records 22 are stored. The integrated record 22 includes data items A1 to An, NA1 to NAn,
B1 to Bm and NB1 to NBm.

Data item constraint condition checking section 3
Is defined between the data items of the integrated record 22 according to the inter-data item constraint condition check definition 6 (for example, a definition for checking whether the customer name is the same: “data items NA2 and NB2 are the same”). The constraint condition is checked, and a data item NC condition check result record 25 to which a data item NC indicating a check result (for example, the same is “0” and the difference is “9”) is created, and the data item NC is checked. If the value is the same value (“0”), the result is stored in the data constraint condition check result OK file 16. If the value of the data item NC is the value indicating the difference (“9”), the data constraint condition check is performed. Result NG file 17
(Step S19).

[0034]

As described above, according to the present invention,
A unified record is created by creating a unified expression format data item for each data item of the record according to the record unification definition, and a unified record having the same data item value for the unified record group according to the record unification definition To create an integrated record, check the inter-data item constraint conditions in the integrated record according to the inter-data item constraint condition check definition, and add the data item Since it is created and the record is output to a separate file according to the data item value indicating the check result, it is not necessary to separately create an application program for the data cleaning process for each input file as in the past, and all the data must be manually created. Thus, efficiency can be improved without the need for visual confirmation work.

In particular, in the present invention, the data cleaning process can be realized only by the user performing three definitions of the record unification definition, the record integration definition, and the data item constraint condition check definition. In addition, by dividing the data cleaning process into record unification process, record integration process, and data item constraint condition check process, if a change occurs in the data cleaning process content, the correction location of the process content is limited and correction is easy. In addition, each process can be easily reused, the inconsistency record can be removed in stages, and the cause of the inconsistency can be identified at an early stage.

[Brief description of the drawings]

FIG. 1 is a block diagram illustrating a configuration of a data cleaning processing device according to an embodiment of the present invention.

FIG. 2 is an explanatory diagram showing a detailed operation of a record unification processing unit used in the data cleaning processing device shown in FIG.

FIG. 3 is an explanatory diagram showing a detailed operation of a record integration processing unit used in the data cleaning processing device shown in FIG.

FIG. 4 is an explanatory diagram showing a detailed operation of a data item constraint condition check processing unit used in the data cleaning processing device shown in FIG. 1;

FIG. 5 is a flowchart showing an overall process of the data cleaning processing device shown in FIG. 1;

[Explanation of symbols]

 1 Record unification processing unit 2 Record unification processing unit 3 Data item constraint condition check processing unit 4 Record unification definition 5 Record unification definition 6 Data item constraint condition check definition 7 Standard data unification component group 8 Individual data unification component group

 ──────────────────────────────────────────────────の Continued on the front page (72) Inventor Tetsuo Ikeda Nippon Telegraph and Telephone Corporation 3-19-2 Nishishinjuku, Shinjuku-ku, Tokyo

Claims (3)

[Claims] 1. A data cleaning processing method for matching a plurality of records from an input file storing data in which a data value has a non-uniform expression format and removing inconsistent data to create an integrated record, comprising: A record unification definition that describes how to unify the data representation format, a record integration definition that describes how to integrate multiple records, and a data item constraint check definition that describes how to check the constraints between data items in a record A standard data unified component group that unifies the data expression format for each data item, and an individual data unified component group that enables unification specified by the user in addition to the standard data unified component group According to the record unification definition, for each data item of one or more records stored in the input file, Using the standard data unified parts group and the individual data unified parts group to create a data item in a unified expression format, and perform a record unification process of creating a unified record by adding to each record; According to the unification definition, for the unified record group that is the result of the record unification processing, the unified record that unified the unified records that have the same data item value and the non-integrated record that is the unified record that does not have the same data item value Performs a record integration process to output to separate files, checks the constraint condition between data items in the integrated record, which is the result of the record integration process, and adds a data item to display the result according to the above-mentioned constraint definition check between data items Creates a check result record between the data item constraint conditions and displays the check results. According to another item value,
A data cleaning processing method characterized by performing a data item constraint condition check process of outputting a data item constraint condition check result record to another file. 2. A data cleaning processing apparatus for matching a plurality of records from an input file in which data in which a data value expression format is not uniform is stored, and creating an integrated record by removing inconsistent data. A record unification definition that describes how to unify the data representation format, a record integration definition that describes how to integrate multiple records, and a data item constraint check definition that describes how to check the constraints between data items in a record Definition storage means for storing, a standard data unifying component group for unifying the data expression format for each data item, and an individual data unifying component for enabling unification specified by a user other than the standard data unifying component group Component group storage means for defining and storing a group, and stored in an input file according to the record unification definition Using the standard data unified parts group and the individual data unified parts group for each data item of one or more records, create a data item in a unified expression format and add it to each record to create a unified record. According to the record unification processing means to be created, and according to the record unification definition, an integrated record and an identical data item value obtained by integrating unified records having the same data item value are obtained for a unified record group that is a result of the record unification process. A record integration processing means for outputting non-integrated records, which are unified records that do not have, to separate files, and checking the inter-data item constraint conditions in the integrated record, which is the record integration processing result, according to the data item constraint condition check definition And the data item to display the result is added. Create a de, according to the data item value to display the check result,
A data cleaning processing apparatus comprising: a data item constraint condition check processing means for outputting a data item constraint condition check result record to another file. 3. A recording medium storing a data cleaning processing program for matching a plurality of records from an input file in which data in which a data value is expressed in a non-uniform format is stored and removing inconsistent data to create an integrated record. A record unification definition that describes how to unify the data representation format of records, a record integration definition that describes how to integrate multiple records, and a data item that describes how to check constraints between data items in a record A standard data unified component group that provides a constraint condition check definition and unifies the data expression format for each data item, and an individual data unified component that enables unification specified by the user in addition to the standard data unified component group A group is defined, and one or more records stored in the input file are defined according to the record unification definition. The standard data unified parts group and the individual data unified parts group are used for each data item of the code, a data item in a unified expression format is created, and added to each record to create a unified record According to the record integration definition, a unified record that integrates unified records having the same data item value and unification that does not have the same data item value are performed for the unified record group that is the result of the record unification process according to the record unification process Performs a record integration process to output each non-integrated record that is a structured record to a separate file, and checks the inter-data item constraint condition in the integrated record that is the result of the record integration process according to the data item constraint condition check definition. Create a data item constraint condition check result record to which the data item for displaying the result is added, According to the data item value to display the Ekku result,
A recording medium storing a data cleaning processing program for performing a data item constraint condition check process for outputting a data item constraint condition check result record to a separate file.