JP5733434B6 - Data integration apparatus, data integration method, and data integration program - Google Patents

Description

  The present invention relates to a data integration apparatus, a data integration method, and a computer-readable recording medium recording a data integration program, and in particular, data integration for collecting and integrating data existing in a plurality of information sources managed in different systems. The present invention relates to an apparatus, a data integration method, and a computer-readable recording medium on which a data integration program is recorded.

2. Description of the Related Art Conventionally, an apparatus that cooperates between systems by integrating data managed in a plurality of different systems has been realized. For example, ETL (Extract / Transform / Load) extracts data from a database as an information source (ex
It is usually realized by transcribing, transforming it into a form that can be easily used by the user system, and writing it to the database of the user system (load). It is usually developed for each purpose and operated by batch processing. It is. ETL is a typical application example of building a data warehouse.

  In EAI (Enterprise Application Integration), data and processes are linked in accordance with a predetermined standard between linked systems, thereby realizing organic linkage between a plurality of computer systems.

  As a specific example of EAI, in order to realize cooperation between a plurality of business systems designed to use different data formats, a predetermined standard data format is defined, and data between each business system is defined. When linking, there is a technology that realizes data linkage between multiple systems by converting the data of the transfer source business system into a standard data format and then converting it to the data format of the transfer destination business system. (For example, refer to Patent Document 1).

  At that time, the data format of the data from a certain business system is converted into the standard data format by using a plurality of dictionary databases storing correspondence information between the data format used by the business system in data processing and the standard data format. Alternatively, a technique for performing data linkage by performing the reverse is shown. In this method, it is necessary to define the standard data format, and to build a dedicated conversion dictionary database for each information system. When the standard data format changes, all dictionary databases must be changed. Become. Further, at the time of actual cooperation, at least two stages of data format conversion processing are performed, and CPU processing occurs.

  Therefore, a data integration method called EII (Enterprise Information Integration) is required. EII is a mechanism for integrating physically scattered data and using it in a single view.

  On the other hand, there is MDM (Master Data Management) as a mechanism for integrating and managing master data distributed among a plurality of systems. The principle of MDM according to the prior art is shown in FIGS. Here, FIG. 22 is an explanatory diagram showing the state of each subsystem before the introduction of MDM as an example common to the present application, and FIG. 23 is an explanatory diagram showing an implementation example of MDM according to the prior art.

  In FIG. 22, 2201, 2202, 2203 are the integration target subsystems A, B, and A, respectively. The former subsystem 2201 is provided with an former database (DB) 2210 having a table A 2211 and a table B 2212. The former subsystem 2202 is provided with an former database (DB) 2220 having a table C 2221. And a basket database (DB) 2230 having a table D2231. Each table has a column as a column. For example, Table A 2211 has columns of A1, A2, A3, A4, and A5.

  Here, 2241, 2242, 2243, 2244 indicate data strings to be integrated among the data in the DB table managed by each subsystem. For example, in the case of the table A2211, columns “A1”, “A3”, and “A4” are integration targets. In addition, the former subsystem 2201 has a function-X as a representative example of the functions that the data integration target system has before the application of data integration, and the second subsystem 2202 functions as a representative example of application functions that use data integration- Have Y.

  FIG. 23 shows an implementation example of MDM in which the former subsystem 2201 and the heel subsystem 2202 in FIG. First, a master (integrated) DB 2250 is created, and column data 2241, 2242 and 2243 to be integrated are collected from a DB table (source table) managed by each subsystem, and a master table M2251 is provided. Configure as follows. Here, since the column data collected in the master table M2251 becomes master data, in order to avoid duplicate management, the column data collected in the master table M2251 is deleted as much as possible, but all the integration target columns are deleted. Data may not be deleted from the base table.

  For example, since column data serving as a primary key of the base table cannot be deleted, there is data managed by both the base table and the master table M2251. Further, the application realizing the function of each subsystem is changed to handle not only each base table but also the master table M2251. For example, in the case of table A2211, each table has “A1”, “A2”, and “A5” as columns.

  Specifically, information to be shared (“A1”, “A3”, “A4”, “B2”, “B3”, “B4”, “C2”, “C3”) is centrally managed in the master DB 2250 as a table M2251. To do. Information unique to each system (“A2”, “A5”, “B1”, “B5”, “C1”, “C4”, “C5”) is managed by each system. In addition, information overlapping with the master DB 2250 (for example, “A1”, “B4”, etc.) exists in the DB of each system.

  Here, the operation of the function-X and the function-Y will be verified. First, “update the columns A1, A2, A3, A4 of the table A (1)” which is the function-X in the instep subsystem 2201 is executed. Therefore, the subsystem A 2201 updates “A1”, “A3”, and “A4” of the table M2251 of the master DB 2250, and updates “A1” and “A2” of the table A2211 managed by the database A2211. .

  Next, the function -X “write the sum of A2 and A3 in B4 of table B (2)” is executed. Therefore, the subsystem A 2201 acquires “A3” in the table M2251 of the master DB 2250, and writes the sum of “A2” in the table A2211 and “A3” acquired in “B4” in the table B2212. Then, the update data “B4” is reflected on the master DB 2250 (3).

  Furthermore, “update C4 with reference to B4 of table M (4)” which is function-Y in the second subsystem 2202 is executed. Therefore, the second subsystem 2202 updates “C4” of the table C2221 with reference to “B4” of the master DB 2250 in which the update data is reflected in (3) above. Thus, data integration using the master DB 2250 was performed.

JP-A-2005-293047

  However, the conventional system has the following problems. That is, in order to provide the integrated master DB 2250, it is necessary for the application of the user side system to handle not only the table managed by the own system but also the master DB 2250 in consideration of the location of information and the reference and update of information. is there. For this reason, there is a problem that the application needs to be renovated and is one of the factors that complicate the contents of the application.

  In addition, when updating information, there is a problem in that it is necessary to control the information of the own system and the information of the master DB 2250 to be synchronized and updated without contradiction. This control has a problem that it is necessary to control the transaction of the own system and the master DB 2250 and to implement a process such as rollback when the process fails. This increases the burden on the application.

  In addition, the data referenced when updating the user system and the data to be updated may be in a halfway state (a state in which another application is operating and the value is not fixed) to prevent it. Needs some kind of lock control for data related to update. In addition, there is a problem in that the performance of the entire system is deteriorated by performing lock control between subsystems. For example, while the upper subsystem locks the master table M2251 (integrated DB), the integrated DB cannot be used from another system, and thus the other subsystem completes the processing (transaction) of the upper subsystem. I will be waiting for you.

  In addition, changes to each subsystem may cause additions or changes to the data managed by the integrated DB. In this case, all subsystems that use the integrated DB table where the change has occurred In many cases, application changes are required. Furthermore, since the addition of data managed by the integrated DB occurs due to the convenience of each subsystem, the integrated DB tends to enlarge as a result.

  In this way, concentration in the integrated DB and enlargement of the integrated DB occur, and access to the integrated DB increases. As a result, the reference / update performance of the integrated DB deteriorates and each application becomes complicated and integrated. There was a problem that the influence of the DB propagated to each subsystem.

  In order to eliminate the above-described problems caused by the prior art, the present invention can reduce the load on each system when collecting and integrating data existing in a plurality of information sources managed in different systems. An object of the present invention is to provide an ideal data integration apparatus, a data integration method, and a computer-readable recording medium on which a data integration program is recorded, and to realize an ideal EII and an MDM as an application thereof.

  In order to solve the above-described problems and achieve the object, a data integration apparatus according to the present invention is a data integration apparatus that collects and integrates data existing in a plurality of information sources managed in different systems, Data collection means for collecting data from the information source in the form of a data model on each information source side (hereinafter referred to as “physical model”), and data collected by the data collection means are used as an application for using the data A data conversion means for converting at least one of a data structure and a data value so as to become a data model (hereinafter referred to as “logical model”) defined in advance for each data, and the data converted by the data conversion means Data providing means for providing to the user side application.

  The data integration apparatus according to the present invention is characterized in that, in the above invention, the data collection means collects data from the information source in real time based on a request from the user application.

  Further, in the above invention, the data integration device according to the present invention is limited to data to be disclosed among data stored in a master database of the data provided on the system side of the information source. A virtual integrated database creating means for creating a virtual consolidated database based on the meta information of the data, wherein the data converting means has a data structure based on the meta information included in the virtual integrated database created by the virtual integrated database creating means; And conversion of at least one of the data value.

  Further, in the above invention, the data integration device according to the present invention is limited to data to be disclosed among data stored in a master database of the data provided on the system side of the information source. The data collection comprises: a duplicate database creating means for creating a duplicate database comprising data; and a duplicate database updating means for updating the duplicate database created by the duplicate database creating means in response to the update of the master data. The means collects data from the duplicate database.

  The data integration device according to the present invention is characterized in that, in the above invention, at least a table name, a data string name, and a data value type are defined as meta information representing the physical model or the logical model. To do.

  In the data integration device according to the present invention, in the above invention, a type attribute indicating details regarding the type of the data value as an attribute is defined as the meta information, and the conversion unit converts the data value. When the type attributes are different from each other, processing for matching the type attributes is performed.

  The data integration device according to the present invention is characterized in that, in the above-mentioned invention, a data string constraint is defined as meta information representing the physical model or the logical model.

  In the data integration device according to the present invention, in the above invention, the conversion unit defines an intermediate table for converting data between the physical model and the logical model, and collects data as a physical model. It is characterized in that the result of converting the data in the intermediate table is integrated into a logical model.

  In the data integration device according to the present invention, in the above invention, the conversion means includes character code conversion processing for converting a difference between character code systems, blank space removal, and full-width half-width conversion as the data value conversion. Character string conversion processing that normalizes character strings, unit conversion processing that converts differences in character and number units, year notation conversion processing that converts differences in year and Japanese calendar, Chinese numerals / It is characterized in that at least one of a Chinese numeral notation conversion process for converting the difference between an Arabic numeral / Roman numeral and a numerical value, an effective digit conversion process for aligning the effective digits of a numeral, and a conversion process between data types is performed.

  Further, the data integration method according to the present invention is a data integration method in a system including a data integration device that collects and integrates data existing in a plurality of information sources managed in different systems. A data collection process for collecting data in the form of a data model on each information source side (hereinafter referred to as “physical model”), and data collected by the data collection process are defined in advance for each application that uses the data. A data conversion process for converting at least one of a data structure and a data value so as to become a converted data model (hereinafter referred to as a “logical model”), and the data converted by the data conversion process as the user application And a data providing process to be provided.

  In the data integration method according to the present invention, in the above invention, the data existing in the information source is set as master data, and among the master data, data to be disclosed to another system is registered in the data integration apparatus. The user application defines the data to be used in advance as a logical model in the data integration device among the data registered in the data integration device, and searches for the logical model when necessary. When referring to the data registered in the integrated device as a logical model and updating the data of other systems, make an update request for the data to the system that manages the data as master data It is characterized by.

  The computer-readable recording medium according to the present invention is a data integration program executed in a data integration device that collects and integrates data existing in a plurality of information sources managed in different systems. A data collection step for collecting data from the information source in the form of a data model (hereinafter referred to as “physical model”) on each information source side, and data collected by the data collection step on the data use side A data conversion process for converting at least one of the data structure and the data value so that the data model is defined in advance for each application (hereinafter referred to as “logical model”), and the data converted by the data conversion process Providing a data to the user application Wherein the recording data integration program causing a computer to execute the.

  According to the present invention, it is possible to obtain a data integration device, a data integration method, and a computer-readable recording medium that records a data integration program that can reduce the load on the user-side application without modifying the information source. Play.

FIG. 1 is an explanatory view showing the entire configuration of EII according to the present invention. FIGS. 2-1 is explanatory drawing (the 1) which shows the principle of EII concerning this invention. FIGS. 2-2 is explanatory drawing (the 2) which shows the principle of EII concerning this invention. FIG. 3 is an explanatory diagram illustrating an example of a table structure (schema). FIG. 4 is an explanatory diagram showing the architecture (physical model definition) of the integrated engine. FIG. 5 is an explanatory diagram showing the architecture (mapping) of the integrated engine. FIG. 6 is an explanatory diagram showing the architecture (inquiry) of the integrated engine. FIG. 7 is an explanatory diagram showing the architecture (actual operation) of the integrated engine. FIG. 8 is an explanatory diagram (part 1) illustrating the architecture (cleansing) of the integrated engine. FIG. 9 is an explanatory diagram (part 2) of the architecture (cleansing) of the integrated engine. FIG. 10 is a flowchart showing details of cleansing processing. FIG. 11 is an explanatory diagram showing a functional configuration for executing the processing of the flowchart shown in FIG. FIG. 12 is a flowchart showing the contents of the mapping process. FIG. 13 is an explanatory diagram showing an example of mapping definition. FIG. 14 is an explanatory diagram showing an example of evaluation points. FIG. 15 is an explanatory diagram showing mapping control based on the mapping definition of FIG. FIG. 16 is an explanatory diagram illustrating an example of the definition of the search condition. FIG. 17 is a flowchart showing the contents of the mapping process. FIG. 18 is an explanatory diagram of an example of mapping definition. FIG. 19 is an explanatory diagram of an example of the created search expression. FIG. 20 is a flowchart showing the contents of the mapping process. FIG. 21 is a flowchart showing the contents of the mapping process. FIG. 22 is an explanatory diagram showing the state of each subsystem before the introduction of MDM. FIG. 23 is an explanatory diagram showing an implementation example of MDM according to the prior art.

  Exemplary embodiments of a data integration apparatus, a data integration method, and a computer-readable recording medium recording a data integration program according to the present invention will be explained below in detail with reference to the accompanying drawings. FIG. 1 is an explanatory view showing the entire configuration of EII according to the present invention. In FIG. 1, reference numeral 101 denotes a data integration device (hereinafter referred to as “EII”), which includes an integration engine 110 and a data storage function including a physical model 111 and a logical model 112. The EII 101 is connected to the Back system (information source) 103 and the Front system (utilization application) 104 via the service bus 102 or directly.

  The EII 101 includes an integration engine 110 that executes data structure conversion processing (mapping) and data value conversion processing (cleansing) from the physical model 111 to the logical model 112. You can also define table names, data column names, data types, data types with type attributes that define type details, and data column constraints such as primary key constraints and dependent key constraints The meta information (repository) 116 can be defined, and the meta information maintenance function 117 maintains the meta information. By improving this meta information, the above-mentioned mapping and cleansing certainty and efficiency are improved.

  The above functions can be realized by the CPU executing a program recorded in a memory such as a RAM or a ROM (not shown) in the computer system constituting the EII 101. The database (DB) described in the present embodiment has the functions of data recorded on a data recording medium such as a hard disk (not shown) in each computer system and database management software (DBMS). Can be realized.

  FIGS. 2-1 and 2-2 are explanatory views showing the principle of EII according to the present invention. In FIG. 2A, 201 is a former subsystem, 202 is a second subsystem, and 203 is a spear subsystem. The A subsystem 201 includes an A database (DB) 210 having a table A 211 and a table B 212, the B subsystem 202 includes a B database (DB) 220 having a table C221, and the 丙 subsystem 203 includes And a bag database (DB) 230 having a table D231.

  Information of each subsystem, that is, information of the former DB 210, the second DB 220, and the second DB 230 is used as a master. In this way, independent data management is performed for each subsystem. In addition, the information that is desired to be shared in the master DB (the former DB 210, the second DB 220, the second DB 230) is disclosed to EII 101. This allows each subsystem to manage all its information. 2A and 2B, the table A211 includes data strings “A1” to “A5”, but the table A′241 of the physical model 111 includes “A1”, “A3”, “ Information of only “A4” exists virtually. From this, it can be seen that regarding the table A211, only “A1”, “A3”, and “A4” are shared, and “A2” and “A5” are not shared.

  Here, in the virtual integration, information is collected from the master DBs 210, 220, and 230 when they are needed. In physical integration, a master DB replica 240 is placed on the EII 101 in advance, that is, information replication is performed.

  Further, the EII 101 provides a function of providing the integration result to the application to be used. 2A and 2B, the integration result can be referred to as the logical model 112 as the table MC251 dedicated to the second system. Details regarding the conversion (integration processing) from the physical model 111 to the logical model 112 will be described later.

  In FIG. 2A, the solid line indicates a table entity, and the broken line indicates a virtual table. Therefore, the table A 211 of the former DB 210, the table B 212, the table C 221 of the second DB 220, the table D 231 of the bag DB 230, and the replica table D ′ 245 are shown by solid lines, and the tables 241 to 243 of the physical model 111 and the tables of the logical model 112 MC251 is indicated by a wavy line. The same applies to FIG. 2B, and 241 to 244 are replicas, and are indicated by solid lines.

  Here, the operation of the function-X and the function-Y will be verified. The operations of the function-X and the function-Y, and the table A 211, the table B 212, and the table C 221 that are the targets of these operations are the operations of the function-X and the function-Y in the prior art shown in FIGS. The table A 2211, the table B 2212, and the table C 2221 are the same as the operation target.

  FIG. 2A is an explanatory diagram of an example of virtual integration as the principle of EII according to the present invention. First, “update the columns A1, A2, A3, and A4 of the table A (1)” that is the function-X in the subsystem A 201 is executed. Therefore, the subsystem A 201 updates “A1”, “A2”, “A3”, and “A4” of the database 210.

  Next, “write the sum of A2 and A3 into B4 of table B (2)” is executed. Therefore, the subsystem A 201 writes the sum of “A2” and “A3” in the table A211 into “B4” in the table B212. As described above, the processing in the subsystem realized before the data integration is completed independently as a transaction closed in the subsystem, including the update processing. These processes have been realized as functions of the subsystem A before implementation of data integration, and it is shown that the subsystem improvement by the implementation of data integration is unnecessary.

  In particular, because processing related to data update is closed in the subsystem, if something abnormal occurs during rollback, that is, transaction processing, and processing cannot be completed normally, the related processing is stopped and the related information is displayed. Even when it is necessary to return to the state before the processing, there is an effect that the rollback can be executed easily and reliably. Therefore, the burden on the application is light and the effect of changing the application does not reach the outside of the subsystem.

  Next, “update C4 with reference to B4 of table MC (3)” which is function-Y in the second subsystem 202 is executed. Therefore, the subsystem B 202 requests “B4” from the table MC managed as the logical model 112 of the EII 101. The EII 101 refers to “B4” in the former DB 210 and returns the result to the subsystem 202 as “B4” in the table MC. The second subsystem 202 updates “C4” based on the answer result. Therefore, in the virtual integration, in order to collect data from the information source when requested by the utilization system, it can be ensured that the information is the latest information at that time. In addition, a reference load of the subsystem A 201 that is an information source is generated at the time of data collection, but is significantly reduced as compared with the transaction load at the time of update of the prior art shown in FIG.

  As described above, when the data integration apparatus (EII 101) according to the embodiment of the present invention collects and integrates data existing in a plurality of information sources managed in different systems, the information source (for example, the former DB 210). From this, data is collected with the data model (physical model 111) on each information source side, and the collected data is converted into a data model defined in advance for each application (for example, the second subsystem 202) of the data. (Logical model 112) The necessary conversion of at least one of the data structure and the data value is performed, and the converted data is provided to the use side application.

  In addition, data is collected from information sources in real time based on requests from applications on the user side. In addition, a virtual integrated database (tables 241 to 243) including meta information of data limited to data to be disclosed among data stored in a master database of data provided on the subsystem side is created. Based on the meta information included in the created virtual integrated database, conversion (mapping, cleansing) of at least one of the data structure and the data value is performed.

  FIG. 2B is an explanatory diagram showing an example of physical integration as the principle of EII according to the present invention. In FIG. 2B, in order to reduce the reference load of the information source side subsystem in FIG. 2A, a journal for recording DB updates in units of transactions is provided in the DB of each information source side subsystem. Is used to reflect the update of the information source on the replica of the integrated DB.

  In FIG. 2B, “-update the columns A1, A2, A3, and A4 of the table A (1)” and “write the sum of A2 and A3 to B4 of the table B (2)” of the function-X are illustrated. When executed in the same manner as in 2-1, an update record of the former DB is generated as a journal 261 for each transaction. This journal 261 is used to notify the EII 101 of the update of the former DB, and by reflecting it in the table A ′ 241 and the table B ′ 242 of the replica 240 on the EII 101, the former DB 210 as the information source and the replica 240 on the EII 101 Realize synchronization.

Similarly to FIG. 2-1, the sub-system 202 also accesses the logical model 112 of the EII 101, acquires “B4” of the table MC, and updates “C4”. At this time, the EII 101 only has to reply to the system B with reference to “B4” held in the table B ′ 242 of the replica 240. In this way, physical integration has the effect that the access performance from the utilization system (B subsystem 202 in FIG. 2-2) is high, and the load on the information source (A subsystem 201 in FIG. 2-2) can be minimized. Play. In addition, even when the information source is stopped, the information immediately before the stop held in the replica can be used.

  As described above, the physical integration of the data integration apparatus (EII 101) according to the embodiment of the present invention is performed on the data to be disclosed among the data stored in the master database provided on each subsystem side. A replication database (replica 240) made up of limited data is created, the created replication database is updated in synchronization with the update of the master database, and data is collected from the replication database.

  Next, a specific example of data integration in EII 101 will be described. FIG. 3 is an explanatory diagram illustrating an example of a table structure (schema). In FIG. 3, reference numeral 301 denotes a table schema, which indicates table name = table A and column names, types, and constraints of each column, and 302 indicates an actual table A based on the above 301 schema.

  That is, the column names in Table A are composed of “employee number”, “name”, and “telephone number”. The data value type of each column consists of Integer (integer type) and String (character string type). Each data value can have a type attribute as information for defining the details of the type. For example, in the integer (integer type), the type attribute “MaxLength = 10” can indicate that the integer type has a maximum of 10 digits. Similarly, by setting the type attribute “CharCode = S_JIS” in String (character string type), it is possible to indicate that the character code of the character string is Shift JIS. Each column constraint is composed of Mkey (primary key constraint) and Sky (dependent key constraint).

  In FIG. 3, the employee number is Integrer (integer type), and the name and telephone number are String (character string type). The employee number indicates that there is a restriction of Mkey (primary key restriction).

  FIG. 4 is an explanatory diagram showing the architecture (physical model definition) of the integrated engine. The definition of the physical model is executed, for example, as an operation of the meta information maintenance function 117 shown in FIG. In FIG. 4, 401 is an information source system 1 having a table (table A411), 402 is an information source system 2 having tables (table B412 and table D413), and 403 is 401 formed by a table (table A421). Corresponding physical model 1, 404 is physical model 2 corresponding to 402 consisting of tables (Table B422 and Table D423).

  Here, the EII 101 acquires the schema of the table to be shared from the table of each information source system, and creates a physical model based on the acquired information. At that time, unnecessary columns are deleted, column names, types, type attributes, constraints, etc. are corrected for the registered physical model. In FIG. 4, the information about the “address” column in Table A411 is not shared by the system 1 and is deleted from the physical model. The same applies to the “extension number” column in the table B412 and the “extension” column in the table D413.

  FIG. 5 is an explanatory diagram showing the architecture (definition of mapping) of the integration engine. The mapping definition is executed, for example, as an operation of the meta information maintenance function 117 shown in FIG. The mapping definition defines a logical model based on a defined physical model. As preconditions of the mapping definition in FIG. 5, the information sources are the system 1 (physical model 1) 403 and system 2 (physical model 2) 404 shown in FIG. 4, and the user side is the system 3 (601). The required logical model 3 (501) and the logical model 4 (502) required by the system 4 (602). It is assumed that the same employee number code (key) is used in the four systems.

  In FIG. 5, first, necessary items of the physical model are set in the logical model. Specifically, from the table A421 of the physical model 1, "employee number", "name", "phone number", and from the table B422 of the physical model 2, "employee number", "affiliation" are respectively logical models. 3 is set in Table C511. Here, when a plurality of tables (for example, Table A and Table B) are joined (JOIN), it is defined by connecting the Mkey constraint column of the target table to the Mkey constraint column of the logical model. In the logical model, column names, types, type attributes, and constraints inherited from the physical model are set, and mapping definitions are also created automatically.

  Further, when a specific column is converted by another table when set in the table E of the logical model 4, it is defined by connecting an intermediate table (table D423) between them. That is, “affiliation” in the table B422 is “Integrer” representing the affiliation code, but is converted to “String” representing the affiliation name using the intermediate table D423. Then, in the table E512 of the logical model 4, “String” as the conversion result is set as the “affiliation” column. This is defined by connecting Sky of the target table to Mkey of the intermediate table and connecting the conversion destination column to the logical model.

  Next, necessary corrections are made to the logical model. For example, unnecessary columns are deleted (however, columns having Mkey constraints cannot be deleted), column names are changed, types are changed, and type attributes are changed. For example, since the “name” column of the table D423 is String (character string type) and “CharCode = JEF” is set as the type attribute, the column to which the original logical model table E512 belongs is column name = “name”, Data type = character string, type attribute = “CharCode = JEF”. Here, the column name is changed to “affiliation” and the type attribute is changed to “CharCode = S_JIS” in order to change to a column necessary for the logical model. These changes are reflected in the mapping definition and cleansing definition. The meta information necessary for the integration process is stored in the repository 116 and used from the integration engine 110 when necessary.

  FIG. 6 is an explanatory diagram showing the architecture (inquiry) of the integrated engine. The system on the use side (system 3 (for example, having table 611 as an internal information format), system 4 (for example, having table 612 as an internal information format)) is prepared in advance as a logical model (logical model 3, logical Necessary information can be obtained by issuing a search (SQL) sentence to the model 4). Since the obtained information matches the internal representation of each system, it can be used as it is. Here, the actual operation when the system 4 issues a request to the table E512 to request the employee number 7500 information will be described with reference to FIG.

  FIG. 7 is an explanatory diagram showing the architecture (actual operation) of the integrated engine. The actual integration process is executed, for example, in the integration engine 110 shown in FIG. In FIG. 7, “employee number = 7500” is the search condition for the table E512 of the logical model 4 first. EII converts the search condition for the obtained logical model into the search condition for the physical model. This is a retrieval condition derivation (inverse transformation) process. The “employee number” column of the table E512 of the logical model 4 is obtained from the employee number of the table A421 of the physical model 1 and the employee number of the table B422 of the physical model 2; A search condition of “number = 7500” and “employee number of table B = 7500” is generated. Then, a search statement is executed on Table A421 to obtain a result (A). From the search result (A) of the table A421, the employee number column and the name column are copied to the table E512. This is the result integration process.

  Next, a search statement is executed on the table B422 of the physical model 2 to obtain a search result (B). Although the affiliation column is brought from the search result (B) of the table B422, the affiliation column of the search result (B) is an affiliation code. Therefore, the result converted into the name using the table D423 is shown in Table E512. Set to the value of the belonging column. This is a value conversion process. At this time, the name string in the table D423 is a JEF code character string, whereas the column belonging to the table E512 requires a shift JIS character string, so that the “JEF → shift JIS conversion” cleansing function (details will be described later) And the result is copied to the affiliation column of Table E512.

  8 and 9 are explanatory diagrams showing the architecture (cleansing) of the integrated engine. Cleansing is a type check when copying of values occurs. If the type or type attribute differs between the copy source (From type) and the copy destination (To type), the type or type attribute corresponds to the cleansing. Necessary processing is executed. FIG. 8 is an example of cleansing due to the difference in type attributes. In FIG. 8, both data types match with String (character string type), but since the type attributes are different, the From type attribute (CharCode = JEF) changes to the To type attribute (CharCode = S_JIS). In order to perform conversion, cleansing processing for converting JEF to shift JIS is executed, and the result is copied to the copy destination.

  In addition to character code system conversion (character string type), a cleansing function executed when the type attributes are different includes, for example, character string conversion (character string type). Specifically, remove all blanks, remove leading and trailing blanks, consolidate continuous blanks into one, tab / blank conversion, line feed code removal, line feed code conversion, full-width / half-width conversion, uppercase / lowercase conversion, character replacement (Use replacement table).

  Other cleansing functions include unit conversion (from 3,000 yen to 3,000 yen or vice versa) (string / numeric type), year notation (from 2004 to 2004, or its) Inverse) (character string type / numeric type), Chinese numeral notation (from 16 to 16 or vice versa) (character string type / numeric type), significant digit conversion (numeric type, increase or decrease in the number of significant digits), Conversion of the number of effective characters (character string type, increase or decrease in the number of effective characters).

  FIG. 9 is an example of a cleansing function executed when the types are different. In FIG. 9, it is a string (character string type), and JEF code character string “2000” (all four characters) is converted from a character string to an integer and converted to an integer of 10 digits or less. Execute cleansing processing. As a result, the data is converted to “2000” (4-digit integer) data.

  As described above, the data integration device according to the embodiment of the present invention performs character code normalization including character code system conversion processing for converting differences in character code systems, white space removal, and full-width half-width conversion as data value conversion. Character string conversion processing, unit conversion processing to convert the difference between characters and numbers, year notation conversion processing to convert the year / difference between the Western and Japanese calendars, Chinese numerals / Arabic numerals / Roman numerals as numeric expressions At least one of a Chinese numeral notation conversion process for converting the difference between a numerical value, a significant digit conversion process for aligning the effective digits of a number, and a conversion process between data types.

  FIG. 10 is a flowchart showing the details of the cleansing process, and FIG. 11 is an explanatory diagram showing a functional configuration for executing the process of the flowchart shown in FIG. In the flowchart of FIG. 10, first, the cleansing control unit 1103 determines whether the From type of the From value 1101 and the To type of the To value 1102 shown in FIG. 11 are the same (step S1001). Here, when both are the same (step S1001: Yes), the top type attribute common to the From type and the To type is pointed (step S1002).

  Next, it is determined whether or not the values of the type attributes are the same (step S1003). If the value of the type attribute is the same (step S1003: Yes), the next type attribute common to the From type and the To type is pointed (step S1004). Then, it is determined whether there is still a type attribute to be confirmed (step S1005). If there is (step S1005: Yes), the process returns to step S1003, and thereafter, steps S1003 to S1005 are repeatedly executed. Then, when there is no type attribute to be confirmed (step S1005: No), the series of cleansing processing is terminated.

  If the From type is different from the To type in step S1001 (step S1001: No), it is next determined whether or not the data type can be converted (step S1006). If data type conversion is not possible (step S1006: No), cleansing process failure processing is performed. On the other hand, when the data type can be converted (step S1006: Yes), the type conversion calling unit 1104 shown in FIG. 11 calls the type conversion, and the type conversion processing unit 1105 shown in FIG. The corresponding type conversion process is executed (step S1007).

  Then, it is determined whether or not the type conversion process is successful (step S1008). If the type conversion process is successful (step S1008: YES), the process proceeds to step S1002. On the other hand, when the type conversion process fails (step S1008: No), the cleansing process failure process is performed.

  If the value of the type attribute is different in step S1003 (step S1003: No), the cleansing call unit 1106 shown in FIG. 11 calls the type attribute conversion to determine whether or not there is a corresponding cleansing function ( Step S1009). Here, when there is no corresponding cleansing function (step S1009: No), a cleansing process failure process is performed. On the other hand, if there is a corresponding cleansing function (step S1009: Yes), the corresponding cleansing process is executed by the cleansing processing unit 1107 shown in FIG. 11 (step S1010).

  Then, it is determined whether or not the executed cleansing process is successful (step S1011). If the cleansing process is successful (step S1011: Yes), the process proceeds to step S1004. On the other hand, when the cleansing process fails (step S1011: No), the cleansing process failure process is performed.

  FIG. 12 is a flowchart showing the contents of the mapping process. In the flowchart of FIG. 12, first, a physical model search formula is created based on the search formula and mapping definition for the logical model (step S1201). Details of this will be described later (see flow 1 in FIG. 17). Next, the search formula of the top physical model is pointed (step S1202), and the search formula is executed on the pointed physical model to acquire the corresponding data (step S1203).

  Then, it is determined whether or not the search is successful. If the search fails (step S1204: No), a mapping failure process is executed. On the other hand, if the search is successful (step S1204: Yes), the corresponding data of the search result is copied to the logical model (step S1205). This detailed content will also be described later (see flow 2 in FIG. 20). Then, it is determined whether or not the copy has succeeded (step S1206). If the copy has failed (step S1206: No), a mapping failure process is executed.

  On the other hand, if the copy is successful in step S1206 (step S1206: Yes), the next search expression is pointed (step S1207). Then, it is determined whether or not there is a remainder (step S1208). If there is a remainder (step S1208: Yes), the process returns to step S1203, and thereafter, repeatedly executes steps S1203 to S1208. In step S1208, when there is no remaining (step S1208: No), a series of mapping processing is ended.

  FIG. 13 is an explanatory diagram showing an example of mapping definition, FIG. 14 is an explanatory diagram showing an example of evaluation points, and FIG. 15 is an explanatory diagram showing mapping control based on the mapping definition of FIG. . 13, “Table E. Name” indicates the name column of Table E, and the numbers (1) to (6) in parentheses in FIG. 13 correspond to (1) to (6) in FIG. doing. For example, the mapping definition shown in (1) of FIG. 13 indicates that the employee number column is associated (duplicated) with the employee number column of Table E for the corresponding data in Table A of FIG.

  Here, as shown in FIG. 13 (6), a mapping definition corresponding to the conversion by the intermediate table is created. This mapping definition is created for each table of the logical model, and its contents are sorted in the order of evaluation points. An example of evaluation point derivation grounds is shown in FIG. FIG. 16 is an explanatory diagram showing an example of a search condition issued from the user application.

  FIG. 17 is a flowchart (flow 1) showing the processing contents of mapping (details of S1201), and FIG. 18 is an explanatory diagram showing an example of the mapping definition used in FIG. In the flowchart of FIG. 17, first, the mapping definition of the logical model table (table E) designated by the search formula shown in FIG. 16 is acquired, and the resolved flag is cleared (step S1701). That is, all the “solutions” in FIG. 18 are set to “0”.

  Next, the To column of the mapping definition is searched from above, and the first From column for the column specified by the search condition is obtained (step S1702). At that time, the mapping definition with the resolution flag is skipped. For example, “Table E. Employee number” specified as the search condition shown in FIG. 16 is searched from the To column, and “Table A. Employee number” is obtained as the From column. Then, cleansing of the search condition is executed using the corresponding From column as a search target (step S1703). Details of step S1703 will be described later (see flow 3 in FIG. 21).

  Then, it is determined whether the From column is a terminal column (step S1704). Here, the terminal column means a column that does not exist in the To column (original From column) in the mapping definition. If the From column is not a terminal column (step S1704: No), then the cleansing result is set as a search condition with the corresponding From column as a search target, and the search condition cleansing is reprocessed (step S1704: No). S1705). Thereafter, the process returns to step S1702. For example, in the case where the search condition for “Table D. Name” in the From column in FIG. 18 is derived, “Table D. Name” exists in the To column (element on the sixth row). It is necessary to derive a search expression for “affiliation”. In this case, it is converted again into a search expression of “Table B. Affiliation” (From element in the third row), and this becomes the end column. This loop of steps S1702 to S1705 supports multi-step mapping such as conversion by an intermediate table.

  In step S1704, when the From column is a terminal column (step S1704: Yes), a search expression using the cleansing result as a search condition is created using the corresponding From column as a search target (step S1706). FIG. 19 shows an example of the created search expression. Then, a resolution flag is set in the mapping definition that can be resolved by this search (step S1707). That is, “Solution” in FIG. 18 is set to “1”. Here, “solution” means that the corresponding mapping can be realized by copying the data string of the search result. For example, since the mapping corresponding to (1) and (4) in FIG. 15 is resolved by the search in the first row in FIG. 19, the resolution columns in the first and fourth rows in FIG. 18 are set to “1”. it can.

  Next, it is determined whether or not there is an unresolved mapping definition in which the From column is a terminal column (that is, whether there is a “resolution” of “0”) (step S1708). Here, when there is still unresolved (step S1708: Yes), the process returns to step S1702. Then, steps S1702 to S1708 are repeatedly executed. If there is no unsolved in step S1708 (step S1708: No), the series of processing of flow 1 is terminated, and the process proceeds to step S1202 shown in FIG. Here, FIG. 18 shows this state (when there is no outstanding in step S1708). That is, all mapping definitions (1) to (4) in which the From column is a terminal column have been solved.

  FIG. 20 is a flowchart (flow 2) showing the mapping process contents (details of S1205), and shows the contents of the reflection process to the logical model. In the flowchart of FIG. 20, first, the top of the corresponding data is pointed (step S2001). Next, it is determined whether or not the pointed data can be directly reflected in the logical model (step S2002). If the data can be directly reflected in the logical model (step S2002: Yes), cleansing is performed, and then the result is copied to the logical model (step S2003).

  Next, it is determined whether or not the copy is successful (step S2004). If the copy fails (step S2004: No), it is determined that the process of flow 2 is unsuccessful, and step S1206 of the flowchart shown in FIG. Migrate to On the other hand, when the copy is successful (step S2004: Yes), the next value is pointed (step S2005). Then, it is determined whether or not there is any remaining information (step S2006). Here, when there is a remainder (step S2006: Yes), the process returns to step S2002. On the other hand, when there is no remaining (step S2006: No), a series of processing of the flow 2 is complete | finished and it transfers to step S1206 of the flowchart shown in FIG.

  If it is not possible to directly reflect the data in the logical model in step S2002 (step S2002: No), the search condition cleansing is performed using the target data value as the search condition and the corresponding intermediate table column as the search target. Is executed (step S2007). Details of this processing will be described later (see FIG. 21). Then, it is determined whether or not the cleansing has succeeded (step S2008). If the cleansing has failed (step S2008: No), it is determined that the process of flow 2 has failed, and the process proceeds to step S1206 in the flowchart shown in FIG. .

  Next, the intermediate table is searched (step S2009), and it is determined whether the search is successful (step S2010). Here, if it has failed (step S2010: No), it is determined that the process of flow 2 has failed, and the process proceeds to step S1206 of the flowchart illustrated in FIG. On the other hand, if successful (step S2010: Yes), the pointed value is replaced with the search result (step S2011), and then the process returns to step S2002. In S2007 to S2011, a data value conversion process using the intermediate table is realized.

  FIG. 21 is a flowchart showing the details of the mapping process (details of S2007 and S1703), and shows the contents of the search condition cleansing process. In the flowchart of FIG. 21, first, the search condition is set to the From value together with the type and the type attribute, and the column of the search target table is set to the To value (step S2101). Next, cleansing is executed (step S2102). Then, it is determined whether or not cleansing is successful (step S2103). Here, when cleansing fails (step S2103: No), it is determined that the process of flow 3 has failed, and the process proceeds to step S2008 in the flowchart illustrated in FIG. 20 or step S1704 in the flowchart illustrated in FIG.

  On the other hand, when cleansing is successful (step S2103: Yes), the cleansing result (To value) is set as a search condition together with the type and type attribute (step S2104). As a result, the series of processing ends, and the process proceeds to step S2008 in the flowchart shown in FIG. 20 or step S1704 in the flowchart shown in FIG. The value cleansing process (S2003, S2102) executed in these flowcharts is realized by the cleansing process already described with reference to FIG.

  As described above, according to the present invention, when collecting and integrating data existing in a plurality of information sources 103 managed separately, data collection from the information source 103 is performed by data on each information source side. The model (physical model 111) is used, and the integration processing is performed so that the data structure is converted to a data model (logical model 112) defined in advance for each user application 104 (mapping) and each value is aligned. The processing (cleansing) is performed and the result is provided to the user application as a view (logical model 112) for each application, so that the modification on the information source 103 side is unnecessary and the burden on the user application 104 is reduced. Can do.

  In addition, according to the present invention, information is collected from the information source 103 in real time when requested by the use side application 104, data integration processing is executed, and the result is provided to the use side application 104 in real time. So-called virtual integration can be realized.

  Further, according to the present invention, a replica 240 (replication database) that is a copy limited to information publicly disclosed by the information source 103 is created on the EII 101 side in advance, and in synchronization with the update of the information source 103, a transaction unit. So-called physical integration in which the difference is applied to the replica 240 (replication database), data is collected from the replica 240 and integrated in response to a request from the user application 104, and the result is provided to the user application 104. By realizing the above, it is possible to reduce the access load to the information source 103 and to realize data integration using the data immediately before the stop even during the stop time of the information source 104.

  In addition, according to the present invention, the virtual integration or the physical integration can be selected for each information source 103 or for each table according to the property and operation mode of the data managed by the information source 103. Data integration can be realized.

  Further, according to the present invention, at least the name of the table, the name of the data string, the data as the meta information representing the format (physical model 111) of each information source 103 and the format (logical model 112) of each user application 104 The type of data is defined, and the type attribute that defines the details of the type can be defined for the data type. In the cleansing process for converting the value of each data from the physical model 111 to the logical model 112, when the data type is different, the copy source is the From type, the copy destination is the To type, and the From data value is the To type data value. Fine cleansing can be efficiently performed by executing a type conversion function for converting to, and executing a cleansing process for matching the type attributes when there is a difference in the type attributes.

  In addition, according to the present invention, constraints on data columns such as a primary key constraint and a subordinate key constraint are also defined as the meta information, and the correspondence relationship between the tables and columns of the physical model 111 and the tables and columns of the logical model 112 A mapping definition indicating the physical model 111 is generated based on the mapping definition from the search condition expression for the table of the logical model 112 requested by the user application 104.

  This search condition expression is created in the order evaluated by the evaluation function of the mapping definition, and the search condition is the clean type processing in the reverse direction of integration with the logical model 112 side as the From type and the physical model 111 side as the To type. Data is collected from each information source 103 (physical model 111) using the search condition formulas generated by these, and the collected data is mapped and cleansed based on the mapping definition, the data value type, and the type attribute definition. Can be integrated into the data of the logical model 112 and provided to the user application 104.

  Further, according to the present invention, as a mapping definition, an intermediate table (for example, data conversion) is performed between the logical model 112 required by the system of the user application 104 and the physical model 111 as a format on the information source 103 side. Table D 423) shown in FIG. 5 and the like can be defined, and by integrating the data collected as the physical model 111 using the intermediate table into the logical model 112, integration between data models having a larger difference is achieved. Is possible.

  Further, according to the present invention, cleansing is a character code system conversion process that converts the difference between character code systems, a character string conversion function that performs character string normalization such as white space removal and full-width half-width conversion, and a unit of characters and numbers. Unit conversion function to convert the difference between the year, year notation conversion to convert the difference between the Western calendar, Japanese calendar, etc., Chinese numeral notation conversion to convert the difference between Chinese numerals / Arabic numerals / Roman numerals and numbers, Either a significant digit conversion that aligns the significant digits of a number or a conversion function between data types is possible.

  In addition, according to the present invention, when managing master data managed independently by a plurality of systems, master data is managed independently by each application in each system, and in each master data, Data that is useful for other systems is defined as a physical model 111 in the form of master data, and data disclosure is defined for the EII 101. Each system of the user application 104 uses a logical model that is easy to use for each application. EII101 is defined as follows, and each usage system integrates and uses (refers to) the published data through a logical model that is a unique format, and data is updated by each information source using SOA (Service Oriented Architecture) or the like. Provided by the application To adopt a method carried out by requesting the information update function.

  This eliminates the need to modify each information source system when introducing master data management (MDM), simplifies the application of the system used, eliminates the need for a physical common master database, and provides transaction control within each system. By limiting, the master update can be simplified. In addition, even if the format of each master data (information source) changes, only the definition of the physical model for the data integration device can be changed, preventing the spread to other systems and efficient master data management (MDM) Can be realized.

  In addition, according to the present invention, as a result, it is optimized for the work in charge of each subsystem due to easy introduction into an existing system, high flexibility in changing and replacing the system, and high independence of each subsystem. It is possible to achieve both the pursuit of partial optimization and the pursuit of overall optimization for the entire information system.

  As described above, the data integration device, the data integration method, and the computer-readable recording medium that records the data integration program according to the present invention are useful for integrating and utilizing information of a plurality of different systems. Suitable for use in EII and MDM.

