JP2024093613A - Information processing system, information processing method, and program - Google Patents

Abstract

To provide a mechanism to perform appropriate mapping on a column that is difficult to be mapped with information on a single column.SOLUTION: An information processing system comprises: first specification means that specifies the order relationship between the dates for a plurality of data columns related to date data in each of a first table and a second table; and second specification means that, based on the order relationship specified by the first specification means, specifies the correspondence relationship between the data columns in the first table and the data columns in the second table.SELECTED DRAWING: Figure 1

Description

The present invention relates to an information processing system, an information processing method, and a program.

Order placement and other transactions between companies are increasingly being carried out using EDI, which often involves converting various data formats from a company's own format to a standard format. When converting data from a company's own format to a standard format, it is necessary to define the correspondence between data columns (mapping). Performing the mapping process manually places a heavy burden on users, so automatic mapping is desirable.

The technology that automatically determines the correspondence between columns in two tabular data sets is called schema matching technology. In addition to being used in EDI, schema matching technology is also used for database integration and creating data for machine learning.

Schema matching techniques include methods that use schema information (column names, column types, etc.) and column value information (presence or absence of common values, numerical distribution, etc.).

However, there are cases where it is not possible to determine which mapping should be made based on the value of a single column.

Patent document 1 discloses a method for matching columns that cannot be matched using only the values of a single column by considering multiple columns.

JP 2012-038066 A

Patent document 1 assumes that the "association rules" for column pairs do not change between the two tables being matched.

A specific example of a case where matching cannot be performed using the value of a single column alone is a date type column. For example, it is impossible to determine whether "2022/04/22" is the "order date" or the "delivery date" based on the value alone. Also, there are cases where a correlation rule cannot be found between a date type column and other columns, and the method of Patent Document 1 cannot solve the problem.

The present invention aims to provide a mechanism for appropriate mapping of columns that are difficult to map using information from a single column.

The present invention relates to a method and apparatus for determining an order relationship of dates for a plurality of data strings related to date data in each of a first table and a second table;
a second specifying means for specifying a correspondence relationship between a data string in the first table and a data string in the second table based on the order relationship specified by the first specifying means;
The present invention is characterized by comprising:

The present invention makes it possible to perform appropriate mapping for columns that are difficult to map using information from a single column.

FIG. 1 is a diagram showing an example of a configuration of an information processing system according to the present invention. FIG. 1 is a block diagram showing an example of a hardware configuration of an information processing device that can be used as a client terminal 101 and a server device 102. Flowchart showing the mapping process between in-house format data and standard format data Diagram showing proprietary format data and standard format data A diagram showing an example of the upload screen for in-house format data Flowchart showing details of the process in step S104 FIG. 2 is a diagram illustrating the process of step S201. FIG. 2 is a diagram illustrating the process of step S202. FIG. 2 is a diagram illustrating the process of step S203. FIG. 2 is a diagram illustrating the process of step S204. Diagram showing the definition of histogram intersection An example of a screen showing the mapping results An example of a screen for accepting corrections to mapping results

Figure 1 shows an example of the configuration of an information processing system according to the present invention.

As shown in FIG. 1, a client terminal 101 and a server device 102 are connected so as to be able to communicate with each other.

The client terminal 101 has a function of accepting operations and instructions from a user and transmitting a request based on the instructions to the server device 102, etc. It also has a function of notifying the user of information acquired from the server device 102, etc., by displaying the information on a display unit, etc.

The server device 102 transmits various information to the client terminal 101 based on the request received from the client terminal 101.

Figure 2 is a block diagram showing an example of the hardware configuration of an information processing device that can be used as the client terminal 101 and server device 102 of the present invention.

As shown in FIG. 2, the information processing device is connected to a CPU (Central Processing Unit) 201, a ROM (Read Only Memory) 202, a RAM (Random Access Memory) 203, a storage device 204, an input controller 205, an audio controller 206, a video controller 207, a memory controller 208, and a communication I/F controller 209 via a system bus 200.

The CPU 201 provides overall control over each device and controller connected to the system bus 200.

