JP4832952B2 - Database analysis system, database analysis method and program - Google Patents

Description

  The present invention relates to a technique for improving the efficiency of database system integration processing, for example, a technique for improving efficiency of database system integration processing when different systems related to the same business are integrated.

The conventional database integration method compares the attribute values of the attributes of the tables belonging to different databases, and creates a mapping model that associates the attribute items in each table based on the comparison result of the attribute values. When the comparison result is output, data is integrated by comparing the attribute values described above and generating a mapping model that associates the attribute items in each table with the one with the highest degree of matching. (For example, Patent Document 1).
JP 2004-067882 A, pages 1 to 8, FIG.

  In the conventional database system integration method, the matching value is calculated by checking the attribute value information of the table and creating a mapping model, and the same item candidate is found. Therefore, for the database system to be integrated, the attribute value information of the table There is a problem that the amount of information that must be given increases as the scale of the system increases, and the consistency of database format changes and the like must be managed by the user.

The main object of the present invention is to solve the above-mentioned problems, and the main object is to reduce the man-hours for user input and maintenance.
Specifically, it is possible to realize a mechanism that allows the user to know the same item candidate for database system integration only by providing information on one or more frequently used key items without having to provide table information. Is the main purpose.

The database analysis system according to the present invention includes:
A database analysis system for performing analysis on a first database and a second database each including a plurality of data items,
Among the plurality of data items included in the first database and the plurality of data items included in the second database, some of the data items having the same item attribute between the first database and the second database are common attribute data. A common attribute data item information input unit for inputting common attribute data item information indicated as an item;
Using the common attribute data item information input by the common attribute data item information input unit and the access log of the first database, other than the common attribute data item of the first database and the common attribute data item of the first database A first database correlation analysis unit that analyzes a correlation with each of the data items and generates first correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Using the common attribute data item information input by the common attribute data item information input unit and the access log of the second database, other than the common attribute data item of the second database and the common attribute data item of the second database A second database correlation analysis unit that analyzes a correlation with each of the data items and generates second correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Based on the correlation value for each common data item of each data item indicated in the first correlation information and the correlation value for each common data item of each data item indicated in the second correlation information, the first database Data item candidates other than the common attribute data item and data items other than the common attribute data item in the second database are data item candidates having common item attributes between the first database and the second database. And a common attribute data item candidate extraction unit that extracts as

  According to the present invention, using the access log of the first database and the access log of the second database, the correlation between the common attribute data item and the other data items is obtained, and the similarity between the correlations is calculated. Since common attribute data item candidates in the two databases are extracted, the man-hours required for database system integration can be reduced.

Embodiment 1 FIG.
FIG. 1 is a diagram illustrating a configuration example of a database analysis system 100 according to the present embodiment.
In FIG. 1, the database analysis system 100 according to the present embodiment can be broadly divided into a company A system 1, a company B system 2, and the same item candidate extraction / display system 14.

Processing blocks 1 and 2 represent processing blocks in each of the company A system and the company B system that integrate the systems. Although not shown, the database of the company A system is an example of the first database, and the database of the company B system is an example of the second database.
The user additional information 8 is information indicating common items in the database systems of the company A system 1 and the company B system 2. In other words, the user additional information 8 is the data between the two of the plurality of data items included in the database of the company A system (first database) and the plurality of data items included in the database of the company B system (second database). This is information indicating some common items (common attribute data items) having common item attributes, and is an example of common attribute data item information.
The company A system 1 and the company B system 2 both include a user additional information input unit 12 (common attribute data item information input unit) for inputting user additional information 8.
The access log 3 is a database access log when a business application is executed in each system. The access log 3 of the A company system is an example of the access log of the first database, and the access log 3 of the B company system is an example of the access log of the second database.
The access log processing unit 4 is a processing unit that extracts information necessary for analysis from the access log 3.
An analysis DB (database) 5 is an analysis database group generated by the access log processing unit 4.
The correlation extracting unit 6 is a processing unit that obtains a correlation from the information specified by the analysis DB 5 and the user additional information 8.
The item correlation information 7 is the correlation information obtained by the correlation extraction unit 6.
In the present embodiment, the access log processing unit 4, the analysis DB 5, and the correlation extraction unit 6 constitute a database correlation analysis unit, and the access log processing unit 4, the analysis DB 5 and the correlation extraction unit 6 of the company A system. Constitutes a first database correlation analysis unit, and the access log processing unit 4, the analysis DB 5, and the correlation extraction unit 6 of the B company system constitute a second database correlation analysis unit.
These access log processing unit 4, analysis DB 5, and correlation extraction unit 6 include data item information 13 indicating data items in the database of each system, user additional information 8, and access log 3 of each database. Is used to analyze the correlation between the common item in each database and each of the data items other than the common item, and generate correlation information 7 indicating a correlation value for the common item of each data item.
The correlation information 7 output by the correlation extraction unit 6 of the company A system is an example of the first correlation information, and the correlation information 7 output by the correlation extraction unit 6 of the company B system is the second correlation information. It is an example of information.

