JP2012104005A - Data verification system and data verification method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To verify data by non-programming by using a common component for performing check processing of the validity of data.SOLUTION: A task table 11 includes a plurality of tables in which transaction data which generate for every transaction is registered. A relevant master table 12 includes a plurality of tables in which master data to be commonly used in each transaction is registered. A check item table 13 is configured as a table in which check specifications showing conditions which should be satisfied by the data registered in every data item in each table included in the task table 11 are registered. A standard check component 14 is configured to extract data from the task table 11 and the relevant master table 12, and to verify whether or not the check specifications are satisfied about each data item of each table included in the task table 11 by referring to the check item table 13. The standard check component 14 is configured to store information showing the check result in a check result table 15.

Description

  The present invention relates to a data verification system and a data verification method for verifying the validity of data set in a table.

Most new development projects for mission-critical systems require data migration from the current system. Therefore, the tool “Data Migration Tool” that extracts data from the current system and converts it into a form that matches the table structure of the new system, and the tool “Data that verifies whether the consistency of the migrated data is ensured” A “verification tool” is required.
However, in business application development projects, data migration tools and data verification tools are generally used for reasons such as the tendency to prioritize application development and the difficulty of securing manpower for experts in the current system. Often, short-term and low-cost development is forced.
Therefore, standardizing the data migration tool and the data verification tool and reducing the development cost is one of the important issues, and the generalization of the tool is required so that individual development is not required for each project.

Therefore, for example, Patent Document 1 discloses an invention that automatically creates a shell script related to data migration and automates data migration with respect to the “data migration tool”.
Patent Document 2 discloses an invention in which a representative data pattern before and after migration is extracted with respect to the “data verification tool”, and an administrator visually checks the result to verify the data migration.

JP 2008-171225 A JP 2009-70244 A

However, the “data verification tool” disclosed in Patent Document 2 only extracts and compares representative data patterns before and after migration, and is not sufficient for data validation. Further, even if a data pattern is extracted, a human being checks whether or not the value of each item of the data pattern is the same before and after the migration, so that the development process is still burdened. Furthermore, it can be used only in a specific data format and is not versatile.
On the other hand, in the data verification, the table definition of the new system is different for each customer, but the viewpoint of data validity check is the same in any system.

  The present invention has been made in view of the above circumstances, and can perform data verification by non-programming using a common component for performing data validity check processing, and develop a data verification tool for each project. An object of the present invention is to provide a data verification system and a data verification method that eliminate the need for.

In order to achieve the above object, the data verification system of the present invention includes:
A business table including a plurality of tables in which data generated for each transaction is registered, a related master table in which data commonly used in each transaction is registered, and data items in each table included in the business table Storage means for storing a check item table in which a check specification indicating a condition to be satisfied by data registered in each data item is registered, and a check result table in which information indicating a check result is stored;
Check specification registration means for registering each check specification created by a user in the check item table;
Parameter character string extracting means for extracting a parameter character string indicating a check specification from the check item table;
Query execution means for creating a query based on the parameter character string extracted by the parameter character string extraction means, and executing the created query on the business table or both the business table and the related master table; ,
A check for determining whether data registered in each table included in the business table satisfies a check specification registered in the check item table based on a result of a query executed by the query execution unit Specification judging means;
Check result table storage means for storing information indicating a result of determination by the check specification determination means in the check result table;
Is provided.

Further, the data verification method of the present invention includes:
A business table including a plurality of tables in which data generated for each transaction is registered, a related master table in which data commonly used in each transaction is registered, and data items in each table included in the business table Data provided with a storage means for storing a check item table in which a check specification indicating a condition to be satisfied by data registered in each data item is stored and a check result table in which information indicating a check result is stored A data verification method in a verification system,
Registering each check specification created by a user in the check item table;
Extracting a parameter character string indicating a check specification from the check item table;
Creating a query based on the extracted parameter string, and executing the created query on the business table or both the business table and the related master table;
Determining whether data registered in each table included in the business table satisfies a check specification registered in the check item table based on a result of the executed query;
Storing information indicating a result of the determination in the check result table;
Is provided.

  According to the present invention, it is possible to perform data verification by non-programming using a common component that performs a data validity check process, and it becomes unnecessary to develop a data verification tool for each project.

It is a figure which shows an example of a structure of the data verification system which concerns on embodiment of this invention. It is a figure which shows an example of a structure of a check item definition document. It is a figure which shows the list of typical standard check components. It is a figure which shows an example of the check specification of each standard check component. It is a figure which shows an example of the table definition of a check item table. It is a figure which shows an example of the processing flow at the time of the check execution of a data verification system. It is a figure which shows the general processing flow of a standard check component. It is a figure which shows an example of the processing flow of a master presence check component. It is a figure which shows an example of a structure of a check result table. It is a figure which shows an example of the processing flow of a digit number check component.

  Hereinafter, a data verification system according to an embodiment of the present invention will be described with reference to the drawings.