101 Data integration device (EII)
102 Service Bus 103 Information Source 104 Application 111 Physical Model 112 Logical Model 201, 202, 203 Subsystem 210, 220, 230 Master Database 240 Replica

Claims (5)

A data integration device for collecting and integrating data existing in a plurality of information sources managed in different systems,
For each of the plurality of information sources, one or more data items included in the information source and a physical data model that is a definition including the data value type of the data item, and one or more data items used in the user application And a logical data model that is a definition including a data value type of the data item, and mapping information indicating a correspondence relationship between the data item of the logical data model and the data item of the physical data model; ,
Generating means for receiving a search condition for the physical data model based on the received search condition and the mapping information;
Data collection means for collecting data corresponding to the search condition for the physical data model from each of the plurality of information sources using the search condition for the generated physical data model as it is.
The data collected, based on the mapping information, the not such to put the conversion from the physical data model to the data corresponding to the logical data model, and, on the basis of the mapping information, the plurality of information sources, respectively Data processing means for integrating the data collected from and converted .
Data transmission means for transmitting the integrated data to the user application;
A data integration device comprising: Virtual integrated database creation that creates a virtual integrated database based on meta information of data limited to data that is made public among data stored in the master database of the data provided on the system side of the information source With means,
The data processing unit performs conversion of at least one of a data structure and a data value based on meta information included in the virtual integrated database created by the virtual integrated database creating unit. The data integration device described in 1. Of the data stored in the master database of the data provided on the system side of the information source, a replication database creating means for creating a replication database consisting of data limited to data to be disclosed,
Create the replicated database was made, and a duplicate database updating means for updating in response to said master data base stores the data of the update,
The data integration apparatus according to claim 1, wherein the data collection unit collects data from the duplicate database. A data integration method in a system including a data integration device for collecting and integrating data existing in a plurality of information sources managed in different systems,
A system including the data integration device;
For each of the plurality of information sources, one or more data items included in the information source and a physical data model that is a definition including the data value type of the data item, and one or more data items used in the user application And a logical data model that is a definition including a data value type of the data item, and mapping information indicating a correspondence relationship between the data item of the logical data model and the data item of the physical data model, and
A search condition for the logical data model is received from the user side application, and based on the received search condition and the mapping information, a search condition for the physical data model is generated,
Using the generated search condition for the physical data model, from the plurality of sources, respectively, while the physical data model, not such to put the collection of data corresponding to the search condition for the physical data model,
The data collected, based on the mapping information, the not such to put the conversion from the physics data model to the data corresponding to the logical data model, and, on the basis of the mapping information, the plurality of information sources, respectively Integrating the data collected from and converted
Data integration methods and transmitting a unified the data to the utilization-side application. A data integration program executed in a data integration device that collects and integrates data existing in a plurality of information sources managed in different systems,
For each of the plurality of information sources, one or more data items included in the information source and a physical data model that is a definition including the data value type of the data item, and one or more data items used in the user application And a logical data model that is a definition including a data value type of the data item, and mapping information indicating a correspondence relationship between the data item of the logical data model and the data item of the physical data model;
Receiving a search condition for the logical data model from the user-side application, and generating a search condition for the physical data model based on the received search condition and the mapping information;
Using the generated search condition for the physical data model, from the plurality of sources, respectively, while the physical data model, and performing collection of data corresponding to the search condition for the physical data model,
The data collected, based on the mapping information, the not such to put the conversion from the physical data model to the data corresponding to the logical data model, and, on the basis of the mapping information, the plurality of information sources, respectively Integrating the data collected from and converted .
Transmitting the integrated data to the consuming application ;
Data integration program for causing execute the data integration system.

Images