The same item candidate extraction unit 9 is a processing unit for finding the same item candidate from the correlation information 7 of each of the company A system 1 and the company B system 2. The same item candidate display unit 11 (display unit) is a part that displays the same item candidate 10 that is information obtained by the same item candidate extraction unit 9.
The same item candidate extraction unit 9 calculates the correlation value for the common item of each data item shown in the correlation information 7 of the company A system and the correlation value for the common item of each data item shown in the correlation information 7 of the company B system. Based on the above, the same item candidate (common attribute data item candidate) having a high possibility of having the same item attribute between both databases is extracted and displayed by the same item candidate display unit 11. The same item candidate extraction unit 9 is an example of a common attribute data item candidate extraction unit.

  In this embodiment, it is considered that database systems are integrated, for example, when different systems performing the same business are integrated. The user gives in advance common information about representative items (key items) of both databases to be integrated, such as name and branch number. By collecting an access log to the database system at the time of execution of the business application and performing a correlation analysis on the key item, it is possible to know the correlation between items in each system. About both database systems, the same item candidate at the time of database integration is discovered and displayed from the correlation information regarding the key items, thereby improving the efficiency at the time of data integration.

Here, the operation of the database analysis system 100 according to the present embodiment will be outlined with reference to FIG.
First, in each of the company A system 1 and the company B system 2, the user additional information input unit 12 displays user additional information 8 in which common items whose item attributes are known to be common between the databases of both companies are indicated. Input (S1601). Although details will be described later, the user additional information 8 indicates known common items as shown in FIG. 3, for example.
Next, the access log processing unit 4 analyzes the SQL sentence in the access log 3 (S1602) (first database correlation analysis step) (second database correlation analysis step). As will be described in detail later, as shown in FIG. 2, the access log 3 records the SQL sentence when accessing each database, and analyzes the number of appearances of data items included in the SQL sentence. .
Next, the correlation extraction unit 6 analyzes the correlation between common items and other items in the database of each system using the analysis result of the access log processing unit 4, the user additional information 8 and the like, Correlation information 7 indicating a correlation value with respect to a common item of each item is generated (S1603) (first database correlation analysis step) (second database correlation analysis step). The above steps S1601 to S1603 are executed in each of the company A system 1 and the company B system 2.
Next, in the same item candidate extraction / display system 14, the same item candidate extraction unit 9 calculates a similarity value from the correlation values of the correlation information 7 from the company A system 1 and the correlation information 7 from the company B system 2. In accordance with the calculated similarity value, the same item candidate having a high possibility that the item attribute is common between the database of the company A system and the database of the company B system is extracted (S1604) (common attribute data item candidate extraction step), The same item candidate display unit 11 displays the same item candidate (S1605).
Thereby, the same candidate item with the high possibility of the same item is extracted among several databases, and the efficiency at the time of database integration is improved.

  FIG. 1 shows an example in which each of the company A system 1, the company B system 2, and the same item candidate extraction / display system 14 is configured by physically different computers. However, these elements are integrated into one computer. The database analysis system may be realized by a single computer.

  Next, a hardware configuration example of the database analysis system 100, the company A system 1, the company B system 2, and the same item candidate extraction / display system 14 according to the present embodiment will be described.

FIG. 17 is a diagram showing an example of hardware resources of the database analysis system 100, the company A system 1, the company B system 2, and the same item candidate extraction / display system 14 shown in the present embodiment and the embodiments described below. is there.
In FIG. 17, the database analysis system 100, the company A system 1, the company B system 2, and the same item candidate extraction / display system 14 include a CPU 911 (Central Processing Unit, a central processing unit, a processing unit, an arithmetic unit, a micro unit that executes a program. A processor, a microcomputer, and a processor). The CPU 911 is connected to, for example, a ROM (Read Only Memory) 913, a RAM (Random Access Memory) 914, a communication board 915, a display device 901, a keyboard 902, a mouse 903, and a magnetic disk device 920 via a bus 912. Control hardware devices. Further, the CPU 911 may be connected to an FDD 904 (Flexible Disk Drive), a compact disk device 905 (CDD), a printer device 906, and a scanner device 907. Further, instead of the magnetic disk device 920, a storage device such as an optical disk device or a memory card read / write device may be used.
The RAM 914 is an example of a volatile memory. The storage media of the ROM 913, the FDD 904, the CDD 905, and the magnetic disk device 920 are an example of a nonvolatile memory. These are examples of a storage device or a storage unit.
The communication board 915, the keyboard 902, the scanner device 907, the FDD 904, and the like are examples of an input unit and an input device.
Further, the communication board 915, the display device 901, the printer device 906, and the like are examples of an output unit and an output device.

As shown in FIG. 1, the communication board 915 is connected to a network. For example, the communication board 915 may be connected to a LAN (local area network), the Internet, a WAN (wide area network), or the like.
The magnetic disk device 920 stores an operating system 921 (OS), a window system 922, a program group 923, and a file group 924. The programs in the program group 923 are executed by the CPU 911, the operating system 921, and the window system 922.