FIG. 1 shows an example of the configuration of a data verification system 1 according to an embodiment of the present invention.
The data verification system 1 includes a business table 11, a related master table 12, a check item table 13, a standard check component 14, a check result table 15, and a control unit 16.

The business table 11 is described in an order slip, a sales slip, or the like, and stores transaction data that occurs each time a transaction is made. The business table 11 includes a plurality of tables in which transaction data regarding product orders, sales, and the like are registered. For example, the business table 11 includes an order table in which product order data is registered and a sales table in which product sales data is registered. . Each table included in the business table 11 is given a table ID to identify each table. The business table 11 is, for example, a table in which data is extracted from the current system by a data migration tool and converted into a format suitable for the table configuration of the new system. The business table 11 is a verification target by the data verification system 1.
The related master table 12 stores master data that is known in advance, rather than every transaction, such as customer data and product data. The related master table 12 includes a plurality of tables in which master data commonly used in each transaction is registered, and includes, for example, a customer table in which customer data is recorded, a product table in which product data is recorded, and the like. Each table included in the related master table 12 is assigned a master table ID to identify each table.

The check item table 13 is a table in which, for each data item in each table such as an order table or sales table included in the business table 11, a check specification indicating a condition to be satisfied by data registered in the data item is registered. .
The upload tool 17 is a tool that assists the data verification system 1. The user first describes the check specification in the check item definition document 18 while referring to the standard check component list document in which the check viewpoint of each standard check component 14 is described. Next, the user uploads the check specification from the check item definition document 18 to the check item table 13 using the upload tool 17.

As will be described later, the standard check component 14 refers to the check item table 13 and extracts data from the business table 11 and the related master table 12 to execute a check process. Then, the standard check component 14 stores predetermined information indicating the checked result in the check result table 15. Although only one standard check component 14 is shown in FIG. 1, a plurality of standard check components 14 may be provided.
The user can check whether the data registered in the business table 11 is valid by referring to the check result table 15.
The control unit 16 manages and controls the entire data verification system 1.

The functions of the data verification system 1 are realized by a computer including a CPU (Central Processing Unit), a memory, a storage device, an input device, and a display device.
The memory includes a RAM (Random Access Memory), a ROM (Read Only Memory), and the like. The memory stores a data verification program and an upload program. The function of the data verification system 1 is realized by the CPU executing the data verification program. Further, the function of the upload tool 16 is realized by the CPU executing the upload program.
The storage device includes a hard disk device, an optical disk device, and the like. The storage device stores a business table 11, a related master table 12, a check item table 13, and a check result table 15.
The input device includes a keyboard and a mouse. The display device includes a display, a printer, and the like. For example, the user describes the check specification of each data item of the work table 11 in the check item definition 18 using the input device while referring to the list of standard check parts displayed on the display device.

FIG. 2 shows an example of the configuration of the check item definition document 18.
Before the verification by the data verification system 1, the check item definition 18 is determined by each standard check component 14 for each data item included in each table such as an order table or a sales table registered in the business table 11. The user specifies whether to verify that such a check viewpoint is satisfied.
In general, a business system table definition document often describes a data item ID, the number of digits, a data type, a description of the data item, and the like (21 to 23). In the check item definition document 18 according to the present embodiment, in addition to each of these check items, in order to define details (check specifications) of the check viewpoint for each data item, the format of the table definition document is expanded, and each data item The check specifications are set (24 to 26). For example, in the check item definition 18 shown in FIG. 2, in addition to the data item ID 21, the data type 22, and the data item description 23, a check specification 24 for a fixed value check component, a check specification 25 for a digit number check component, A check specification 26 of the master existence check component is set.
Each check specification includes, for example, four check items ((1) to (4)). Each check item corresponds to an argument set in the standard check part.

  FIG. 3 shows a list of typical standard check parts. The standard check component 14 is a component tool that performs data verification on each data item of each table included in the business table 11 based on a predetermined check viewpoint. The standard check component 14 includes, for example, a fixed value check component for checking that the data value is a specified fixed value, and a digit number check component for checking that the data value has a specified number of digits. In this embodiment, it is verified whether or not each data item to be verified satisfies a check specification specified for each standard check component 14.