The ROM 202 or external memory 213 holds the BIOS (Basic Input/Output System) and OS (Operating System), which are control programs executed by the CPU 201, computer-readable and executable programs for implementing this information processing method, and various necessary data (including data tables).

RAM 203 functions as the main memory, work area, etc. of CPU 201. CPU 201 loads programs and the like required for executing processing from ROM 202 or external memory 213 into RAM 203, and executes the loaded programs to realize various operations.

The input controller 205 controls input from input devices such as a keyboard 210 and a pointing device such as a mouse (not shown). If the input device is a touch panel, the user can give various instructions by pressing (touching with a finger, etc.) icons, cursors, or buttons displayed on the touch panel.

The touch panel may also be a touch panel capable of detecting positions touched by multiple fingers, such as a multi-touch screen.

The video controller 207 controls the display on an external output device such as a display 212. The display also includes the display of a notebook computer that is integrated with the main body. Note that the external output device is not limited to a display, and may be, for example, a projector. In addition, for devices that can accept the aforementioned touch operations, an input device is also provided.

The video controller 207 can control a video memory (VRAM) for display control, and can use part of the RAM 203 as a video memory area, or can provide a separate dedicated video memory.

The memory controller 208 controls access to the external memory 213. The external memory can be an external storage device (hard disk) that stores the boot program, various applications, font data, user files, edit files, and various data, a flexible disk (FD), or a compact flash (registered trademark) memory connected to a PCMCIA card slot via an adapter.

The communication I/F controller 209 connects and communicates with external devices via a network, and executes communication control processing on the network. For example, communication using TCP/IP, telephone lines such as ISDN, and 4G and 5G lines for mobile phones are possible.

The CPU 201 can display the outline font on the display 212 by, for example, executing a process of expanding (rasterizing) the outline font in a display information area in the RAM 203. The CPU 201 can also allow the user to give instructions using a mouse cursor (not shown) on the display 212.

Figure 3 is a flowchart showing automatic mapping using a sample file of standard format and in-house format in an EDI system. Each process shown in the flowchart in Figure 3 is a process in which the CPU 201 of the server device 102 reads and executes a specific control program.

In step S101, a sample file in a standard format is obtained. The standard format is a common file format that is not dependent on the company, and the sample file is assumed to be incorporated in the EDI system in advance. The sample file in the standard format is read into memory.

The right side of Figure 4 corresponds to the standard format sample file. Since the standard format sample file is a collection of data from a variety of companies, there are columns that are not used for each record (the None parts).

In step S102, a sample file in the company's format is uploaded. Unlike the standard format, the company's format is unique to each company, so the sample file cannot be incorporated into the EDI system in advance. Therefore, the user uploads a sample file in the company's format and reads the sample file into memory. The left diagram in Figure 4 corresponds to the sample file in the company's format.

Figure 5 shows an example of the upload screen for a sample file.

In step S103, automatic mapping is performed using the value information of a single column using the sample files of the two tables prepared in steps S101 and S102.

When automatic mapping is performed using value information from a single column as in step S103, it is not possible to distinguish which date type column 1 and date type column 2 in the in-house format sample file correspond to which date type column in the standard format sample file (whether they correspond to the order date, the final delivery date, or the accounting date) as in the example in Figure 4.

Therefore, in step S104, mapping is performed using the order relationship of the date type. Details of step S104 are explained in the flowchart in Figure 6.

In step S201, date type columns are extracted from the sample file in our company's format and the number of date type columns is obtained. An image of this step is shown in Figure 7.

As shown in the left table of Figure 7, a file in our company's format is composed of data for various items, and the information for each item can be a string type (information expressed as a string, such as a product name), a date type, or a numeric type (information expressed as a number, such as a product code). From these, columns of date type items are extracted, and the number of extracted columns is obtained.

Figure 7 shows an example where two date type columns are extracted.

In step S202, date type columns are extracted from the standard format sample file, and records are extracted in which the number of date type columns, excluding missing columns, for each record is equal to the number of date type columns in the in-house format sample file.

An illustration of this step is shown in Figure 8.