The program group 923 stores programs for executing functions described as “˜unit” and “˜means” in the following description. The program is read and executed by the CPU 911.
In the file group 924, information, data, and signals described as “determination result of”, “calculation result of”, “processing result of”, “evaluation result of”, etc. in the following description Values, variable values, and parameters are stored as items of “˜file” and “˜database”. The “˜file” and “˜database” are stored in a recording medium such as a disk or a memory. Information, data, signal values, variable values, and parameters stored in a storage medium such as a disk or memory are read out to the main memory or cache memory by the CPU 911 via a read / write circuit, and extracted, searched, referenced, compared, Used for CPU operations such as calculation, calculation, processing, editing, output, printing, and display. Information, data, signal values, variable values, and parameters are stored in the main memory, registers, cache memory, and buffers during the CPU operations of extraction, search, reference, comparison, calculation, processing, editing, output, printing, and display. It is temporarily stored in a memory or the like.
The arrows in the flowchart described below mainly indicate input / output of data and signals. The data and signal values are the RAM 914 memory, FDD 904 flexible disk, CDD 905 compact disk, magnetic disk device 920 magnetic disk, In addition, it is recorded on a recording medium such as an optical disc, a mini disc, or a DVD. Data and signals are transmitted online via a bus 912, signal lines, cables, or other transmission media.

  In addition, what is described as “to part” and “to means” in the following description may be “to circuit”, “to apparatus”, “to apparatus”, and “means”. It may be “˜step”, “˜procedure”, “˜processing”. That is, what is described as “˜unit” and “˜means” may be realized by firmware stored in the ROM 913. Alternatively, it may be implemented only by software, or only by hardware such as elements, devices, substrates, and wirings, by a combination of software and hardware, or by a combination of firmware. Firmware and software are stored as programs in a recording medium such as a magnetic disk, a flexible disk, an optical disk, a compact disk, a mini disk, and a DVD. The program is read by the CPU 911 and executed by the CPU 911. That is, the program causes the computer to function as “to part” and “to means” described below. Alternatively, the procedure or method of “˜unit” and “˜means” described below is executed by a computer.

  As described above, the database analysis system 100, the company A system 1, the company B system 2, and the same item candidate extraction / display system 14 shown in the present embodiment and the embodiments described below are a processing device CPU and a storage device. A computer including a memory, a magnetic disk, etc., a keyboard as an input device, a mouse, etc., a display device as an output device, a communication board, etc. This is realized by using a device, a storage device, an input device, and an output device.

Next, the operation of the database analysis system 100 according to the present embodiment will be described in detail.
First, the access log 3 is collected in each of the company A system 1 and the company B system 2. This is a collection of SQL statements that a business application has accessed the database system for a certain period of time. Any means may be used, such as using a collection tool provided by the database system, a commercially available tool, or a tool attached to the OS.
An example of a business application access log is shown in FIG.
21 represents a file format, and 22 represents an access log. Each line is composed of a line ID 23 for identifying information of the access log and information 24 of the SQL sentence used from the business application. In addition, the SQL sentence targets instructions related to extraction, deletion, insertion, and update.

Here, the user additional information 8 will be described.
As shown in FIG. 3, when the A company database 31 and the B company database 32 exist, the common item information of both companies is represented as 33, the corresponding item name in the A company system, and the item name in the B company system. Is specified. The designation method of the user additional information 8 may be any method such as input by GUI (Graphical User Interface) or file designation.

The access log processing unit 4 generates the analysis DB 5 from the access log as shown in FIG. 2, that is, creates various tables shown in FIGS. An example of the operation of the access log processing unit 4 is as shown in FIG.
First, the item information table 50 shown in FIG. 5 is created by the processing of S41 to S44.
The analysis database 50 shown in FIG. 5 associates the item ID 51 with the item name 52 for all item information existing in the access log, and exists for each of the company A database 31 and the company B database 32. is there. Also, for example, A. A1 and B.I. B3, B. B1 and C.I. A thing having the same meaning as C1 associates a plurality of item names with one item ID.
In step S41, the access log is read line by line. In step S42, it is checked whether all the item information included in the read access log information has been registered in the item information table. Then, register it in the item information table. At this time, the information is reflected by referring to the common item information 33 indicated in the user additional information 8. Repeat these operations until the last line of the file. If the end of the file is checked in S44, the item information table 50 is completed.
Subsequently, the cursor of the file is returned to the first line in S45, and the analysis DB_I 60 shown in FIG. 6 and the analysis DB_II 70 shown in FIG. 7 are created in S46 to S49.

  6 and 7 summarize information for each row ID 23 in the SQL sentence 24. FIG. Since the access log file shown in FIG. 2 is targeted for extraction / deletion / insertion / update, one of the words SELECT / DELETE / INSERT / UPDATE is described at the beginning of each sentence. This is numbered and expressed as “DML type”. In many cases, execution is performed on a line corresponding to a specific condition, so there is a condition phrase for specifying the condition. In the case of FIG. 2, WHERE in each line of the SQL statement 24 is a delimiter for the conditional phrase. For each SQL sentence in FIG. 2, information obtained from the sentence before the conditional phrase is stored in the analysis DB_I 60 in FIG. 6, and information obtained from the conditional phrase is stored in the analytical DB_II 70 in FIG. Both the analysis DB_I 60 and the analysis DB_II 70 have the same format, and are composed of a row ID 23, a DML type 61, and item information 62 or 72. The row ID 23 shown in the analysis databases 60 and 70 corresponds to the row ID 23 in FIG. 2, and the DML type 61 is a numbered SQL statement command SELECT, DELETE, INSERT, and UPDATE. The item information 62 and 72 are the item IDs 51 arranged side by side without overlapping.