FIG. 4 shows an example of a check specification for each standard check component. This check specification is defined in the standard check parts list document.
FIG. 4A shows the check specifications of the digit number check component. The digit number check component checks whether the given verification data has the number of digits specified by the first argument. For this reason, “special character string + number of digits is specified” is described in the explanation column of check item ID = 1 in FIG. The user who sees this designates the number of digits that the data of each data item can take in the first cell (1) of the check specification 25 of the digit number check component in the check item definition 18 of FIG. For example, in the case of item 1 in FIG. 2, it means that “company code” must be four digits.
Since the digit number check component checks only the number of digits, the second argument (check item ID = 2) to the fourth argument (check item ID = 4) have no function.
In the first argument (check item ID = 1), a special character string can be specified together with the number of digits. Special character strings include, for example, “all” and “bar”, where “all” means to verify the number of digits specified by the number of digits with one-byte character string as one unit, and “bar” means byte unit This means that the number of digits specified by the number of digits is verified.

  FIG. 4B shows the check specification of the master presence check part. The first argument (check item ID = 1) of the master existence check component is used to set a master table ID for performing existence check. The second argument (check item ID = 2) sets the data item ID of the master table. This is to set the ID of the data item to be checked in the table included in the related master table 12 specified by the first argument (check item ID = 1). In the third argument (check item ID = 3), a join condition is set. This sets a join condition between the target table and the master table. The fourth argument (check item ID = 4) has no function.

  Note that the arguments of each standard check component must be set (check items that must be set) or set arbitrarily (checks that do not necessarily have to be set) in light of the verification contents of the standard check component. Item). For any setting, if no value is set, the data verification system 1 does not perform data verification.

  The user inputs the value of each cell of the check item definition 18 based on the standard check parts list document. Depending on the data verification target, there is a standard check part that does not need to be implemented. In this case, the value can not be set in the cell for the check part, so that it can be excluded.

  In this embodiment, each standard check component can set a maximum of four arguments, but the number of arguments that can be set is variable according to the verification content.

FIG. 5 shows an example of the table definition of the check item table 13. The table ID and data item ID are used as keys, and an argument (check item) is stored for each standard check component. Here, the table ID is an ID for identifying each table (such as an order table or a sales table) included in the business table 11.
By converting the check item definition document 18 of FIG. 2 with the upload tool 16 or the like, the contents set in the check item definition document 18 are registered in the check item table 13.

  The data verification system 1 according to the present embodiment performs data verification with reference to the check item table 13. The check items 41 of each standard check part in the check item table 13 are arranged in the order of a predetermined standard check part, and each standard check part has up to the fourth argument. It is not necessary to manage whether or not this is true, and can be processed by the data verification system 1.

  FIG. 6 shows an example of a processing flow when the data verification system 1 executes a check. The function of the data verification system 1 is realized by the CPU executing the data verification program. When the execution of the data verification program is started (when the data verification system 1 is started), when the table ID of the table to be checked (order reception table, sales table, etc.) is received as an argument, the control unit 16 Only the table in the check item table 13 specified by the table ID is set as a check target. On the other hand, when there is no argument, the control unit 16 sets all the tables set in the check item table 13 as a check target.

The control unit 16 extracts a parameter character string indicating a check specification from the check item table 13 and acquires a check item (S101). The control unit 16 calls and executes each standard check component 14 corresponding to the check specification in which the parameter character string is set (S102). For this reason, the standard check component 14 corresponding to the check specification in which no parameter character string is set is not executed.
FIG. 7 shows a general processing flow of the standard check component 14 although the process of the standard check component 14 is different depending on each component. The standard check component 14 first receives the parameter character string acquired in step S101, and creates a query composed of the query character string (S201). Next, the created query is executed on the business table 11 and the related master table 12 (S202, S203). Then, the standard check component 14 acquires the result of the executed query, and whether or not the data registered in each table included in the business table 11 satisfies the check specification registered in the check item table 13. judge. As a result of the determination, if it is determined that the check specification is not satisfied, the standard check component 14 stores information indicating that the check specification is not satisfied in the check result table 15 (S204).
If it is determined that the check specification is satisfied as a result of the determination, the standard check component 14 may store information indicating that the check specification is satisfied in the check result table 15.

The control unit 16 sequentially executes the standard check components 14 exemplified in the list of FIG. 3 one by one (S102, S103). As shown in FIG. 7, each standard check component 14 acquires data from the check target business table 11 and performs data check with the related master table 12 as necessary. If an error occurs, the error content is output to the check result table 15. Since error output is implemented to commit asynchronously, it is possible to query error output lines even while the check process is being executed.
When the check by all the standard check components 14 is completed for one check target table (S103: Yes), the control unit 16 determines whether or not the next check target table exists (S104). In S104: Yes, the standard check components 14 are sequentially executed one by one for the check target table (S102, S103).