The top table in Figure 8 is an example of a standard format file. The bottom left table shows date-type columns extracted from this standard format file. The three columns extracted are "Order Date," "Final Delivery Date," and "Accounting Date."

Of the information in these three columns, records that contain the same number of data as the number of date type columns obtained in step S201 are extracted. That is, in this embodiment, of the three columns "Order Date", "Final Delivery Date to Final Delivery Destination", and "Accounting Date", records are extracted that have data in two columns and none in the remaining column (missing data).

The extracted results are shown in the table on the bottom right. The record in the table on the bottom left where the "Order Date" is "2021-03-25" is not extracted because data exists in all three columns, and the remaining three records are extracted.

In step S203, the date type columns in the in-house/standard format sample file are converted into sequence information. In other words, when viewed on a record-by-record basis, the earliest date and time is assigned a numerical value of 0, the next date and time is assigned a numerical value of 1, the date and time after that is assigned a numerical value of 2, and so on.

At this time, missing values (None) are assigned the value -1. An illustration of this step is shown in Figure 9.

The two tables at the top of Figure 9 are data from our company's format file. The table at the top right shows the dates in date type column 1 and date type column 2 converted into order information for each record. For example, for the first record, date type column 1 is "2022-04-22" and date type column 2 is "2022-04-23", so date type column 1 comes earlier in the order, so date type column 1 is converted to "0" and date type column 2 is converted to "1". The other records are converted in the same way.

The two tables at the bottom of Figure 9 show the data from the standard format file. As with the in-house format file, the date information for each record is converted into data according to its order. Note that "-1" is used for None (no data exists).

In step S204, the histogram similarity between date type columns converted into order information for the in-house format/standard format sample files is calculated and scored. Columns with high scores are then mapped. An image of this step is shown in Figure 10.

Histogram intersection can be considered as one measure of similarity between histograms. The definition of histogram intersection is shown in Figure 11.

The histogram intersection is the overlapping area between two normalized histograms, and ranges from 0 to 1; the larger the value, the more similar the histograms are considered to be.

In the case of Figure 10, all values in "Date column 1" in our company's format sample file are "0", so the normalized frequency of "-1" is 0, the normalized frequency of "0" is 1, and the normalized frequency of "1" is 0.

Similarly, all values of "Order Date" in the standard format sample file are 0, so the normalized frequency of "1" is 0, the normalized frequency of "0" is 1, and the normalized frequency of "1" is 0.

Therefore, the histogram intersection of "Date column 1" and "Order date" is 1 (0 + 1 + 0 = 1).

In the "Date column 2" of our in-house format sample file, all values are "1", so the normalized frequency of "-1" is 0, the normalized frequency of "0" is 0, and the normalized frequency of "1" is 1.

For the "Final delivery destination delivery date" in the standard format sample file, the normalized frequency of "-1" is 0.33, the normalized frequency of "0" is 0, and the normalized frequency of "1" is 0.66.

Therefore, the histogram intersection of "Date column 2" and "Final delivery destination delivery date" is 0.66 (0 + 0 + 0.66 = 0.66).

Calculate the histogram intersection for all column combinations and map columns with large values.

In the case of Figure 10, "Date type column 1" can be mapped to "Order date", and "Date type column 2" can be mapped to "Final delivery destination delivery date".

Return to the explanation of Figure 3.

In step S105, control is performed so that the display unit of the client terminal 101 displays the results of the automatic mapping. The display screen for the automatic mapping results displays the names of each column in the company's format, sample values of the columns, and the mapping results by the system, as shown in FIG. 12.

In step S106, the system accepts an instruction as to whether or not to modify the mapping results.

If an instruction to correct has been received (S106: Yes), the process proceeds to step S108; if an instruction to correct has not been received (S106: No), the process of this flowchart ends.

In step S107, modifications to the mapping results are accepted.

The correction can be performed using a UI in which the mapping result by the system is clicked and selected from a pull-down list as shown in Fig. 13. After the correction, the process proceeds to step S104.
After the correction, the process proceeds to step S105, and the processes of steps S105 to S107 are repeated until it is determined in S that the mapping does not need to be corrected.

