An Automatic Generation Method of Multidimensional Model Based on Power Grid DV Data Warehouse

Abstract

The present invention discloses a method for automatically generating a multi-dimensional model based on a power grid DV data warehouse. The method includes the following steps: constructing a power grid DV data warehouse, where the power grid DV data warehouse model includes a DV data warehouse model and a multi-dimensional model. The DV data warehouse model includes three types of tables: a central point table, a link table, and an affiliated table. The multi-dimensional model consists of a fact table, dimension tables, and their hierarchical structures; by traversing the central point tables related to the link table, generating the attributes of the fact table and dimension tables and their relationships; combining the functional dependencies between business entities in the link table, using the data in the affiliated table to verify the dependency relationships between attributes in the business entities, and extracting the implicit functional dependency relationships in the entities; using the functional dependency relationships, constructing and optimizing the multi-dimensional model candidates based on the multi-dimensional model candidate tables; traversing the multi-dimensional model candidate tables and outputting the multi-dimensional model. The present invention can, at the data and schema levels, use the data semantic confidence method, leveraging the characteristics such as the naming rules of the power grid model, to reduce and generate the multi-dimensional model, and ultimately generate the multi-dimensional model of the DV data warehouse efficiently and with high quality.

Description

Technical Field

[0001] The present invention relates to the technical field of power grid data warehouses, and particularly relates to a method for automatically generating a multi-dimensional model based on a power grid DV data warehouse. Background Art

[0002] With the development of digital technology, the massive data continuously obtained by power grid enterprises needs to be efficiently processed. The DataVault (hereinafter referred to as DV) modeling technology, which combines transaction modeling and analytical modeling styles, provides a new overall solution for massive and diverse data, and has become a new type of data warehouse solution for solving big data storage and processing technologies. The power grid DV model uses three types of data tables, namely, center point tables, link tables, and affiliated tables, for building the data warehouse layer, so as to save various types of data input from multiple source business systems of the power grid to the greatest extent. At the same time, in order to support end users to conduct in-depth data analysis, a multi-dimensional (MD) model needs to be constructed to use OLAP software for analysis. The design of the power grid DV link tables and affiliated tables brings good scalability to the DV data warehouse, but also greatly relaxes the data semantic constraints and hides the functional dependency (FD) relationships between data, which is not conducive to subsequent multi-dimensional analysis applications. Therefore, it is necessary to discover and clarify the FD relationships between business data in order to construct a multi-dimensional analysis model and its dimensions and hierarchical structures, so as to support standard OLAP analysis operations.

[0003] Furthermore, since the DV modeling method models the business entity relationships in a many-to-many form, and the data inconsistencies that may be brought about by integrating multiple source business systems will interfere with finding functional dependencies and their transmission paths. Therefore, for a power grid DV data warehouse involving tens of thousands of data tables, it is difficult to directly determine the implicit functional dependency relationships in the link tables and affiliated tables manually. How to efficiently and automatically discover functional dependencies and construct a multi-dimensional model is a difficult problem faced in data warehouse construction.

[0004] Currently, when constructing a multi-dimensional model from a paradigm-based relational database, statistical confidence and manual methods are usually adopted. The statistical confidence method often finds a large number of approximately valid but ineffective functional dependencies in massive data; while the manual method involves complex schema conversion and low efficiency, and it is difficult to traverse numerous relational tables to construct a multi-dimensional model. Therefore, there is an urgent need for a technology that can automatically construct a multi-dimensional model suitable for power grid user analysis from a power grid DV data warehouse. Summary of the Invention

[0005] In order to overcome the defects and deficiencies existing in the prior art, the present invention provides a method for automatically generating a multi-dimensional model based on a power grid DV data warehouse. The present invention automatically constructs a multi-dimensional model suitable for power grid user analysis from a DV data warehouse suitable for storing massive power grid data by means of data semantics, functional dependencies, and their traversal paths.

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

[0007] The present invention provides a method for automatically generating a multi-dimensional model based on a power grid DV data warehouse, comprising the following steps:

[0008] Construct a power grid DV data warehouse, and use a central point table, a link table, and an affiliated table to store power grid business entities, relationships, and their attribute data respectively;

[0009] Establish a function dependency candidate table, a multi-dimensional model candidate table, and an affiliated table data semantic confidence calculation table;

[0010] Select the link table in the power grid DV data warehouse, traverse the central points related to the link table, find the fact table attributes and dimension table attributes and their function dependency relationships, and store the attribute identifiers and their function dependency relationships into the multi-dimensional model candidate table and the function dependency candidate table respectively;

[0011] Select the central point affiliated table in the power grid DV data warehouse, store the affiliated table data in the affiliated table data semantic confidence calculation table, generate all function dependency candidate expressions of the affiliated table according to the rules, and calculate the confidence of each function dependency candidate using the affiliated table data;

[0012] Construct a Bayesian network structure for the data in the affiliated table data semantic confidence calculation table to obtain the dependency relationships between nodes, detect the establishment mode of the function dependency candidates for the data in the affiliated table data semantic confidence calculation table, and calculate the data confidence of the function dependency candidates of the affiliated table in the function dependency candidate table item by item based on the affiliated table data semantic confidence calculation table;

[0013] Construct and optimize the multi-dimensional model candidate based on the multi-dimensional model candidate table;

[0014] Traverse the multi-dimensional model candidate table and output the multi-dimensional model.

[0015] As a preferred technical solution, the function dependency candidate table is represented as: Cand-FD(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 table identifier to which the function dependency belongs, FD_Left represents the left part of the function dependency expression, FD_Right represents the right part of the function dependency expression, and Conf-FD represents the data confidence of the function dependency;

[0016] The multi-dimensional model candidate table is represented as: Cand-MD(MD_id, T_id, Attr, P-node), where MD_id represents the identifier of the multi-dimensional model, T_id represents the fact table or dimension table identifier, Attr represents the attribute identifier, and P-node represents the parent node attribute identifier of Attr;

[0017] The semantic confidence calculation table for the data in the attached table is expressed as: Conf-DS(ST_id, Attr1, …, Attr n , Conf-ST, Incon), where ST_id is the primary key of the attached table, and Attr i represents the i-th attribute of the attached table, Conf-ST represents the data confidence of this record for a certain functional dependency, and Incon represents the inconsistent flag of this record with a certain functional dependency.

[0018] As a preferred technical solution, the functional dependency relationship and its candidate confidence between the center points included in the link table are written into the identifier FD_id of the functional dependency candidate table, the table identifier T_id to which the functional dependency belongs, the left part FD_Left of the functional dependency expression, the right part FD_Right of the functional dependency expression, and the data confidence Conf-FD of the functional dependency respectively according to the functional dependency candidate expression identifier, the table identifier to which it belongs, and the expression pair <business key i, business key j> and the data confidence exceeding the data semantic confidence threshold.

[0019] As a preferred technical solution, select the link table in the power grid DV data warehouse, start traversing from the relevant center point table, generate the fact table attributes and dimension table attributes and their functional dependency relationships, and store the attribute identifier and its functional dependency relationship into the multi-dimensional model candidate table and the functional dependency candidate table respectively. The specific steps include:

[0020] Store the link table nodes and their attached table attributes into the multi-dimensional model candidate table;

[0021] Store the functional dependency relationship between the link table nodes and their attached table attributes into the functional dependency candidate table;

[0022] If it is determined that there is a connection center point in the DV model that has not been traversed for this link table, then traverse the center point nodes related to the link table;

[0023] Store the center point business key into the multi-dimensional model candidate table;

[0024] Store the functional dependency relationship between the link table and this business key into the functional dependency candidate table;

[0025] If it is determined that the current center point has not been traversed, then enter and traverse all the attachments of the center point;

[0026] Until all the center points of the current link table are traversed, and all the link tables in the DV model are traversed.

[0027] As a preferred technical solution, construct a Bayesian network structure for the data in the semantic confidence calculation table of the attached table to obtain the dependency relationship between nodes. The specific steps include:

[0028] Take each attribute of the data semantic confidence calculation table as a node, calculate the information gain between the nodes to obtain the correlation between the nodes, calculate the size of the value range of each node, and take the node with the larger value range as the parent node. After sorting, obtain the Bayesian network structure and get the dependency relationship between the nodes.

[0029] As a preferred technical solution, detect the establishment mode of the data semantic confidence calculation table data of the attached table for the function dependency candidate. The specific steps include:

[0030] For a function dependency candidate of the attached table in the function dependency candidate table, if there are records on the corresponding two attributes in the attached table data semantic confidence calculation table that satisfy its function dependency candidate expression, then the records of the left part value and the right part value of the function dependency candidate expression form the establishment mode of the current function dependency.

[0031] For the records in the data semantic confidence calculation table that conform to the establishment mode, set and update the corresponding flags.

[0032] As a preferred technical solution, for the function dependency candidates of the attached table in the function dependency candidate table, calculate their data confidence based on each record in the attached table data semantic confidence calculation table. The specific steps include:

[0033] According to the relationship between the nodes and the parent nodes in the Bayesian network, calculate the number of occurrences of the instance value a of each attribute, and calculate the conditional probability:

[0034]

[0035] Calculate the data confidence of each attribute instance value a of each record in the attached table data semantic confidence calculation table, which is specifically expressed as:

[0036]

[0037] Perform normalization processing, which is expressed as:

[0038]

[0039] Calculate the data confidence of the records in the attached table data semantic confidence calculation table and write it into the corresponding attributes of the attached table data semantic confidence calculation table until all records are calculated. The specific formula for the data confidence is expressed as:

[0040] Conf-ST=min(Conf_Attr i ′(a),a∈FD_left∪FD_right)

[0041] Among them, Conf-ST represents the data confidence of a record for a certain functional dependency in the calculation table of the semantic confidence of the affiliated table data, FD_left represents the left part of the functional dependency expression, and FD_Right represents the right part of the functional dependency expression.

[0042] As a preferred technical solution, a flag is set in the calculation table of the semantic confidence of the affiliated table data, and the flag is used to mark whether the data record is consistent with the functional dependency;

[0043] For the records in the calculation table of the semantic confidence of the affiliated table data that conform to the establishment mode, set the corresponding flag to the marked value;

[0044] Set the data semantic confidence threshold and the threshold of the effective record ratio of the link table;

[0045] For the calculation table of the semantic confidence of the affiliated table data, if the data confidence of a certain data record for a certain functional dependency reaches the data semantic confidence threshold, set the corresponding flag to the marked value;

[0046] Statistically calculate the ratio of the number of data records corresponding to the marked value of the flag to the total number of data records. When the ratio reaches the threshold of the effective record ratio of the link table, update the data confidence of the functional dependency candidate corresponding to the functional dependency candidate table.

[0047] As a preferred technical solution, construct and optimize the multi-dimensional model candidate based on the multi-dimensional model candidate table. The specific steps include:

[0048] Determine the fact table and fact metrics;

[0049] According to the naming rules of the power grid business entities, merge the secondary business entities into the primary business entities;

[0050] Store the functional dependency relationship of the date dimension into the functional dependency candidate table;

[0051] Determine the dimension table and hierarchy;

[0052] Store the date dimension into the multi-dimensional model candidate table.

[0053] As a preferred technical solution, determine the dimension table and hierarchy. The specific steps include:

[0054] For the multi-dimensional model candidate table, find the records whose table identifier of the functional dependency belongs to the central point table identifier, and set the parent node attribute identifier P-node with the attribute Attr as the business key to the table LK i Primary key, and set the parent node attribute identifier P-node with the attribute Attr as the non-primary attribute to the business key of the central point, LK i Indicates the i-th link table connected to the central point in the power grid DV data warehouse;

[0055] For the multidimensional model candidate table, search for records whose table identifier of the function dependency is the center table identifier and whose attribute Attr is a non-primary attribute, and search for function dependency records with the same center table identifier in the corresponding function dependency candidate table Cand-FD. If there are records that meet the preset rules, place the left value of the function dependency in the function dependency candidate table Cand-FD into the parent node attribute identifier P-node of the attribute Attr;

[0056] For records in the function dependency candidate table Cand-FD whose left side of the function dependency is a non-primary attribute, if there are m records in the function dependency candidate table Cand-FD whose right side values are equal to the attribute Attr value of the same center point in the multidimensional model candidate table, then m-1 records of the attribute Attr are copied and added to the multidimensional model candidate table, and the m left side values in the function dependency candidate table Cand-FD corresponding to the attribute Attr record are respectively placed in the parent node attribute identifier P-node of the m records of the attribute Attr.

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

[0058] (1) The many-to-many relationship of the existing power grid DV data warehouse and the data inconsistency caused by the data input from multiple source business systems will interfere with the search for functional dependencies and their transmission paths, and there is a lack of relevant solutions. The present invention uses Bayesian networks and data semantic confidence methods to discover the functional dependencies between business entity attributes in DV subsidiary table data, constructs a method for automatically extracting implicit functional dependencies, supports the generation of dimensional hierarchies, and thus lays the foundation for generating multidimensional models.

[0059] (2) The existing method of constructing a multidimensional model from a relational database usually adopts statistical confidence and manual methods, and often finds a large number of approximately valid but invalid functional dependencies in massive data, and the model conversion is complex and inefficient; in the power grid DV data warehouse, the links without attached tables imply the functional dependencies between entities, and the center point attached tables imply the functional dependencies within entities. The present invention utilizes the functional dependencies found from the power grid DV data warehouse, and generates multidimensional model candidates by traversing the three tables of the center point table, link table and attached table of the DV model, and simplifies and merges the multidimensional models with the help of the naming rules and functional dependencies of the power grid DV, and finally automatically generates the multidimensional model with high efficiency and high quality. BRIEF DESCRIPTION OF THE DRAWINGS

[0060] Figure 1 It is a flow chart of a method for automatically generating a multidimensional model based on a power grid DV data warehouse according to the present invention;

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

[0062] Figure 3 Schematic diagram of the implicit functional dependencies and confidence levels between the central points included in the link table of the present invention;

[0063] Figure 4 Schematic diagram of the multi-dimensional model candidate after traversing the relevant central points and their appendages in the link table of the present invention;

[0064] Figure 5 Schematic diagram of the functional dependency candidate after traversing the relevant central points and their appendages in the link table of the present invention;

[0065] Figure 6 Schematic diagram of the appendage table data in the data semantic confidence level calculation table of the present invention;

[0066] Figure 7 Schematic diagram of the data in the functional dependency candidate table after generating the functional dependency candidate of the present invention;

[0067] Figure 8 Schematic diagram of the calculation process of the Conf-ST confidence level of FD: City → Province / City in the present invention;

[0068] Figure 9 Schematic diagram of the calculation result of the Conf-ST confidence level of FD: City → Province / City in the present invention;

[0069] Figure 10 Schematic diagram of the calculation result of the Conf-ST confidence level of FD: Address → City in the present invention;

[0070] Figure 11 Schematic diagram of the calculation result of the Conf-ST confidence level of FD: Address → Province / City in the present invention;

[0071] Figure 12 Schematic diagram of the data in the functional dependency candidate table after obtaining the functional dependency candidate of the appendage table in the present invention;

[0072] Figure 13 Schematic diagram of the functional dependency candidate table Cand-FD after adding the date dimension in the present invention;

[0073] Figure 14 Schematic diagram of the functional dependency candidate table Cand-MD after generating the date dimension in the present invention. Detailed implementation manners

[0074] In order to make the objectives, technical solutions and advantages of the present invention clearer and more understandable, 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.

[0075] Embodiment 1

[0076] As shown Figure 1 in the figure, this embodiment provides a method for automatically generating a multi-dimensional model based on a power grid DV data warehouse. From the DV data warehouse suitable for storing massive power grid data, with the help of data semantics, functional dependencies, and their traversal path methods, a multi-dimensional model suitable for power grid user analysis is automatically constructed. First, use the DV modeling method to construct a power grid data warehouse and load data. Among them, three types of data tables store power grid marketing data, production data, material data, etc.; then, based on the FD establishment mode and data semantics method, obtain the implicit FD from the affiliated tables; then, according to the characteristics of power grid business, based on the link table, traverse the relevant central points and link FD paths to construct a candidate multi-dimensional model (a multi-dimensional model is only based on one link table with an affiliated table); finally, according to the confidence level specified by the user, evaluate and optimize to obtain an effective multi-dimensional model, specifically including the following steps:

[0077] 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 function dependency candidates of the link table and their confidence levels have been obtained; in addition, set a data semantics confidence level threshold α and a link table effective record ratio threshold β. The former is the standard for determining the data record semantics and function dependency establishment of the link table linking the central point table, and the latter indicates the effective record ratio that should be possessed in the link table.

[0078] In this embodiment, the preferred values of the data semantics confidence level threshold α and the link table effective record ratio threshold β are both 0.6;

[0079] In this embodiment, the central point table, link table, and affiliated table of the power grid DV model store business entities, relationships, and their attribute data respectively; the central point table is based on the business key to connect the link table and the affiliated table to form a central radiation architecture, connecting all data, and the link table supports storing many-to-many relationships;

[0080] As Figure 2As shown in the figure, the three types of tables, namely the central point table, the link table, and the affiliated table, respectively store the power grid business entities, relationships, and attribute data of the central point / link. In the figure, there are the power grid customer central point table Hub_Cust (with the customer affiliated table Sat_Customer and the customer address affiliated table Sat_CustAddr), the power grid customer classification central point table Hub_CustCls (with the customer classification affiliated table Sat_CustCls), the electricity contract central point table Hub_Contract (with the contract affiliated table Sat_Contract), and the service central point table Hub_Service (with the service affiliated table Sat_Service). There are also the customer-classification link table Lnk_Cust-Cls, the customer-contract link table Lnk_Cust-Cont, and the electricity usage status link table Lnk_Usage (with the electricity usage status affiliated table Sat_Usage).

[0081] In this embodiment, a central point or link may have multiple affiliates. Each affiliate forms historical records of different periods of relevant attributes according to the timestamp (Load_Date). Sequential data of relevant attributes of multiple power grid source business systems can be stored under a central point. At the same time, the record source (Rec_Source) attribute is stored in all three types of tables, which can independently store the data of all power grid source business systems and can also store the inconsistent data brought by multiple power grid source business systems.

[0082] In this embodiment, corresponding data is loaded from multiple source business systems into the above-mentioned power grid DV data warehouse model, which maximally meets the requirements of a centralized, highly scalable, and highly available power grid data platform for the organization of massive power grid data. At the same time, it supports whole-network type and cross-domain data integration, as well as dynamic on-demand supply and real-time resource allocation.

[0083] The power grid DV mode is suitable for efficiently storing massive power grid data, but it is not conducive to users' data analysis. Therefore, in the DV data warehouse environment, a multi-dimensional model based on fact tables and dimension tables is established to support end-users' data analysis. When constructing the multi-dimensional model, it is necessary to clarify the functional dependency relationships between business entities and within entities, that is, it is necessary to find out the functional dependency relationships in the link table and the central point affiliated table.

[0084] S2: Establish a functional dependency candidate table, a multi-dimensional model candidate table, and an affiliated table data semantic confidence calculation table. According to the characteristics of the power grid DV data warehouse, follow the following steps to construct these three types of tables:

[0085] S21: Establish a candidate table for functional dependencies Cand - FD(FD_id, T_id, FD_Left, FD_Right, Conf - FD), where FD_id represents the identifier of the functional dependency expression, T_id represents the table identifier to which the functional dependency belongs, FD_Left represents the left part of the functional dependency expression FD_id, FD_Right represents the right part of the functional dependency expression FD_id, and Conf - FD represents the data confidence of the functional dependency.

[0086] S22: Write the FD (h→h) and its candidate confidence between the possible central points in the link table into the FD_id, T_id, FD_Left, FD_Right, and Conf - FD fields of the candidate table for functional dependencies Cand - FD according to the identifier of the candidate functional dependency expression, the belonging table identifier (such as the link table "LK"), and the expression pair <business key i, business key j> and data confidence (≥α).

[0087] In this embodiment, according to the characteristics of the three types of tables in the DV model, there are clear FD relationships between the subsidiary table and the central table, the subsidiary table and the link table, and the link table and the central point table, namely S→H, S→L, and L→H. At the same time, there are various implicit FD relationships in the link table and the subsidiary table. According to the data characteristics of the power grid DV data warehouse, since the subsidiary S of the central point table H or the link table L is used to store the historical data of the power grid business attribute values, it can be determined that at each time point, at most one s∈S tuple is valid for each h∈H or l∈L tuple, that is, h→s or l→s; in addition, the central point H is connected to at least one link L, that is, l→h. Therefore, for each subsidiary and central point in the power grid DV data warehouse, there is a link, and through this link, at most two FDs (directed lines) can be passed to reach this subsidiary or central point, that is, l→h→s. To sum up, when constructing a multi - dimensional model based on the power grid DV data warehouse, it should start from the fact node of a link table and use the traversal path method of FDs to discover the measure set and the dimension set.

[0088] Among them, for multiple attribute groups in a data table R, if an attribute group X can uniquely determine another attribute group Y, then Y is functionally dependent on X. If each tuple of table H is functionally dependent on P, then P can be used as the primary key of H. If an attribute group F of table S is not the primary key of S but the primary key of another table H then F is the foreign key of S, that is, the FD is denoted as F→P. In the present invention, when there is no confusion, this FD is also denoted as S→H.

[0089] For a multi-dimensional model, it can be represented as a directed acyclic graph composed of nodes and directed lines, where the nodes represent attributes and the directed lines represent the FD relationships between two attributes; there is a fact node f in this directed graph, and each other node can be reached from f through a directed path; these paths and nodes can be divided into a measure set and a dimension set, the measure set contains numerical leaf nodes, and the dimension set contains several non-aggregatable descriptive attributes and aggregatable non-numerical attributes.

[0090] As Figure 3 shown, this embodiment obtains the FD (h→h) contained in the existing link table and its candidate confidence.

[0091] S23: Establish a multi-dimensional model candidate table Cand-MD (MD_id, T_id, Attr, P-node), where MD_id represents the identifier of the multi-dimensional model, T_id represents the identifier of the fact table (link table) or dimension table (central point table), Attr represents the attribute identifier, and P-node represents the parent node of Attr.

[0092] S24: Establish an affiliated table data semantic confidence calculation table Conf-DS (ST_id, Attr1,..., Attr n , Conf-ST, Incon), where ST_id is the primary key of the affiliated table, Attr i represents the i-th attribute of the affiliated table (1 ≤ i ≤ n), Conf-ST represents the data confidence of this Conf-DS record (copied from a certain affiliated table record) for a certain functional dependency, and Incon represents the inconsistency flag of this Conf-DS record with a certain functional dependency.

[0093] S3: Traverse the relevant central points of the link table to generate the fact table attributes, dimension table attributes, and their relationships: Select a link table LK i (with an affiliated table) in the power grid DV data warehouse, start traversing from the relevant central points, and store the relevant nodes (attribute identifiers) and their directed lines (FD relationships) into the multi-dimensional model candidate table Cand-MD and the functional dependency candidate table Cand-FD respectively.

[0094] S31: Store the link table nodes and their affiliated table attributes into the multi-dimensional model candidate table Cand-MD: Store the record ("MD-LK", "LK i ", the primary key of table LK i ,""), and several attributes of the affiliated table corresponding to the link table LK i into the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", "LK i ", measure name,"").

[0095] In this embodiment, MD in MD-LK represents multi-dimensional prefix, and LK represents link list; LK i represents the identifier of link list i;

[0096] S32: Store the functional dependency FD(l→s) between the link list node and its affiliated table attributes into the functional dependency candidate table Cand-FD: Record several functional dependencies FD between the link list and the attributes (sequence number, "LK i ", table LK i primary key, measure name, 1) into the functional dependency candidate table Cand-FD.

[0097] S33: If there is still a connection center point in the DV model for this link list that has not been traversed, then enter and traverse the center point nodes related to LK i :

[0098] a) Store the center point business key in the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", center point table identifier, business key identifier, "").

[0099] b) Store the functional dependency FD(l→h) between the link list and this business key into the functional dependency candidate table Cand-FD: Record the functional dependency FD (sequence number, center point table identifier, table LK i primary key, business key identifier, 1) into the functional dependency candidate table Cand-FD.

[0100] c) If this center point has not been traversed yet, then enter and traverse all the affiliates of the center point:

[0101] i) Store several affiliated attributes of this center point in the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", center point table identifier, affiliated attribute, "").

[0102] ii) Store the FD(h→s) between the center point business key and the affiliated attributes into the functional dependency candidate table Cand-FD: Record several FD records (sequence number, center point table identifier, business key identifier, affiliated attribute, 1) into the functional dependency candidate table Cand-FD.

[0103] iii) If there is a link list connected to this center point in the DV model, check whether this link list has an affiliated table.

[0104] If this link list has no affiliated table and there is an implicit FD (confidence level ≥α) of this link list in the functional dependency candidate table Cand-FD, then (skip this link list) go to S33 of this step to continue traversing the link list related to LK iAn indirectly related central point node, where the implicit FD in the link table in the function dependency candidate table Cand-FD indicates that there is a function dependency between the business keys of the two central points (entities) connected by the link table;

[0105] d) Check whether there are central points in this link table that have not been traversed. If so, go to step S33 of this procedure.

[0106] S34: If there are other link tables in the DV model that have not been traversed, go to step S31 of this procedure.

[0107] In this embodiment, select the contract-service link table Lnk_Cont-Serv with an attached table in the power grid DV data warehouse. First, start traversing the relevant tables from the electricity contract central point table Hub_Contract, and then traverse the service central point table Hub_Service and its relevant tables, and write the relevant attributes and function dependency FD into the multi-dimensional model candidate table Cand-MD and the function dependency candidate table Cand-FD respectively.

[0108] 1) Store the link table node and its attached table attributes in Cand-MD: Store the record ("MD-LK", "Lnk_Cont-Serv", "Cont-Serv_id", "") and the two attributes of the attached table Sat_Cont-Serv of the contract-service link table Lnk_Cont-Serv in the form of records ("MD-LK", "Lnk_Cont-Serv", "electric quantity", ""), ("MD-LK", "Lnk_Cont-Serv", "amount", "") into the table Cand-MD.

[0109] 2) Store the FD (l→s) of the link table node of the contract-service link table Lnk_Cont-Serv and its Sat_Cont-Serv attached table attributes in the function dependency candidate table Cand-FD: Store the FD records ("FD003", "Lnk_Cont-Serv", "Cont-Serv_id", "electric quantity", 1), ("FD004", "Lnk_Cont-Serv", "Cont-Serv_id", "amount", 1) into the function dependency candidate table Cand-FD. These are the measure sets.

[0110] 3) The electricity contract central point table Hub_Contract connected in the contract-service link table Lnk_Cont-Serv has not been traversed. Therefore, first traverse the central point node Hub_Contract related to the contract-service link table Lnk_Cont-Serv and its attachments. These are the dimension sets:

[0111] a) Store the central point service key Cont_key in the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", "Hub_Contract", "Cont_key", "").

[0112] b) Store the FD (l→h) of the link table and this service key in the functional dependency candidate table Cand-FD: Store the FD record ("FD005", "Lnk_Cont-Serv", "Cont-Serv_id", "Cont_key", 1) in the functional dependency candidate table Cand-FD.

[0113] c) Since the contract attachment table Sat_Contract of the power consumption contract central point table Hub_Contract has not been traversed, traverse this attachment table:

[0114] i) As Figure 4 shown, store several of these central point attachment attributes in the multi-dimensional model candidate table Cand-MD in the form of records ("MD-LK", "Hub_Contract", "power consumption address", ""), ("MD-LK", "Hub_Contract", "total amount", ""), ("MD-LK", "Hub_Contract", "discount", "").

[0115] ii) As Figure 5 shown, store the FD (h→s) of the central point service key and the attachment attributes in the functional dependency candidate table Cand-FD: Store several FD records ("FD006", "Hub_Contract", "Cont_key", "power consumption address", 1), ("FD007", "Hub_Contract", "Cont_key", "total amount", 1), ("FD008", "Hub_Contract", "Cont_key", "discount", 1) in the functional dependency candidate table Cand-FD.

[0116] iii) In addition to the contract-service link table Lnk_Cont-Serv, the power consumption contract central point table Hub_Contract is also connected to the customer-contract link table Lnk_Cust-Cont. This link table has no attachment table and there is an implicit FD of this link table in the functional dependency candidate table Cand-FD: Cont_key→Cust_key. Then go to this step S33 and continue to traverse the power grid customer central point table Hub_Cust indirectly related to the contract-service link table Lnk_Cont-Serv.

[0117] Repeat S33 to continue traversing the power grid customer center point table Hub_Cust and its appendages indirectly related to the contract-service link table Lnk_Cont-Serv:

[0118] a) Store the center point service key Cust_key in the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", "Hub_Cust", "Cust_key", "").

[0119] b) Store the FD (l→h) of the link table and this service key in the functional dependency candidate table Cand-FD: Store the FD record ("FD009", "Lnk_Cont-Serv", "Cont-Serv_id", "Cust_key", 1) in the functional dependency candidate table Cand-FD.

[0120] c) Since the appendage tables Sat_Customer and Sat_CustAddr of the power grid customer center point table Hub_Cust have not been traversed, traverse these 2 appendage tables:

[0121] i) Store several appendage attributes of this center point in the multi-dimensional model candidate table Cand-MD in the form of records ("MD-LK", "Hub_Cust", "Name", ""), ("MD-LK", "Hub_Cust", "Phone", ""), ("MD-LK", "Hub_Cust", "Email", ""), ("MD-LK", "Hub_Cust", "Province / City", ""), ("MD-LK", "Hub_Cust", "City", ""), ("MD-LK", "Hub_Cust", "Address", "").

[0122] ii) Store the FD (h→s) of the center point service key and appendage attributes in the functional dependency candidate table Cand-FD: Store several FD records ("FD010", "Hub_Cust", "Cust_key", "Name", 1), ("FD011", "Hub_Cust", "Cust_key", "Phone", 1), ("FD012", "Hub_Cust", "Cust_key", "Email", 1), ("FD013", "Hub_Cust", "Cust_key", "Province / City", 1), ("FD014", "Hub_Cust", "Cust_key", "City", 1), ("FD015", "Hub_Cust", "Cust_key", "Address", 1) in the functional dependency candidate table Cand-FD.

[0123] iii) In addition to the customer - contract link table Lnk_Cust-Cont, the power grid customer center point table Hub_Cust is also connected to the customer - classification link table Lnk_Cust-Cls. This link table has no subordinate tables and there is an implicit FD of this link table in the candidate table of functional dependencies Cand-FD: Cust→Clas. Then, go to this step S33 and continue to traverse the center point table Hub_CustCls that is indirectly related to the contract - service link table Lnk_Cont-Serv.

[0124] Repeat S33 and continue to traverse the center point table Hub_CustCls that is indirectly related to the contract - service link table Lnk_Cont-Serv and its subordinates:

[0125] a) Store the center point business key Clas_key in the multi - dimensional model candidate table Cand-MD in the form of a record ("MD-LK", "Hub_CustCls", "Clas_key", "").

[0126] b) Store the FD (l→h) of the link table and this business key in the candidate table of functional dependencies Cand-FD: Store the FD record ("FD016", "Lnk_Cont-Serv", "Cont-Serv_id", "Clas_key", 1) in the candidate table of functional dependencies Cand-FD.

[0127] c) Since the subordinate table Sat_CustCls of the center point table Hub_CustCls has not been traversed, this subordinate table needs to be traversed:

[0128] i) Store several of these center point subordinate attributes in the multi - dimensional model candidate table Cand-MD in the form of records ("MD-LK", "Hub_CustCls", "Industry", ""), ("MD-LK", "Hub_CustCls", "Power consumption mode", "").

[0129] ii) Store the FD (h→s) of the center point business key and the subordinate attributes in the candidate table of functional dependencies Cand-FD: Store several functional dependency FD records ("FD017", "Hub_CustCls", "Clas_key", "Industry", 1), ("FD018", "Hub_CustCls", "Clas_key", "Power consumption mode", 1) in the candidate table of functional dependencies Cand-FD.

[0130] iii) In addition to the customer - classification link table Lnk_Cust-Cls, the center point table Hub_CustCls is not connected to other link tables, and the repeat loop ends.

[0131] d) The customer-classification link table Lnk_Cust-Cls has no central point to traverse.

[0132] d) The customer-contract link table Lnk_Cust-Cont has no central point to traverse.

[0133] d) The contract-service link table Lnk_Cont-Serv still has the service central point table Hub_Service to traverse, go to this step S33.

[0134] Repeat S33 to continue traversing the service central point table Hub_Service and its appendages indirectly related to the contract-service link table Lnk_Cont-Serv:

[0135] a) Store the central point business key Serv_key in the multi-dimensional model candidate table Cand-MD in the form of a record ("MD-LK", "Hub_Service", "Serv_key", "").

[0136] b) Store the FD (l→h) of the link table and this business key in the functional dependency candidate table Cand-FD: Store the FD record ("FD019", "Lnk_Cont-Serv", "Cont-Serv_id", "Serv_key", 1) in the functional dependency candidate table Cand-FD.

[0137] c) Since the service central point table Hub_Service and its service appendage table Sat_Service have not been traversed yet, this appendage table needs to be traversed:

[0138] i) Store several appendage attributes of this central point in the multi-dimensional model candidate table Cand-MD in the form of records ("MD-LK", "Hub_Service", "Service name", ""), ("MD-LK", "Hub_Service", "Measurement method", "").

[0139] ii) Store the FD (h→s) of the central point business key and appendage attributes in the functional dependency candidate table Cand-FD: Store the FD records ("FD020", "Hub_Service", Serv_key, "Service name", 1), ("FD021", "Hub_Service", Serv_key, "Measurement method", 1) in the functional dependency candidate table Cand-FD.

[0140] iii) Except for the contract-service link table Lnk_Cont-Serv, the service central point table Hub_Service is not connected to other link tables, and the repeated loop ends.

[0141] d) There are no other central points in the contract - service link table Lnk_Cont - Serv that need to be traversed.

[0142] 4) There are no other link tables in the power grid DV model that need to be traversed, and this step ends.

[0143] S4: Obtain the implicit functional dependencies in the central point affiliated table: Select a central point affiliated table in the power grid DV data warehouse (processing one affiliated table each time), store the data of this affiliated table in the affiliated table data semantic confidence calculation table Conf - DS, generate all the functional dependency candidate expressions of this affiliated table in the functional dependency candidate table Cand - FD, and calculate the confidence of each functional dependency candidate.

[0144] S41: Input the data in the affiliated table data semantic confidence calculation table Conf - DS: Input all the data of the latest loading date of this affiliated table in the power grid DV data warehouse, and write them into ST_id, Attr1,..., Attr of the affiliated table data semantic confidence calculation table Conf - DS n 。

[0145] S42: Generate the functional dependency candidate relationship FD(s→s): Construct candidate FDs for the non - primary attributes and non - numerical attributes of this affiliated table: Attr i →Attr j where i≠j; if the number of values of two attributes in the affiliated table data semantic confidence calculation table Conf - DS |Attr i |<|Attr j |, then delete this functional dependency candidate; Record several candidate FDs in the form of (sequence number, central point table identifier, Attr i ,Attr j ,0) and write them into the functional dependency candidate table Cand - FD.

[0146] S43: Construct a Bayesian network structure for the data in the data semantic confidence calculation table Conf - DS to obtain the dependency relationship between nodes: Take each attribute Attr i in the data semantic confidence calculation table Conf - DS as a node, calculate the information gain between nodes to obtain the correlation between nodes, and then calculate the size of the value range of each node. The node with a larger value range is used as the parent node. After sorting, obtain the Bayesian network structure and the dependency relationship between nodes. Among them, the information gain Attr j is the parent node of Attr i .

[0147] S44: Detect the establishment mode of the function dependency candidate of the subsidiary table data semantic confidence calculation table Conf-DS: For a function dependency candidate of the subsidiary table in the function dependency candidate table Cand-FD, if there are records that satisfy its function dependency candidate expression FD_Left→FD_Right on the corresponding two attributes in the subsidiary table data semantic confidence calculation table Conf-DS, that is, for the power grid business, the number of records with an FD_Left value having the same FD_Right value is greater than the number of records with different FD_Right values, then the records of the FD_Left value and the FD_Right value constitute the establishment mode of the function dependency; for the records that meet the establishment mode, set the corresponding Incon to 0, otherwise it is 1.

[0148] S45: For the function dependency candidate FD, the data confidence is calculated record by record based on the attached table data semantic confidence calculation table Conf-DS to obtain the data confidence Conf-ST value.

[0149] a) Calculate conditional probability: Calculate each attribute Attr according to the relationship between nodes and parent nodes in the Bayesian network i The number of times the instance value a appears is used to calculate the conditional probability according to the following formula:

[0150]

[0151] b) Calculate each Attr i The data confidence of the attribute instance value a is normalized: The following formula is used to calculate the attribute Attr of each record in the attached table data semantic confidence calculation table Conf-DS: i The data confidence of instance value a is normalized.

[0152]

[0153] Conf_Attr i (a) Normalization formula:

[0154]

[0155] c) Data confidence Conf-ST of the record in the subsidiary table data semantic confidence calculation table Conf-DS: Calculate the data confidence according to the following formula and write the corresponding attribute of the subsidiary table data semantic confidence calculation table.

[0156] Conf-ST=min(Conf_Attr i ′(a),a∈FD_left∪FD_right)

[0157] d) Repeat steps a) to c) to calculate the data confidence of other records until all records in the data semantic confidence calculation table Conf-DS of the attached table are processed.

[0158] e) Calculate the data confidence value of Conf-FD for the functional dependency candidate relationship FD in the functional dependency candidate table Cand-FD.

[0159] i) For the data semantic confidence calculation table Conf-DS, if the data confidence value Conf-ST of a certain data record for a certain functional dependency < α, then set Incon of this record to 1.

[0160] ii) For the data semantic confidence calculation table Conf-DS, if the number of data records with Incon = 0 / the total number of records ≥ the effective record ratio β, then for the corresponding functional dependency candidate Conf-FD in the functional dependency candidate table Cand-FD = the sum of the Conf-ST values of the data records / the total number of records.

[0161] S46: If there are other functional dependency candidate relationships FD of this attached table in the functional dependency candidate table Cand-FD that have not been processed, then go to step S44 of this step to execute.

[0162] S47: Repeat this step to obtain the implicit functional dependencies of other central point attached tables until all central point attached tables are processed.

[0163] In this embodiment, a central point attached table Sat_CustAddr in the power grid DV data warehouse is selected for illustration. The data of this attached table is saved in the data semantic confidence calculation table Conf-DS, and all functional dependency candidate expressions of this attached table are generated in the functional dependency candidate table Cand-FD, and the confidence of each functional dependency candidate is calculated.

[0164] 1) Input the data of the data semantic confidence calculation table Conf-DS: Input all the data of the latest loading date (Load_Date) of the Sat_CustAddr attached table in the power grid DV data warehouse, and store them in the data semantic confidence calculation table Conf-DS (ST_id, province and city, city, address) respectively, as Figure 6 shown.

[0165] 2) Generate candidate FD(s→s): Construct candidate FD for the non-primary attributes and non-numerical attributes of this attached table: Attr i →Attr j , where i ≠ j; if the number of two attribute values in the data semantic confidence calculation table Conf-DS of the attached table |Attr i | < |Attr j |, then delete this functional dependency candidate;

[0166] In this embodiment, three function dependency candidates FD are found: City → Province / City, Address → City, Address → Province / City. As Figure 7 shown, write them into the function dependency candidate table Cand-FD in the form of (sequence number, "Hub_Cust", Attr i , Attr j , 0).

[0167] 3) Construct a Bayesian network structure for the data in the data semantic confidence calculation table Conf-DS to obtain the dependency relationship between nodes: For each attribute Attr i in the affiliated table data semantic confidence calculation table Conf-DS, as a node, first calculate the information gain between nodes to obtain the correlation between nodes, and then calculate the size of the value range of each node. The one with a larger value range is used as the parent node. After sorting, the Bayesian network structure is obtained, and the dependency relationship between nodes is obtained.

[0168] In this embodiment, by calculating the information gain (IG) between the nodes "Province / City", "City" and "Address", we can get: IG(Province / City, City) = H(Province / City) - H(Province / City|City) = 0.41, IG(City, Address) = H(City) - H(City|Address) = 1.32, IG(Province / City, Address) = H(Province / City) - H(Province / City|Address) = 0.71,; The value range of each node: |Province / City| = 2, |City| = 4, |Address| = 8. According to the information gain between nodes and the size of the value range of each node, the one with a larger value range is used as the parent node, and the dependency relationship between nodes is obtained as: Address → City → Province / City.

[0169] 4) For a function dependency candidate of a certain affiliated table, detect the establishment mode of the data in the data semantic confidence calculation table Conf-DS for the function dependency candidate:

[0170] For a function dependency candidate of this affiliated table in the function dependency candidate table Cand-FD, if there are records that satisfy its expression FD_Left → FD_Right on the corresponding two attributes in the data semantic confidence calculation table Conf-DS, that is, for the power grid service, the number of records with the same FD_Right value for an FD_Left value is greater than the number of records with different FD_Right values, then the records with the FD_Left value and the FD_Right value form the establishment mode of this function dependency; for the records that conform to the establishment mode, set the corresponding Incon to 0, otherwise to 1.

[0171] In this embodiment, the function dependency candidates of "city → province / municipality", "address → city", and "address → province / municipality" are detected. For "city → province / municipality", CA003 and CA010 do not conform to the establishment pattern; for "address → city", CA003 does not conform to the establishment pattern; for "address → province / municipality", all records conform to the establishment pattern. For the records that do not conform to the establishment pattern, set Incon to 1, otherwise set it to 0.

[0172] 5) For this candidate FD, calculate the data confidence of each record in the data semantic confidence calculation table Conf-DS of the affiliated table based on the data semantics, and obtain the data confidence Conf-ST value.

[0173] a) Calculate the conditional probability: According to the relationship between nodes and parent nodes in the Bayesian network, calculate the number of occurrences of each Attri value a, and calculate the conditional probability according to the following formula:

[0174]

[0175] b) Calculate the data confidence of each Attri instance value a and normalize this value: Calculate the data confidence of each Attri instance value a of each record in the data semantic confidence calculation table Conf-DS of the affiliated table according to the following formula and perform normalization.

[0176]

[0177] For Conf_Attr i (a) The formula for normalization:

[0178]

[0179] c) Calculate the data confidence Conf-ST of this record in the data semantic confidence calculation table Conf-DS of the affiliated table: Calculate the data confidence according to the following formula and write it into the attribute Conf-ST.

[0180] Conf-ST = min(Conf_Attr i ′(a), a ∈ FD_left ∪ FD_right)

[0181] d) Continue to execute a) to c) to calculate the data confidence of other records until all records in the data semantic confidence calculation table Conf-DS of the affiliated table are processed.

[0182] In this embodiment, the calculation process and results of Conf-ST for FD: city → province / municipality are as Figure 8 、 Figure 9 shown.

[0183] e) For this candidate FD, calculate the confidence of the Conf-FD data in the candidate function dependency table Cand-FD.

[0184] i) For the affiliated table data semantic confidence calculation table Conf-DS, if the Conf-ST value of a certain data record < α, then set the Incon of this record to 1.

[0185] In this embodiment, in the affiliated table data semantic confidence calculation table Conf-DS, when the Conf-ST of ST_id = CA003 and CA010 < α = 0.6, then set the Incon value of these two records to 1.

[0186] ii) For the affiliated table data semantic confidence calculation table Conf-DS, if the number of data records with Incon = 0 / total number of records ≥ β = 0.6, then for the corresponding function dependency candidate Conf-FD in the candidate function dependency table Cand-FD = sum of the Conf-ST values of the data records / total number of records.

[0187] For this function dependency candidate Conf-FD corresponding to the candidate function dependency table Cand-FD in this embodiment = 0.62.

[0188] 6) If there are other candidate FDs of this affiliated table in the candidate function dependency table Cand-FD that have not been processed, then go to step 4) of this step.

[0189] In this embodiment, according to the above process, continue to verify the candidate FD of this affiliated table: address → city, and the calculation result of Conf-ST is as Figure 10 shown.

[0190] For the affiliated table data semantic confidence calculation table Conf-DS, the Conf-ST of each record in the table > 0.6; the corresponding function dependency candidate Conf-FD = sum of the Conf-ST values of the data records / total number of records = 0.85.

[0191] In this embodiment, according to the above process, continue to verify the candidate FD of this affiliated table: address → province and city, and the calculation result of Conf-ST is as Figure 11 shown.

[0192] For the affiliated table data semantic confidence calculation table Conf-DS, the Conf-ST of ST_id being CA003 and CA010 in the table < 0.6, set their Incon values to 1; then, if the number of data records with Incon = 0 / total number of records = 0.8 ≥ β = 0.6, for the corresponding function dependency candidate Conf-FD in the candidate function dependency table Cand-FD = sum of the Conf-ST values of the data records / total number of records = 0.62. Finally, as Figure 12As shown, the confidence of the implicit functional dependencies in the candidate table of functional dependencies Cand-FD is obtained.

[0193] S5: Based on the candidate table of multi-dimensional models Cand-MD, construct a multi-dimensional model candidate: starting from a fact table in the candidate table of multi-dimensional models Cand-MD, evaluate and optimize the multi-dimensional model candidate.

[0194] S51: Determine the fact table and fact metrics: For the candidate table of multi-dimensional models Cond_MD, find the record with T_id = "LK i ", and set "MD-LK" for the P-node whose Attr = the primary key of table LK i ; set the primary key of table LK i for those with the Attr value type being numeric.

[0195] S52: Dimension merging: According to the naming rules of power grid business entities, merge secondary business entities into primary business entities, such as merging the customer classification entity into the customer entity.

[0196] a) In the candidate table of multi-dimensional models Cand-MD, find all node records of the primary / secondary business entities. First, delete the records whose Attr of the secondary business entity is the business key, and then place the T_id value (center point identifier) of the primary business entity into the T_id of the secondary business entity;

[0197] b) In the candidate table of functional dependencies Cand-FD, find the FD(l→h) records of the primary / secondary business entities. First, delete the FD(l→h) records of the secondary business entity, and then find several FD(h→s) records of the secondary business entity. Place the T_id value (center point identifier) of the primary business entity FD into the T_id of the secondary business entity FD, and place the FD_Right value of the primary business entity FD(l→h) into the FD_Left of several secondary business entity FD(h→s);

[0198] c) In the candidate table of functional dependencies Cand-FD, find several FD(s→s) records of the secondary business entity, and place the T_id value (center point identifier) of the primary business entity FD(l→h) into the T_id of several FD(s→s) records of the secondary business entity.

[0199] S53: Store the functional dependency relationships FD(h→s, s→s) of the date dimension into the candidate table of functional dependencies Cand-FD: Store the date dimension FD according to the record (sequence number, "LK i ", table LK iThe primary key, "Date_id", 1) and several records (sequence number, "Date", "Date_id", date attribute, 1) are stored in the candidate functional dependency table Cand-FD; then several FDs (s→s) between date dimension attributes are stored in the candidate functional dependency table Cand-FD in the form of records (sequence number, "Date", date attribute i, date attribute j, 1) (i≠j).

[0200] S54: Determine the dimension table and hierarchy:

[0201] a) For the candidate multi-dimensional model table Cond_MD, find the records in which the table identifier T_id to which the functional dependency belongs is the center table identifier, and set the table LK for the parent node attribute identifier P-node of the entity's attribute Attr that is the business key i For the primary key, set the business key for the parent node attribute identifier P-node of the entity's attribute Attr that is a non-primary attribute

[0202] b) For the candidate multi-dimensional model table Cond_MD, find the records in which the table identifier T_id to which the functional dependency belongs is the center table identifier and the attribute identifier Attr is a non-primary attribute, and then find the functional dependency relationship FD(s→s) records in the corresponding candidate functional dependency table Cand-FD with the same center table identifier. If there is a case where Cand-FD.FD_Right = Cond_MD.Attr in these records, then place Cand-FD.FD_Left of the functional dependency candidate table into the parent node attribute identifier P-node of this attribute identifier Attr

[0203] c) For the records in the candidate functional dependency table Cand-FD where the left part FD_Left of the functional dependency expression is a non-primary attribute, that is, the functional dependency relationship FD(s→s) records, if there are m records in the candidate functional dependency table Cand-FD whose right part FD_Right values are equal to the attribute Attr value of the same center point in the candidate multi-dimensional model table Cand-MD, then copy and add m - 1 records of this attribute Attr in the candidate multi-dimensional model table Cand-MD, and place the left part FD_Left values of the m functional dependency expressions in the corresponding candidate functional dependency table Cand-FD of this attribute Attr record into the parent node attribute identifier P-node of the m records of this attribute Attr respectively

[0204] S55: Store the date dimension in the candidate multi-dimensional model table Cand-MD: Store the record ("MD-LK", "Date", "Date_id", table LK iThe primary key), and several date attributes are stored in the multi-dimensional model candidate table Cand-MD in the form of records ("MD-LK", "Date", date attributes, "Date_id"); then find several FD(s→s) records in the corresponding function dependency candidate table Cand-FD where T_id = "Date". If FD_Right = Attr and FD_Left ≠ "Date_id" in these records, then place the FD_Left of this record into the P-node of this Attr.

[0205] In this embodiment, starting from a fact table in the multi-dimensional model candidate table Cand-MD, the multi-dimensional model candidate is evaluated and optimized. The specific example is as follows:

[0206] 1) Determine the fact table and fact metrics: For the multi-dimensional model candidate table Cond_MD, find the records where T_id = "Lnk_Cont-Serv", set "MD-LK" for the P-node with Attr = "Cont-Serv_id"; set "Cont-Serv_id" for the Attr with a numeric value type.

[0207] 2) Dimension merging: According to the naming rules of power grid business entities, merge secondary business entities into primary business entities. For example, merge the customer classification entity into the customer entity.

[0208] a) Find all node records with T_id = "Hub_CustCls" in the multi-dimensional model candidate table Cand-MD. First, delete the records with T_id = "Hub_CustCls" and Attr = "Clas_key", and then change the value of T_id = "Hub_CustCls" to "Hub_Cust".

[0209] b) Find the FD(l→h) records of the primary / secondary business entities in the function dependency candidate table Cand-FD. Find the FD(l→h) record (FD016) with T_id = "Lnk_Cont-Serv" and delete it. Then, for several FD(h→s) records (FD017, FD018) with T_id = "Hub_CustCls", change their T_id to "Hub_Cust"; set the FD_Left of records FD017 and FD018 to "Cust_Key".

[0210] c) There are no FD(s→s) records of secondary business entities in the function dependency candidate table Cand-FD, so skip this step.

[0211] 3) Store the FDs (h→s, s→s) of the date dimension into the candidate function dependency table Cand-FD: Store the date dimension FDs in the form of records (sequence number, "Date", "LKi_id", Date_id, 1) and several records in the "month" and "year" attributes in the form of (sequence number, "Date", "Date_id", "date", 1); as Figure 13 shown, then store several FDs (s→s) between the date dimension attributes: date → month, month → year into the candidate function dependency table Cand-FD.

[0212] 4) Determine the dimension tables and hierarchies:

[0213] a) For the multi-dimensional model candidate table Cond_MD, find the central table identification records with T_id being the central table of the electricity contract center table Hub_Contract, the power grid customer center table Hub_Cust, and the service center table Hub_Service. Set "Cont-Serv_id" for the P-nodes with Attr being the business keys (Cont_key, Cust_key, Serv_key) of these three entities; set the P-nodes with Attr being non-primary attributes to the corresponding business keys, such as "electricity usage address", "total amount", and "discount", etc.

[0214] b) For the multi-dimensional model candidate table Cond_MD, find the records with T_id being the central table identification and Attr being non-primary attributes, and then find the FD (s→s) records with the same central table identification in the corresponding candidate function dependency table Cand-FD. If there are records with FD_Left being non-business keys and FD_Right = Attr among these records, then place FD_Left into the P-node of this Attr. For example, the P-nodes with Attr = "province and city", "city" are respectively placed with "city" and "address".

[0215] c) For the records in the candidate function dependency table Cand-FD with FD_Left being non-primary attributes, that is, FD (s→s) records, such as "city → province and city" and "address → province and city" with 2 records having the FD_Right value "province and city" equal to the Attr value "province and city" of the customer service center table in the multi-dimensional model candidate table Cand-MD, then copy and add 1 record with the Attr value "province and city" in the multi-dimensional model candidate table Cand-MD, and place "city" and "address" into the P-nodes of the Attr value "province and city" respectively.

[0216] 5) As Figure 14As shown, store the date dimension in the multi-dimensional model candidate table Cand-MD: In this embodiment, records such as "date", "month", and "year" are added, and P-nodes are set according to the FD(s→s) records in the function dependency candidate table Cand-FD.

[0217] S6: The above is for a link table LK i (with an attached table) The process of generating a multi-dimensional model candidate. If there are other link tables (with attached tables) in the power grid DV data warehouse, steps S3 - S6 can be repeated (it is necessary to clear all FDs generated by the link table LK i beforehand), and then generate another multi-dimensional model candidate.

[0218] In the above specific example, it involves the process of generating a multi-dimensional model candidate for a contract-service link table Lnk_Cont-Serv, and the multi-dimensional model candidate table Cand-MD is generated, which has a total of 24 nodes.

[0219] S7: Output the multi-dimensional model candidate table Cand-MD: From the multi-dimensional model candidate table Cand-MD, with the MD_id value of the multi-dimensional model's identifier as a multi-dimensional model, sequentially output the fact table or dimension table identifier T_id as the attributes of the link table and the center point table, Attr parent node P_node→Attr.

[0220] The present invention is oriented to the power grid DV data warehouse. According to the characteristics of power grid business data and the semantic characteristics of function dependencies, it adopts a method based on data semantics, function dependencies, and their traversal paths to automatically extract implicit function dependencies in the attached table, and uses function dependencies to traverse the metrics and dimension hierarchies of the multi-dimensional model, solving the technical problem of automatically generating a multi-dimensional model starting from the power grid business entity link table, and providing effective support for efficiently and high-quality constructing a multi-dimensional model.

[0221] 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. A method for automatically generating a multi-dimensional model based on a power grid DV data warehouse, characterized in that Including the following steps: Construct a power grid DV data warehouse, and use a central point table, a link table, and an affiliated table to store power grid business entities, relationships, and their attribute data respectively; Establish a function dependency candidate table, a multi-dimensional model candidate table, and an affiliated table data semantic confidence calculation table; The function dependency candidate table is expressed as: Cand-FD(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 table identifier to which the function dependency belongs, FD_Left represents the left part of the function dependency expression, FD_Right represents the right part of the function dependency expression, and Conf-FD represents the data confidence of the function dependency; The multi-dimensional model candidate table is expressed as: Cand-MD(MD_id, T_id, Attr, P-node), where MD_id represents the identifier of the multi-dimensional model, T_id represents the fact table or dimension table identifier, Attr represents the attribute identifier, and P-node represents the parent node attribute identifier of Attr; The calculation table for the semantic confidence of the attached table data is expressed as: Conf-DS(ST_id, Attr1, …, Attr n , Conf-ST, Incon), where ST_id is the primary key of the attached table, and Attr i represents the i-th attribute of the attached table, Conf-ST represents the data confidence of the record for a certain functional dependency, and Incon represents the flag indicating that the record is inconsistent with a certain functional dependency; Select the link table in the power grid DV data warehouse, traverse the central points related to the link table, find the fact table attributes and dimension table attributes and their function dependency relationships, and store the attribute identifiers and their function dependency relationships into the multi-dimensional model candidate table and the function dependency candidate table respectively; Select the central point affiliated table in the power grid DV data warehouse, store the affiliated table data in the affiliated table data semantic confidence calculation table, generate all function dependency candidate expressions of the affiliated table according to the rules, and calculate the confidence of each function dependency candidate using the affiliated table data; Construct a Bayesian network structure for the data in the affiliated table data semantic confidence calculation table, obtain the dependency relationships between nodes, detect the establishment mode of the data in the affiliated table data semantic confidence calculation table for the function dependency candidates, and calculate the data confidence of the function dependency candidates of the affiliated table in the function dependency candidate table item by item based on the affiliated table data semantic confidence calculation table; Construct and optimize the multi-dimensional model candidate based on the multi-dimensional model candidate table; Traverse the multi-dimensional model candidate table and output the multi-dimensional model.

2. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, wherein Write the function dependency relationships and their candidate confidences between the central points included in the link table into the identifier FD_id of the function dependency candidate table, the table identifier T_id to which the function dependency belongs, the left part FD_Left of the function dependency expression, the right part FD_Right of the function dependency expression, and the data confidence Conf-FD of the function dependency according to the function dependency candidate expression identifier, the table identifier to which it belongs, and the expression order pair <business key i, business key j> and the data confidence exceeding the data semantic confidence threshold respectively; 3. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, wherein Select the link table in the power grid DV data warehouse, traverse the central points related to the link table, find the fact table attributes and dimension table attributes and their function dependency relationships, and store the attribute identifiers and their function dependency relationships into the multi-dimensional model candidate table and the function dependency candidate table respectively. The specific steps include: Store the link table nodes and their affiliated table attributes into the multi-dimensional model candidate table; Store the function dependency relationship between the link table nodes and their affiliated table attributes into the function dependency candidate table; If it is determined that there are still connection center points in the link table of the DV model that have not been traversed, traverse the center point nodes related to the link table; Store the center point business key in the multi-dimensional model candidate table; Store the functional dependency relationship between the link table and the business key in the functional dependency candidate table; If it is determined that the current center point has not been traversed, enter and traverse all the subordinates of the center point; Until all the center points of the current link table are traversed and all the link tables in the DV model are traversed.

4. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, characterized in that, Construct a Bayesian network structure for the data in the data semantic confidence calculation table of the subordinate table to obtain the dependency relationship between nodes. The specific steps include: Take each attribute of the data semantic confidence calculation table as a node, calculate the information gain between the nodes to obtain the correlation between the nodes, calculate the size of the value range of each node, and take the one with the larger value range as the parent node. After sorting, obtain the Bayesian network structure and the dependency relationship between the nodes.

5. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, wherein, Detect the establishment mode of the data in the data semantic confidence calculation table of the subordinate table for the functional dependency candidates. The specific steps include: For a functional dependency candidate of the subordinate table in the functional dependency candidate table, if there are records that satisfy the functional dependency candidate expression on the corresponding two attributes in the data semantic confidence calculation table of the subordinate table, the records of the left part value and the right part value of the functional dependency candidate expression form the establishment mode of the current functional dependency; Set and update the corresponding flags for the records in the data semantic confidence calculation table that conform to the establishment mode.

6. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, wherein, For the functional dependency candidates of the subordinate table in the functional dependency candidate table, calculate their data confidence level record by record based on the data semantic confidence calculation table of the subordinate table. The specific steps include: According to the relationship between the nodes and the parent nodes in the Bayesian network, calculate the number of times the attribute instance value a appears, and calculate the conditional probability: Calculate the data confidence level of each attribute instance value a of each record in the data semantic confidence calculation table of the subordinate table, which is specifically expressed as: Perform normalization processing, which is expressed as: Calculate the data confidence level of the records in the data semantic confidence calculation table of the subordinate table and write it into the corresponding attribute of the data semantic confidence calculation table of the subordinate table until all records are calculated. The specific formula for the data confidence level is expressed as: Conf-ST = min(Conf_Attr i ′(a), a ∈ FD_left ∪ FD_right) Among them, Conf-ST represents the data confidence level of the records in the data semantic confidence calculation table of the subordinate table for a certain functional dependency, FD_left represents the left part of the functional dependency expression, and FD_Right represents the right part of the functional dependency expression.

7. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, characterized in that Set a flag in the data semantic confidence calculation table of the subordinate table, and the flag is used to mark whether the data record is consistent with the functional dependency; For the records in the data semantic confidence calculation table of the subordinate table that conform to the establishment mode, set the corresponding flag to the marked value; Set the data semantic confidence threshold and the effective record ratio threshold of the link table; For the data semantic confidence calculation table of the subordinate table, if the data confidence level of a certain data record for a certain functional dependency reaches the data semantic confidence threshold, set the corresponding flag to the marked value; Count the ratio of the number of data records corresponding to the marked value of the flag to the total number of data records. When the ratio reaches the effective record ratio threshold of the link table, update the data confidence level of the corresponding functional dependency candidate in the functional dependency candidate table.

8. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, characterized in that Construct and optimize the multi-dimensional model candidate based on the multi-dimensional model candidate table. The specific steps include: Determine the fact table and fact metrics; Merge the secondary business entities into the primary business entity according to the naming rules of the power grid business entities; Store the functional dependency relationship of the date dimension into the functional dependency candidate table; Determine the dimension table and hierarchy; Store the date dimension into the multi-dimensional model candidate table.

9. The method for automatically generating a multi-dimensional model based on a power grid DV data warehouse according to claim 1, wherein Determine the dimension table and hierarchy. The specific steps include: For the multi-dimensional model candidate table, find the record in the table where the functional dependency belongs and mark the table identifier as the center point table identifier. Set the table LK for the parent node attribute identifier P-node whose attribute Attr is the business key. i Primary key. Set the center point business key, LK, for the parent node attribute identifier P-node whose attribute Attr is a non-primary attribute. i Indicates the i-th linked table connected to this center point in the power grid DV data warehouse; For the multi-dimensional model candidate table, search for records where the table identifier of the functional dependency belongs to the central table identifier and the attribute Attr is a non-primary attribute. Search for the functional dependency relationship records with the same central table identifier in the corresponding functional dependency candidate table. If there are records that meet the preset rules, place the left-hand side value of the functional dependency in the parent node attribute identifier P-node of the attribute Attr; For the records in the functional dependency candidate table where the left-hand side of the functional dependency is a non-primary attribute, if there are m records in the functional dependency candidate table whose right-hand side values are equal to the attribute Attr value of a central point in the multi-dimensional model candidate table, then copy and add m-1 records of the attribute Attr in the multi-dimensional model candidate table, and place the m left-hand side values of the corresponding functional dependency candidate table of the attribute Attr record into the parent node attribute identifier P-node of the m records of the attribute Attr respectively.

Images