In step S46, the access log is read line by line. In S47, for the sentence before the conditional phrase, ie, WHERE, when WHERE is not included, all specified item names are extracted from the entire sentence, and line ID 23, DML type 61 are extracted according to the format of DB_I60 for analysis. The item information 62 is created and added to the analysis DB_I 60. For the item information 62, True is set when the corresponding item is designated for each item ID, and False is set when there is no designation. For example, in the first line of FIG. 2, A1, A2, A3, A4 are shown before WHERE, and in FIG. 6, items 1, 2, 3, 4 (items 3, 4, 4 are reflected). (Not shown) is True, and items other than these are False.
In S48, all specified item names are extracted for the sentence after the conditional phrase, that is, after the WHERE statement, and the information of the row ID 23, the DML type 61, and the item information 72 is created according to the format of the analysis DB_II 70. It adds to DB_II70 for analysis. For the item information 72, True is set when a corresponding item is designated for each item ID, and False is set when there is no item information 72. Further, when there is no designation of WHERE, False is set for all item IDs of the item information 72. For example, in the first line of FIG. 2, A2 is shown after WHERE, and reflecting this, in FIG. 7, item 2 is True, and other items are False.

When the processing of the access log processing unit 4 is completed and the analysis DB 5 is created, the processing of the correlation extraction unit 6 is performed with the analysis DB 5 as an input. The flow of processing of the correlation extraction unit 6 is shown in FIG.
For the information 33 specified by the user additional information 8, the item name list is organized in S81.
If the example of 33 is arranged about a company A database, the 1st = A. A1,2 = A. A4, 3 = B. B1, 4 = C. C2.
The processing from S82 to S85 is performed on the first to fourth data. For example, A. In contrast to A1, in step S82, the data of the analysis DB_I60 is used. With A1 as the target and all other item IDs as inputs, correlation analysis is performed in S83, and the result is output to a file.
In S84, the analysis target is changed to the analysis DB_II 70, or the conditions for the analysis are changed such as specifying conditions for the DML type 61. Correlation analysis is performed in S83, and the correlation result is output to a file. When correlation analysis is performed for all conditions, the results are collected in S85, for example, in a format as shown in Table 90 of FIG. Such processing is performed for all items arranged in S81, and the file is output as item correlation information 7.

The above processing is performed for both the company A system 1 and the company B system 2 in FIG. 1, and the item correlation information 7 in the company A system and the item correlation information 7 in the company B system are generated. An example is shown in FIG. When four pieces of user additional information are given as indicated by 33 in FIG. 3, correlation analysis is performed on the four items in the systems of Company A, Company B, and both companies. In each correlation analysis result, 90 and 94, 91 and 95, 92 and 96, and 93 and 97 respectively correspond. An example of the result of correlation analysis performed on S90 and 94 under three conditions in S84 is shown.
In FIG. Correlation result 91 of A4, B.E. B1 correlation result 92, C.I. The correlation result 93 of C2 does not show the details. The configuration is the same as the correlation result 90 of A1. Similarly, in the correlation information of system B, X. X5 correlation result 95; Correlation result 96 of Y1, Z. The correlation result 97 of Z3 is X. The configuration is similar to the correlation result 94 of X1.

Next, the same item candidate extraction unit 9 inputs the correlation information 7 of the company A system 1 and the correlation information 7 of the company B system 2, and first performs a process of scoring the information of the correlation analysis results 90 to 97.
For example, in the correlation result table 90 and the correlation result table 94, if three conditions are designated in S84, the simple sum of the correlation coefficients in each case of case 1 to case 3 is obtained for each item, and 102 in FIG. , 104. In each case, the totals of the number of times that two items are considered to be correlated simultaneously are summarized in a table like 103 and 105 in FIG.
For example, in case 1 of correlation result 90, A.I. A. A2, A. A4, B.I. B2, B.B. B5, C.I. Since C2 was considered to be correlated at the same time, A2 and A.I. A4, A. A2 and B.I. B2, A.B. A2 and B.I. B5, A. A2 and C.I. C2, A.I. A4 and B.I. B2, A.B. A4 and B.I. B5, A. A4 and C.I. C2, B.I. B2 and B.B. B5, B.B. B2 and C.I. C2, B.I. B5 and C.I. Count once each at C2. When three conditions are designated in S84, the maximum value is 3. Similar calculations are performed for the correlation result tables 91 to 93 and 95 to 97 to obtain 106 to 117. The result for system A is 100 and the result for system B is 101.

User information entered in the tables 102, 104 and 33 of the simple sum of correlation coefficients A1 = X. From X1, it can be considered that either the item group included in Table 102 or the item group included in 104 may be the same item candidate, so the value and rank of the simple sum of correlation coefficients, etc. are specified at the same time Stored item information is stored as detailed correlation information of each item. Such an operation is summarized for all items specified by the user additional information, that is, all item IDs subjected to correlation analysis. In the example of 33 in FIG. 3, for system A, item ID = 1 (A.A1 or B.B3), item ID = 4 (A.A4), item ID = 8 (B.B1 or C.C1) , Item ID = 12 (C.C2), and system B are also summarized in the same manner.
An example of the detailed correlation information is shown in FIG.
118 is an example of the detailed correlation information of the company A system. The vertical axis represents each item obtained by removing the item designated by the user additional information 33 from the item name of the company A database 31, and the horizontal axis represents each item designated by the user additional information 33. In the case of 33 in FIG. 3, there are four items on the horizontal axis. When valid information is obtained when the correlation is examined, the ID number of the correlation detailed information table 119 is stored. The detailed correlation information table 119 is a compilation of the information shown in FIG. 10 and includes an ID number for identification, the item name for which the correlation information is examined, the correlation coefficient simple sum, the rank, the number of times, and simultaneous item designation information. . For each item in the B company database 32, the correlation detail information 118 and the correlation detail information table 119 are similarly created.

Next, the content of the correlation detailed information table 119 is scored for each ID and collected in the same format as 118.
An example of this is shown in FIG. 120 is an example of the correlation result score table for the A company database 31, and 121 is an example of the correlation result score table for the B company database 32.

As a scoring method, for example, the following procedure can be considered.
1) For the same “item name” (for example, A.A1) in the table 119 of FIG. In the example of FIG. 11, between four rows of data with ID = 1 to ID = 4, between five rows of data with ID = 5 to ID = 9, and between four rows of data with ID = 10 to ID = 13. , ID = 14 to ID = 19 are converted into deviation values between 6 rows of data. The rank is converted to the reverse order (descending order) and then the deviation value is obtained. Thus, three kinds of deviation values are obtained for each ID.
2) A total of three types of deviation values is obtained for each ID.
3) For each “item name”, the total value of the correlation coefficient simple sum “correlation coefficient simple sum_item name” and the average value of all “correlation coefficient simple sum_item name” are obtained.
4) “Correlation coefficient simple sum_item name” ÷ “Correlation coefficient simple” obtained in 3) above to the total value of the three types of deviation values (correlation coefficient simple sum, rank, number of times) obtained in 2) above The product of “sum_item name” is rounded to give a score of 5 points in a format like the table 120 of FIG.
Note that the table 120 and the table 121 in FIG. 12 show an image of the result of the scoring calculation, and do not show the result of performing the above calculation from the numerical values in FIG.

Next, the similarity value is calculated by scoring the contents of the correlation result scoring tables 120 and 121 and the contents of the simultaneous item designation information in the correlation detailed information table 119, and the same items satisfying a certain condition are calculated. The data is output as a candidate 10 and displayed on the same item candidate display unit 11.
For example, the similarity value is calculated from the table 120 and the table 121 in FIG. 12, and the same item candidate and the similarity value (score) are shown as in the table 123 in FIG.

As a method for calculating the similarity value, for example, the following procedure can be considered.
1) Since each column of the table 120 and the table 121 in FIG. 12 can be determined as the same item from the common item information 33, a process paying attention thereto is performed. For each row of the table 120 (each item of the company A system), the percentage distribution ratio (%) and rank of each column are obtained. The rank is “−” (not applicable) for a column without a score. The following 2) and subsequent processes are performed for a row in which a score is included in one or more columns. Since similar information cannot be obtained for all rows that do not have a score in each column, the processing skips to the next row.
2) If there is a score in one or more columns of the row of the table 121, the score distribution ratio (%) and rank of each column are obtained in the same manner as in 1) above and compared with the information in the row of the table 120. Thus, the similarity between the corresponding item of system A and the corresponding item of system B is calculated. 10 points when the ranks match for each column (0 points when matching with “-” (not applicable)), 10 points when the ratios match within ± 5% (0 when matching at 0%) Points), 10 points when the score of each row matches within ± 5 points (5 points if they match on “-” (not applicable), plus 10 points if both are 150 points or more) To do.
3) By performing the above operation 2) for all the rows of the table 121 for each row of the table 120, the similarity values of all the combinations are calculated and summarized as shown in the table 123 of FIG.
The table 123 in FIG. 12 shows a display image of the same item candidate and the similarity value, and does not show the result of the above calculation from the numerical values of the table 120 and the table 121.

  In this way, by displaying the same item candidates as in the table 123 of FIG. 12, a person who integrates the database can know data items that are likely to be the same item in both databases, and the same item The man-hours and labor required for the process of selecting can be greatly reduced.

  As described above, using the access logs of business applications in two different systems, the correlation between the items input in advance by the user is obtained, and the same item candidate when integrating the two systems from the similar information of the correlation Since this information is used, it can be expected to reduce the man-hours during system integration.

  Thus, the database analysis system according to the present embodiment includes a plurality of data items included in the database of the company A system (first database) and a plurality of data items included in the database of the company B system (second database). A user who inputs user additional information (common attribute data item information) indicating, as common items (common attribute data items), some data items having common item attributes between the first database and the second database among the data items Using the additional information input unit (common attribute data item information input unit), the user additional information and the access log input by the user additional information input unit, the correlation between the common item and each of the data items other than the common item Is generated, and correlation information (first correlation value, second correlation value) indicating a correlation value with respect to a common item of each data item is generated. Based on the correlation values for the common items of each data item indicated in the correlation information, the company A system database and the company B system are based on the correlation analysis units (first database correlation analysis unit and second database correlation analysis unit) The same item candidate extraction unit (common attribute data item candidate extraction unit) for extracting data item candidates having common item attributes as the same item candidate (common attribute data item candidate).