Although the above describes an embodiment, the present invention can be embodied, for example, as a system, device, method, program, or recording medium. Specifically, the present invention may be applied to a system made up of multiple devices, or to a device made up of a single device.

The program of the present invention is a program that enables a computer to execute the processing method of the flowchart shown in FIG. 3, and the storage medium of the present invention stores a program that enables a computer to execute the processing method of FIG. 3. The program of the present invention may be a program for each processing method of each device in FIG. 3.

As described above, it goes without saying that the object of the present invention can be achieved by supplying a recording medium on which a program that realizes the functions of the above-mentioned embodiments is recorded to a system or device, and having the computer (or CPU or MPU) of that system or device read and execute the program stored on the recording medium.

In this case, the program read from the recording medium itself realizes the novel functions of the present invention, and the recording medium on which the program is recorded constitutes the present invention.

Recording media for supplying the program may include, for example, a flexible disk, a hard disk, an optical disk, a magneto-optical disk, a CD-ROM, a CD-R, a DVD-ROM, a magnetic tape, a non-volatile memory card, a ROM, an EEPROM, a silicon disk, etc.

Furthermore, it goes without saying that not only are the functions of the above-mentioned embodiments realized by the computer executing a program it has read, but also that the functions of the above-mentioned embodiments can be realized by an operating system (OS) or the like running on the computer carrying out some or all of the actual processing based on the instructions of the program.

Furthermore, it goes without saying that this also includes cases where a program read from a recording medium is written into a memory provided on a function expansion board inserted into a computer or a function expansion unit connected to a computer, and then a CPU or the like provided on the function expansion board or function expansion unit performs some or all of the actual processing based on the instructions of the program code, thereby realizing the functions of the above-mentioned embodiments.

The present invention may be applied to a system made up of multiple devices, or to a device made up of a single device. Needless to say, the present invention can also be applied to cases where the effects of the present invention are achieved by supplying a program to a system or device. In this case, the effects of the present invention can be enjoyed by reading a recording medium that stores a program for achieving the present invention into the system or device.

Furthermore, by downloading and reading a program for achieving the present invention from a server, database, etc. on a network using a communication program, the system or device can enjoy the effects of the present invention. Note that the present invention also includes configurations that combine the above-mentioned embodiments and their modified examples.

101 Client terminal 102 Server device 103 LAN

Claims (7)

a first specifying means for specifying an order relationship of dates for a plurality of data strings related to date data in each of the first table and the second table;
a second specifying means for specifying a correspondence relationship between a data string in the first table and a data string in the second table based on the order relationship specified by the first specifying means;
An information processing system comprising: an acquisition means for acquiring the number of date type columns from the first table;
an extracting means for extracting from the second table a record including the number of date data items acquired by the acquiring means;
Equipped with
The information processing system described in claim 1, characterized in that the second identification means identifies the correspondence between a data column in the first table and a data column in the second table based on the order relationship of dates in a column related to date data in the first table and the order relationship of dates in records extracted from the second table. The information processing system according to claim 1, further comprising a conversion means for converting a plurality of data columns related to date data in each of the first table and the second table into order information indicating the order relationship of the dates. The information processing system according to claim 3, characterized in that the second identification means creates a histogram of the conversion result by the conversion means, and identifies the correspondence between the data strings in the first table and the data strings in the second table based on the similarity relationship of the histogram. The information processing system according to any one of claims 1 to 4, characterized in that the first table is data in a company's own format in EDI, and the second table is data in a standard format in EDI. a first specification step in which a first specification means of the information processing system specifies an order relationship of dates for a plurality of data strings related to date data in each of the first table and the second table;
a second specifying step in which a second specifying means of the information processing system specifies a correspondence relationship between a data string in the first table and a data string in the second table based on the order relationship specified in the first specifying step;
An information processing method comprising: Computer,
a first specifying means for specifying an order relationship of dates for a plurality of data strings related to date data in each of the first table and the second table;
A program for functioning as second specifying means for specifying a corresponding relationship between a data string in the first table and a data string in the second table based on the order relationship specified by the first specifying means.

Images