About the process shown in FIG. 7, the specific processing content for every standard check component 14 is illustrated.
FIG. 8 shows an example of the processing flow of the master existence check component. FIG. 8A shows processing related to query execution for the business table 11 and the related master table 12 in steps S202 and S203. The master existence check component assembles a query character string that externally joins the check target business table 11 and the related master table 12 with data items as keys (S301). Next, a query composed of this query character string is executed to obtain the number of cases where the corresponding data item of the related master table 12 is NULL (S302).
Next, FIG. 8B is a process of determining an error based on the query result in step S204 and storing it in the check result table 15. The master existence check component makes a result determination based on the number of results of the query executed in step S302 (S303). First, if the result is 0 (S303: Yes), it means that all the data items existing in the business table 11 are also present in the related master table 12, so that the master existence check component is consistent. (S304).

  On the other hand, if the acquired number is greater than 0 (S303: No), it is equivalent to the existence of a value that exists in the business table 11 and does not exist in the related master table 12, so that the master existence check component is an error. And Specifically, the master existence check component sets the error message to "Check that the code that does not exist in the related master table exists in the check target table", the execution date of the data verification system 1, the table name to be verified, etc. 9 is added to the check result table 15 (S305).

FIG. 10 shows an example of the processing flow of the digit number check component. In FIG. 10A (corresponding to step S202 in FIG. 7), a record of the data item to be checked is acquired from the work table 11 based on the check item table 13 (S401).
In FIG. 10B (corresponding to step S203 in FIG. 7), in the case of the digit number check, nothing is done because the related master table 12 is not referred to (S402).
10 (C)) (corresponding to step S204 in FIG. 7), whether or not a special character string is specified and specified based on the setting value of “number of digits” set as the check item 41 of the check item table 13. The contents are checked (S403). If a special character string is designated and “full (full-width)” is designated, the data acquired in step S401 is counted by the full-width character count (S404). If no special character string is specified, or if a special character string is specified and “bar (byte)” is specified, the data acquired in step S401 is counted by the number of bytes (S405). Then, it is determined whether the number of digits specified in the check item 41 of the check item table 13 matches the number of digits acquired in the process of step S404 or step S405 (S406). If they match (S406: Yes), it is determined that there is no error (S407). If they do not match (S406: No), an error message is set that “a value outside the range of the specified number of digits exists” and is stored in the check result table 15 together with other items (S408). Then, the processes in steps S403 to S408 are repeated for all the records acquired in step S401 (S409).
When the type of the data item to be checked is the CHAR type, it may be checked whether the value is within the CHAR type byte count (16 bytes), regardless of the setting of the check item.

FIG. 9 shows an example of the configuration of the check result table 15. The check result table 15 includes a corresponding table ID and data item ID in the check item table 13, a check date (check execution date), a standard check component name, a table name to be verified, an error message, and a query executed when an error occurs. Record.
The error message is different for each standard check component 14 in which an error has occurred. For example, in the case of a digit number check component, the content “the value outside the specified number of digits exists” is registered in the error message.
After the verification is completed, the user checks the error content by referring to the check result table 15. The user can grasp immediately from which check point a problem has occurred, and further, by executing a registered error query, it is possible to identify data that causes an error.

The data verification system of the present invention has the following effects.
(1) This tool can be used in all systems by setting the check specification of the data item in the document for each table to be verified without depending on the development target system.
(2) If the check specification of the data item is a check specification that can be realized by a standard check component, it is possible to realize data validity verification without programming.

DESCRIPTION OF SYMBOLS 1 ... Data verification system, 11 ... Business table, 12 ... Related master table, 13 ... Check item table, 14 ... Standard check part, 15 ... Check result table, 16 ... Control part, 17 ... Upload tool, 18 ... Check item definition book

Claims (2)

A business table including a plurality of tables in which data generated for each transaction is registered, a related master table in which data commonly used in each transaction is registered, and data items in each table included in the business table Storage means for storing a check item table in which a check specification indicating a condition to be satisfied by data registered in each data item is registered, and a check result table in which information indicating a check result is stored;
Check specification registration means for registering each check specification created by a user in the check item table;
Parameter character string extracting means for extracting a parameter character string indicating a check specification from the check item table;
Query execution means for creating a query based on the parameter character string extracted by the parameter character string extraction means, and executing the created query on the business table or both the business table and the related master table; ,
A check for determining whether data registered in each table included in the business table satisfies a check specification registered in the check item table based on a result of a query executed by the query execution unit Specification judging means;
Check result table storage means for storing information indicating a result of determination by the check specification determination means in the check result table;
A data verification system comprising: A business table including a plurality of tables in which data generated for each transaction is registered, a related master table in which data commonly used in each transaction is registered, and data items in each table included in the business table Data provided with a storage means for storing a check item table in which a check specification indicating a condition to be satisfied by data registered in each data item is stored and a check result table in which information indicating a check result is stored A data verification method in a verification system,
Registering each check specification created by a user in the check item table;
Extracting a parameter character string indicating a check specification from the check item table;
Creating a query based on the extracted parameter string, and executing the created query on the business table or both the business table and the related master table;
Determining whether data registered in each table included in the business table satisfies a check specification registered in the check item table based on a result of the executed query;
Storing information indicating a result of the determination in the check result table;
A data verification method comprising: