An automatic data loading method for power grid data warehouse based on ontology

Abstract

The present invention discloses an automatic data loading method for a power grid data warehouse based on ontology, the method comprising the following steps: configuration and data preparation, constructing the center point of a power grid DV data warehouse and its subsidiary tables; performing pattern matching and data matching of a benchmark table and a secondary table, constructing an approximate business key matching table and performing approximate matching of business key values; searching for tables corresponding to business objects and storing data in the benchmark table and the secondary table, searching for whether there is a foreign key field in the benchmark table pointing to the record of this table and whether there is a foreign key in the benchmark table pointing to other relationship tables, repeating the data loading process until the data loading process of multiple secondary tables is completed; determining the relationship between associated business objects and loading data, searching for a link table, and loading the associated data of the temporary storage area benchmark table and the secondary table into the link table. The present invention efficiently extracts source data from data tables of multiple power grid source business systems and accurately loads them into the power grid DV data warehouse. Its storage method of accommodating inconsistent data ensures the traceability and auditability of data.

Description

Technical Field

[0001] The invention relates to the technical field of data loading for a power grid data warehouse, and in particular to an ontology-based automatic data loading method for a power grid data warehouse. Background Art

[0002] In the construction of power grid data warehouse, how to organize data efficiently has always been a technical problem faced by power grid enterprises. In particular, with the enhancement of data collection methods and the expansion of business markets in recent years, the organization of massive data in power grids is required to have a centralized, highly scalable, and highly available platform that supports full-network and cross-domain data integration, as well as dynamic on-demand supply and real-time allocation of resources. The traditional use of multidimensional data models and their associations to implement data warehouses can no longer meet the requirements for organizing massive data in the big data era. Therefore, power grid enterprises began to explore the introduction of the Data Vault (DV) modeling method, that is, to build a new type of data warehouse using the fusion method of paradigm modeling and analytical modeling to meet the special requirements of power grid enterprises for big data organization.

[0003] The basic goal of the power grid DV data warehouse is to separate the central points and their associations (links) from the attributes of business objects (which may change frequently and are stored in the attachments) through business keys (which uniquely identify a business object and are stable and unchanging). Business keys can come from multiple source business systems, are independent and do not depend on other information to exist, and are used to locate and uniquely identify business records, such as customer identification, order identification, product identification, etc. Since the main component of the power grid center is the business key, and both links and attachments rely on business keys to build relationships and attachment components, after determining the modeling subject and its central point, the first step in loading data into the DV model is to determine and load the business key value in the central point. Subsequent link and attachment data loading will be carried out based on the business key; at the same time, in the data of the entire DV model, only the business key remains unchanged, and all other data can be changed and its historical data can be saved.

[0004] Therefore, the accuracy and consistency of the central business key in the power grid DV model will directly determine the data quality of the entire DV model. In a data warehouse environment, business keys usually come from multiple source business systems, and each source business system has a unique identifier for real-world objects, such as a customer ID number. This unique identifier can be used in a single business system as long as it is not repeated, but when multiple business systems are integrated (into a data warehouse), it may be found that an object has multiple unique identifiers and errors may occur. In the current situation where the same object has multiple identifiers, a method of selecting a unique identifier is usually adopted by manual judgment or mandatory regulations, but this method often leads to incorrect selection, resulting in a decrease in the data quality of the power grid DV data warehouse.

[0005] Puonti et al. used Data Vault modeling to create certain constraints on data warehouse objects, namely, the central point principle, link principle, subsidiary principle, reference principle, and conversion method. By establishing rules for filling data into data tables and automatic conversion, data loading code is automatically generated, but only partial code automation can be achieved. Puonti et al. believe that similar situations have not been described in scientific literature. However, the literature does not consider how to determine the business key from the source data table, does not handle the different business keys of the same object between different source business systems, and does not provide specific data loading methods for subsequent central tables, link tables, and subsidiary tables.

[0006] The existing technology for building a DV data warehouse aims to automatically build and load data in the DV data warehouse, but does not consider how to determine the business key from the source data table and how to extract the corresponding correct and complete other data. As a result, the data quality of the entire DV data warehouse cannot be ensured. The main defects are as follows:

[0007] (1) After copying the source business system data to the temporary storage area, it is often found that there are some conflicting or inconsistent low-quality data in these multi-source heterogeneous data. In order to ensure the traceability and auditability of the data, the DV modeling method allows these inconsistent data to be loaded into the DV data warehouse, and then corrects the inconsistent data as needed before the user needs to analyze the data. However, there is still a lack of technical solutions to control inconsistent data in the DV data warehouse;

[0008] (2) Looking further, the business key in the DV model is the key to the entire data warehouse. The accuracy and consistency of the business key will directly determine the data quality of the data warehouse. However, there is currently a lack of specific technical solutions for the handling of inconsistent business keys and the subsequent data loading logic for central tables, link tables, and subsidiary tables. Summary of the invention

[0009] In order to overcome the defects and deficiencies of the prior art, the present invention provides an ontology-based automatic data loading method for a power grid data warehouse. The present invention matches business key values ​​for the same object with different identifiers from data tables of multiple power grid source business systems (hereinafter referred to as source data tables), thereby determining the accurate business key, and then gradually loading the data of the central table, link table and subsidiary table from the source data table, achieving efficient and high-quality extraction of the source data table and accurate loading into the power grid DV data warehouse. Its storage method of accommodating inconsistent data ensures the traceability and auditability of the data.

[0010] The second object of the present invention is to provide an ontology-based automatic data loading system for power grid data warehouse;

[0011] In order to achieve the above object, the present invention adopts the following technical solutions:

[0012] The present invention provides an ontology-based automatic data loading method for a power grid data warehouse, comprising the following steps:

[0013] Build an ontology knowledge base to support pattern matching of data table semantics;

[0014] Based on the power grid DV data warehouse, the three types of tables, namely, the center point, link, and subsidiary, store the power grid business entities, relationships, and their attribute data respectively, forming the target table for data loading;

[0015] Setting a source table of the power grid business system, grouping source data tables of the same business object to form a source table for data loading, wherein the source table includes a benchmark table and multiple secondary tables;

[0016] Copy the data of the benchmark table and the secondary table to the temporary storage area according to their original mode;

[0017] Match the primary keys and field names of the benchmark table and secondary table based on the ontology knowledge base;

[0018] Based on the benchmark table, perform equal matching of business key values, build an approximate business key matching table based on the matching results, and then perform approximate matching of unequal business key values ​​based on the approximate business key matching table;

[0019] Find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the source and loading time data related to the benchmark table and secondary table;

[0020] Based on the approximate business key matching table, an approximate business key link table of the corresponding business key is constructed, and relevant data of the benchmark table and the secondary table are stored;

[0021] Find the table Hub in the target table ST The subsidiary table Sat-Hub-S and the subsidiary table Sat-Hub-T of the benchmark table are stored in the subsidiary table Sat-Hub-S, and the data of the secondary table is stored in the subsidiary table Sat-Hub-T;

[0022] Find out whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the source and loading time data related to the benchmark table and the secondary table; if there is no foreign key field pointing to the record of this table, find out whether there is a foreign key in the benchmark table pointing to other relationship tables. If there is a foreign key pointing to other relationship tables, create a corresponding link table for the primary and foreign keys of the benchmark table and load data, and repeat the search and judgment until all foreign key fields are found;

[0023] Repeat the data loading process of integrating a benchmark table and a secondary table of the business object until the data loading process of multiple secondary tables is completed;

[0024] Determine the relationship table of the associated business object and load the data, find the link table, and load the associated data of the temporary area benchmark table and the secondary table into the link table.

[0025] As a preferred technical solution, the benchmark table and its model are expressed as: S(S PK ,S 1 ,S 2 ,...,S n ), where S PK Represents the primary key of the benchmark table S, S 1 ,S 2 ,...,S n Represents the n field names of the benchmark table S;

[0026] The secondary table and its schema are represented as: T(T PK ,T 1 ,T 2 ,...T m ), where T PK Represents the primary key of the secondary table T, T 1 ,T 2 ,...T m Represents the m field names of the secondary table T;

[0027] Based on the ontology knowledge base, the primary key S of the benchmark table PK and the primary key of the secondary table T PK Perform ontology reasoning to determine the primary key S PK and primary key T PK Whether they are the business keys of the same business object;

[0028] Based on the ontology knowledge base, the field names of the benchmark table and the secondary table are matched and reasoned, and the corresponding matching fields are output.

[0029] As a preferred technical solution, based on the benchmark table, business key value equal matching is performed, and an approximate business key matching table is constructed according to the matching results, specifically including:

[0030] Based on the benchmark table S, r[S PK ] and r[T PK ] Perform equal value matching, and the result is that the two tables have p and q business key values ​​that are not equal. Among them, r[] represents the existing value set, r - [] represents the set of values ​​that do not match the existing values. The cardinality of the two sets | r - [S PK ]|=p,|r- [T PK ]|=q;

[0031] The schema of the approximate business key matching table is expressed as: BusKey-Sim(PK S ,PK T ,V SIM );

[0032] If p>0 and q=0, then r - [S PK ]Insert PK S , null value is placed in PK T , 1 into V SIM ;

[0033] If p=0 and q>0, then r - [T PK ]Insert PK T , null value is placed in PK S , 1 into V SIM .

[0034] As a preferred technical solution, approximate matching of unequal business key values ​​is performed based on an approximate business key matching table, specifically including:

[0035] R - [S PK ]、r - [T PK ], for each business key value in r - [S PK ,S 1 ,S 2 ,...S h ] and r - [T PK ,T 1 ,T 2 ,...T h ] in the matching field S j ≡T j Perform data equality matching between two non-business key fields, and for business key sp∈r - [S PK ] record i and the corresponding business key tp∈r - [T PK ] is sim=z(sp i ,tp k ) / h, where z(sp i ,tp k )=|{s ij |s ij =t kj ,s ij ∈r- [S j ],t kj ∈r - [T j ], <sp i ,s ij >∈r - [S PK ,S j ], <tp k ,t kj >∈r - [T PK ,T j ],1≤j≤h,1≤i≤p,1≤k≤q}|;

[0036] r - [S PK ]×r - [T PK ] are placed in PK S and PK T ,in, And r[V SIM ]={sim|sim≥α}, that is, the approximate business key matching table stores the similarities greater than the matching similarity threshold α, which are from the primary key S PK and primary key T PK Two approximate business keys.

[0037] As a preferred technical solution, find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the relevant data in the benchmark table and secondary table. The specific steps include:

[0038] Find the hub table Hub corresponding to the business object ST in the target table ST , whose mode is represented by Hub ST (HASHPK, BusKey, RecSou, LDT), where HASHPK is the hash value of the business key BusKey, which is the primary key of the table, RecSou is the record source, and LDT is the loading date;

[0039] All primary keys S in the benchmark table S PK Loading values ​​into the Hub ST Table, r[S PK ] is placed into the BusKey field, the source system name of the benchmark table is placed into the RecSou field, the current time is placed into the LDT field, and HASHPK is calculated by the BusKey field using the HASK function;

[0040] r- [T PK ] append the q instance values ​​in the Hub ST Table, r - [T PK ]The instance value is placed in the BusKey field. At the same time, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, and HASHPK is calculated by the BusKey field using the HASH function.

[0041] As a preferred technical solution, an approximate business key link table corresponding to a business key is constructed based on the approximate business key matching table. The specific steps include:

[0042] Construct an approximate business key link table corresponding to the business key. The schema of the approximate business key link table is represented as: Link-SimKey(HASHLPK,PK S ,PK T ,V SIM ,RecSou,LDT), where HASHLPK is the primary key PK of the benchmark table S and secondary table primary key PK T The concatenated HASH value, RecSou is the record source, and LDT is the loading date;

[0043] Using approximate business key matching table BusKey-Sim(PK S ,PK T ,V SIM ) Generates a link table Link-SimKey table of approximate business keys of a business key, where r[PK S ]、r[PK T ] point to the table Hub corresponding to the business object ST Different business keys in V SIM The value of represents the similarity from two similar business keys;

[0044] Link-SimKey table RecSou field, if r[PK T ]=null, insert the source system name of the benchmark table, otherwise insert the source system name of the secondary table.

[0045] As a preferred technical solution, find out whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table, which specifically includes:

[0046] The benchmark table S has a foreign key field Mode and It is determined that the foreign key of the benchmark table points to another business key in the benchmark table. r[] represents the existing value set. Another hierarchical link table Link-Hier is searched in the target table. The mode is expressed as: Link-Hier(HASHLPK,PK S ,FK S ,RecSou,LDT);

[0047] All S in the benchmark table PK and S FK The instance value is loaded into the Link-Hier table, and r[S PK ]Insert PK S field, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PK S and FK S The HASK function value after the fields are concatenated;

[0048] If q>0, then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-Hier table, and r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated.

[0049] As a preferred technical solution, if there is a foreign key pointing to other relationship tables, a corresponding link table is created for the primary and foreign keys of the benchmark table and data is loaded, specifically including:

[0050] If there is a foreign key field in the benchmark table Mode and Create a corresponding link table for the primary and foreign keys of the benchmark table S. The mode is expressed as: Link-PFK(HASHLPK,PK S ,FK S ,RecSou,LDT);

[0051] Set all S in the benchmark table S PK and S FK The instance value is loaded into the Link-PFK table, and r[S PK ]Insert PK Sfield, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PK S and FK S The HASK function value after the fields are concatenated;

[0052] If q>0, then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-PFK table and r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated.

[0053] As a preferred technical solution, the relationship table of the associated business object is determined and data is loaded, the link table is searched, and the associated data of the temporary storage area benchmark table and the secondary table are loaded into the link table. The specific steps include:

[0054] The link table mode in the target area is represented as: Link-Asso(HASHLPK,FK 1 ,...,FK v ,LDT,RecSou), where FK i The foreign key that represents the business key of the associated center point table, 1≤i≤v, HASHLPK represents the hash value after the foreign key is joined, RecSou is the record source, and LDT is the loading date;

[0055] Identify each foreign key FK in the relationship's primary and secondary tables i The values ​​all correspond to the business key values ​​of the target table Hub, find the approximate business key link table Link-SimKey of the corresponding business key, and obtain the equal PK based on the foreign key S Value record, take max(V SIM )The corresponding business key value replaces the foreign key FK i Values, until all business key values ​​are determined;

[0056] Place each business key value into FK 1 ,...,FK v , HASHLPK is FK 1 ,...,FK vThe concatenated HASH value, the source system name of the benchmark table is placed in the RecSou field, and the current time is placed in the LDT field;

[0057] Load each field of the relational benchmark table into the subsidiary table Sat-Link-Asso-S, put the source system name of the benchmark table into the RecSou field, and put the current time into the LDT field;

[0058] Load the fields of the relational secondary table T into the subsidiary table Sat-Link-Asso-T, put the secondary table source system name into the RecSou field, and put the current time into the LDT field.

[0059] In order to achieve the second purpose of the present invention, the present invention adopts the following technical solutions:

[0060] The present invention also provides an ontology-based automatic data loading system for a power grid data warehouse, comprising: a data configuration module, a matching module, an approximate business key link table construction module, an approximate matching module, a center point table and its subsidiary table construction module, and a link table and its subsidiary table construction module;

[0061] The data configuration module is used to construct an ontology knowledge base, as well as a target table and a source table for data loading, specifically including:

[0062] Build an ontology knowledge base to support pattern matching of data table semantics;

[0063] Based on the power grid DV data warehouse, the three types of tables, namely, the center point, link, and subsidiary, store the power grid business entities, relationships, and their attribute data respectively, forming the target table for data loading;

[0064] The source table of the power grid business system is set, and the source data tables of the same business object are grouped to form a source table for data loading. The source table includes a benchmark table and multiple secondary tables. The data of the benchmark table and the secondary tables are copied to the temporary storage area according to their original mode.

[0065] The matching module is used to perform pattern matching and data matching of the benchmark table and the secondary table, specifically including:

[0066] Match the primary keys and field names of the benchmark table and secondary table based on the ontology knowledge base;

[0067] Based on the benchmark table, business key value equal matching is performed;

[0068] The approximate business key link table construction module is used to construct an approximate business key matching table according to the matching results;

[0069] The approximate matching module is used to perform approximate matching of unequal business key values ​​based on the approximate business key matching table;

[0070] The central point table and its subsidiary table construction module is used to construct the central point table and its subsidiary table of the power grid DV data warehouse, specifically including:

[0071] Find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the relevant data in the benchmark table and secondary table;

[0072] Based on the approximate business key matching table, an approximate business key link table of the corresponding business key is constructed, and relevant data of the benchmark table and the secondary table are stored;

[0073] Find the table Hub in the target table ST The subsidiary table Sat-Hub-S and the subsidiary table Sat-Hub-T of the benchmark table are stored in the subsidiary table Sat-Hub-S, and the data of the secondary table is stored in the subsidiary table Sat-Hub-T;

[0074] Find out whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table; if there is no foreign key field pointing to the record of this table, find out whether there is a foreign key in the benchmark table pointing to other relationship tables. If there is a foreign key pointing to other relationship tables, create a corresponding link table for the primary and foreign keys of the benchmark table and load data, and repeat the search and judgment until all foreign key fields are found;

[0075] Repeat the data loading of a benchmark table of the business object and a secondary table until the data loading of multiple secondary tables is completed;

[0076] The link table and its subsidiary table construction module are used to construct the link table of the power grid DV data warehouse, specifically including:

[0077] Determine the relationship table of the associated business object and load the data, find the link table, and load the associated data of the temporary area benchmark table and the secondary table into the link table.

[0078] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0079] (1) The present invention uses the power grid ontology knowledge base to perform pattern matching and uses the DV linking technology method to create an approximate business key link table to solve the technical problem that the same power grid business object has multiple business key values ​​(object identity) due to the integration of multiple source business systems.

[0080] (2) The present invention utilizes the characteristics of the approximate business key link table and the power grid DV model to automatically load the central point Hub table and its subsidiary tables for the source business object table in sequence, and then automatically loads the link Link table and its subsidiary tables for the source association relationship table. At the same time, the ontology pattern matching is used to support the automatic data loading method of the power grid DV data warehouse, so as to achieve high-quality and accurate extraction of source data and load it into the power grid DV data warehouse, and support parallel hardware facilities. Its storage method that accommodates inconsistent data ensures the traceability and auditability of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0081] Figure 1 It is a schematic diagram of the overall process of the automatic data loading method of the power grid data warehouse based on ontology of the present invention;

[0082] Figure 2 It is a fragmentary schematic diagram of two power grid source business system models of the present invention;

[0083] Figure 3 It is a fragmentary schematic diagram of the power grid DV data warehouse model of the present invention;

[0084] Figure 4 A schematic diagram of the process flow of user configuration and data preparation of the present invention;

[0085] Figure 5 A schematic diagram of the flow of pattern matching and data matching of the present invention;

[0086] Figure 6 A schematic diagram of the process of establishing a Hub table and its subsidiary tables according to the present invention;

[0087] Figure 7 The present invention is a flow chart of establishing a Link table and its subsidiary tables. DETAILED DESCRIPTION

[0088] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0089] Example 1

[0090] like Figure 1 As shown, this embodiment provides an automatic data loading method for a power grid data warehouse based on ontology. The data loading process of the power grid DV data warehouse in this embodiment is a process of integrating and loading data from multiple power grid source business systems into a DV data warehouse model. The data warehouse model established according to the DV model has three types of relationship tables: a hub table, a link table, and a satellite (Sat) table.

[0091] In addition, this embodiment also constructs an ontology knowledge base, which contains the semantics of all data table names, field names, synonyms, hyponyms, and other terms, which can be used to support pattern matching of data tables; the ontology knowledge base has an automatic reasoning mechanism. If two terms A and B are input into the ontology knowledge base, it can automatically infer that the knowledge base satisfies and Then we say that terms A and B are equivalent. This embodiment will use the ontology knowledge base to support pattern matching of term semantics, that is, for two input attribute names or field names, although the surface names are different, their semantics (referring objects) are the same;

[0092] This embodiment aims to load multiple power grid source data tables in the temporary storage area into the power grid DV data warehouse with high quality. Figure 2 and Figure 3 As shown, specifically, it is equivalent to Figure 2 The data in the model is loaded into Figure 3 In the model. Figure 2 As shown, there are two source business system models S1 ( Figure 2 Upper part) and S2( Figure 2 lower part). Figure 3 As shown, the power grid DV data warehouse model is obtained.

[0093] The ontology-based automatic data loading method for power grid data warehouse of this embodiment specifically includes the following steps:

[0094] (1) Figure 4 Perform user configuration and data preparation as shown.

[0095] Step 1: input an established power grid DV data warehouse model, which includes all three types of DV tables that need to load data. These tables constitute the target tables of the data loading method of this embodiment;

[0096] This embodiment also specifies several source business system tables of the power grid. These tables are grouped according to the source data tables that store the same power grid business object (usually there are multiple source data tables containing instance values ​​for the same business object), and they constitute the source tables of the data loading method of this embodiment.

[0097] Combination Figure 3 As shown, all three types of DV tables that need to load data in this embodiment include three center point tables: customer center point table Hub_Customer, contract center point table Hub_Contract and service center point table Hub_service, two link tables: customer-contract link table Lnk_Cust-Cont and contract-service link table Lnk_Cont-Serv, and their corresponding six subsidiary tables, which constitute the target table;

[0098] Step 2: Specify a group of source tables of the power grid business system, one of which is determined as a benchmark source data table (hereinafter referred to as the benchmark table), multiple secondary source data tables (hereinafter referred to as secondary tables), and corresponding target tables. The benchmark table refers to the primary basis or reference source data table when integrating multiple data tables; the secondary table refers to the source data table that can be matched with the benchmark table (with the same primary key and foreign key).

[0099] Step 3: The following takes the matching and data loading process of a benchmark table and a secondary table as an example to illustrate the data loading process of a Hub table and its subsidiary tables of a business object ST:

[0100] First, copy the benchmark table data to the temporary storage area. Suppose the benchmark table S and its mode S(S PK ,S 1 ,S 2 ,...,S n ), where: S PK is the primary key of the benchmark table S, S 1 ,S 2 ,...,S n are the names of the n fields of the benchmark table S. Next, copy the secondary table data to the temporary storage area. Suppose there is a secondary table T and its schema T(T PK ,T 1 ,T 2 ,...T m ), where: T PK is the primary key of the secondary table T, 1 ,T 2 ,...T m are the names of m fields of the secondary table T. j ],1≤j≤n represents a field S in the benchmark table S j The existing value set of r[S 1 ,S 2 ,...,S i ], 1≤i≤n represents the field S in the benchmark table S 1 ,S 2 ,...,S i The set of existing values ​​of .

[0101] In this embodiment, a set of source tables of the power grid business system S1 and S2 is specified, such as (S1_Customer, S2_Customer), where the former is the benchmark table and the latter is the secondary table, as well as the corresponding target tables (Hub_Customer, Sat_Customer, Sat_Customer1). For the customer hub table Hub_Customer and customer subsidiary tables Sat_Customer and Sat_Customer1 of the customer business object Customer of the power grid, there is the following data replication process: first, the benchmark table data is copied to the temporary storage area, and there is the benchmark table S1_Customer (Cust_key, last name, first name, phone, email), and secondly, the secondary table S2_Customer data is copied to the temporary storage area, and there is the secondary table S2_Customer (Cust_id, name, landline, email).

[0102] (2) Figure 5 As shown, pattern matching and data matching are performed;

[0103] Step 4: Perform pattern matching on the benchmark table S and the secondary table T:

[0104] 4.1 Business key matching: Using the ontology knowledge base, input the primary key S of the benchmark table S PK and the primary key T of secondary table T PK , output the reasoning result of the ontology, that is, the primary key S PK and primary key T PK Whether it is the business key of the same business object ST; There are two ways to reason about the ontology knowledge base:

[0105] 4.1.1 If the ontology knowledge base stores “S PK ≡T PK " rule, then a positive result is obtained directly;

[0106] 4.1.2 Otherwise, perform reasoning on the ontology knowledge base and If it is satisfied, then a positive result is obtained, otherwise S PK and T PK Mismatch, output data loading fails, and the algorithm ends.

[0107] 4.2 Non-business key matching: Match the fields of the benchmark table S and the secondary table T except for the business key, and also use the ontology knowledge base to match the field names. Assume that there are h (h ≤ n, h ≤ m) fields that are successfully matched, that is, (S PK ,S 1 ,S 2 ,...S h ) and (T PK ,T1 ,T 2 ,...T h ) becomes the corresponding matching field, where S j ≡T j (1≤j≤h). There are two main ways to match the ontology knowledge base:

[0108] 4.2.1 If the ontology knowledge base stores “S j ≡T j , (1≤j≤h)" rule, then the matching result is obtained directly;

[0109] 4.2.2 Otherwise, perform reasoning on the ontology knowledge base and (1≤j≤h), if it is satisfied, then the matching result is obtained, otherwise it is not matched;

[0110] 4.2.3 In addition, S j and T j It can be a comparison of multiple sets and a single item, such as "surname" + "first name" = "name".

[0111] In this embodiment, there is a benchmark table S1_Customer (Cust_key, last name, first name, phone, email) and a secondary table S2_Customer (Cust_id, first name, landline, email). The ontology knowledge base is used to perform business key matching and non-business key matching, that is, Cust_key and Cust_id are input to obtain the inference result of the ontology library, that is, Cust_key and Cust_id are both business keys of the business object Customer; the fields other than the business keys of tables S1_Customer and S2_Customer are matched, and the ontology knowledge base is also used to perform field name matching, and three non-business key fields (Cust_key, last name + first name, phone, email) and (Cust_id, first name, landline, email) become corresponding matching fields, among which "last name + first name" matches "first name", and "landline" matches "phone".

[0112] Step 5: Business key data matching:

[0113] 5.1 Business key value matching: Based on the benchmark table S, r[S PK ] and r[T PK ] Perform equal value matching, and the result is that the two tables have p and q business key values ​​that are not equal.

[0114] Among them, r[] represents the existing value set, r - [] represents the set of values ​​that do not match the existing values. The cardinality of the two sets | r - [SPK ]|=p,|r - [T PK ]|=q;

[0115] 5.2 Establish an approximate business key matching table BusKey-Sim (PK S ,PK T ,V SIM );

[0116] 5.2.1 If p>0 and q=0, then r - [S PK ]Insert PK S , null value is placed in PK T , 1 into V SIM , skip to step 6;

[0117] 5.2.2 If p = 0 and q > 0, then r - [T PK ]Insert PK T , null value is placed in PK S , 1 into V SIM , skip to step 6;

[0118] 5.3 Business key value approximate matching (object identity recognition): - [S PK ]、r - [T PK ], for each business key value in r - [S PK ,S 1 ,S 2 ,...S h ] and r - [T PK ,T 1 ,T 2 ,...T h ] in the matching field S j ≡T j (1≤j≤h) performs data equality matching on non-business key fields, and performs matching on business key sp∈r - [S PK ] record i and the corresponding business key tp∈r - [T PK ] is sim=z(sp i ,tp k ) / h, where z(sp i ,tp k )=|{s ij |s ij =t kj ,s ij∈r - [S j ],t kj ∈r - [T j ], <sp i ,s ij >∈r - [S PK ,S j ], <tp k ,t kj >∈r - [T PK ,T j ],1≤j≤h,1≤i≤p,1≤k≤q}|. That is, the similarity sim is the number of fields with equal instance values ​​in several matching fields j divided by the total number of matching fields h for the i and k rows of records corresponding to the benchmark table S and the secondary table T. In other words, the more common fields two objects have, the higher their similarity;

[0119] 5.4 The user specifies a threshold α[0,1] and sets r - [S PK ]×r - [T PK ] are placed in PK S and PK T ,in And r[V SIM ]={sim|sim≥α}, that is, the approximate business key matching table only stores the similarities greater than α, which are from S PK and T PK Two approximate business keys.

[0120] In this embodiment, the business key value matching is performed on the table S1_Customer and S2_Customer, and r[Cust_key] and r[Cust_id] are matched equally. Assume that the two tables have 3 and 4 unequal business key values, that is, |r - [Cust_key]|=3,|r - [Cust_id]|=4, as shown in Table 1 below. The established approximate business key matching table is represented as: BusKey-Sim(Cust_key, Cust_id, V SIM ), for r - [Cust_key], r - For each customer key value in [Cust_id], S1 - [Cust_key,lastname,firstname,telephone,email] with r S2 -[Cust_id, name, landline, email] perform equal matching on two non-business key fields and obtain their similarity sim = z / 3, where z represents the number of equal matching in these three fields. S1 With r S2 In the comparison, 2 records match on 2 fields, 1 record matches on 3 fields, and 1 record has no field match. The threshold α is selected as α = 0.6, and V SIM Cust_key×Cust_id pairs ≥0.6 are stored in the approximate business key matching table BusKey-Sim, as shown in Table 2 below.

[0121] Table 1 Two example tables of mismatched records

[0122]

[0123] Table 2 Approximate business key matching table BusKey-Sim example

[0124] Cust_key Cust_id VSIM 55001 56002 0.67 56001 56011 0.67 60001 50001 1.00

[0125] (3) Figure 6 As shown, a central point table and its subsidiary tables of the power grid DV are established.

[0126] Step 6: Create the DV Hub table:

[0127] 6.1 Find the Hub table corresponding to the business object ST in the target table ST , assuming its mode is Hub ST (HASHPK, BusKey, RecSou, LDT), where HASHPK is the hash value of the business key BusKey, which is the primary key of the table, RecSou is the record source, and LDT is the loading date;

[0128] 6.2 All primary keys S in the benchmark table S PK Loading values ​​into the Hub ST Table, that is, where r[S PK ] is placed into the BusKey field, the source system name of the benchmark table is placed into the RecSou field, the current time is placed into the LDT field, and HASHPK is calculated by the BusKey field using the HASK function;

[0129] 6.3 r - [T PK ] append the q instance values ​​in the Hub ST Table, that is, where r - [T PK] The instance value is placed in the BusKey field. At the same time, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, and HASHPK is calculated by the BusKey field using the HASH function;

[0130] In this embodiment, the Hub_Customer table of the DV data warehouse is constructed as follows: find the table Hub_Customer (Cust_key, Load_Date, Rec_Source) corresponding to the business object Customer in the target table (for simplicity and convenience, the hash primary key is not considered here, and the same applies below);

[0131] Place r[Cust_key] in the benchmark table S1_Customer into the Cust_Key field of Hub_Customer, the current time into Load_Date, and RecSou into the Rec_Source field;

[0132] Add the secondary table S2_Customer to - The 4 unmatched values ​​in [Cust_id] are appended to the Hub_Customer table, i.e. - [Cust_id] instance value is placed in the Cust_key field, the current time is placed in the Load_Date field, and the RecSou field is placed in the Rec_Source field;

[0133] Step 7: Create a DV approximate business key link table Link-SimKey (HASHLPK, PK S ,PK T ,V SIM ,RecSou,LDT), where HASHLPK is the primary key PK of the S table S And the primary key PK of the T table T The concatenated HASH value. The meanings of other fields are the same as above:

[0134] 7.1 Using approximate business key matching table BusKey-Sim(PK S ,PK T ,V SIM ) Generates a link table Link-SimKey table of approximate business keys of a business key, where r[PK S ]、r[PK T ] instance values, pointing to Hub ST Different business keys (different records) in the table, V SIM The value of represents the similarity from two similar business keys;

[0135] 7.2The primary key of the Link-SimKey table HASHLPK is PKS and PK T The HASH value after concatenation with PK, and the current time is placed into the LDT field;

