A method for automatically extracting functional dependencies based on power grid data warehouse

Abstract

The present invention discloses a method for automatically extracting functional dependencies based on a power grid data warehouse. The method includes the following main steps: constructing a power grid DV data warehouse; detecting the establishment pattern of functional dependency candidates based on power grid data characteristics; calculating the data confidence of the business key and the data confidence of the business entity association record based on the data semantics of the power grid business key itself and the evidence attribute; calculating the data confidence of the functional dependency candidate based on a set confidence threshold; and finally outputting all established functional dependency candidates. The present invention overcomes data noise interference caused by inconsistent multi-source data, solves the technical problem of automatically extracting functional dependencies from many-to-many link tables, improves the accuracy of calculating different entity attribute data and their record confidence, and realizes efficient and high-quality data semantic relationship extraction, thereby laying the foundation for automatically building multidimensional models.

Description

Technical Field

[0001] The present invention relates to the technical field of power grid data processing, and in particular to a method for automatically extracting functional dependencies based on a power grid data warehouse. Background Art

[0002] A data warehouse is a system that stores an enterprise's historical transaction data, specifically used by managers for management analysis and decision support. Traditional data warehouses present many technical challenges, such as scalability, traceability, and auditability. These challenges are compounded by high implementation risks and lengthy development cycles. Traditional data warehouse implementations based on multidimensional data models and their relationships are no longer sufficient to meet the massive data organization requirements of the big data era. Consequently, power grid companies are exploring the Data Vault (DV) modeling approach, which combines paradigm modeling with analytical modeling to build power grid data warehouses that meet the unique requirements of big data organization.

[0003] To establish the power grid's DV model at the data warehouse level, three types of data tables were constructed to maximize the preservation of various data input from the power grid's various source business systems, even conflicting and inconsistent data. This approach maximizes the scalability, traceability, and auditability of the massive data processing needs of the power grid. While the many-to-many relationship design of the power grid's DV link table enhances the scalability of the DV modeling approach, it also relaxes data semantic constraints and increases the potential for errors. When end users conduct in-depth data analysis, especially when constructing multidimensional (MD) models and conducting analysis using general-purpose OLAP software, it is necessary to clearly define the functional dependency constraints between data in order to establish dimensions and their hierarchical structures to support standard OLAP analysis operations.

[0004] In the power grid DV model, due to the storage characteristics of its three types of tables, there are clear explicit functional dependencies between the subsidiary and central tables, the subsidiary and link tables, and the link tables and the central point table. However, there are also multiple implicit functional dependencies within these tables. Furthermore, because the power grid DV data warehouse stores data from multiple source business systems, data inconsistencies may occur, posing new challenges to finding implicit functional dependencies.

[0005] Current methods for extracting functional dependencies from relational datasets typically employ statistical confidence calculations, often resulting in the discovery of numerous approximately valid but invalid functional dependencies. While DV modeling can meet the organizational structure and operational efficiency requirements of power grid enterprises for big data, its structural characteristics—that is, both many-to-many and many-to-one relationships between two or more central points are modeled using a single link table—and data inconsistencies associated with integrating multiple source business systems can interfere with the method for determining functional dependencies. Consequently, the implicit functional dependencies within the link tables of power grid data warehouses are difficult to determine directly, necessitating the search for new technologies to automatically extract these implicit functional dependencies. Summary of the Invention

[0006] To overcome the shortcomings and deficiencies of existing technologies, the present invention provides a method for automatically extracting functional dependencies based on a power grid data warehouse. This method addresses the power grid DV data warehouse and, targeting the semantic characteristics of power grid data and functional dependencies, employs a method based on functional dependency patterns and data semantics to evaluate and calculate data confidence and the confidence of functional dependency candidates. This method solves the technical problem of automatically extracting functional dependencies from power grid business entity link tables, providing effective support for the subsequent construction of dimensional and hierarchical structures in multidimensional models.

[0007] The second object of the present invention is to provide an automatic extraction function dependency system based on a power grid data warehouse.

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

[0009] The present invention provides a method for automatically extracting functional dependencies based on a power grid data warehouse, comprising the following steps:

[0010] Construct a power grid DV data warehouse, using central point tables, link tables, and subsidiary tables to store power grid business entities, relationships, and their attribute data respectively;

[0011] Set the data semantic confidence threshold and the link table valid record ratio threshold;

[0012] Constructing a data semantic confidence calculation table and a function dependency candidate table for the link table, and setting a flag in the data semantic confidence calculation table, the flag being used to mark whether the data record is consistent with the function dependency candidate;

[0013] For the function dependency candidate expressions in the function dependency candidate table, the data semantic confidence calculation table is used to detect the establishment pattern of the function dependency candidate, and for the records in the data semantic confidence calculation table that meet the establishment pattern, the corresponding flag is set to a mark value;

[0014] Calculate the data confidence of each business key and the functional dependency data confidence of linked records one by one based on data semantics;

[0015] Calculating the data confidence of the functional dependency for the functional dependency candidate expressions in the functional dependency candidate table;

[0016] When the data confidence of the function dependency in the function dependency candidate table reaches the data semantic confidence threshold, the function dependency candidate is determined to be established, the current function dependency candidate is output, and the data confidence of the current record for a certain function dependency and the corresponding flag are updated in the data semantic confidence calculation table; the statistical update flag is the ratio of the number of data records corresponding to the mark value to the total number of data records. When the ratio value reaches the valid record ratio threshold of the link table, the function dependency candidate data confidence corresponding to the function dependency candidate table is updated;

[0017] Traverse all the function dependency candidate expressions in the function dependency candidate table, and output all valid function dependency candidates according to the data confidence of the corresponding function dependency in the function dependency candidate table.

[0018] As an optimal technical solution, a data semantic confidence calculation table is constructed for the link table, and the specific model is expressed as: Conf-DS (LK_id, BK1, Attr_Bk1, Conf_Bk1, ..., BK n , Attr_Bk n ,Conf_Bk n , Conf-LK, Incon);

[0019] Among them, LK_id represents the primary key of the link table, BK i Indicates the i-th business key, Attr_Bk i Represents the evidence attribute of the i-th business key, Conf_Bk i Indicates the data confidence of the i-th business key, Conf-LK indicates the data confidence of the link table record on a certain function dependency, and Incon indicates whether the link table record is consistent with a certain function dependency.

[0020] As a preferred technical solution, a data semantic confidence calculation table is constructed for the link table. The specific steps include:

[0021] Write each record in the link table into BK1,…,BK in the order of business key values n , until all records in the link table are completed;

[0022] Query all the attributes of the subsidiary tables of the i-th business key by the latest timestamp, calculate the cardinality of the value range of each attribute, excluding numeric and date attributes, and designate the attribute with the largest cardinality as the evidence attribute of the i-th business key. Repeat this process until the evidence attributes of all business keys are determined.

[0023] Press the business key BK i The instance value query corresponds to the evidence attribute value, and is written into Attr_Bk1, Attr_Bk2, ..., Attr_Bk n , until all records in the link table are completed.

[0024] As a preferred technical solution, a function dependency candidate table is constructed for the link table, and the specific pattern is expressed as: Cand-FD (FD_id, FD_Left, FD_Right, Conf-FD);

[0025] Among them, FD_id represents the identifier of the function dependency candidate expression, FD_Left represents the left part of the function dependency candidate expression, FD_Right represents the right part of the function dependency candidate expression, and Conf-FD represents the data confidence of the function dependency.

[0026] As a preferred technical solution, a function dependency candidate table is constructed for the link table. The specific steps include:

[0027] Combine the business key names in the link table into pairs, and specify a function dependency candidate expression for each pair;

[0028] The function dependency candidate expression and the expression sequence pair are written into the left part and the right part of the function dependency candidate expression respectively until all function dependency candidate table records are generated.

[0029] As a preferred technical solution, a data semantic confidence calculation table is used to detect the establishment mode of function dependency candidates. The specific steps include:

[0030] For a function dependency candidate expression, if the corresponding two business keys in the data semantic confidence calculation table have records that satisfy its function dependency candidate expression, then the records of the left value of the function dependency candidate expression and the right value of the function dependency candidate expression constitute the establishment mode of the current function dependency;

[0031] For the records in the data semantic confidence calculation table that meet the established pattern, set and update the corresponding flags.

[0032] As a preferred technical solution, the data confidence of each business key is calculated one by one based on data semantics, and the specific steps include:

[0033] Construct event relationships: take the evidence attribute as the preceding event of the business key, and the business key as the subsequent event of its evidence attribute;

[0034] Based on the constructed event relationship, we get the dependency relationship between nodes, calculate the number of occurrences of each business key value, and calculate the conditional probability, which can be expressed as follows:

[0035]

[0036] Among them, p represents the conditional probability, #(bk,Attr_Bk i (bk)) represents the business key bk value and its evidence attribute Attr_Bk i(bk) The number of records where the value appears at the same time, #(Attr_Bk i (bk)) represents the evidence attribute Attr_Bk of the business key bk value i (bk) The number of records where the value appears;

[0037] Calculate the data semantic confidence of the business key instance value of each record in the calculation table, specifically expressed as:

[0038]

[0039] After normalization, it is expressed as:

[0040]

[0041] Among them, Conf_Bk i ′(bk) represents the normalized value;

[0042] Calculate the data confidence of the linked records in the data semantic confidence calculation table and write the corresponding attributes in the data semantic confidence calculation table until all records are calculated. The calculation formula of data confidence is specifically expressed as:

[0043] Conf-LK=min(Conf_Bk i ′(bk),BK i ∈FD_left∪FD_right)

[0044] Among them, Conf-LK represents the data confidence of the link record on a function dependency, BK i Indicates the business key of each record in the data semantic confidence calculation table. FD_Left indicates the left part of the function dependency candidate expression, and FD_Right indicates the right part of the function dependency candidate expression.

[0045] As a preferred technical solution, for the records in the data semantic confidence calculation table that meet the established pattern, the corresponding flag is set to a mark value, specifically:

[0046] The mark value of the corresponding flag of the record that meets the establishment mode is 0;

[0047] When the data confidence of the function dependency in the function dependency candidate table is greater than or equal to the data semantic confidence threshold, the mark value of the corresponding flag of the current record is 0;

[0048] When the data confidence of the functional dependency in the functional dependency candidate table is less than the data semantic confidence threshold, the corresponding flag of the current record is set to 1.

[0049] As a preferred technical solution, the statistical update flag is the ratio of the number of data records corresponding to the mark value to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, the confidence level of the function dependency candidate data corresponding to the function dependency candidate table is updated. The specific steps include:

[0050] The ratio of the number of data records with a statistical mark value of 0 to the total number of data records is calculated. When the ratio reaches the threshold of the valid record ratio of the linked table, the confidence level of the function dependency candidate data corresponding to the function dependency candidate table is the ratio of the confidence level of the function dependency data of all data records to the total number of records.

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

[0052] The present invention provides an automatic extraction function dependency system based on a power grid data warehouse, comprising: a power grid DV data warehouse construction module, a threshold setting module, a data semantic confidence calculation table construction module, a function dependency candidate table construction module, a mode detection module, a data confidence calculation module, a judgment and update module, and a function dependency candidate output module;

[0053] The power grid DV data warehouse construction module is used to construct the power grid DV data warehouse, using a central point table, a link table, and an auxiliary table to store power grid business entities, relationships, and their attribute data respectively;

[0054] The threshold setting module is used to set the data semantic confidence threshold and the link table valid record ratio threshold;

[0055] The data semantic confidence calculation table construction module is used to construct a data semantic confidence calculation table for the link table, and set a flag in the data semantic confidence calculation table, wherein the flag is used to mark whether the data record is consistent with the functional dependency;

[0056] The function dependency candidate table construction module is used to construct a function dependency candidate table for the link table;

[0057] The establishment pattern detection module is used to detect the establishment pattern of the function dependency candidate expressions in the function dependency candidate table using the data semantic confidence calculation table, and set the corresponding flag to a mark value for the record in the data semantic confidence calculation table that meets the establishment pattern;

[0058] The data confidence calculation module is used to calculate the data confidence of each business key and the functional dependency data confidence of the link record one by one based on data semantics;

[0059] Calculating the data confidence of the functional dependency for the functional dependency candidate expressions in the functional dependency candidate table;

[0060] The judgment and update module is used to judge the data semantic confidence threshold, mark and update the data confidence, specifically including:

[0061] When the data confidence of the function dependency in the function dependency candidate table reaches the data semantic confidence threshold, the function dependency candidate is determined to be established, the current function dependency candidate is output, and the data confidence of the current record for a function dependency and the corresponding flag are updated in the data semantic confidence calculation table;

[0062] The statistical update flag is the ratio of the number of data records corresponding to the flag value to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, the confidence of the function dependency candidate data corresponding to the function dependency candidate table is updated;

[0063] The function dependency candidate output module is used to traverse all function dependency candidate expressions in the function dependency candidate table, and output all valid function dependency candidates according to the data confidence of the corresponding function dependency in the function dependency candidate table.

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

[0065] (1) In traditional data warehouses, it is assumed that data are clean and consistent, so it is easier to judge their functional dependencies. Statistical confidence calculation methods are usually used for inconsistent data, which often finds a large number of approximately valid but invalid functional dependencies. The present invention adopts a data confidence calculation method based on data semantics. With the help of the characteristics of the power grid business key itself and the effectiveness of the evidence attribute, it avoids the interference in judging the validity of functional dependencies caused by data inconsistency, improves the accuracy of calculating the confidence of different entity attribute data and their records, and thus achieves the technical effect of more accurately calculating the confidence of implicit functional dependencies.

[0066] (2) The link table of the existing power grid DV data warehouse represents the many-to-many and many-to-one relationships between different business entities in a many-to-many form, thereby bringing scalability features. However, when building a multidimensional model, it is necessary to clarify its functional dependencies. In order to solve the technical problem of automatically extracting functional dependencies from the power grid DV link table, the present invention utilizes the characteristics of the power grid business, effectively limits the expression form of functional dependencies, and adopts the establishment model and data semantic confidence calculation method to achieve the technical effect of automatically extracting implicit functional dependencies with high efficiency and high quality. BRIEF DESCRIPTION OF THE DRAWINGS

[0067] Figure 1 Schematic diagram of the process of the method for automatically extracting functional dependencies based on the power grid data warehouse of the present invention;

[0068] Figure 2 This is a schematic diagram of a specific model of the power grid DV data warehouse of the present invention;

[0069] Figure 3 A schematic diagram of establishing a data semantic confidence calculation table for the present invention;

[0070] Figure 4 A schematic diagram of generating a function dependency candidate table for the present invention;

[0071] Figure 5 Schematic diagram of the calculation process of data confidence of the functional dependency candidate example 1 of the present invention;

[0072] Figure 6 Schematic diagram of the calculation results of data confidence of the functional dependency candidate example 1 of the present invention;

[0073] Figure 7 Schematic diagram of the data confidence calculation process of the functional dependency candidate example 2 of the present invention;

[0074] Figure 8 Schematic diagram of the calculation results of data confidence of the function dependency candidate example 2 of the present invention;

[0075] Figure 9 This is a result diagram of the function dependency candidate table of the present invention. DETAILED DESCRIPTION

[0076] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, 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 intended to limit the present invention.

[0077] Example 1

[0078] like Figure 1 As shown, this embodiment provides a method for automatically extracting functional dependencies based on a power grid data warehouse. The power grid data warehouse is constructed and data is loaded using the DV modeling method. The three types of data tables store power grid marketing data, production data, and material data, etc.; then, a data confidence calculation table and a function dependency candidate table containing evidence attributes are constructed, and based on the function dependency establishment pattern and data semantics method, the data confidence and the function dependency candidate confidence are calculated; finally, according to the user-specified confidence, it is determined which function dependency candidates are valid.

[0079] The specific steps include:

[0080] S1: Construct a power grid DV data warehouse, which includes the three types of tables in the power grid DV model and the data stored in the tables; in addition, set a data semantic confidence threshold α and a valid record ratio β. The former is the standard for determining the data record semantics and functional dependency of the link table connecting the central point table, and the latter indicates the valid record ratio that the link table should have.

[0081] In this embodiment, the power grid DV model uses three types of tables: center point, link, and subsidiary tables to store power grid business entities, relationships, and their attribute data. The center point table connects the link table and subsidiary table based on the business key to form a hub-and-spoke architecture, connecting all data. The link table supports the storage of many-to-many relationships.

[0082] The above-mentioned organization of massive power grid data maximizes the demand for a centralized, highly scalable, and highly available power grid data platform, while supporting full-network, cross-domain data integration, as well as dynamic on-demand supply and real-time allocation of resources.

[0083] like Figure 2 As shown in the figure, there is a grid customer center table Hub_Customer (with a customer subsidiary table Sat_Customer and a customer address subsidiary table Sat_CustAddr), a power contract center table Hub_Contract (with a contract subsidiary table Sat_Contract), and a service center table Hub_Service (with a service subsidiary table Sat_Service), as well as a power usage status table Lnk_Usage (with a power usage status subsidiary table Sat_Usage) and a contract-service link table Lnk_Cont-Serv;

[0084] In this embodiment, a central point or link may have multiple attachments. Each attachment forms a historical record of related attributes for different periods of time based on the timestamp (Load_Date). Sequence data of related attributes of a source power grid business system can be stored under a central point. At the same time, these three tables store the record source (Rec_Source) attribute, which can independently store data from all source power grid business systems and also preserve inconsistent data caused by multiple source power grid business systems.

[0085] In this embodiment, corresponding data is loaded into the above-mentioned power grid DV data warehouse model from multiple source business systems.

[0086] The power grid DV model is suitable for efficiently storing massive amounts of power grid data, but it hinders user access to the data. Therefore, in a DV data warehouse environment, a multidimensional model based on fact tables and dimension tables should be established to support end-user data analysis. When building a multidimensional model, it is necessary to clearly define the functional dependencies between business entities. This means that implicit functional dependencies must be extracted from the link table.

[0087] S2: Construct a data semantic confidence calculation table and a function dependency candidate table for the link table. According to the characteristics of the power grid DV data warehouse, follow the following steps to construct these two tables for a link table LK (Lnk_Cust-Cont):

[0088] S21: Create a data semantic confidence calculation table Conf-DS (LK_id, BK1, Attr_Bk1, Conf_Bk1, ..., BK n , Attr_Bk n ,Conf_Bk n , Conf-LK, Incon), where LK_id is the link table primary key, BK i Indicates the i-th business key (1≤i≤n), Attr_Bk i Represents the evidence attribute of the i-th business key, Conf_Bk i Indicates the data confidence of the i-th business key, Conf-LK indicates the data confidence of the link table record to a function dependency, and Incon indicates whether the data record is consistent with a function dependency. When the data record is inconsistent with a function dependency, the corresponding flag Incon is set to 1; when the data record is consistent with a function dependency, the corresponding flag Incon is set to 0;

[0089] Specifically include:

[0090] a) Write business key values: Write each record in the link table LK into BK1, ..., BK in the order of business key values. n , until all records in the link table LK are completed.

[0091] b) Determine the evidence attributes: First, query all the subsidiary table attributes of the i-th business key according to the latest timestamp. Then, calculate the cardinality of the value range of each attribute (excluding numeric and date attributes). Finally, designate the attribute with the largest cardinality as the evidence attribute of the i-th business key. Repeat until the evidence attributes of all business keys are determined.

[0092] c) Write evidence attribute value: Press BK of the business key i Instance value query corresponds to the evidence attribute value, and writes Attr_Bk1, Attr_Bk2,…,Attr_Bk in sequence n , until all records in the link table LK are completed;

[0093] like Figure 3As shown, a data semantic confidence calculation table Conf-DS (LK_id, BK1, Attr_Bk1, Conf_Bk1, BK2, Attr_Bk2, Conf_Bk2, Conf-LK, Incon) is established, where LK_id is the primary key of the link table, BK1 represents the business key Cust_key, BK2 represents the business key Cont_key, Attr_Bk1 represents the evidence attribute of the business key Cust_key, Attr_Bk2 represents the evidence attribute of the business key Cont_key, Conf_Bk1 represents the data confidence of the business key Cust_key, Conf_Bk2 represents the data confidence of the business key Cont_key, Conf-LK represents the data confidence of the data record for the function dependency, and Incon represents the flag of whether the data record is consistent with the function dependency.

[0094] Specifically:

[0095] a) Write each record in the link table Lnk_Cust-Cont into BK1 and BK2 in order of Cust_key and Cont_key according to the business key value until all records in the link table Lnk_Cust-Cont are completed.

[0096] b) Determine the evidence attributes: First, query all the subsidiary tables of the business key Cust_key according to the latest timestamp Load_Date to find out that they have the attributes "province, city, address, name, telephone number, email"; then calculate the cardinality of the value range of each attribute (excluding numeric and date attributes), and designate the attribute with the largest cardinality "telephone number" as the evidence attribute of the business key Cust_key; similarly, determine that the evidence attribute of Cont_key is "electricity address".

[0097] c) Query the corresponding evidence attribute values ​​according to the instance values ​​of the business keys Cust_key and Cont_key in turn, and write the evidence attribute values ​​into Attr_Bk1 and Attr_Bk2 in turn until all records in the link table Lnk_Cust-Cont are completed.

[0098] S22: Establish a function dependency candidate table Cand-FD (FD_id, FD_Left, FD_Right, Conf-FD), where FD_id represents the identifier of the function dependency candidate expression FD_Left→FD_Right, FD_Left represents the left part of the function dependency candidate expression, FD_Right represents the right part of the function dependency candidate expression, and Conf-FD represents the data confidence of a certain function dependency.

[0099] S23: Generate partial data of the function dependency candidate table Cand-FD.

[0100] a) Generate function dependency candidate expression: link the business key name BK in the table i Combine two pairs into pairs (the same name cannot form a pair), and specify a function dependency candidate expression for each pair;

[0101] b) Write the function dependency candidate expression: Write the function dependency candidate expression and the expression sequence pair into the FD_Left and FD_Right attributes respectively until all the function dependency candidate table Cand-FD records are generated.

[0102] like Figure 4 As shown, this embodiment generates partial data of the function dependency candidate table Cand-FD, specifically:

[0103] a) Generate function dependency candidate expressions: Combine the business key names Cust_key and Cont_key in the link table Lnk_Cust-Cont into pairs (Cont_key, Cust_key) and (Cust_key, Cont_key), and assign expression identifiers FD001 and FD002, respectively.

[0104] b) Write the generated functional dependency candidate expressions into the functional dependency candidate table Cand-FD.

[0105] S3: For a function dependency candidate expression in the function dependency candidate table Cand-FD, use the data semantic confidence calculation table Conf-DS to detect the establishment mode of the function dependency candidate.

[0106] S31: Detecting the establishment pattern of the function dependency candidate: For a function dependency candidate expression, if there are records that satisfy its function dependency candidate expression FD_Left→FD_Right on the corresponding two business keys in the data semantic confidence calculation table Conf-DS, that is, for the power grid business, one FD_Left value can and only can determine one FD_Right value record, then the FD_Left value and the FD_Right value record constitute the establishment pattern of the function dependency;

[0107] S32: Set the flag Incon to indicate whether the data record is consistent with the functional dependency: for the records in the data semantic confidence calculation table Conf-DS that meet the establishment pattern, set the corresponding flag Incon to 0, otherwise to 1;

[0108] In this embodiment, first, the first record in the functional dependency candidate table Cand-FD, ie, Cust_key→Cont_key, is checked to see whether the functional dependency candidate exists.

[0109] 1) Detect the establishment pattern of the functional dependency candidate: In the data semantic confidence calculation table Conf-DS, for the functional dependency Cust_key→Cont_key, find the establishment pattern. That is, according to the characteristics of the power grid business, a Cust_key value can and only can determine a record with a Cont_key value. The records with the Cust_key value and the Cont_key value constitute the establishment pattern of the functional dependency. Find the record with LK_id 12 that meets the establishment pattern.

[0110] 2) Set the flag Incon of the record with LK_id 1-11 to 1, and set the flag Incon value of the record with LK_id 12 to 0;

[0111] S4: Based on the data semantics, calculate the confidence level of each business key data Conf_Bk1,…,Conf_Bk for the function dependency candidate. n , and the data confidence Conf-LK value of the data record's dependency on a certain function.

[0112] S41: Calculate the data confidence of each business key in the data semantic confidence calculation table Conf-DS, Conf_Bk1, ..., Conf_Bk n :

[0113] a) Set the evidence attribute Attr_Bk i As business key BK i The business key is considered as the subsequent event of its evidence attribute;

[0114] b) Calculate conditional probability: Based on the event relationship constructed in step a), the dependency relationship between nodes is obtained, and the number of occurrences of each business key value is calculated. #(bk,Attr_Bk i (bk)) refers to the business key bk value and its evidence attribute Attr_Bk i (bk) The number of records where the value appears at the same time, #(Attr_Bk i (bk)) refers to the evidence attribute Attr_Bk of the business key bk value i Number of records where the (bk) value appears:

[0115]

[0116] c) Calculate each business key BK i The data confidence of the instance value bk is normalized: the business key BK of each record in the data semantic confidence calculation table Conf-DS is calculated according to the following formula: i The data confidence of the instance value is normalized and then written into the corresponding attribute Conf_Bk in the data semantic confidence calculation table Conf-DSi , until all records are calculated.

[0117]

[0118] Conf_Bk i (bk) Normalization formula:

[0119]

[0120] S42: Calculate the data confidence Conf-LK of the record (the row record being calculated in Conf-DS) in the data semantic confidence calculation table Conf-DS: Calculate the data confidence according to the following formula and write it into the attribute.

[0121] Conf-LK=min(Conf_Bk i ′(bk),BK i ∈FD_left∪FD_right)

[0122] S43: Calculate data confidence until all records are processed.

[0123] like Figure 5 、 Figure 6 As shown, for this function dependency candidate, based on data semantics, the confidence of each business key data is calculated one by one. The confidence of each business key data Conf_Bk1,…,Conf_Bk is calculated one by one. n , and the data confidence Conf-LK value of the data record on the function dependency, specifically:

[0124] 1) Calculate the data confidence of each business key in the data semantic confidence calculation table Conf-DS Conf_Bk1,…,Conf_Bk n :

[0125] a) Take the evidence attribute "telephone" as the preceding event of the business key Cust_key, and "electricity address" as the preceding event of the business key Cont_key;

[0126] b) Calculate the conditional probability: Based on the event relationship constructed in step a), obtain the dependency relationship between nodes, calculate the number of records f1 where the Cust_key value and the "Phone" number appear at the same time, and the number of records f2 where the "Phone" number appears. Also calculate the number of records f1 where the Cont_key value and the "Electricity Address" appear at the same time, and the number of records f2 where the "Electricity Address" appears. Then calculate the conditional probability:

[0127]

[0128] c) Calculate the data confidence of the instance values ​​of the business keys Cust_key and Cont_key, and normalize the values.

[0129]

[0130]

[0131] Normalize the data confidence:

[0132]

[0133]

[0134] 2) Calculate the data confidence Conf-LK of the data record in the data semantic confidence calculation table Conf-DS:

[0135]

[0136] 3) Calculate the data confidence of each record.

[0137] S5: Calculate the data confidence Conf-FD of a function dependency for the function dependency candidate expression in the function dependency candidate table Cand-FD.

[0138] S51: For the data semantic confidence calculation table Conf-DS, if the data confidence Conf-LK value of the data record on the function dependency is less than the data semantic confidence threshold α, the data confidence Conf-LK of the record on the function dependency is set to 0 and the flag Incon is set to 1.

[0139] S52: For the data semantic confidence calculation table Conf-DS, if the number of data records with the flag Incon = 0 / the total number of records ≥ the valid record ratio β, then the data confidence Conf-FD of the function dependency candidate corresponding to the function dependency candidate in the function dependency candidate table Cand-FD = the data confidence Conf-LK of the summary data record for a certain function dependency / the total number of records;

[0140] Otherwise, the data confidence Conf-FD of the function dependency candidate corresponding to the function dependency candidate table Cand-FD is 0.

[0141] In this embodiment, the user specifies a data semantic confidence threshold α = 0.7 and a valid record ratio β = 0.8; the data semantic confidence threshold α is used to determine the standard for determining whether the data semantics of the business key itself and the evidence attribute of the link table connection center point are valid, and to determine the standard for determining whether the functional dependency of the two center points linked by the link table is valid. The valid record ratio β refers to the minimum valid record ratio that should be in the link table.

[0142] Combine Figure 6 As shown, for the data semantic confidence calculation table Conf-DS, the data confidence Conf-LK of the record with LK_id=5 for a function dependency is <α (data semantic confidence threshold), the data confidence Conf-LK of the record for the function dependency is set to 0, and the flag Incon is set to 1.

[0143] For the data semantic confidence calculation table Conf-DS, the number of data records with the mark Incon=0 / total number of records=0.083<β=0.8 (valid record ratio threshold), then the confidence Conf-FD of the function dependency candidate data corresponding to the function dependency candidate table Cand-FD is set to 0.

[0144] S6: If there are other unprocessed function dependency candidates in the function dependency candidate table Cand-FD, go to step S3;

[0145] like Figure 7 、 Figure 8 As shown, the following repeatedly executes S3, S4 and S5 for the function dependency candidate Cont_key→Cust_key:

[0146] Repeat S3: Check whether the expression Cont_key → Cust_key in the functional dependency candidate table is valid. According to the characteristics of the power grid business, records with LK_id = 1-9 and 11 meet the validity pattern; set the flag Incon value of these records to 0, and set the flag Incon value of the records with LK_id = 10 and LK_id = 12 to 1.

[0147] Repeat S4: Calculate the confidence of each business key data Conf_Bk1,…,Conf_Bk one by one for the data semantic confidence calculation table Conf-DS n , and the value of the data confidence Conf-LK that a function depends on.

[0148] Repeat S5: for the function dependency candidate in the function dependency candidate table Cand-FD, calculate the data confidence Conf-FD for a certain function dependency.

[0149] For the data semantic confidence calculation table Conf-DS, if the data confidence Conf-LK value of the record with LK_id=5 for a function dependency is less than α=0.7, the data confidence Conf-LK of the record for the function dependency is set to 0, and the flag Incon is set to 1.

[0150] like Figure 9As shown, for the data semantic confidence calculation table Conf-DS, the number of data records with the flag Incon = 0 / the total number of records = 0.83>β = 0.8, the data records in the data semantic confidence calculation table Conf-DS are valid, and the function dependency candidate data confidence Conf-FD corresponding to the function dependency candidate table Cand-FD is the data confidence Conf-LK of all data records for a certain function dependency / the total number of records = 0.77.

[0151] Since all the functional dependency candidates in the functional dependency candidate table Cand-FD have been processed, step S7 is directly executed;

[0152] S7: Output the valid function dependency candidate according to the data confidence Conf-FD of a function dependency in the function dependency candidate table Cand-FD.

