An Ontology-Based Automatic Data Loading Method for Power Grid Data Mart

Abstract

The present invention discloses an ontology-based automatic data loading method for a power grid data mart. The method includes the following steps: constructing a power grid data warehouse based on the DV modeling method; establishing a DV inconsistent data detection and repair data set; detecting the central point and its affiliated tables, and verifying the data dependency relationship and its establishment mode; detecting and calculating the data semantic confidence level and determining the repair value; constructing a power grid data mart; establishing a temporary dimension table, repairing inconsistent data and loading dimension table data; establishing a temporary fact table, repairing inconsistent data and loading fact table data. By using the ontology knowledge base, the functional dependency relationship and the data semantic confidence level calculation method, the present invention detects and repairs inconsistent data without changing the power grid DV data warehouse, realizes the effective management of DV inconsistent data, and finally achieves the goal of automatically loading the power grid data mart with high quality.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and particularly relates to an automatic data loading method for a power grid data mart based on ontology. Background Art

[0002] Implementing a data warehouse according to a multidimensional data model and its association method can no longer meet the requirements for organizing massive data in the big data era. Therefore, power grid enterprises have begun to introduce a new Data Vault (hereinafter referred to as DV) modeling method, that is, using a fusion method of normal form modeling and analytical modeling to construct a new data warehouse to meet the special requirements for organizing power grid big data.

[0003] The DV modeling method emphasizes establishing an auditable raw data layer (data warehouse layer), paying attention to the historicity, traceability and atomicity of data organization, and is suitable for providing long-term historical storage for multiple power grid source business systems. At the same time, to support the data analysis work of power grid end users, it is also necessary to establish a data mart, and convert the data in the raw data layer into data saved in a multidimensional (hereinafter referred to as MD) data model, so as to meet the needs of end users to carry out OLAP analysis using the data in the data mart.

[0004] The power grid DV modeling method brings good scalability to the data warehouse, but also relaxes the semantic constraints on data. The raw data layer integrates data from multiple power grid source business systems. Even if these data are well-consistent in each source system, they may bring conflicting and inconsistent data after integration. Traditional data warehouses require modifying inconsistent data during the integration of multi-source systems, but these modifications often lead to new and bigger errors. Therefore, the power grid DV data warehouse requires retaining the data of each source business system in the raw data layer, and delaying the task of modifying inconsistent data to the data loading process of constructing the data mart. Therefore, in the data loading process of the data mart, how to efficiently discover and repair these inconsistent data and finally complete the data loading has become a technical problem to be solved urgently in the construction of the power grid DV data warehouse.

[0005] Currently, the publicly disclosed patents related to constructing a DV data warehouse only target methods and system devices for automatically building and loading data in a Data Vault data warehouse, without considering how to detect inconsistent data in the DV data warehouse and how to specifically repair these inconsistent data, and have the following defects:

[0006] 1) After the data loading of the power grid DV data warehouse is completed, it is often found that there are inconsistent and poor-quality data in the original data layer. To ensure data traceability and auditability, the DV modeling method allows the integration and loading of inconsistent data from multiple source business systems into the original data layer. These inconsistent data are processed before data analysis, that is, before the data is loaded into the data mart. However, existing literature lacks a complete solution for detecting and repairing inconsistent data in DV.

[0007] 2) Existing statistical data credibility detection methods or repair methods that delete data records often detect invalid functional dependencies and conflicting data, and simple deletion will cause problems such as data loss, which cannot meet the data quality requirements for building a high-quality power grid data warehouse.

[0008] In summary, the technical problem that needs to be solved urgently is: how to discover and repair inconsistent data in the original data of the power grid DV data warehouse while maintaining the requirement of not changing the original data layer, and automatically load it into the data mart with high quality for end-users to use. Summary of the Invention

[0009] To overcome the defects and deficiencies of the existing technology, the present invention provides an ontology-based automatic data loading method for a power grid data mart. The present invention uses the DV modeling method to build a power grid data warehouse and load data, and three types of data tables therein store the original data of power grid marketing data, equipment data, production data, etc.; then a corresponding device is built, and based on the ontology and functional dependencies, the data semantic method is used to calculate its confidence level, detect the original data to find inconsistent data; then, according to the characteristics of power grid operations, the inconsistent data is specifically repaired based on the ontology, data semantic confidence level and functional dependencies; finally, the data in the power grid data warehouse is automatically loaded into the data mart in the modeling order.

[0010] To achieve the above object, the present invention adopts the following technical solutions:

[0011] An ontology-based automatic data loading method for a power grid data mart of the present invention includes the following steps:

[0012] Build a power grid DV data warehouse, and use three types of tables: center point, link, and subsidiary to store the power grid business entities, relationships, and their attribute data respectively as source tables;

[0013] Build a power grid ontology knowledge base, and set a benchmark subsidiary table and secondary subsidiary tables for multiple similar subsidiary tables;

[0014] Build a functional dependency table, a data semantic confidence level calculation table, and a functional dependency repair table;

[0015] For several functional dependency expressions of the selected central point in the functional dependency table, search for the central point and its benchmark attachment table, detect the establishment mode of the functional dependency for the benchmark attachment table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table;

[0016] For the functional dependency expressions of the selected central point in the functional dependency table, search for the corresponding secondary attachment table, detect the establishment mode of the functional dependency for the secondary attachment table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table;

[0017] Detect the establishment mode of the functional dependency for the records in the data semantic confidence calculation table, calculate the data confidence in the data semantic confidence calculation table, and determine the repair value, which is stored in the functional dependency repair table;

[0018] Construct a power grid data mart as the target table based on a multi-dimensional model, where the multi-dimensional model includes a fact table and dimension tables;

[0019] Load data into the dimension tables of the multi-dimensional model and load data into the fact table of the multi-dimensional model.

[0020] As a preferred technical solution, construct a functional dependency table, specifically expressed as:

[0021] FD-List(FD_id, T_id, FD-Left, FD-Right, Conf-FD)

[0022] Among them, FD_id represents the identifier of the functional dependency expression, T_id represents the identifier of the central point table or link table to which the functional dependency belongs, FD-Left represents the left part name of the functional dependency expression, FD-Right represents the right part name of the functional dependency expression, and Conf-FD represents the data confidence of the functional dependency;

[0023] Construct a data semantic confidence calculation table, specifically expressed as:

[0024] DS-Conf(DS_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Conf-L, Conf-R, Repair)

[0025] Among them, DS_id represents the sequential primary key of this table, FD_id represents the identifier of the functional dependency expression, Sat_id represents the attachment table identifier, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left part value of the functional dependency expression, FD-R represents the right part value of the functional dependency expression, Conf-L represents the data confidence of the left part of the functional dependency expression, Conf-R represents the data confidence of the right part of the functional dependency expression, and Repair represents the repair value;

[0026] Construct a function dependency repair table, specifically represented as:

[0027] FD-Repair(RP_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Repair), where RP_id represents the sequential primary key of the table, FD_id represents the identifier of the function dependency expression, Sat_id represents the affiliated table identifier, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left-hand value of the function dependency expression, FD-R represents the right-hand value of the function dependency expression, and Repair represents the repair value of the function dependency expression.

[0028] As a preferred technical solution, for several function dependency expressions of the selected central point in the function dependency table, find the corresponding central point and its affiliated table, detect the establishment mode of the function dependency for the benchmark affiliated table, and output the data fields that do not meet the function dependency to the data semantic confidence calculation table. The specific steps include:

[0029] Obtain the same type of affiliated tables;

[0030] Taking the benchmark affiliated table as the main one, match the field names in the secondary affiliated tables based on the power grid ontology knowledge base;

[0031] According to the field name matching results, for the field values corresponding to the left and right parts of the function dependency expression in the affiliated table, detect whether all data records meet the function dependency expression;

[0032] Based on the power grid ontology knowledge base, determine that the latest modified data in the benchmark affiliated table meets the function dependency expression and do not output it to the data semantic confidence calculation table, and output the data fields in the benchmark affiliated table that do not meet the function dependency to the data semantic confidence calculation table.

[0033] As a preferred technical solution, detect the establishment mode of the function dependency for the benchmark affiliated table. The processing steps for the data with the establishment mode value include:

[0034] If the number of records with the same right-hand value of the function dependency expression for a left-hand value of the function dependency expression is greater than the number of records with different right-hand values of the function dependency expression, then use the same right-hand value of the function dependency expression as the establishment mode value;

[0035] Output the records with different right-hand values of the function dependency expression to the function dependency repair table and update the corresponding identifier of the current function dependency repair table;

[0036] The processing steps for the data without the establishment mode value include:

[0037] Output all data records that do not conform to the functional dependencies to the data semantic confidence calculation table, and update the corresponding identifiers in the data semantic confidence calculation table.

[0038] As a preferred technical solution, for the functional dependency expression of the selected center point in the functional dependency table, find the corresponding secondary affiliated table, detect the establishment mode of the functional dependency for the secondary affiliated table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table. The specific steps are as follows:

[0039] Compare the left and right values of the functional dependency expression of a certain business key value and timestamp record in the secondary affiliated table with the corresponding field values of the corresponding records in the benchmark affiliated table that conform to the functional dependency. If the field values of the secondary affiliated table and the benchmark affiliated table are the same in both the left value field and the right value field of the functional dependency expression, it is determined that the record in the secondary affiliated table conforms to the functional dependency.

[0040] Detect the establishment mode of the functional dependency for the secondary affiliated table. Based on the power grid ontology knowledge base, determine that the latest modified data in the secondary affiliated table satisfies the functional dependency expression and do not output it to the data semantic confidence calculation table. Output the data records in the secondary affiliated table that do not conform to the current functional dependency to the data semantic confidence calculation table.

[0041] As a preferred technical solution, compare the left and right values of the functional dependency expression of a certain business key value and timestamp record in the secondary affiliated table with the corresponding field values of the corresponding records in the benchmark affiliated table that conform to the functional dependency.

[0042] If the field values of the secondary affiliated table and the benchmark affiliated table are the same in the left value field of the functional dependency expression, but different in the right value field of the functional dependency expression, then use the right field value of the functional dependency expression in the benchmark affiliated table as the establishment mode value and output it to the functional dependency repair table, and update the corresponding identifier in the functional dependency repair table.

[0043] If the field values of the secondary affiliated table and the benchmark affiliated table are different in the left value field of the functional dependency expression, then output this record to the data semantic confidence calculation table.

[0044] As a preferred technical solution, calculate the data confidence in the data semantic confidence calculation table and determine the repair value. The specific steps are as follows:

[0045] Use the ontology knowledge base to determine the evidence attributes of the left and right parts of the functional dependency respectively, calculate the secondary table identifier in the data semantic confidence calculation table, and the field names of the left and right parts of the functional dependency expression. Search for the decision attributes of the left and right parts of the benchmark secondary table or secondary secondary table in the power grid ontology knowledge base as the parent nodes of the Bayesian network.

[0046] Calculate the data confidence in the data semantic confidence calculation table based on the Bayesian method;

[0047] Determine the repair value according to the data confidence.

[0048] As a preferred technical solution, calculate the data confidence in the data semantic confidence calculation table based on the Bayesian method. The specific steps include:

[0049] Calculate the conditional probability: According to the node and parent node relationship of the Bayesian network, calculate the occurrence times of the left part value l and the right part value r of each functional dependency expression, and calculate the conditional probability according to the following formula:

[0050]

[0051] Calculate the data confidence of the left part value l and the right part value r of the functional dependency expression, and perform normalization processing: Specifically expressed as:

[0052]

[0053]

[0054]

[0055] Determine the repair value according to the data confidence, specifically including:

[0056] When the left part values of the functional dependency expression are the same, but the right part values are different, select the r value corresponding to the maximum Conf_R’(r) as the repair value and place it into the functional dependency repair table. If the Conf_R’(r) values are all equal, then select the r value of the record with the maximum Conf_L’(l) as the repair value and place it into the functional dependency repair table;

[0057] When the left part values of the functional dependency expression are the same, each group has an equal number of right part values F of the functional dependency expression, the right part values within the group are equal but different between groups, then select the r value with the maximum cumulative value of Conf_R’(r) within the group as the repair value and place it into the functional dependency repair table; If the cumulative maximum values of Conf_R’(r) within the group are all equal, then select the r value of the record with the maximum cumulative value of Conf_L’(l) within the group as the repair value and place it into the functional dependency repair table.

[0058] As a preferred technical solution, loading data into the dimension tables of the multi-dimensional model, the specific steps include:

[0059] Using the power grid ontology knowledge base and the naming rules of power grid data tables, search for the customer dimension table in the target table, and determine the matching between the primary key of the customer dimension table and the business key name of the customer center point;

[0060] Using the power grid ontology knowledge base and the naming rules of power grid data tables, search for the customer center point and its affiliated tables in the source table;

[0061] Store the business key value of the center point into the temporary dimension table;

[0062] Store the data of the benchmark affiliated table related to the customer dimension table into the temporary dimension table;

[0063] Store the data of the secondary affiliated table related to the customer dimension table into the temporary dimension table;

[0064] For each business key value in the temporary dimension table, keep a record with the latest timestamp and delete the remaining records;

[0065] Based on the function dependency repair table, obtain all the repair records with the same affiliated table identifier in the function dependency repair table as the benchmark affiliated table identifier, obtain all the repair records with the same affiliated table identifier in the function dependency repair table as the secondary affiliated table identifier, and repair the data in the temporary dimension table;

[0066] Write the data in the temporary dimension table into the corresponding customer dimension table to complete the data loading of a single dimension table;

[0067] Repeat the data loading steps of a single dimension table to create temporary dimension tables for all dimension tables of the multi-dimensional model.

[0068] As a preferred technical solution, loading data into the fact table of the multi-dimensional model, the specific steps include:

[0069] Using the power grid ontology knowledge base and the naming rules of power grid data tables, search for the fact table to be established in the target table, determine the primary key of the fact table, and the matching between the foreign key in the primary key of the fact table and the business key name of the center point table;

[0070] Using the power grid ontology knowledge base and the naming rules of power grid data tables, determine the corresponding link table of the fact table and several similar affiliated tables under the link table;

[0071] Store the business key values in the link table into the temporary fact table;

[0072] Store the data of the benchmark affiliated table of the link table into the temporary fact table;

[0073] Store the data of the secondary affiliated table of the link table into the temporary fact table;

[0074] Write the data in the temporary fact table into the corresponding fact table, and write the timestamp into the date dimension value of the fact table to complete the data loading of a single fact table;

[0075] Repeat the data loading steps for a single fact table to create temporary fact tables for all fact tables in the multi-dimensional model.

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

[0077] (1) Based on the power grid DV data warehouse, the present invention adopts a strategy of accommodating inconsistent data to achieve traceability and auditability; the present invention uses the ontology knowledge base and the power grid naming rules to match the semantics of the data table names and field names, distinguish the benchmark affiliated tables and the secondary affiliated tables, then detect the inconsistent data within and between the DV affiliated tables by means of functional dependency relationships, and finally, according to the characteristics of the power grid business, calculate its confidence level by using data semantics and Bayesian network methods, and determine the repair value, thus realizing the management scheme for DV inconsistent data.

[0078] (2) By using the functional dependency relationship and the data semantics confidence level calculation method in the power grid DV data, the present invention repairs inconsistent data without changing the power grid DV data warehouse, and automatically completes the loading of the data mart according to the characteristics of the power grid multi-dimensional model, solving the technical problem of automatically loading the data mart with high quality. BRIEF DESCRIPTION OF THE DRAWINGS

[0079] Figure 1 It is a schematic flow chart of the method for automatically loading power grid data mart based on ontology of the present invention;

[0080] Figure 2 It is a partial example diagram of the power grid DV data warehouse of the present invention;

[0081] Figure 3 It is an example diagram of obtaining the functional dependencies and their confidence levels included in the power grid DV data warehouse of the present invention;

[0082] Figure 4 It is a schematic diagram of the data record of the benchmark affiliated table Sat_CustAddr1 of the present invention;

[0083] Figure 5 It is a schematic diagram of the data record of the secondary affiliated table Sat_CustAddr2 of the present invention;

[0084] Figure 6 It is a schematic diagram of the calculation of the data semantics confidence level after verifying the establishment of the functional dependency of the benchmark affiliated table of the present invention;

[0085] Figure 7Schematic diagram of function dependency repair after verifying the establishment of minor secondary table function dependencies in the present invention;

[0086] Figure 8 Schematic diagram of the calculation results of conditional probability and confidence in the present invention;

[0087] Figure 9 Schematic diagram of the result of function dependency repair table after processing the data semantic confidence calculation table in the present invention;

[0088] Figure 10 Schematic diagram of the multi-dimensional model of the power grid in the present invention;

[0089] Figure 11 Schematic diagram of the initial data of the customer temporary dimension table in the present invention;

[0090] Figure 12 Schematic diagram of the repaired customer temporary dimension table in the present invention;

[0091] Figure 13 Schematic diagram of the customer dimension table in the present invention;

[0092] Figure 14 Schematic diagram of the temporary fact for writing the business key in the present invention;

[0093] Figure 15 Schematic diagram of the temporary fact for writing the data of the benchmark secondary table in the present invention;

[0094] Figure 16 Schematic diagram of the data example for writing into the fact table in the present invention. Detailed implementation manners

[0095] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to 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 used to limit the present invention.

[0096] Embodiment

[0097] As Figure 1 shown, this embodiment provides an ontology-based automatic data loading method for a power grid data mart, including the following steps:

[0098] S1: Construct a power grid DV data warehouse, which includes three types of tables in the power grid DV model and the data stored in the tables; assume that the functional dependencies and their confidence levels included in the power grid DV data warehouse have been obtained; construct a power grid ontology knowledge base, which includes the vocabulary definitions related to power grid operations and transmission and distribution equipment, the interrelated semantic information, the relationships of the three specific types of DV tables, and the specific instances of vocabulary concepts; set a benchmark subsidiary table and secondary subsidiary tables for multiple similar subsidiary tables. In addition, a data semantic confidence threshold α is also specified, which is used as the criterion for determining the validity of data record semantics and functional dependencies.

[0099] (1) As Figure 2 shown, the power grid DV model uses three types of tables: center point, link, and subsidiary to store the power grid business entities, relationships, and the attribute data of the center point / link respectively; in the figure, there are the power grid customer center point table Hub_Cust (with the customer subsidiary table Sat_Customer and the customer address subsidiary tables Sat_CustAddr1, Sat_CustAddr2), the power consumption contract center point table Hub_Contract (with the contract subsidiary table Sat_Contract), and the service center point table Hub_Service (with the service subsidiary table Sat_Service), as well as the customer-contract link table Lnk_Cust-Cont and the contract-service link table Lnk_Cont-Serv (with the contract-service subsidiary table Sat_Usage);

[0100] (2) The center point table is connected to the link table and the subsidiary table based on the business key, and the link table stores many-to-many relationships;

[0101] (3) A center point or link may have multiple subsidiary tables, and each subsidiary table can correspond to a source business system and form historical records of different periods of related attributes according to the timestamp (Load_Date). The record source (Rec_Source) attribute is stored in these three types of tables, which can record the data sets of all source power grid business systems.

[0102] The power grid DV schema is suitable for efficiently storing the massive data of the power grid, but it is not conducive to users using the data. Therefore, in the power grid data warehouse environment, a multidimensional model based on fact tables and dimension tables is established to support end-users in analyzing data.

[0103] S2: Establish a functional dependency table, a data semantic confidence calculation table, and a functional dependency repair table. According to the characteristics of the power grid DV data warehouse, follow the following steps to construct these four tables:

[0104] (1) Establish a function dependency table FD-List (FD_id, T_id, FD-Left, FD-Right, Conf-FD), where FD_id represents the identifier of the function dependency expression, T_id represents the identifier of the central point / link table to which the function dependency belongs, FD-Left represents the left part name of the function dependency expression, FD-Right represents the right part name of the function dependency expression, and Conf-FD represents the data confidence of the function dependency.

[0105] (2) Input the determined function dependencies and their data confidence (≥α), and store them in the FD_id, T_id, FD-Left, FD-Right, and Conf-FD fields of the function dependency table FD-List according to the function dependency expression identifier, the table identifier to which it belongs, the left and right parts of the expression, and the data confidence respectively.

[0106] (3) Establish a data semantic confidence calculation table DS-Conf (DS_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Conf-L, Conf-R, Repair), where DS_id represents the sequential primary key of this table, FD_id represents the identifier of the function dependency expression, Sat_id represents the identifier of the affiliated table, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left part value of the function dependency expression, FD-R represents the right part value of the function dependency expression, Conf-L represents the data confidence of the left part of the function dependency expression, Conf-R represents the data confidence of the right part of the function dependency expression, and Repair represents the repair value.

[0107] (4) Establish a function dependency repair table FD-Repair (RP_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Repair), where RP_id represents the sequential primary key of this table, FD_id represents the identifier of the function dependency expression, Sat_id represents the identifier of the affiliated table, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left part value of the function dependency expression, FD-R represents the right part value of the function dependency expression, and Repair represents the repair value of the function dependency expression.

[0108] As Figure 3 shown, the function dependencies and their confidence levels included in the power grid DV data warehouse have been obtained. In this embodiment, the data semantic confidence threshold is specified as α = 0.65;

[0109] S3: For several functional dependency expressions of a certain central point in the functional dependency table FD-List, find the corresponding central point and its affiliated table, detect whether these functional dependencies hold for the benchmark affiliated table, and output the data fields that do not conform to the functional dependencies to the data semantic confidence calculation table DS-Conf.

[0110] S31: Obtain the same type of affiliated tables: For these functional dependencies, obtain the T_id (central point identifier) in the functional dependency table FD-List, the left part FD-Left of the functional dependency expression, and the right part FD-Right of the functional dependency expression (non-numeric type, the same below). Use the user settings and the power grid ontology knowledge base to clarify the same type of affiliated tables, as well as the benchmark affiliated table and the secondary affiliated table.

[0111] a) Obtain the set benchmark affiliated table: According to the user settings (using the naming rules of the power grid data table), obtain the benchmark affiliated table. In this embodiment, Sat_CustAddr1 is set as the benchmark affiliated table;

[0112] b) Determine whether it is the same type of affiliated table: Use the power grid ontology knowledge base to confirm that the affiliated table is an attribute description of the same type of features of the central table or the linked table T_id to which the functional dependency belongs for the same business object. For example, an affiliated table with the address feature of the user business object; By querying the power grid ontology knowledge base, it can be known that both Sat_CustAddr1 and Sat_CustAddr2 are affiliated tables of the same type with the address feature of the user business object;

[0113] c) Obtain all affiliated tables of the same type: Except for the benchmark affiliated table, other affiliated tables of the same type are secondary affiliated tables. In this embodiment, Sat_CustAddr1 is the benchmark affiliated table and Sat_CustAddr2 is the secondary affiliated table.

[0114] S32: Field name matching in the table: Use the power grid ontology knowledge base, with the benchmark affiliated table as the main, match the field names in multiple affiliated tables. Suppose there is a field F1 in affiliated table 1 and a field F2 in affiliated table 2. There are two ways of reasoning in the ontology knowledge base:

[0115] a) If the rule "F1≡F2" is saved in the ontology knowledge base, a positive result can be directly obtained;

[0116] b) Otherwise, perform reasoning on the ontology knowledge base and If it is satisfied, a positive result is obtained; otherwise, F1 and F2 do not match.

[0117] In this embodiment, by using the power grid ontology knowledge base, with Sat_CustAddr1 as the main, the field names in the secondary affiliated table Sat_CustAddr2 are matched. The matching results are as follows: "Name" in Sat_CustAddr1 matches "Name" in Sat_CustAddr2, "Phone" in Sat_CustAddr1 matches "Fixed Phone" in Sat_CustAddr2, "Province and City" in Sat_CustAddr1 matches "Province" in Sat_CustAddr2, "City" in Sat_CustAddr1 matches "City" in Sat_CustAddr2, and "Address" in Sat_CustAddr1 matches "Power Usage Address" in Sat_CustAddr2.

[0118] S33: Detect the establishment mode of functional dependencies for the benchmark affiliated table: According to the field name matching results, for the field values corresponding to the left part FD-Left and the right part FD-Right of the functional dependency expression in the affiliated table, check whether all data records satisfy the functional dependency expression FD-Left → FD-Right, that is, for power grid business objects, a value of the left part FD-Left of a functional dependency expression must correspond to the same FD-Right value;

[0119] S34: If there are data records in the benchmark affiliated table that do not conform to this functional dependency, output them to the data semantic confidence calculation table DS-Conf:

[0120] a) Processing of new data (the latest data is not an error): By using the power grid ontology knowledge base, query for the value of the right part FD-Right of the functional dependency expression corresponding to a certain value of the left part FD-Left of the functional dependency expression. The right part FD-Right value is the most recently updated value. That is, for a new record, although the left part value of the function is the same as before, it is allowed that the right part value of the function is different from before. Therefore, this record conforms to the functional dependency and is not output to the data semantic confidence calculation table DS-Conf;

[0121] b) Processing of data with established mode values:

[0122] i) If the number of records with the same value of the right part FD-Right of a functional dependency expression for a certain value of the left part FD-Left of the functional dependency expression is greater than the number of records with different values of the right part FD-Right of the functional dependency expression, then the most common value of the right part FD-Right of the functional dependency expression will be used as the established mode value and will not be output to the data semantic confidence calculation table DS-Conf;

[0123] ii) Directly output the records (the number of records > 0) with different right - hand values FD - Right of the functional - dependency expression to the functional - dependency repair table, that is, generate a new RP_id, place the current functional - dependency identifier into FD_id, place the benchmark - affiliated - table identifier into Sat_id, place the business key into Bus_key, place the timestamp into DTS, place the left - hand value of the functional - dependency into FD - L, place the right - hand value of the functional - dependency into FD - R, and place the establishment - mode value into Repair.

[0124] c) Processing for data without establishment - mode data: Generate a new sequence primary key DS_id of this table for all data records that do not conform to the functional - dependency in the following form, place the current functional - dependency identifier into FD_id, place the benchmark - affiliated - table identifier into Sat_id, place the business - key value into Bus_key, place the timestamp into DTS, place the left - hand value of the functional - dependency into FD - L, place the right - hand value of the functional - dependency into FD - R, and output to the data - semantic confidence - degree calculation table DS - Conf.

[0125] As Figure 4 shown, there are data records of the benchmark - affiliated table Sat_CustAddr1, as Figure 5 shown, there are data records of the secondary - affiliated table Sat_CustAddr2;

[0126] In this embodiment, for the functional - dependency "City → Province - City", when detecting all data records of the benchmark - affiliated table Sat_CustAddr1, it is found that there is a problem with the establishment - mode of this functional - dependency. That is, for the two records with business keys "CU006" and "CU007", although the "City" fields on the left - hand side of the functional - dependency both have the value "Fuzhou City", the "Province - City" fields on the right - hand side of the functional - dependency have the values "Fujian Province" and "Guangdong Province" respectively. Therefore, it is determined that there is no establishment - mode. As Figure 6 shown, records ("DS001", "FD018", "Sat_CustAddr1", "CU006", "2022 / 9 / 21", "Fuzhou City", "Fujian Province", "", "", "") and ("DS002", "FD018", "Sat_CustAddr1", "CU007", "2022 / 9 / 21", "Fuzhou City", "Guangdong Province", "", "", "") are stored in the data - semantic confidence - degree calculation table DS - Conf.

[0127] S4: For these functional - dependency expressions of this center point in the functional - dependency table FD - List, in the secondary - affiliated table, obtain the field values corresponding to the left - hand side FD - Left and the right - hand side FD - Right of several functional - dependency expressions, detect whether all data records conform to the functional - dependency, and output the data fields that do not conform to the functional - dependency to the data - semantic confidence - degree calculation table DS - Conf.

[0128] S41: Compare with the records in the benchmark subsidiary table that conform to the functional dependency: For the records in the benchmark subsidiary table other than the data records saved in DS-Conf, according to the matching results of the foregoing field names, compare the functional dependency expression left part FD-Left value and the functional dependency expression right part FD-Right value of a certain business key value and timestamp record in the secondary subsidiary table with the corresponding field values of the corresponding records in the benchmark subsidiary table that conform to the functional dependency, and process them separately according to the comparison results:

[0129] a) If the field values of the secondary subsidiary table and the benchmark subsidiary table are the same in the FD-Left and FD-Right fields, then these secondary subsidiary table records conform to the functional dependency;

[0130] b) If the field values of the secondary subsidiary table and the benchmark subsidiary table are the same in the FD-Left field, but the field values of the secondary subsidiary table and the benchmark subsidiary table are different in the FD-Right field, then use the FD-Right field value of the benchmark subsidiary table as the established mode value and directly output it to the functional dependency repair table, that is, generate a new RP_id, place the current functional dependency identifier into FD_id, place the secondary subsidiary table identifier into Sat_id, place the business key value into Bus_key, place the timestamp into DTS, place the left value of the secondary subsidiary table functional dependency into FD-L, the right value into FD-R, and place the established mode value into Repair;

[0131] c) If the field values of the secondary subsidiary table and the benchmark subsidiary table are different in the FD-Left field, then output this record to the data semantic confidence calculation table DS-Conf, that is, generate a new DS_id, place the current functional dependency identifier into FD_id, place the secondary subsidiary table identifier into Sat_id, place the business key value into Bus_key, place the timestamp into DTS, place the left value of the functional dependency into FD-L, place the right value of the functional dependency into FD-R, and output it to the data semantic confidence calculation table DS-Conf.

[0132] S42: Detect the established mode of the functional dependency for the secondary subsidiary table: For the data records of the secondary subsidiary table corresponding to the benchmark subsidiary table data records saved in DS-Conf, detect whether it satisfies the expression functional dependency expression FD_Left → FD_Right, that is, for the power grid business object, one FD-Left value must correspond to one same FD-Right value;

[0133] S43: If there are data records in these secondary subsidiary tables that do not conform to this functional dependency, then output them to the data semantic confidence calculation table DS-Conf:

[0134] a) Processing of updated data: Utilize the power grid ontology knowledge base to query for the FD_Right value corresponding to a certain FD_Left value that is the most recently updated value. That is, for the latest record, although the left - hand value of the function is the same as before, the right - hand value of the function is allowed to be different from before. Therefore, this record conforms to the functional dependency and is not output to the DS - Conf table;

[0135] b) Processing of data with established pattern values:

[0136] i) If the number of records with the same FD_Right value for a functional dependency expression's left - hand value FD_Left is greater than the number of records with different FD_Right values for the functional dependency expression, then this majority of the same FD_Right values will be used as the established pattern value and will not be output to the data semantic confidence calculation table DS - Conf;

[0137] ii) Directly output the minority of records with different FD_Right values (number of records > 0) to the functional dependency repair table, that is, generate a new RP_id, place the current functional dependency identifier into FD_id, the secondary affiliated table identifier into Sat_id, the business key into Bus_key, the timestamp into DTS, the left - hand value of the functional dependency into FD_L, the right - hand value of the functional dependency into FD_R, and the established pattern value into Repair.

[0138] c) Processing of data without established pattern data: Generate a new DS_id for all data records that do not conform to the functional dependency in the following form, place the current functional dependency identifier into FD_id, the secondary affiliated table identifier into Sat_id, the business key value into Bus_key, the timestamp into DTS, the left - hand value of the functional dependency into FD_L, the right - hand value of the functional dependency into FD_R, and output to the data semantic confidence calculation table DS - Conf.

[0139] In this embodiment, detect the established pattern of functional dependencies in the secondary affiliated table Sat_CustAddr2 and process data records that do not conform to the functional dependencies. For the functional dependency "city → province", there are two records "CU003" and "CU008". When comparing with the corresponding records in the benchmark affiliated table (matched by synonyms as above, corresponding to the "city" and "province - city" fields in the benchmark affiliated table), it is found that in the FD - Left (city) field of the functional dependency expression, the field values of the secondary affiliated table and the benchmark affiliated table are the same, but in the FD - Right field (province), the field values of the secondary affiliated table and the benchmark affiliated table are inconsistent, such as Figure 7As shown, the output function dependency repair table records ("RP001", "FD021", "Sat_CustAddr2", "CU203", "2022 / 9 / 21", "Guangzhou City", "Fujian Province", "Guangdong Province"), ("RP002", "FD021", "Sat_CustAddr2", "CU208", "2022 / 9 / 21", "Xiamen City", "Guangdong Province", "Fujian Province").

[0140] In this embodiment, the secondary attachment table records corresponding to the two records of the "CU006" and "CU007" benchmark attachment tables that have been saved in the data semantic confidence calculation table DS-Conf are detected. The detection results show that these secondary attachment table records satisfy the establishment mode of the functional dependency "city → province", so these records are not stored in the data semantic confidence calculation table DS-Conf.