Then, the correlation analysis unit refers to the data items used for accessing the database indicated in the access log, analyzes the data items other than the common items used for accessing the database together with the common items, Analyze the correlation between the item and each of the data items other than the common item.
More specifically, the correlation analysis unit refers to the data item described in the SQL statement to the database indicated in the access log, and the data item other than the common item described in the same SQL statement as the common item. And the correlation between the common item and each of the data items other than the common item is analyzed.

  In addition, the correlation analysis unit calculates a plurality of correlation values for the common item of each data item, generates correlation information indicating the calculated plurality of correlation values, and the same item candidate extraction unit calculates each data indicated in the correlation information A similarity value between data items is calculated based on a plurality of correlation values with respect to a common item, and the same item candidate is extracted based on the calculated similarity value. Then, the same item candidate display unit displays the same item candidate extracted by the same item candidate extraction unit and the similarity value calculated by the same item candidate extraction unit.

Embodiment 2. FIG.
In the first embodiment described above, correlation analysis results in a plurality of cases can be obtained by specifying different conditions when performing correlation analysis. Next, the same results are obtained from the correlation analysis results. An embodiment in which elements of correlation analysis result information can be selected when calculating an item will be described.

FIG. 13 shows an example of a screen for selecting information elements of correlation analysis results in such a case.
The correlation detail information table 119 of FIG. 11 includes correlation item simple sum, rank, number of times, and simultaneous item designation information. In the element designation table 130 of FIG. 13, the presence / absence of use for the item list that is an element of the correlation analysis result indicated on the horizontal axis of the detailed correlation information table 119 is designated. Specify ○ for items to be used and × for items not to be used.
For example, when “correlation simple sum” and “number of times” are used and “rank” and “simultaneous item designation information” are not used as shown in FIG. 13, when creating the correlation result scoring table shown in FIG. In the stage, the score is calculated using only item information of “correlation simple sum” and “number of times”.

  As described above, in this embodiment, an element to be used is designated from among the elements of the correlation analysis result. To realize this, for example, the same item candidate extraction / display system 14 in FIG. A correlation value designation information input unit is provided so that the correlation value designation information input unit outputs the information of FIG. 13 and inputs correlation value designation information for designating a correlation value used for extracting the same item candidate from the screen of FIG. You can do it.

  The database analysis system according to the present embodiment is characterized in that, when a correlation analysis is performed, when a means for changing information obtained from an access log is provided, a calculation method for calculating the same candidate can be changed. . As described above, when obtaining the same item candidate for integrating the two systems from the similar information of the correlation obtained from the access logs of the business applications in the two different systems, the calculation method can be changed to The same item candidates can be shown in various ways, so that when a user performs additional work, it is possible to know which material is missing and which part should be added, reducing the man-hours during system integration You can expect the effect.

  As described above, in the present embodiment, the database analysis system inputs correlation value specifying information for specifying correlation values used for extracting the same item candidate among a plurality of correlation values indicated in the correlation information. It has been described that the same item candidate extraction unit has the input unit and extracts the same item candidate based on the correlation value designated by the correlation value designation information among the plurality of correlation values indicated in the correlation information.

Embodiment 3 FIG.
In the above embodiment, the same item candidate of the database at the time of integration of the two different systems such as the company A system and the company B system is obtained and displayed. An embodiment that can be switched between a display format based on the format and a display format based on the B company system format is shown.

FIG. 14 shows an example when the display format is switched. When 140 displays the corresponding items in the order of the scores (the same contents as the table 123 of FIG. 12), when 141 is displayed according to the format of the database 31 of company A, 142 is matched with the format of the database 32 of company B. This is the case.
140 is displayed in descending order of scores, that is, in the order of high possibility as the same item candidate, and is effective in knowing how much possibility the item as the same item candidate is indicated. It is.
The table 141 and the table 142 are displayed based on the format of each database system, and when searching while referring to the item specifications of each database, the corresponding items are displayed when there are a large number of tables or items. Investigation and confirmation can be performed without the need for searching.

  When the database analysis system according to the present embodiment displays the same item candidate when the two database systems of system A and system B are integrated, the same item candidate list display, the same item based on the database information of system A The display method can be switched such as displaying candidates and displaying the same item candidates based on the system B database information. In this way, by making it possible to switch the display method of similar item candidates, when performing additional work manually during system integration, it is possible to grasp which part of each system needs investigation and information disclosure Can be expected to improve efficiency for system integration.

  As described above, in the present embodiment, the same candidate display unit displays either the same item candidate by selecting either the display format that matches the database of the company A system or the display format that matches the database of the company B system. Explained what to do.

Embodiment 4 FIG.
In the above-described third embodiment, when the same item candidate in the database when integrating the two different systems with the company A system and the company B system is obtained and displayed, the same item list display, display based on the company A system format The display format can be switched to the display format based on the B company system format, but when displaying with each method, it is possible to display more than a certain score clearly. The form is shown.

FIG. 15 is an example in which 60 or more items are displayed in bold in the same way as FIG. 14. 150, 151, and 152 are items in which items of 60 points or more of 140, 141, and 142 are displayed in bold.
In addition to this, it is also possible to know the tendency of the same item candidate by using a different name display method such as displaying in different colors, or by sequentially switching and displaying the reference value of 70 points or more and 50 points or more.

  When the database analysis system according to the present embodiment displays the same item candidates when the two database systems of system A and system B are integrated, each display format displays a bold value as a valid value that exceeds the specified score. It is characterized by easy-to-understand display such as. In this way, when displaying similar item candidates, it is possible to display the display of items above a certain reference value in an easy-to-understand manner and further change the reference value, so that which item in which table is the same item candidate It is possible to recognize how effective it is, and when conducting additional work manually during system integration, it is possible to deepen a common understanding of what part of each system needs investigation and information disclosure, The efficiency improvement effect for system integration can be expected.

  As described above, in the present embodiment, it has been described that the same candidate display unit highlights the same item candidate having a similarity value of a certain value or more.

  In the above embodiment, the example in which the databases of the two companies, ie, the database of the company A system and the database of the company B system are integrated has been described. As long as two databases including data items are integrated, the database analysis system described in the above embodiment can be applied in any form.

  In the above embodiment, as the analysis of the access log, the execution result of the SQL statement indicated in the access log is analyzed. However, the access log is not limited to the SQL statement, and the access log is accessed for each data item. If the results are shown, the same analysis can be performed by analyzing the access results for each data item.

  In addition, the method of calculating the correlation value between the common item and the other items is not limited to that described in the above embodiment, and if the correlation between the common item and the other items can be obtained, Any calculation method may be used.

1 is a diagram illustrating a configuration example of a database analysis system according to Embodiment 1. FIG. FIG. 3 is a diagram showing an example of an access log according to the first embodiment. FIG. 6 is a diagram showing an example of user additional information according to the first embodiment. FIG. 3 is a flowchart showing an operation example of an access log processing unit according to the first embodiment. FIG. 4 is a diagram showing an example of an item information table according to the first embodiment. FIG. 6 is a diagram illustrating an example of a processing result of an access log processing unit according to the first embodiment. FIG. 6 is a diagram illustrating an example of a processing result of an access log processing unit according to the first embodiment. FIG. 6 is a diagram illustrating an operation example of a correlation extraction unit according to the first embodiment. FIG. 4 is a diagram illustrating an example of correlation information according to the first embodiment. The figure which shows progress of scoring of the same item extraction part which concerns on Embodiment 1. FIG. FIG. 6 is a diagram showing an example of detailed correlation information according to the first embodiment. The figure which shows the example of a display of the calculation result of the similarity value which concerns on Embodiment 1, and the same item candidate. The figure which shows the example of the screen which designates the element used for extraction of the same item candidate which concerns on Embodiment 2. FIG. The figure which shows the example which changes the display format which concerns on Embodiment 3, and displays the same item candidate. The figure which shows the example which highlights in the case of the display of the same item candidate which concerns on Embodiment 4. FIG. FIG. 3 is a flowchart showing an operation example of the database analysis system according to the first embodiment. FIG. 3 is a diagram illustrating a hardware configuration example of the database analysis system according to the first embodiment.

Explanation of symbols

  1 Company A system 2 Company B system 3 Access log 4 Access log processing unit 5 Analysis DB 6 Correlation extraction unit 7 Correlation information 8 User additional information 9 Same item candidate extraction unit 10 Same item Candidate, 11 Same item candidate display part, 12 User additional information input part, 13 Data item information, 14 Same item candidate extraction / display system, 100 Database analysis system.

Claims (12)