[0153] S71: If the data confidence Conf-FD of a function dependency candidate ≥ the data semantic confidence threshold α, then the function dependency candidate is established and the function dependency candidate is output;

[0154] S72: Repeat step S71 until all valid functional dependency candidates are output.

[0155] In this embodiment, the data confidence Conf-FD of the first functional dependency candidate Cust_key→Cont_key is 0, which is less than the confidence threshold 0.7, and the functional dependency candidate is not established.

[0156] In this embodiment, the data confidence Conf-FD of the second functional dependency candidate Cont_key→Cust_key is 0.77, which is greater than the confidence threshold 0.7. The functional dependency candidate is established and is output.

[0157] At this point, all valid function dependency candidates have been output. This embodiment uses a method based on the function dependency establishment pattern and data semantics from the DV data warehouse suitable for storing massive power grid data, targeting the semantic characteristics of power grid data and function dependencies, to calculate data confidence and function dependency candidate confidence, and automatically extract function dependency relationships from the power grid business entity link table, providing effective support for the subsequent construction of dimensions and hierarchical structures of multidimensional models.

[0158] In this embodiment, the power grid data warehouse primarily consists of a DV data warehouse model and a multidimensional model. The DV model primarily includes a central point table, a link table, and an ancillary table, and is used to store massive amounts of data from multi-source business systems. The multidimensional model primarily consists of fact tables and dimension tables, supporting user data analysis. Traditional multidimensional model design typically involves manually discovering functional dependencies and constructing a multidimensional model based on the data semantics of specific power grid businesses. This process is complex and prone to omissions.

[0159] Based on the data semantics of the business key itself and the evidence attributes, the present invention calculates the data confidence of the power grid business key, the data confidence of the business entity association record, and the data confidence of the function dependency, and stipulates the function dependency establishment mode according to the characteristics of the power grid business data. It overcomes the data noise interference caused by the inconsistency of multi-source data, solves the technical problem of automatically extracting function dependencies from the link table, and realizes efficient and high-quality data semantic relationship extraction.

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

Claims

1. A method for automatically extracting functional dependencies based on a power grid data warehouse, characterized in that: The steps include: Construct a power grid DV data warehouse, using central point tables, link tables, and subsidiary tables to store power grid business entities, relationships, and their attribute data respectively; Set the data semantic confidence threshold and the link table valid record ratio threshold; Constructing a data semantic confidence calculation table and a function dependency candidate table for the link table, and setting a flag in the data semantic confidence calculation table, the flag being used to mark whether the data record is consistent with the function dependency; For the function dependency candidate expressions in the function dependency candidate table, the data semantic confidence calculation table is used to detect the establishment pattern of the function dependency candidate, and for the records in the data semantic confidence calculation table that meet the establishment pattern, the corresponding flag is set to a mark value; Calculate the data confidence of each business key and the functional dependency data confidence of linked records one by one based on data semantics; Calculating the data confidence of the functional dependency for the functional dependency candidate expressions in the functional dependency candidate table; When the data confidence of the function dependency in the function dependency candidate table reaches the data semantic confidence threshold, the function dependency candidate is determined to be established, the current function dependency candidate is output, and the data confidence of the current record for a function dependency and the corresponding flag are updated in the data semantic confidence calculation table; The statistical update flag is the ratio of the number of data records corresponding to the flag value to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, the confidence of the function dependency candidate data corresponding to the function dependency candidate table is updated; Traverse all the function dependency candidate expressions in the function dependency candidate table, and output all valid function dependency candidates according to the data confidence of the corresponding function dependency in the function dependency candidate table.

2. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1 is characterized in that: Construct a data semantic confidence calculation table for the link table. The specific model is expressed as: Conf-DS(LK_id, BK1, Attr_Bk1, Conf_Bk1, ..., BK n , Attr_Bk n ,Conf_Bk n , Conf-LK, Incon); Among them, LK_id represents the primary key of the link table, BK i Indicates the i-th business key, Attr_Bk i Represents the evidence attribute of the i-th business key, Conf_Bk i Indicates the data confidence of the i-th business key, Conf-LK indicates the data confidence of the link table record on a certain function dependency, and Incon indicates whether the link table record is consistent with a certain function dependency.

3. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 2, characterized in that: Construct a data semantic confidence calculation table for the link table. The specific steps include: Write each record in the link table into BK1,…,BK in the order of business key values n , until all records in the link table are completed; Query all the attributes of the subsidiary tables of the i-th business key by the latest timestamp, calculate the cardinality of the value range of each attribute, excluding numeric and date attributes, and designate the attribute with the largest cardinality as the evidence attribute of the i-th business key. Repeat this process until the evidence attributes of all business keys are determined. Press the business key BK i The instance value query corresponds to the evidence attribute value, and is written into Attr_Bk1, Attr_Bk2, ..., Attr_Bk n , until all records in the link table are completed.

4. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1 is characterized in that: Construct a function dependency candidate table for the link table. The specific pattern is: Cand-FD (FD_id, FD_Left, FD_Right, Conf-FD); Among them, FD_id represents the identifier of the function dependency candidate expression, FD_Left represents the left part of the function dependency candidate expression, FD_Right represents the right part of the function dependency candidate expression, and Conf-FD represents the data confidence of the function dependency.

5. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1 or 4, characterized in that: Construct a function dependency candidate table for the link table. The specific steps include: Combine the business key names in the link table into pairs, and specify a function dependency candidate expression for each pair; The function dependency candidate expression and the expression sequence pair are written into the left part and the right part of the function dependency candidate expression respectively until all function dependency candidate table records are generated.

6. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1, characterized in that: The data semantic confidence calculation table is used to detect the establishment mode of the function dependency candidate. The specific steps include: For a function dependency candidate expression, if the corresponding two business keys in the data semantic confidence calculation table have records that satisfy its function dependency candidate expression, then the records of the left value of the function dependency candidate expression and the right value of the function dependency candidate expression constitute the establishment mode of the current function dependency; For the records in the data semantic confidence calculation table that meet the established pattern, set and update the corresponding flags.

7. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1 is characterized in that: The data confidence of each business key is calculated one by one based on data semantics, and the specific steps include: Construct event relationships: take the evidence attribute as the preceding event of the business key, and the business key as the subsequent event of its evidence attribute; Based on the constructed event relationship, we get the dependency relationship between nodes, calculate the number of occurrences of each business key value, and calculate the conditional probability, which can be expressed as follows: Among them, p represents the conditional probability, #(bk,Attr_Bk i (bk)) represents the business key bk value and its evidence attribute Attr_Bk i (bk) The number of records where the value appears at the same time, #(Attr_Bk i (bk)) represents the evidence attribute Attr_Bk of the business key bk value i (bk) The number of records where the value appears; Calculate the data semantic confidence of the business key instance value of each record in the calculation table, specifically expressed as: After normalization, it is expressed as: Among them, Conf_Bk i ′(bk) represents the normalized value; Calculate the data confidence of the linked records in the data semantic confidence calculation table and write the corresponding attributes in the data semantic confidence calculation table until all records are calculated. The calculation formula of data confidence is specifically expressed as: Conf-LK=min(Conf_Bk i ′(bk),BK i ∈FD_left∪FD_right) Among them, Conf-LK represents the data confidence of the link record on a function dependency, BK i Indicates the business key of each record in the data semantic confidence calculation table. FD_Left indicates the left part of the function dependency candidate expression, and FD_Right indicates the right part of the function dependency candidate expression.

8. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 1 is characterized in that: For the records in the data semantic confidence calculation table that meet the established pattern, the corresponding flag is set to the mark value, specifically: The mark value of the corresponding flag of the record that meets the establishment mode is 0; When the data confidence of the function dependency in the function dependency candidate table is greater than or equal to the data semantic confidence threshold, the mark value of the corresponding flag of the current record is 0; When the data confidence of the functional dependency in the functional dependency candidate table is less than the data semantic confidence threshold, the corresponding flag of the current record is set to 1.

9. The method for automatically extracting functional dependencies based on a power grid data warehouse according to claim 8, characterized in that: The statistical update flag is the ratio of the number of data records corresponding to the flag value to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, the confidence level of the function dependency candidate data corresponding to the function dependency candidate table is updated. The specific steps include: The ratio of the number of data records with a statistical mark value of 0 to the total number of data records is calculated. When the ratio reaches the threshold of the valid record ratio of the linked table, the confidence level of the function dependency candidate data corresponding to the function dependency candidate table is the ratio of the confidence level of the function dependency data of all data records to the total number of records.

10. An automatic extraction function dependency system based on power grid data warehouse, characterized in that: include: Power grid DV data warehouse construction module, threshold setting module, data semantic confidence calculation table construction module, function dependency candidate table construction module, establishment pattern detection module, data confidence calculation module, judgment and update module, function dependency candidate output module; The power grid DV data warehouse construction module is used to construct the power grid DV data warehouse, using a central point table, a link table, and an auxiliary table to store power grid business entities, relationships, and their attribute data respectively; The threshold setting module is used to set the data semantic confidence threshold and the link table valid record ratio threshold; The data semantic confidence calculation table construction module is used to construct a data semantic confidence calculation table for the link table, and set a flag in the data semantic confidence calculation table, wherein the flag is used to mark whether the data record is consistent with the functional dependency; The function dependency candidate table construction module is used to construct a function dependency candidate table for the link table; The establishment pattern detection module is used to detect the establishment pattern of the function dependency candidate expressions in the function dependency candidate table using the data semantic confidence calculation table, and set the corresponding flag to a mark value for the record in the data semantic confidence calculation table that meets the establishment pattern; The data confidence calculation module is used to calculate the data confidence of each business key and the functional dependency data confidence of the link record one by one based on data semantics; Calculating the data confidence of the functional dependency for the functional dependency candidate expressions in the functional dependency candidate table; The judgment and update module is used to judge the data semantic confidence threshold, mark and update the data confidence, specifically including: When the data confidence of the function dependency in the function dependency candidate table reaches the data semantic confidence threshold, the function dependency candidate is determined to be established, the current function dependency candidate is output, and the data confidence of the current record for a function dependency and the corresponding flag are updated in the data semantic confidence calculation table; The statistical update flag is the ratio of the number of data records corresponding to the flag value to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, the confidence of the function dependency candidate data corresponding to the function dependency candidate table is updated; The function dependency candidate output module is used to traverse all function dependency candidate expressions in the function dependency candidate table, and output all valid function dependency candidates according to the data confidence of the corresponding function dependency in the function dependency candidate table.

Images