[0136] 7.3 For the RecSou field of the Link-SimKey table, if r[PK T = null, place the name of the source system of the benchmark table, otherwise place the name of the source system of the secondary table.

[0137] In this embodiment, an approximate business key link table for the DV data warehouse is constructed for the Cust_key business key, specifically represented as: Link-SimKey-Cust(HASHLPK, Cust_key, Cust_id, V SIM , RecSou, LDT);

[0138] Using the approximate business key matching table BusKey-Sim(Cust_key, Cust_id, V SIM ), generate the Link-SimKey-Cust table, where the instance values of r[Cust_key] and r[Cust_id] respectively point to different business keys (different records) in the Hub_Customer table;

[0139] The primary key HASHLPK of the Link-SimKey-Cust table is the HASH value after concatenation of Cust_key and Cust_id, and the current time is placed into the LDT field;

[0140] For the RecSou field of the Link-SimKey-Cust table, place the name of the source system "S2".

[0141] Step 8, find the satellite tables Sat-Hub-S and Sat-Hub-T of the Hub table in the target table. According to the DV modeling method, assume their schemas are (HASHPK, LDT, End-LDT, RecSou, Attr ST ,..., Attr 1 ,..., Attr w ) and (HASHPK, LDT, End-LDT, RecSou, Attr 1 ,..., Attr y ):

[0142] 8.1 Load the benchmark table S (by the business key of the central table) into the satellite table Sat-Hub-S, Attr 1 ,..., At tr w , and the source table record field is the name of the source system of the benchmark table;

[0143] 8.2 Load the secondary table T (by the central table business key) into the Sat-Hub-T subsidiary table Attr 1 ,...,At tr y ,The source table record field is the source system name of the secondary table.

[0144] 8.3HASHPK corresponds to Hub ST The primary key of the table is the current time, and the End-LDT is set to null. When the subsidiary table is subsequently updated, the End-LDT of the original subsidiary record can be set to the current time.

[0145] In this embodiment, the subsidiary tables Sat_Customer and Sat_Customer1 of the Hub_Customer table are found in the target table.

[0146] Load S1_Customer (by Cust_key of Hub_Customer) into Sat_Customer subsidiary table, put the current time into Load_Date, put "S1" into Rec_Source, and put the "Last Name", "First Name", "Phone" and "Email" data in S1_Customer table into the corresponding Sat_Customer fields;

[0147] Load S2_Customer (by Cust_key of Hub_Customer) into Sat_Customer1 subsidiary table, put the current time into Load_Date, put "S2" into Rec_Source, and put the "Name", "Landline" and "Email" data in the S2_Customer table into the corresponding Sat_Customer1 fields.

[0148] Step 9: If there is a foreign key field in the benchmark table S Mode and (The foreign key value and the primary key value have the same value range), that is, the foreign key in S points to another business key in S, then find another hierarchical link table Link-Hier in the target table. If it cannot be found, skip this step.

[0149] Assume the mode Link-Hier (HASHLPK, PK S ,FK S ,RecSou,LDT), and then load data into Link-Hier:

[0150] 9.1 All S in the benchmark table S PK and S FK The instance value is loaded into the Link-Hier table, where r[S PK ]Insert PKS field, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PK S and FK S The HASK function value after the fields are concatenated;

[0151] 9.2 If q>0 (the secondary table has mismatched business keys), then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-Hier table, where r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated.

[0152] In this embodiment, there is no foreign key in the benchmark table S1_Customer, so this step is skipped.

[0153] Step 10: If there is a foreign key field in the benchmark table S Mode and (Foreign key values ​​point to primary keys of other tables), then prepare to create corresponding link tables for the primary and foreign keys of benchmark table S and load data;

[0154] First, find another primary foreign key link table Link-PFK (HASHLPK, PK S ,FK S ,RecSou,LDT) table, and then load data into Link-PFK.

[0155] In this embodiment, there is no foreign key in the benchmark table S1_Customer, so this step is skipped.

[0156] 10.1 All S in the benchmark table S PK and S FK The instance value is loaded into the Link-PFK table, where r[S PK ]Insert PK S field, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PKS and FK S The HASK function value after the fields are concatenated;

[0157] 10.2 If q>0, then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-PFK table, where r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated;

[0158] 10.3 If there are other foreign key fields in the benchmark table S, repeat steps 9-10 until all foreign key fields are processed.

[0159] Step 11. So far, the data loading process of integrating a benchmark table and a secondary table of the business object ST has been completed; for a business object composed of a benchmark table and multiple secondary tables, refer to the above steps 3-10 to copy and integrate one secondary table in the temporary storage area at a time; the method for generating the business key approximate matching table is the same, and for a business key, only one approximate business key link table Link-SimKey, one Link-Hier table or multiple Link-PFK tables are needed; each secondary table needs to correspond to an independent subsidiary table.

[0160] In this embodiment, the subsidiary table Sat_CustAddr (the customer address subsidiary table about the customer hub point) of the Hub_Customer table is found in the target table:

[0161] Load Hub_Customer (by Hub_Customer's Cust_key) into the Sat_CustAddr subsidiary table, place the current time into Load_Date, place "S2" into Rec_Source, and place the "Name", "Landline", "Province", "City" and "Electricity Address" data in the S2_CustAddr table into the corresponding Sat_CustAddr fields.

[0162] At this point, the data loading of the hub table Hub_Customer and its three subsidiary tables has been completed.

[0163] (4) Figure 7As shown, a link table and its subsidiary table of the power grid DV are established.

[0164] Step 12: If the target table contains a Link table and its subsidiary tables, the source table represents the relationship between multiple business objects. The Link table can be used to store relationships of any cardinality:

[0165] 12.1 Before establishing this Link table, you must first establish the Hub table and its subsidiary tables of the linked business object according to steps 3-8 above, that is, you should first determine the business key of the associated Hub and load the corresponding data;

[0166] 12.2 After the Hub of each relevant business key is successfully established, find the link table Link-Asso that needs to be established this time in the target area. Assume that its mode is Link-Asso(HASHLPK,FK 1 ,...,FK v ,LDT,RecSou), where F i (1≤i≤v) is the foreign key pointing to the business key of the associated center point table, HASHLPK is the hash value of the union of these foreign keys, and LDT and RecSou have the same meanings as above;

[0167] 12.3 If there is a relational benchmark table S and a secondary table T in the temporary storage area, load data into the Link-Asso table:

[0168] 12.3.1 First determine each foreign key FK in the relationship benchmark table and the secondary table i (Business key) values ​​all correspond to the business key value of the target table Hub (check whether there is a Link-SimKey table with the corresponding business value);

[0169] 12.3.2 If you find a link table Link-SimKey that corresponds to the business key, use FK i Get equal PK value S Value of the record, when equal to PK S Value record number>0, take max(V SIM ) replaces FK with the corresponding business key value (non-null) i Values, until all business key values ​​are determined;

[0170] 12.3.3 Place each business key value into FK 1 ,...,FK v , HASHLPK is FK 1 ,...,FK v The concatenated HASH value, the source system name of the benchmark table is placed in the RecSou field, and the current time is placed in the LDT field;

[0171] 12.3.4 Similar to step 8, load each field of the benchmark table S (excluding the key field) into the Sat-Link-Asso-S subsidiary table, place the source system name of the benchmark table into the RecSou field, and place the current time into the LDT field; load each field of the secondary table T (excluding the key field) into the Sat-Link-Asso-T subsidiary table, place the source system name of the secondary table into the RecSou field, and place the current time into the LDT field.

[0172] In this embodiment, because the target table contains the contract-service link table Lnk_Cont-Serv and the contract-service attachment table Sat_Cont-Serv, it is necessary to establish the relationship between the business object electricity contract and the service:

[0173] Before establishing the contract-service link table Lnk_Cont-Serv, you must first establish the electricity contract center point table Hub_Contract and service center point table Hub_Service of the linked business object according to the above steps 3-8;

[0174] Find the contract-service link table Lnk_Cont-Serv and its mode that needs to be established in the target area;

[0175] In the temporary storage area of ​​this embodiment, there is only one power usage status table S1_Usage to store the power usage and amount of each power usage contract on a certain service, which is used to load data into the contract-service link table Lnk_Cont-Serv:

[0176] Make sure that the foreign key Cont_key and Serv_key values ​​of the S1_Usage table have corresponding business key values ​​Cont_key and Serv_key of the Hub_Contract table and the Hub_Service table respectively.

[0177] Find out whether there is a contract approximate business key link table Link-SimKey-Cont and a service approximate business key link table Link-SimKey-Serv. Assuming that the Link-SimKey-Cont table exists in this embodiment, when reading the S1_Usage record, check whether there is an approximate value record of the Cont_key instance value in the Link-SimKey-Cont table. If there is, take max(V SIM ) replaces the Cont_key value with the corresponding Cont_key (non-null), and continues in this way until all business key values ​​are determined;

[0178] Place the combined value of the foreign keys Cont_key and Serv_key in S1_Usage into the Cont_key and Serv_key fields in the contract-service link table Lnk_Cont-Serv, and use the concatenated HASH value of these two fields as the primary key Cont_Serv_id value of S1_Usage. Place the current time into the Load_Date field, and omit the Rec_Source field here.

[0179] According to the record sequence in the contract-service link table Lnk_Cont-Serv, the corresponding S1_Usage table loads the power consumption and amount fields into the contract-service attachment table Sat_Cont-Serv, "S1" is placed in the Rec_Source field, and the current time is placed in the Load_Date field.

[0180] Example 2

[0181] This embodiment provides an ontology-based automatic data loading system for a power grid data warehouse, comprising: a data configuration module, a matching module, an approximate business key link table construction module, an approximate matching module, a center point table and its subsidiary table construction module, and a link table and its subsidiary table construction module;

[0182] In this embodiment, the data configuration module is used to construct an ontology knowledge base, as well as to construct a target table and a source table for data loading, specifically including: constructing an ontology knowledge base to support pattern matching of data table semantics; storing power grid business entities, relationships and their attribute data based on the center point, link and subsidiary tables of the power grid DV data warehouse, respectively, to form a target table for data loading; setting a source table for the power grid business system, grouping the source data tables according to the same business object, to form a source table for data loading, the source table including a benchmark table and multiple secondary tables, and copying the data of the benchmark table and the secondary table to the temporary storage area according to their original mode.

[0183] In this embodiment, the matching module is used to perform pattern matching and data matching of the benchmark table and the secondary table, specifically including: matching the primary keys and field names of the benchmark table and the secondary table based on the ontology knowledge base; and matching business key values ​​based on the benchmark table.

[0184] In this embodiment, the approximate business key link table construction module is used to construct an approximate business key matching table according to the matching results.

[0185] In this embodiment, the approximate matching module is used to perform approximate matching of unequal business key values ​​based on the approximate business key matching table.

[0186] In this embodiment, the central point table and its subsidiary table construction module is used to construct the central point table and its subsidiary table of the power grid DV data warehouse, specifically including: searching the central point table Hub corresponding to the business object ST in the target table; ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the relevant data of the benchmark table and the secondary table; build the approximate business key link table of the corresponding business key based on the approximate business key matching table, and store the relevant data of the benchmark table and the secondary table; search the Hub table in the target table ST The subsidiary table Sat-Hub-S and the subsidiary table Sat-Hub-T of the benchmark table are stored in the subsidiary table Sat-Hub-S, and the data of the secondary table is stored in the subsidiary table Sat-Hub-T; check whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table; if there is no foreign key field pointing to the record of this table, find and determine whether there is a foreign key in the benchmark table pointing to other relationship tables. If there is a foreign key pointing to other relationship tables, establish a corresponding link table for the primary and foreign keys of the benchmark table and load data, and repeat the search and judgment until all foreign key fields are found; repeat the data loading of a benchmark table and a secondary table integrated with the business object until the data loading of multiple secondary tables is completed.

[0187] In this embodiment, the link table and its subsidiary table construction module are used to construct the link table of the power grid DV data warehouse, specifically including: determining the relationship table of the associated business object and loading data, searching the link table, and loading the associated data of the temporary area benchmark table and the secondary table into the link table.

[0188] The above embodiments are preferred implementation modes of the present invention, but the implementation modes of the present invention are not limited to the above embodiments. Any other changes, modifications, substitutions, combinations, and simplifications that do not deviate from the spirit and principles of the present invention should be equivalent replacement methods and are included in the protection scope of the present invention.

Claims

1. An automatic data loading method for power grid data warehouse based on ontology, It is characterized in that The steps include: Build an ontology knowledge base to support pattern matching of data table semantics; Based on the power grid DV data warehouse, the three types of tables, namely, the center point, link, and subsidiary, store the power grid business entities, relationships, and their attribute data respectively, forming the target table for data loading; Setting a source table of the power grid business system, grouping source data tables of the same business object to form a source table for data loading, wherein the source table includes a benchmark table and multiple secondary tables; Copy the data of the benchmark table and the secondary table to the temporary storage area according to their original mode; Match the primary keys and field names of the benchmark table and secondary table based on the ontology knowledge base; Based on the benchmark table, perform equal matching of business key values, build an approximate business key matching table based on the matching results, and perform approximate matching of unequal business key values ​​based on the approximate business key matching table; Find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the data in the benchmark table and the secondary table; Based on the approximate business key matching table, an approximate business key link table of the corresponding business key is constructed, and the data of the benchmark table and the secondary table are stored; Find the table Hub in the target table ST The subsidiary table Sat-Hub-S and the subsidiary table Sat-Hub-T of the benchmark table are stored in the subsidiary table Sat-Hub-S, and the data of the secondary table is stored in the subsidiary table Sat-Hub-T; Find out whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table; if there is no foreign key field pointing to the record of this table, find out whether there is a foreign key in the benchmark table pointing to other relationship tables. If there is a foreign key pointing to other relationship tables, create a corresponding link table for the primary and foreign keys of the benchmark table and load data, and repeat the search and judgment until all foreign key fields are found; Repeat the data loading process of integrating a benchmark table and a secondary table of the business object until the data loading process of multiple secondary tables is completed; Determine the relationship table of the associated business object and load the data, find the link table, and load the associated data of the temporary area benchmark table and the secondary table into the link table.

2. The automatic data loading method for power grid data warehouse based on ontology according to claim 1, It is characterized in that The benchmark table and its model are expressed as: S(S PK ,S 1 ,S 2 ,...,S n ), where S PK Represents the primary key of the benchmark table S, S 1 ,S 2 ,...,S n Represents the n field names of the benchmark table S; The secondary table and its schema are represented as: T(T PK ,T 1 ,T 2 ,...,T m ), where T PK Represents the primary key of the secondary table T, T 1 ,T 2 ,...,T m Represents the m field names of the secondary table T; Based on the ontology knowledge base, the primary key S of the benchmark table PK and the primary key of the secondary table T PK Perform ontology reasoning to determine the primary key S PK and primary key T PK Whether they are the business keys of the same business object; Based on the ontology knowledge base, the field names of the benchmark table and the secondary table are matched and reasoned, and the corresponding matching fields are output.

3. The automatic data loading method for power grid data warehouse based on ontology according to claim 2, It is characterized in that Based on the benchmark table, perform business key value matching and build an approximate business key matching table based on the matching results, including: Based on the benchmark table S, r[S PK ] and r[T PK ] Perform equal value matching, and the result is that the two tables have p and q business key values ​​that are not equal. Among them, r[] represents the existing value set, r - [] represents the set of values ​​that do not match the existing values. The cardinality of the two sets | r - [S PK ]|=p,|r - [T PK ]|=q; The schema of the approximate business key matching table is expressed as: BusKey-Sim(PK S ,PK T ,V SIM ); If it is determined that p>0 and q=0, then r - [S PK ]Insert PK S , null value is placed in PK T , 1 into V SIM ; If p=0 and q>0, then r - [T PK ]Insert PK T , null value is placed in PK S , 1 into V SIM .

4. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that Approximate matching of unequal business key values ​​is performed based on the approximate business key matching table, specifically including: R - [S PK ]、r - [T PK ], for each business key value in r - [S PK ,S 1 ,S 2 ,...,S h ] and r - [T PK ,T 1 ,T 2 ,...,T h ] in the matching field S j ≡T j Perform data equality matching between two non-business key fields, and for business key sp∈r - [S PK ] record i and the corresponding business key tp∈r - [T PK ] is sim=z(sp i ,tp k ) / h, where z(sp i ,tp k )=|{s ij |s ij =t kj ,s ij ∈r - [S j ],t kj ∈r - [T j ], <sp i ,s ij >∈r - [S PK ,S j ], <tp k ,t kj >∈r - [T PK ,T j ],1≤j≤h,1≤i≤p,1≤k≤q}|; r - [S PK ]×r - [T PK ] are placed in PK S and PK T ,in, And r[V SIM ]={sim|sim≥α}, that is, the approximate business key matching table stores the similarities greater than the matching similarity threshold α, which are from the primary key S PK and primary key T PK Two approximate business keys.

5. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that Find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the data in the benchmark table and the secondary table. The specific steps include: Find the hub table Hub corresponding to the business object ST in the target table ST , whose mode is represented by Hub ST (HASHPK, BusKey, RecSou, LDT), where HASHPK is the hash value of the business key BusKey, which is the primary key of the table, RecSou is the record source, and LDT is the loading date; All primary keys S in the benchmark table S PK Loading values ​​into the Hub ST Table, r[S PK ] is placed into the BusKey field, the source system name of the benchmark table is placed into the RecSou field, the current time is placed into the LDT field, and HASHPK is calculated by the BusKey field using the HASK function; r - [T PK ] and append the q instance values ​​in the Hub ST Table, r - [T PK ]The instance value is placed in the BusKey field. At the same time, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, and HASHPK is calculated by the BusKey field using the HASH function.

6. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that Based on the approximate business key matching table, an approximate business key link table of corresponding business keys is constructed. The specific steps include: Construct an approximate business key link table corresponding to the business key. The schema of the approximate business key link table is represented as: Link-SimKey(HASHLPK,PK S ,PK T ,V SIM ,RecSou,LDT), where HASHLPK is the primary key PK of the benchmark table S and secondary table primary key PK T The concatenated HASH value, RecSou is the record source, and LDT is the loading date; Using approximate business key matching table BusKey-Sim(PK S ,PK T ,V SIM ) Generates a link table Link-SimKey table of approximate business keys of a business key, where r[PK S ]、r[PK T ] point to the table Hub corresponding to the business object ST Different business keys in V SIM The value of represents the similarity from two similar business keys; Link-SimKey table RecSou field, if r[PK T ]=null, insert the source system name of the benchmark table, otherwise insert the source system name of the secondary table.

7. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that Find out whether there is a foreign key field in the benchmark table pointing to the record in this table. If there is a foreign key field pointing to the record in this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table, including: The benchmark table S has a foreign key field Mode and It is determined that the foreign key of the benchmark table points to another business key in the benchmark table. r[] represents the existing value set. Another hierarchical link table Link-Hier is searched in the target table. The mode is expressed as: Link-Hier(HASHLPK,PK S ,FK S ,RecSou,LDT); All S in the benchmark table PK and S FK The instance value is loaded into the Link-Hier table, and r[S PK ]Insert PK S field, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PK S and FK S The HASK function value after the fields are concatenated; If q>0, then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-Hier table, and r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated.

8. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that If there is a foreign key pointing to other relational tables, a corresponding link table is created for the primary and foreign keys of the benchmark table and data is loaded, including: If there is a foreign key field in the benchmark table Mode and Create a corresponding link table for the primary and foreign keys of the benchmark table S. The mode is expressed as: Link-PFK(HASHLPK,PK S ,FK S ,RecSou,LDT); Set all S in the benchmark table S PK and S FK The instance value is loaded into the Link-PFK table, and r[S PK ]Insert PK S field, corresponding to r[S FK ]Insert FK S Field, the source system name of the benchmark table is placed in the RecSou field, the current time is placed in the LDT field, and HASHLPK is PK S and FK S The HASK function value after the fields are concatenated; If q>0, then r - [T PK ] and its corresponding T FK The instance value is loaded into the Link-PFK table and r - [T PK ]Insert PK S Field, corresponding to r - [T FK ]Insert FK S Field, the secondary table source system name is placed in the RecSou field, the current time is placed in the LDT field, HASHLPK is PK S and FK S The HASK function value after the fields are concatenated.

9. The automatic data loading method for power grid data warehouse based on ontology according to claim 3, It is characterized in that Determine the relationship table of the associated business object and load the data, find the link table, and load the associated data of the temporary area benchmark table and the secondary table into the link table. The specific steps include: The link table mode in the target area is represented as: Link-Asso(HASHLPK,FK 1 ,...,FK v ,LDT,RecSou), where FK i The foreign key that represents the business key of the associated center point table, 1≤i≤v, HASHLPK represents the hash value after the foreign key is joined, RecSou is the record source, and LDT is the loading date; Identify each foreign key FK in the primary and secondary tables i The values ​​all correspond to the business key values ​​of the target table Hub, find the approximate business key link table Link-SimKey of the corresponding business key, and obtain the equal PK based on the foreign key S Value record, take max(V SIM )The corresponding business key value replaces the foreign key FK i Values, until all business key values ​​are determined; Place each business key value into FK 1 ,...,FK v , HASHLPK is FK 1 ,...,FK v The concatenated HASH value, the source system name of the benchmark table is placed in the RecSou field, and the current time is placed in the LDT field; Load each field of the benchmark table into the subsidiary table Sat-Link-Asso-S, put the source system name of the benchmark table into the RecSou field, and put the current time into the LDT field; Load the fields of the secondary table T into the subsidiary table Sat-Link-Asso-T, put the source system name of the secondary table into the RecSou field, and put the current time into the LDT field.

10. An automatic data loading system for power grid data warehouse based on ontology, It is characterized in that include: Data configuration module, matching module, approximate business key link table construction module, approximate matching module, center point table and its subsidiary table construction module, link table and its subsidiary table construction module; The data configuration module is used to construct an ontology knowledge base, as well as a target table and a source table for data loading, specifically including: Build an ontology knowledge base to support pattern matching of data table semantics; Based on the power grid DV data warehouse, the three tables of center point, link and subsidiary respectively store the power grid business entities, relations and their attribute data, forming the target table for data loading; The source table of the power grid business system is set, and the source data tables of the same business object are grouped to form a source table for data loading. The source table includes a benchmark table and multiple secondary tables. The data of the benchmark table and the secondary tables are copied to the temporary storage area according to their original mode. The matching module is used to perform pattern matching and data matching of the benchmark table and the secondary table, specifically including: Match the primary keys and field names of the benchmark table and secondary table based on the ontology knowledge base; Based on the benchmark table, business key value equal matching is performed; The approximate business key link table construction module is used to construct an approximate business key matching table according to the matching results; The approximate matching module is used to perform approximate matching of unequal business key values ​​based on the approximate business key matching table; The central point table and its subsidiary table construction module is used to construct the central point table and its subsidiary table of the power grid DV data warehouse, specifically including: Find the hub table Hub corresponding to the business object ST in the target table ST , store all business key values ​​of the benchmark table and business key values ​​of the secondary table that failed to match the value to the Hub table ST , and store the data in the benchmark table and the secondary table; Based on the approximate business key matching table, an approximate business key link table of the corresponding business key is constructed, and the data of the benchmark table and the secondary table are stored; Find the table Hub in the target table ST The subsidiary table Sat-Hub-S and the subsidiary table Sat-Hub-T of the benchmark table are stored in the subsidiary table Sat-Hub-S, and the data of the secondary table is stored in the subsidiary table Sat-Hub-T; Find out whether there is a foreign key field in the benchmark table pointing to the record of this table. If there is a foreign key field pointing to the record of this table, find another hierarchical link table Link-Hier in the target table and store the data of the benchmark table and the secondary table; if there is no foreign key field pointing to the record of this table, find out whether there is a foreign key in the benchmark table pointing to other relationship tables. If there is a foreign key pointing to other relationship tables, create a corresponding link table for the primary and foreign keys of the benchmark table and load data, and repeat the search and judgment until all foreign key fields are found; Repeat the data loading of a benchmark table of the business object and a secondary table until the data loading of multiple secondary tables is completed; The link table and its subsidiary table construction module are used to construct the link table of the power grid DV data warehouse, specifically including: Determine the relationship table of the associated business object and load the data, find the link table, and load the associated data of the temporary area benchmark table and the secondary table into the link table.

Images