[0141] S5: Detect the establishment mode of functional dependencies for the records in the data semantic confidence calculation table DS-Conf, calculate the data confidence in the data semantic confidence calculation table DS-Conf, and determine the repair value, which is stored in the function dependency repair table.

[0142] S51: For the secondary attachment table records in the data semantic confidence calculation table DS-Conf, detect the establishment mode of the functional dependency: for the FD-L and FD-R fields corresponding to this functional dependency in the table, detect whether all the records in this table satisfy the expression FD-Left → FD-Right of the FD_id of this record, that is, for the power grid business object, one FD-L value must correspond to one same FD-R value;

[0143] S52: For the data records that conform to the functional dependency, delete their records:

[0144] a) Processing of data that conforms to the functional dependency: Query the benchmark attachment table according to the business key value and timestamp. If no record with the same key is found, and if one FD-L value corresponds to one same FD-R value, then delete it from the data semantic confidence calculation table DS-Conf;

[0145] b) Processing of data with establishment mode values:

[0146] i) If the number of records with one FD-L value having the same FD-R value is greater than the number of records with different FD-R values, then the majority of the same FD-R values will be used as the establishment mode value, and delete these records with mode values from the data semantic confidence calculation table DS-Conf;

[0147] ii) Directly store the records with a small number of different FD-R values (number of records > 0) into the functional dependency repair table, that is, generate a new RP_id, place the current functional dependency identifier into FD_id, place the secondary attachment table identifier into Sat_id, place the business key value into Bus_key, place the timestamp into DTS, place the left value of the functional dependency into FD-L, place the right value of the functional dependency into FD-R, and place the establishment mode value into Repair;

[0148] iii) Delete from the data semantic confidence calculation table DS-Conf the records with these small numbers of different FD-R values that have been stored in the functional dependency repair table.

[0149] S53: For all records in the data semantic confidence calculation table DS-Conf, calculate the data confidence and determine the repair value: For the records in the data semantic confidence calculation table DS-Conf, calculate the data confidence in the data semantic confidence calculation table DS-Conf according to the data semantics and determine the repair value.

[0150] a) Use the ontology knowledge base to respectively determine the evidence attributes of the left and right parts of the functional dependency: According to the attachment table identifier Sat_id in the data semantic confidence calculation table DS-Conf, and the field names of the left part FD-Left and the right part FD-Right of the functional dependency expression (in FD-List) involved in FD_id, respectively search in the power grid ontology knowledge base for the decision attributes of the left and right parts of the benchmark attachment table or the secondary attachment table, that is, the attributes on which the left and right parts depend, as evidence attributes (parent nodes), and then, according to the Bayesian method, perform the following calculations:

[0151] i) Calculate the conditional probability: According to the node and parent node relationship of the Bayesian network, calculate the number of occurrences of each FD-L value l and FD-R value r, and calculate the conditional probability according to the following formula:

[0152]

[0153] ii) Calculate the data confidence of the FD-L and FD-R instance values l and r in the data semantic confidence calculation table DS-Conf according to the following formula and perform normalization processing.

[0154]

[0155] Normalization formula:

[0156]

[0157] b) Determine the repair value according to the data confidence: There are two cases for several data records that do not conform to the functional dependency, and they are processed separately as follows:

[0158] i) If the left - hand values FD - L of the function - dependency expressions are the same, but the right - hand values FD - R of several function - dependency expressions are different, then select the r - value with the maximum Conf_R’(r) as the repair value and place it into the Repair fields of these several records; if the values of Conf_R’(r) are all equal, then select the r - value of the record with the maximum Conf_L’(l) as the repair value and place it into the Repair fields of these several records.

[0159] ii) The FD - L values are the same, but there are more than two groups, that is, each group has an equal number of right - hand values FD - R of function - dependency expressions. The FD - R values within each group are equal but different between groups. Then select the r - value with the cumulative maximum value of Conf_R’(r) within the group as the repair value and place it into the Repair fields of these several records; if the cumulative maximum values of Conf_R’(r) within the group are all equal, then select the r - value of the record with the cumulative maximum value of Conf_L’(l) within the group as the repair value and place it into the Repair fields of these several records.

[0160] S54: Generate repair records for the function - dependency repair table: For the records in the data - semantic confidence - calculation table DS - Conf, merge the records with the same FD_id, Sat_id, Bus_key, DTS, FD - L, and FD - R, and only keep the first record; store all the records in the data - semantic confidence - calculation table DS - Conf into the function - dependency repair table FD - Repair. Among them, a new sequential primary key RP_id is generated for this table. Place the FD_id, Sat_id, Bus_key, DTS, FD - L, FD - R, and Repair in the data - semantic confidence - calculation table DS - Conf into the corresponding field names of the FD - Repair table.

[0161] S55: If there are still function - dependencies involving other central points in the function - dependency table FD - List that have not been processed, then obtain the function - dependencies of other central points and go to step S3.

[0162] In this embodiment, there are only two records in the data - semantic confidence - calculation table DS - Conf, but their FD_L values are the same and their FD_R values are different, which does not conform to the establishment mode of the function - dependency FD_id and cannot be deleted.

[0163] In this embodiment, according to the power grid ontology knowledge base, it is determined that the attribute on which the function dependency FD_Left involved in the data semantic confidence calculation table DS-Conf depends on "city" is "address", the attribute on which FD_Left depends on "municipality" is "power consumption address", the attribute on which FD_Right depends on "province and city" is "city", and the attribute on which FD_Right depends on "province" is "municipality". The dependent attributes are used as the attributes on which the left or right part depends, and the conditional probability and confidence are calculated according to the Bayesian method, as Figure 8 shown, and the specific calculation results are obtained;

[0164] For the two records "DS001" and "DS002" in the data semantic confidence calculation table DS-Conf, belonging to the same FD_L value but several different FD_R values, the "Fujian Province" with the cumulative maximum value within the Conf_R’(r) group is selected as the repair value and placed into the Repair field of these two records.

[0165] After merging the records of the data semantic confidence calculation table DS-Conf, new records are generated and stored in the function dependency repair table FD-Repair, as Figure 9 shown, and the processing results are obtained;

[0166] S6: A multi-dimensional model is adopted to construct a power grid data mart as the target table, which includes two types of tables (fact table and dimension table) in the power grid multi-dimensional model, and the power grid DV data warehouse constructed in step S1 of this embodiment is used as the source table.

[0167] As Figure 10 shown, a power grid multi-dimensional model is obtained. In the figure, there are dimension tables of power grid business objects: customer dimension table MD_Customer, power consumption contract dimension table MD_Contract, and service dimension table MD_Service, as well as a date dimension table, and a power consumption situation fact table MD_Usage.

[0168] S7: Load data into the dimension tables of the multi-dimensional model. Assume that data is loaded from the customer objects (involving the central point and its affiliated tables) in the source table into the customer dimension table in the target table (there is only one dimension table for power grid customer objects).

[0169] S71: Utilize the power grid ontology knowledge base and the power grid data table naming rules to find the customer dimension table in the target table, and clarify the matching between the primary key name of the customer dimension table and the business key name of the customer central point. For example, Cust_key is used as the primary key.

[0170] S72: Locate the customer center point and its affiliated tables in the source table: By using the power grid ontology knowledge base and the naming rules of power grid data tables (both with the Customer identifier or synonyms), it can be determined that the center point table corresponding to the customer dimension table is the customer center point. Similarly, it can be determined that there are several types of affiliated tables under this center point, and the affiliated tables of the same type are distinguished into benchmark affiliated tables and secondary affiliated tables (the benchmark affiliated tables carry the BS identifier according to the naming rules).

[0171] S73: Store the center point business key values in the temporary dimension table:

[0172] a) Create a temporary dimension table according to the dimension table schema of the target table (an additional timestamp field needs to be added);

[0173] b) With the center point business keys determined in step S72 being the same as the business keys of the temporary dimension table, store all the center point business keys in the business keys of the temporary dimension table.

[0174] S74: Store the data of the benchmark affiliated tables related to the customer dimension table in the temporary dimension table:

[0175] a) Use the power grid ontology knowledge base and the naming rules of power grid data tables to determine the corresponding relationships between the fields of the benchmark affiliated tables and the fields of the temporary dimension table;

[0176] b) According to the existing business key values in the temporary dimension table, store all the other field data (including the timestamp) with the same business key values in the benchmark affiliated table into the temporary dimension table; due to different timestamps, there may be multiple records with the same business key value.

[0177] S75: Store the data of the secondary affiliated tables related to the customer dimension table in the temporary dimension table:

[0178] a) Use the power grid ontology knowledge base and the naming rules of power grid data tables to determine the corresponding relationships between the fields of the secondary affiliated tables and the fields of the temporary dimension table;

[0179] b) For all the records in the secondary affiliated table, compare them with the existing records in the temporary dimension table according to the business key values and timestamps:

[0180] i) If there are matching records, use the records in the secondary affiliated table to supplement the fields of the temporary dimension table that have no specific values, and do not store the fields that already have specific values;

[0181] ii) If there are no matching records, store the specific values of the records in the secondary affiliated table into the fields of the temporary dimension table (including the timestamp).

[0182] S76: Delete the redundant records in the temporary dimension table: For each business key (such as Cust_key) value in the temporary dimension table, only keep the record with the latest timestamp, and delete the remaining records;

[0183] S77: Repair the table using functional dependencies and repair the data in the temporary dimension table: Field name matching can be performed using the ontology knowledge base.

[0184] a) Obtain all repair records in the functional dependency repair table FD-Repair where the benchmark attachment table identifier is the same as Sat_id. Retrieve the set of data records A in the temporary dimension table by Bus_key and DTS values, and query the field names of the corresponding T_id in the functional dependency table FD-List based on FD_id to obtain the field names of the benchmark attachment table FD-Left and FD-Right, thereby determining the field names corresponding to the FD-L and FD-R values. If the field names of FD-Left and FD-Right in set A match the FD-L and FD-R values, then repair the FD-R value to the Repair field value of the FD-Repair table.

[0185] b) Obtain all repair records in the functional dependency repair table FD-Repair where the secondary attachment table identifier is the same as Sat_id. Retrieve the set of data records B in the temporary dimension table by Bus_key and DTS values, and query the field names of the corresponding T_id in the functional dependency table FD-List based on FD_id to obtain the field names of the secondary attachment table FD-Left and FD-Right, thereby determining the field names corresponding to the FD-L and FD-R values. If the field names of FD-Left and FD-Right in set B match the FD-L and FD-R values, then repair the FD-R value to the Repair field value of the FD-Repair table.

[0186] S78: Store the data in the temporary dimension table into the corresponding customer dimension table (without storing the timestamp).

[0187] S79: Repeat this step to create temporary dimension tables for other dimension tables of the multi-dimensional model.

[0188] In this embodiment, MD_Customer is used as an example for illustration. Assume that data is loaded from the customer business objects in the source table, namely the customer central point Hub_Customer and its attachment tables Sat_Customer, Sat_CustAddr1, and Sat_CustAddr2, into the customer dimension table in the target table.

[0189] 1) Use the power grid ontology knowledge base and the power grid data table naming rules to find the customer dimension table MD_Customer in the target table, and determine that the primary key of the customer dimension table matches the customer central point business key, that is, use the business key Cust_key of the customer central point Hub_Customer as the primary key of MD_Customer.

[0190] 2) By using the power grid ontology knowledge base and the naming rules of power grid data tables (both with the Customer identifier or synonyms), the center point corresponding to the customer dimension table can be determined as the customer center point Hub_Customer. Similarly, it can be determined that there are subordinate tables Sat_Customer, Sat_CustAddr1, and Sat_CustAddr2 under this center point, and the similar subordinate tables Sat_CustAddr1 and Sat_CustAddr2 are classified into the benchmark subordinate table Sat_CustAddr1 and the secondary subordinate table Sat_CustAddr2.

[0191] 3) Store the business key value Cust_key of the customer center point Hub_Customer into the temporary dimension table: First, create a temporary dimension table (an additional timestamp field is required), and then store all the values of the center point business key Cust_key into the business key field of the temporary dimension table.

[0192] 4) Store the data of the benchmark subordinate table Sat_CustAddr1 related to the customer dimension table into the temporary dimension table:

[0193] a) By using the power grid ontology knowledge base and the naming rules of power grid data tables, determine the corresponding relationship between the fields of the benchmark subordinate table and the fields of the temporary dimension table as name - name, phone - phone, province and city - province and city, city - city, address - electricity consumption address;

[0194] b) According to the existing business key values in the temporary dimension table, store all the other field data (including the timestamp) with the same business key value in the benchmark subordinate table into the temporary dimension table. In this embodiment, each business key has only one timestamp, and 10 records are stored in the customer temporary dimension table.

[0195] 5) The secondary subordinate table related to the customer dimension table is Sat_CustAddr2, and store its data into the temporary dimension table:

[0196] a) By using the power grid ontology knowledge base and the naming rules of power grid data tables, determine the corresponding relationship between the fields of the secondary subordinate table and the fields of the temporary dimension table as name - name, landline - phone, province - province and city, city - city, electricity consumption address - electricity consumption address;

[0197] b) Since the records in the secondary subordinate table Sat_CustAddr2 all match the records in the temporary dimension table and there is no need to supplement specific values for the temporary dimension table, as Figure 11 shown, there are still 10 records in the customer temporary dimension table;

[0198] 6) Since each Cust_key in the customer temporary dimension table has only one record and there are no redundant records, the temporary table remains unchanged.

[0199] 7) Use the function dependency repair table FD-Repair to repair the data in the customer temporary dimension table:

[0200] a) From the function dependency repair table FD-Repair, obtain all the repair records RP003 and RP004 where the benchmark subsidiary table identifier Sat_CustAdddr1 is the same as Sat_id. Retrieve the data record set A in the temporary dimension table according to the Bus_key and DTS values, including Figure 11 the 6th and 7th records in. Among them, the 6th record does not need to be repaired, and the "Province / City" field of the 7th record is repaired to "Fujian Province".

[0201] b) From the function dependency repair table FD-Repair, obtain all the repair records RP001 and RP002 where the secondary subsidiary table identifier Sat_CustAdddr2 is the same as Sat_id. Retrieve the data record set B in the temporary dimension table according to the Bus_key and DTS values, including Figure 11 the 3rd and 8th records in. Then use the ontology knowledge base to determine that "Province" and "City" correspond to the "Province / City" and "City" fields in the temporary table, but no FD_L and FD_R value matches are found, so no repair is needed.

[0202] As Figure 12 shown, obtain the repaired customer temporary dimension table.

[0203] 8) As Figure 13 shown, store the data in the customer temporary dimension table into the corresponding customer dimension table MD_Customer.

[0204] 9) Repeat this step to load data into other dimension tables of the multi-dimensional model.

[0205] S8: Load data into the fact table of the multi-dimensional model. Assume that data is loaded from the business object relationships in the source table (such as contract-service links and their subsidiary tables) into the fact table in the target table (such as the electricity consumption fact table).

[0206] S81: Use the power grid ontology knowledge base and the power grid data table naming rules to find the electricity consumption fact table in the target table, clarify the primary key of the electricity consumption fact table, and the matching of the foreign keys and the central point table business key names that make up the primary key of the fact table, such as Cust_key and Cont_key, etc.

[0207] S82: Locate the contract - service link and its affiliated tables, as well as the relevant central point table in the source table: By using the power grid ontology knowledge base and the naming rules of power grid data tables (with Lnk_Cont - Serv, Customer, Contract, and Service identifiers or synonyms), it can be determined that the link table corresponding to the electricity consumption fact table is the contract - service link table. Similarly, several homogeneous affiliated tables under this link table can be determined and classified into benchmark affiliated tables and secondary affiliated tables (for example, the naming rule of the benchmark affiliated table has a BS identifier).

[0208] S83: Store the business key values in the link table into the temporary fact table:

[0209] a) Create a temporary table: Create a temporary fact table according to the fact table schema of the target table;

[0210] b) Store the business key into the temporary table:

[0211] i) Check the business key: If the number of foreign keys contained in the primary key of the target table is greater than the number of foreign keys in the source link table + 1, it is necessary to query the function dependency table FD - List to find the functional dependency relationship between the foreign keys in the target table and the corresponding source table link. If the implicit function dependency and the corresponding source table link are found, continue; otherwise, report an error and end the algorithm.

[0212] ii) Determine the business key: If the number of foreign keys contained in the primary key of the target table is equal to the number of foreign keys in the source link table, or there is a functional dependency and the corresponding source table link between the foreign keys in the primary key of the target table, use the ontology knowledge base to detect whether these foreign keys match each other. If they match, continue; otherwise, report an error and end the algorithm.

[0213] iii) Store the business key values into the temporary table: Use the foreign keys in the source table link and the link containing the foreign key functional dependency (if any) to obtain the corresponding instance values of the foreign keys in the target table, and store them into the temporary fact table in sequence.

[0214] S84: Store the data of the benchmark affiliated table of the contract - service link table into the temporary fact table:

[0215] a) Use the power grid ontology knowledge base and the naming rules of power grid data tables to determine the corresponding relationship between each field of the benchmark affiliated table and each field of the temporary fact table, and also determine the primary key of the date dimension table;

[0216] b) According to the existing business key values in the temporary fact table, store all the other field data (including timestamps) with the same business key values in the benchmark affiliated table into the temporary fact table; due to different timestamps, there may be multiple records with different timestamps for the same business key value combination.

[0217] S85: Store the data of the secondary affiliated table of the contract - service link table into the temporary fact table:

[0218] a) Determine the correspondence between each field of the secondary auxiliary table and each field of the temporary fact table by using the power grid ontology knowledge base and the naming rules of the power grid data table;

[0219] b) Compare all records in the secondary auxiliary table with the existing records in the temporary fact table according to the business key value combination and time stamp:

[0220] i) If there are matching records, use the records in the secondary auxiliary table to supplement the fields of the temporary dimension table that do not yet have specific values, and do not store the fields that already have specific values;

[0221] ii) If there are no matching records, store the specific values of the records in the secondary auxiliary table into the fields of the temporary dimension table (including the time stamp).

[0222] S86: Store the data in the temporary fact table into the corresponding fact table, and store the time stamp into the date dimension value of the fact table.

[0223] S87: Repeat this step to load data into other fact tables of the multi-dimensional model.

[0224] 1) In this embodiment, by using the power grid ontology knowledge base and the naming rules of the power grid data table, find the power consumption situation fact table MD_Usage in the target table, and clarify that its primary key includes: Cust_key, Cont_key, Serv_key, Date_id, where Cust_key matches the business key of the central point table Hub_Customer, Cont_key matches the business key of the central point table Hub_Contract, and Serv_key matches the business key of the central point table Hub_Service.

[0225] 2) In this embodiment, by using the power grid ontology knowledge base and the naming rules of the power grid data table, find the contract-service link Lnk_Cont-Serv and its auxiliary table Sat_Cont-Serv in the source table, as well as the related central points: Hub_Customer, Hub_Contract and Hub_Service. Determine that the power consumption situation fact table MD_Usage corresponds to the link table Lnk_Cont-Serv, and its benchmark auxiliary table is Sat_Cont-Serv.

[0226] 3) As Figure 14 shown, store the business key values in the link table into the temporary fact table:

[0227] a) Create a temporary fact table according to the fact table schema of the target table;

[0228] b) Store the business key into the temporary table:

[0229] i) Check that the number of foreign keys contained in the primary key of the target table is 4, while the number of foreign keys in the source link table is 2. Query the FD_List to find the functional dependency FD001: Cont_key → Cust_key and the link identifier Lnk_Cust-Cont between the foreign keys in the target table.

[0230] ii) Determine that the foreign keys contained in the primary key of the target table are: Cust_key, Cont_key, Serv_key, and the date dimension identifier Date_id.

[0231] iii) Based on the key value of Cont_key, obtain the corresponding instance values of the foreign key joins of the source tables Lnk_Cont-Serv and Lnk_Cust-Cont, and store them in the temporary fact table in sequence.

[0232] 4) As Figure 15 shown, store the data of the benchmark subsidiary table of the contract-service link table Lnk_Cont-Serv in the temporary fact table:

[0233] a) Use the power grid ontology knowledge base and the naming rules of power grid data tables to determine that the Load_Date timestamp field of Sat_Cont-Serv corresponds to the primary key Date_id of the date dimension table. The corresponding relationships between Sat_Cont-Serv and the fields of the temporary fact table are: among them, the "power consumption" of Sat_Cont-Serv corresponds to the "power consumption" in the temporary fact table, and the "amount" of Sat_Cont-Serv corresponds to the "amount" in the temporary fact table.

[0234] b) According to the existing business key values in the temporary fact table, store all the data of other fields (including timestamps) with the same business key values in the benchmark subsidiary table in the temporary fact table.

[0235] 5) In this embodiment, the contract-service link table Lnk_Cont-Serv has no secondary subsidiary table, so the temporary fact table remains unchanged.

[0236] 6) As Figure 16 shown, store the data in the temporary fact table into the corresponding fact table.

[0237] 7) Repeat this step to load data into other fact tables of the multi-dimensional model.

[0238] S9: So far, all the data in the target table of the multi-dimensional model has been loaded.

[0239] The present invention utilizes an ontology knowledge base and power grid naming rules, detects inconsistent data within and between entity data tables by means of functional dependency relationships, then obtains repair values for some inconsistent data according to the characteristics of power grid operations, also calculates confidence levels using data semantic methods to determine repair values, and finally, without changing the DV data warehouse, modifies the inconsistent data to automatically complete the loading of the data mart.

[0240] The present invention matches the semantics of data table names and field names using an ontology knowledge base and power grid naming rules, differentiates between benchmark attached tables and secondary attached tables, then detects inconsistencies within and between DV attached tables by means of functional dependency, and finally, according to the characteristics of power grid operations, calculates their confidence levels using data semantic and Bayesian network methods and determines repair values, thus implementing a DV inconsistent data management solution.

[0241] The present invention uses the method of functional dependency relationships in power grid DV data to repair inconsistent data without changing the power grid DV data warehouse; automatically completes the loading of the data mart, and solves the technical problem of high-quality automatic loading of the data mart.

[0242] The above embodiments are preferred embodiments of the present invention, but the embodiments of the present invention are not limited to the above embodiments. Any other changes, modifications, substitutions, combinations, and simplifications made without departing from the spirit and principle of the present invention shall be equivalent replacement methods and are all included in the protection scope of the present invention.

Claims

1. An ontology-based automatic data loading method for a power grid data mart, characterized in that It includes the following steps: Construct a power grid DV data warehouse, and use three types of tables, namely the central point table, the link table, and the affiliated table, to store power grid business entities, relationships, and their attribute data respectively as source tables; Construct a power grid ontology knowledge base, and set a benchmark affiliated table and a secondary affiliated table for multiple similar affiliated tables; Construct a functional dependency table, a data semantic confidence calculation table, and a functional dependency repair table; For several functional dependency expressions of the selected central point in the functional dependency table, find the central point and its benchmark affiliated table, detect the establishment mode of the functional dependency for the benchmark affiliated table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table; For the functional dependency expressions of the selected central point in the functional dependency table, find the corresponding secondary affiliated table, detect the establishment mode of the functional dependency for the secondary affiliated table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table; Detect the establishment mode of the functional dependency for the records in the data semantic confidence calculation table, calculate the data confidence in the data semantic confidence calculation table, and determine the repair value, which is stored in the functional dependency repair table; Construct a power grid data mart as the target table based on a multi-dimensional model, and the multi-dimensional model includes a fact table and dimension tables; Load data into the dimension tables of the multi-dimensional model and load data into the fact table of the multi-dimensional model; Store the business key values of the central point into a temporary dimension table; Store the data of the benchmark affiliated table related to the customer dimension table into the temporary dimension table; Store the data of the secondary affiliated table related to the customer dimension table into the temporary dimension table; Based on the functional dependency repair table, obtain all the repair records with the same affiliated table identifier as that in the functional dependency repair table for the benchmark affiliated table, obtain all the repair records with the same affiliated table identifier as that in the functional dependency repair table for the secondary affiliated table, and repair the data in the temporary dimension table; Write the data in the temporary dimension table into the corresponding customer dimension table to complete the data loading of a single dimension table; Repeat the data loading steps of a single dimension table to create a temporary dimension table for all dimension tables of the multi-dimensional model; Store the business key values in the link table corresponding to the fact table into a temporary fact table; Store the data of the benchmark affiliated table of the link table corresponding to the fact table into the temporary fact table; Store the data of the secondary affiliated table of the link table corresponding to the fact table into the temporary fact table; Write the data in the temporary fact table into the corresponding fact table, and write the time stamp into the date dimension value of the fact table to complete the data loading of a single fact table; Repeat the data loading steps of a single fact table to create a temporary fact table for all fact tables of the multi-dimensional model.

2. The ontology-based automatic data loading method for power grid data mart according to claim 1, wherein Construct a functional dependency table, specifically expressed as: FD-List(FD_id, T_id, FD-Left, FD-Right, Conf-FD) Among them, FD_id represents the identifier of the functional dependency expression, T_id represents the identifier of the central point table or link table to which the functional dependency belongs, FD-Left represents the left part name of the functional dependency expression, FD-Right represents the right part name of the functional dependency expression, and Conf-FD represents the data confidence of the functional dependency; Construct a data semantic confidence calculation table, specifically expressed as: DS-Conf(DS_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Conf-L, Conf-R, Repair) Among them, DS_id represents the sequential primary key of this table, FD_id represents the identifier of the functional dependency expression, Sat_id represents the affiliated table identifier, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left value of the functional dependency expression, FD-R represents the right value of the functional dependency expression, Conf-L represents the data confidence of the left part of the functional dependency expression, Conf-R represents the data confidence of the right part of the functional dependency expression, and Repair represents the repair value; Construct a functional dependency repair table, specifically represented as: FD-Repair(RP_id, FD_id, Sat_id, Bus_key, DTS, FD-L, FD-R, Repair), where RP_id represents the sequential primary key of this table, FD_id represents the identifier of the functional dependency expression, Sat_id represents the affiliated table identifier, Bus_key represents the business key, DTS represents the timestamp, FD-L represents the left value of the functional dependency expression, FD-R represents the right value of the functional dependency expression, and Repair represents the repair value of the functional dependency expression.

3. The ontology-based automatic data loading method for power grid data mart according to claim 1, wherein For several functional dependency expressions of the selected center point in the functional dependency table, find the corresponding center point and its affiliated table, detect the establishment mode of the functional dependency for the benchmark affiliated table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table. The specific steps include: Obtain the same type of affiliated tables; Taking the benchmark affiliated table as the main one, match the field names in the secondary affiliated tables based on the power grid ontology knowledge base; According to the field name matching results, for the field values corresponding to the left part and the right part of the functional dependency expression in the affiliated table, detect whether all data records satisfy the functional dependency expression; Based on the power grid ontology knowledge base, determine that the latest modified data in the benchmark affiliated table satisfies the functional dependency expression and does not output it to the data semantic confidence calculation table, and output the data fields in the benchmark affiliated table that do not conform to the functional dependency to the data semantic confidence calculation table.

4. The ontology-based automatic data loading method for power grid data mart according to claim 1, characterized in that Detect the establishment mode of the functional dependency for the benchmark affiliated table. The processing steps for the data with the establishment mode value include: If the number of records with the same right value of the functional dependency expression for a left value of the functional dependency expression is greater than the number of records with different right values of the functional dependency expression, then use this same right value of the functional dependency expression as the establishment mode value; Output the records with different right values of the functional dependency expression to the functional dependency repair table and update the corresponding identifier of the current functional dependency repair table; The processing steps for the data without the establishment mode value include: Output all data records that do not conform to the functional dependency to the data semantic confidence calculation table and update the corresponding identifier of the data semantic confidence calculation table.

5. The ontology-based automatic data loading method for power grid data mart according to claim 1, wherein For the functional dependency expression of the selected central point in the functional dependency table, find the corresponding secondary attachment table, detect the establishment pattern of the functional dependency for the secondary attachment table, and output the data fields that do not conform to the functional dependency to the data semantic confidence calculation table. The specific steps are as follows: Compare the functional dependency expression left - hand value and the functional dependency expression right - hand value of a business key value and timestamp record in the secondary attachment table with the corresponding field values of the corresponding records in the benchmark attachment table that conform to the functional dependency. If the field values of the secondary attachment table and the benchmark attachment table are the same in both the functional dependency expression left - hand value and the functional dependency expression right - hand value fields, it is determined that the record in the secondary attachment table conforms to the functional dependency; Detect the establishment pattern of the functional dependency for the secondary attachment table. Based on the power grid ontology knowledge base, determine that the latest modified data in the secondary attachment table satisfies the functional dependency expression and do not output it to the data semantic confidence calculation table. Output the data records in the secondary attachment table that do not conform to the current functional dependency to the data semantic confidence calculation table.

6. The ontology-based automatic data loading method for power grid data mart according to claim 5, wherein Compare the functional dependency expression left - hand value and the functional dependency expression right - hand value of a business key value and timestamp record in the secondary attachment table with the corresponding field values of the corresponding records in the benchmark attachment table that conform to the functional dependency; If the field values of the secondary attachment table and the benchmark attachment table are the same in the functional dependency expression left - hand value field, but different in the functional dependency expression right - hand value field, then use the functional dependency expression right - hand field value of the benchmark attachment table as the establishment pattern value and output it to the functional dependency repair table, and update the corresponding identifier in the functional dependency repair table; If the field values of the secondary attachment table and the benchmark attachment table are different in the functional dependency expression left - hand value field, then output this record to the data semantic confidence calculation table.

7. The ontology-based automatic data loading method for power grid data mart according to claim 1, characterized in that, Calculate the data confidence in the data semantic confidence calculation table and determine the repair value. The specific steps are as follows: Use the ontology knowledge base to respectively determine the evidence attributes of the left and right parts of the functional dependency. According to the attachment table identifier in the data semantic confidence calculation table, and the field names of the left and right parts of the functional dependency expression, respectively find the determining attributes of the left and right parts of the benchmark attachment table or the secondary attachment table in the power grid ontology knowledge base as the parent nodes of the Bayesian network, Calculate the data confidence in the data semantic confidence calculation table based on the Bayesian method; Determine the repair value according to the data confidence.

8. The ontology-based automatic data loading method for power grid data mart according to claim 7, wherein Calculate the data confidence in the data semantic confidence calculation table based on the Bayesian method. The specific steps are as follows: Calculate the conditional probability: According to the node and parent - node relationship of the Bayesian network, calculate the occurrence times of each functional dependency expression left - hand value \(l\) and functional dependency expression right - hand value \(r\), and calculate the conditional probability according to the following formula: Calculate the data confidence of the functional dependency expression left - hand value \(l\) and the functional dependency expression right - hand value \(r\) and perform normalization processing: Specifically expressed as: Determine the repair value according to the data confidence, specifically including: When the left - hand values of the functional - dependency expressions are the same, but the right - hand values of the functional - dependency expressions are different from each other, select the r value corresponding to the maximum value of Conf_R’(r) as the repair value and place it into the functional - dependency repair table. If the values of Conf_R’(r) are all equal, then select the r value of the record with the maximum value of Conf_L’(l) as the repair value and place it into the functional - dependency repair table; When the left - hand values of the functional - dependency expressions are the same, and each group has an equal number of right - hand values F of the functional - dependency expressions, the right - hand values of the functional - dependency expressions within the group are equal but different between groups, then select the r value corresponding to the cumulative maximum value of Conf_R’(r) within the group as the repair value and place it into the functional - dependency repair table; If the cumulative maximum values of Conf_R’(r) within the group are all equal, then select the r value of the record with the cumulative maximum value of Conf_L’(l) within the group as the repair value and place it into the functional - dependency repair table.

9. The ontology-based automatic data loading method for power grid data mart according to claim 1, wherein Load data into the dimension tables of the multi - dimensional model. The specific steps include: Using the power - grid ontology knowledge base and the naming rules of power - grid data tables, search for the customer dimension table in the target table and determine the matching between the primary key of the customer dimension table and the business key name of the customer center point; Using the power - grid ontology knowledge base and the naming rules of power - grid data tables, search for the customer center point and its affiliated tables in the source table; For each business key value in the temporary dimension table, retain the record with the latest timestamp and delete the remaining records.

10. The ontology-based automatic data loading method for power grid data mart according to claim 1, characterized in that Load data into the fact table of the multi - dimensional model. The specific steps include: Using the power - grid ontology knowledge base and the naming rules of power - grid data tables, search for the fact table to be established in the target table, determine the primary key of this fact table, and the matching between the foreign keys that make up the primary key of the fact table and the business key name of the center - point table; Using the power - grid ontology knowledge base and the naming rules of power - grid data tables, determine the corresponding link table of this fact table and several similar affiliated tables under the link table.

Images