A database analysis system for performing analysis on a first database and a second database each including a plurality of data items,
Among the plurality of data items included in the first database and the plurality of data items included in the second database, some of the data items having the same item attribute between the first database and the second database are common attribute data. A common attribute data item information input unit for inputting common attribute data item information indicated as an item;
Using the common attribute data item information input by the common attribute data item information input unit and the access log of the first database, other than the common attribute data item of the first database and the common attribute data item of the first database A first database correlation analysis unit that analyzes a correlation with each of the data items and generates first correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Using the common attribute data item information input by the common attribute data item information input unit and the access log of the second database, other than the common attribute data item of the second database and the common attribute data item of the second database A second database correlation analysis unit that analyzes a correlation with each of the data items and generates second correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Based on the correlation value for each common data item of each data item indicated in the first correlation information and the correlation value for each common data item of each data item indicated in the second correlation information, the first database Data item candidates other than the common attribute data item and data items other than the common attribute data item in the second database are data item candidates having common item attributes between the first database and the second database. A database analysis system comprising: a common attribute data item candidate extraction unit that extracts as: The first database correlation analysis unit includes:
The data item used for accessing the first database indicated in the access log of the first database is referred to, and the data item used for accessing the first database together with the common attribute data item of the first database is referred to. Analyzing a data item other than the common attribute data item of the first database and analyzing a correlation between the common attribute data item of the first database and each of the data items other than the common attribute data item of the first database;
The second database correlation analysis unit includes:
The data item used for access to the second database indicated in the access log of the second database is referred to, and the second item used for accessing the second database together with the common attribute data item of the second database. Analyzing data items other than the common attribute data items in the database 2 and analyzing the correlation between the common attribute data items in the second database and the data items other than the common attribute data items in the second database. The database analysis system according to claim 1. The first database correlation analysis unit includes:
The first data item described in the same SQL statement as the common attribute data item of the first database is referred to the data item described in the SQL statement to the first database indicated in the access log of the first database. Analyzing data items other than the common attribute data items in the database, analyzing the correlation between the common attribute data items in the first database and each of the data items other than the common attribute data items in the first database,
The second database correlation analysis unit includes:
The second data item described in the same SQL statement as the common attribute data item of the second database is referred to the data item described in the SQL statement to the second database indicated in the access log of the second database. Analyzing a data item other than the common attribute data item of the second database, and analyzing a correlation between the common attribute data item of the second database and each of the data items other than the common attribute data item of the second database. The database analysis system according to claim 2, wherein the system is a database analysis system. The first database correlation analysis unit includes:
Calculating a plurality of correlation values for the common attribute data item of each data item, and generating first correlation information indicating the calculated plurality of correlation values;
The second database correlation analysis unit includes:
Calculating a plurality of correlation values for the common attribute data item of each data item, and generating second correlation information indicating the calculated plurality of correlation values;
The common attribute data item candidate extraction unit
Based on a plurality of correlation values for the common attribute data item of each data item indicated in the first correlation information and a plurality of correlation values for the common attribute data item of each data item indicated in the second correlation information, The database analysis system according to claim 1, wherein common attribute data item candidates are extracted. The database analysis system further includes:
The database analysis system according to claim 1, further comprising a display unit that displays the common attribute data item candidates extracted by the common attribute data item candidate extraction unit. The common attribute data item candidate extraction unit
Based on the correlation value for each common data item of each data item indicated in the first correlation information and the correlation value for each common data item of each data item indicated in the second correlation information, the first database Calculate similarity values between data items in data items other than common attribute data items and data items other than common attribute data items in the second database, and extract common attribute data item candidates based on the calculated similarity values The database analysis system according to claim 1, wherein: The database analysis system further includes:
A display unit for displaying the common attribute data item candidate extracted by the common attribute data item candidate extraction unit and the similarity value of the common attribute data item candidate calculated by the common attribute data item candidate extraction unit; The database analysis system according to claim 6, wherein the system is a database analysis system. The database analysis system further includes:
Correlation value designation information for designating a correlation value used for extraction of a common attribute data item candidate in the common attribute data item candidate extraction unit among a plurality of correlation values indicated in the first correlation information and the second correlation information is input. It has a correlation value designation information input part,
The common attribute data item candidate extraction unit
5. The common attribute data item candidate is extracted based on a correlation value designated by the correlation value designation information among a plurality of correlation values indicated by the first correlation information and the second correlation information. The database analysis system described. The display unit
6. The database analysis system according to claim 5, wherein a common attribute data item candidate is displayed by selecting one of a display format adapted to the first database and a display format adapted to the second database. The display unit
7. The database analysis system according to claim 6, wherein common attribute data item candidates having similarity values equal to or greater than a certain value are highlighted. A database analysis method for performing analysis on a first database and a second database each including a plurality of data items,
Among the plurality of data items included in the first database and the plurality of data items included in the second database, some of the data items having the same item attribute between the first database and the second database are common attribute data. Common attribute data item information input step for inputting common attribute data item information indicated as an item,
Using the common attribute data item information input in the common attribute data item information input step and the access log of the first database, other than the common attribute data item of the first database and the common attribute data item of the first database A first database correlation analysis step of analyzing a correlation with each of the data items and generating first correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Using the common attribute data item information input in the common attribute data item information input step and the access log of the second database, other than the common attribute data item of the second database and the common attribute data item of the second database A second database correlation analysis step of analyzing a correlation with each of the data items and generating second correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Based on the correlation value for each common data item of each data item indicated in the first correlation information and the correlation value for each common data item of each data item indicated in the second correlation information, the first database Data item candidates other than the common attribute data item and data items other than the common attribute data item in the second database are data item candidates having common item attributes between the first database and the second database. And a common attribute data item candidate extraction step to extract as a database analysis method. A computer for performing analysis on the first database and the second database each including a plurality of data items;
Among the plurality of data items included in the first database and the plurality of data items included in the second database, some of the data items having the same item attribute between the first database and the second database are common attribute data. Common attribute data item information input processing for inputting common attribute data item information indicated as an item,
Using the common attribute data item information input by the common attribute data item information input process and the access log of the first database, other than the common attribute data item of the first database and the common attribute data item of the first database A first database correlation analysis process for analyzing a correlation with each of the data items and generating first correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Using the common attribute data item information input by the common attribute data item information input process and the access log of the second database, other than the common attribute data item of the second database and the common attribute data item of the second database A second database correlation analysis process for analyzing a correlation with each of the data items and generating second correlation information indicating a correlation value with respect to the common attribute data item of each data item;
Based on the correlation value for each common data item of each data item indicated in the first correlation information and the correlation value for each common data item of each data item indicated in the second correlation information, the first database Data item candidates other than the common attribute data item and data items other than the common attribute data item in the second database are data item candidates having common item attributes between the first database and the second database. And a common attribute data item candidate extraction process to be extracted as a program.