A method for automatically building a multidimensional model based on a power grid data warehouse model

Abstract

The present invention discloses a method for automatically constructing a multidimensional model based on a power grid data warehouse model, the method comprising the following steps: constructing a power grid data warehouse model based on a DV modeling method; merging the center point or link table of the power grid data warehouse model into an auxiliary table, retaining the relationship of the original model, and forming a user view; mapping the user view to the power grid data warehouse model to form a first mapping table; converting the power grid data warehouse model into a semi-dimensional model according to the evolution mode of the DV model, and constructing a second mapping table from the power grid data warehouse model to the semi-dimensional model; mapping the multidimensional model user view to the semi-dimensional model to form a multidimensional model user view and a third mapping table. The present invention utilizes the characteristics of power grid business and manipulates the mapping operation rules to automatically construct a multidimensional data model from the DV data warehouse model, so as to achieve efficient, high-quality, and cross-regional construction of data models.

Description

Technical Field

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

[0002] By implementing the Enterprise Common Information Model, power grid companies have implemented naming standards for all data tables and attributes. For example, at the data warehouse level, table names begin with TWB (the following table names are shortened and do not use standard naming). The amount of power grid time series data is large, and there is a separate time series database for storage without layering. The source business data may have duplicate data, and the existing processing is to remove duplicates in the warehouse. In the construction of power grid data warehouses, how to organize data efficiently has always been a technical problem faced by power grid companies.

[0003] In response to the needs of power grid enterprises for centralized data management and analysis, traditional data warehouse solutions use multidimensional data models, which can well meet the data analysis requirements of end users. However, traditional data warehouse solutions cannot meet the requirements of power grid enterprises: massive data organization with a centralized, highly scalable, and highly available platform that supports full-network and cross-domain data integration, as well as dynamic on-demand supply and real-time allocation of resources.

[0004] In the traditional data warehouse environment, manual methods are usually used to achieve the evolution from data warehouse to data mart, and finally generate the model evolution process of multidimensional data model or user view. Due to the cross-regional evolution from storage-oriented to analysis-oriented, the complexity of model evolution leads to low efficiency and difficulty in ensuring quality. Moreover, in the traditional power grid data warehouse environment, the manual evolution method is used to construct the multidimensional model of the data mart, which is complex, cumbersome and prone to errors.

[0005] In order to meet the special requirements of power grid enterprises for big data organization, power grid enterprises began to explore the introduction of Data Vault (hereinafter referred to as DV) modeling method, that is, to build a new data warehouse by integrating paradigm modeling and analytical modeling. The DV modeling method emphasizes the establishment of an auditable original data layer (data warehouse layer), but in order to support the data analysis work of end users, it is also necessary to transform this original data layer into a multidimensional data model (MD model for short, a data analysis model for business personnel) to meet the needs of end users. In the existing data warehouse construction method, the central table, link table and subsidiary table of the Data Vault data warehouse are generated in an automated manner, and its business logic relationship is very complex. The table logic relationship is difficult to completely cover all model generation. It does not involve the specific processing process of the source data table, nor does it involve how to solve the technical problem of converting from the Data Vault data warehouse to the multidimensional data model. Summary of the invention

[0006] In order to overcome the defects and shortcomings of the prior art, the present invention provides a method for automatically constructing a multidimensional model based on a power grid data warehouse model. The present invention uses a mapping method as a technical means to automatically convert a DV data warehouse model suitable for storing power grid business data into an MD model suitable for storing power grid analysis data. The characteristics of the power grid business and the manipulation mapping operation rules are used to realize efficient and simple pattern evolution manipulation, and the MD model is automatically constructed from the DV data warehouse model, thereby achieving an efficient and high-quality automatic evolution process of cross-regional data models.

[0007] The second object of the present invention is to provide a system for automatically building a multidimensional model based on a power grid data warehouse model.

[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 constructing a multidimensional model based on a power grid data warehouse model, comprising the following steps:

[0010] The power grid data warehouse model is constructed based on the DV modeling method, and the center point table, link table and subsidiary table are used to store the power grid business entities, relationships and their attribute data respectively;

[0011] Merge the central point or link table of the power grid data warehouse model into the subsidiary table, retain the relationship of the original model, and form a user view;

[0012] Map the user view to the power grid data warehouse model, corresponding to the entity table in the user view, establish a mapping table for each power grid business entity and its subsidiary table in the power grid data warehouse model, corresponding to the relationship table in the user view, establish a mapping table for each power grid business relationship and its subsidiary table, and form a first mapping table;

[0013] The power grid data warehouse model is converted into a semi-dimensional model according to the evolution mode of the DV model, and a mapping table is established for each power grid business entity center point and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business entity center point and its subsidiary table in the semi-dimensional model, and a mapping table is established for each power grid business relationship link and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business relationship link and its subsidiary table in the semi-dimensional model, and a mapping mode between rows and columns is established for each mapping table, so as to form a second mapping table mapped from the power grid data warehouse model to the semi-dimensional model;

[0014] By using the user view and mapping transfer, an initial multidimensional model user view is established, and the initial multidimensional model user view is mapped to the half-dimensional model to form a final multidimensional model user view and a third mapping table, specifically including:

[0015] The first mapping table and the second mapping table are combined into a fourth mapping table, the mapping that has not been transferred is written into a fifth mapping table, the user view and the mapping of the fourth mapping table are copied, and the initial multi-dimensional model user view and the third mapping table are generated;

[0016] Delete the useless mapping corresponding points in the fifth mapping table, build a many-to-one relationship between entity tables in the initial multidimensional model user view, delete transitive dependencies, delete useless entity tables and relationship tables, establish a date dimension, and form the final multidimensional model user view and the third mapping table.

[0017] As a preferred technical solution, the central point or link table of the power grid data warehouse model is merged into the subsidiary table, the relationship of the original model is retained, and the user view is formed. The specific steps include:

[0018] Multiple or one subsidiary table are merged with their central point table, retaining the timestamp attribute of the subsidiary table to form an entity table with the business key and timestamp as the primary key;

[0019] Multiple or one subsidiary table is merged with its linked table respectively, retaining the timestamp attribute of the subsidiary table to form a relational table with several entity business keys and timestamp as primary keys;

[0020] The entity table establishes a relational connection between the entity table and the relationship table according to the relationship between the original center point and the original link before the merger. If a many-to-one relationship is determined, several foreign keys of the non-key attributes of the relationship table are adjusted to primary key attributes, and the original primary key is deleted to form a user view.

[0021] As a preferred technical solution, the user view is mapped to the power grid data warehouse model to form a first mapping table. The specific steps include:

[0022] Corresponding to the entity table in the user view, a mapping table is established for each power grid business entity and its subsidiary table in the power grid data warehouse model, wherein the rows are the attributes of the entity in the user view, and the columns are the attributes of the business entity and its subsidiary table in the power grid data warehouse model;

[0023] Corresponding to the relationship table in the user view, a mapping table is established for each power grid business relationship and its subsidiary table. Its rows are the attributes of the relationship table in the user view, and its columns are the attributes of the business relationship and its subsidiary table in the power grid data warehouse model.

[0024] The mapping tables are assigned values, and mapping corresponding points between rows and columns are established for each mapping table, so as to form a first mapping table that maps the user view to the power grid data warehouse model.

[0025] As a preferred technical solution, the power grid data warehouse model is converted into a semi-dimensional model according to the evolution mode of the DV model, and the specific steps include:

[0026] Copy all the center point tables, link tables and subsidiary tables in the power grid data warehouse model, and establish tables corresponding to the semi-dimensional model;

[0027] Check the links without subsidiary tables that connect two central points in the semi-dimensional model. If the two business key attributes in the link table meet the functional dependency relationship, add the business key of the other party to the primary key of the central table of the multi-party, and delete the current link table without subsidiary tables.

[0028] The common dimension date table of the multidimensional model is added to the semi-dimensional model to form the final semi-dimensional model.

[0029] As a preferred technical solution, a corresponding mapping table is constructed for the deleted link table, with the deleted link table attributes as rows and the center point table attributes added to the business key column as columns.

[0030] As a preferred technical solution, the initial multi-dimensional model user view is established by using the user view and mapping transfer, which specifically includes the following steps:

[0031] For each corresponding point in the second mapping table <row DV ,col S >, check whether there is a corresponding point in the first mapping table <row UV ,col DV >, if row is determined DV =col DV , then the corresponding points of the transfer mapping are formed <row UV ,col S >, write into the fourth mapping table, otherwise it is determined that the transfer is not achieved, and the mapping that is not transferred is written into the fifth mapping table;

[0032] Copying the mapping of the user view and the fourth mapping table to generate an initial multi-dimensional model user view and a third mapping table;

[0033] As a preferred technical solution, a many-to-one relationship between entity tables is constructed in the initial multidimensional model user view. The specific steps include:

[0034] In the fourth mapping table, the mapping correspondence point between the relationship table from the initial multidimensional model user view and the foreign key of the center point of the half-dimensional model is found, and the current foreign key is placed in the primary key of the corresponding entity table in the initial multidimensional model user view to establish a many-to-one relationship between the two entity tables.

[0035] As a preferred technical solution, the specific steps of deleting transitive dependencies include:

[0036] In the initial multidimensional model user view, if it is determined that a certain relationship table R has a many-to-one relationship with a certain entity table A, and entity table A also has a many-to-one relationship with another entity table B, then the relationship table R transitively depends on entity table B, a many-to-one relationship to entity table B is added to the relationship table R, and the many-to-one relationship between entity table A and entity table B is deleted, and a mapping corresponding point from the entity table business key to the corresponding half-dimensional model center point business key is added to the third mapping table.

[0037] As a preferred technical solution, the specific steps for establishing the date dimension include:

[0038] Determine whether an entity table that has a many-to-one relationship with a certain relationship table R contains a timestamp, point the timestamp attribute in the relationship table R to the primary key of the public dimension date table, and delete the timestamp contained in the many-to-one relationship entity table.

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

[0040] The present invention provides an automatic multidimensional model construction system based on a power grid data warehouse model, comprising: a power grid data warehouse model construction module, a user view generation module, a first mapping table generation module, a half-dimensional model conversion module, a second mapping table generation module, a multidimensional model user view and a third mapping table generation module;

[0041] The power grid data warehouse model construction module is used to construct a power grid data warehouse model based on the DV modeling method, using a central point table, a link table, and an auxiliary table to store power grid business entities, relationships, and attribute data thereof respectively;

[0042] The user view generation module is used to merge the central point or link table of the power grid data warehouse model into the subsidiary table, retain the relationship of the original model, and form a user view;

[0043] The first mapping table generation module is used to generate a first mapping table, specifically including: mapping the user view to the power grid data warehouse model, corresponding to the entity table in the user view, establishing a mapping table for each power grid business entity and its subsidiary table in the power grid data warehouse model, corresponding to the relationship table in the user view, establishing a mapping table for each power grid business relationship and its subsidiary table to form a first mapping table;

[0044] The semi-dimensional model conversion module is used to convert the power grid data warehouse model into a semi-dimensional model according to the evolution mode of the DV model.

[0045] The second mapping table generation module is used to generate a second mapping table, specifically including: establishing a mapping table for each power grid business entity center point and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business entity center point and its subsidiary table in the semi-dimensional model, establishing a mapping table for each power grid business relationship link and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business relationship link and its subsidiary table in the semi-dimensional model, establishing a mapping method between rows and columns for each mapping table, and forming a second mapping table mapped from the power grid data warehouse model to the semi-dimensional model;

[0046] The multidimensional model user view and third mapping table generation module is used to establish an initial multidimensional model user view by using the user view and mapping transfer, and map the initial multidimensional model user view to the half-dimensional model to form a final multidimensional model user view and a third mapping table, specifically including:

[0047] The first mapping table and the second mapping table are combined into a fourth mapping table, the mapping that has not been transferred is written into a fifth mapping table, the user view and the mapping of the fourth mapping table are copied, and the initial multi-dimensional model user view and the third mapping table are generated;

[0048] Delete the useless mapping corresponding points in the fifth mapping table, build a many-to-one relationship between entity tables in the initial multidimensional model user view, delete transitive dependencies, delete useless entity tables and relationship tables, establish a date dimension, and form the final multidimensional model user view and the third mapping table.

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

[0050] (1) The traditional environment for building a power grid data warehouse lacks a set of universal and certain operating tools to establish a multidimensional model. The quality of its construction varies from person to person and is highly uncertain. The present invention introduces a mapping method for building a power grid data warehouse model, adopts a unified model manipulation method and mapping table device for calculation, thereby laying the foundation for realizing an efficient and consistent manipulation algorithm at the model or metadata level.

[0051] (2) In order to meet the data analysis needs of users, the process of manually constructing a multidimensional model is complicated, tedious, and prone to errors. The present invention uses a mapping device and method to establish an automatic process for evolving a DV model into a multidimensional model based on the characteristics of the power grid DV data warehouse. It uses general manipulation methods such as matching, merging, and differentiating models and mappings to achieve a simple and clear process for automatically generating a multidimensional model of the power grid. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0053] Figure 2 A schematic diagram of a process of mapping a user view to a power grid data warehouse model to form a first mapping table according to the present invention;

[0054] Figure 3 This is an example diagram of the power grid data warehouse model of the present invention;

[0055] Figure 4 UV for user view of the present invention NR Example diagram of

[0056] Figure 5 The present invention is to view the user UV NR The entity table in S DV An example diagram of a center point and its associated mapping table;

[0057] Figure 6 The present invention is to view the user UV NR The relationship table in S DV Example diagram of link and its associated mapping table;

[0058] Figure 7 A schematic diagram of a process for constructing a power grid data warehouse model and mapping it to a half-dimensional model to form a second mapping table according to the present invention;

[0059] Figure 8 This is an example diagram of a half-dimensional model of the present invention;

[0060] Fig. 9 A partial example diagram of mapping the center point of the power grid data warehouse model and its subsidiary tables to a half-dimensional model to form a second mapping table;

[0061] FIG. 10( a ) is a schematic diagram showing a mapping of the links and the subsidiary tables of the power grid data warehouse model of the present invention to the links and the subsidiary tables of the semi-dimensional model;

[0062] FIG10( b ) is a schematic diagram of a mapping table corresponding to a deleted link table of the present invention;

[0063] Fig.11A schematic diagram of a process of mapping a user view to a half-dimensional model to form a multi-dimensional model user view and a third mapping table according to the present invention;

[0064] Fig.12 is a partial example diagram of the fourth mapping table of the present invention;

[0065] FIG. 13( a ) is a schematic diagram of a fifth mapping representation of a center point and its associated components of the present invention;

[0066] FIG. 13( b ) is a schematic diagram of a fifth mapping table related to the link table related mapping of the present invention;

[0067] Fig.14 A schematic diagram of a user view of a multidimensional model of the present invention;

[0068] FIG. 15( a ) is a schematic diagram of mapping the multidimensional model user view V2_Customer entity table and the corresponding table of the half-dimensional model of the present invention;

[0069] FIG. 15( b ) is a schematic diagram of mapping the multidimensional model user view V2_Contract entity table and the corresponding table of the half-dimensional model of the present invention;

[0070] FIG. 15( c ) is a schematic diagram of the mapping between the multidimensional model user view V2_Service entity table and the corresponding table of the half-dimensional model of the present invention.

[0071] FIG. 15( d ) is a schematic diagram of mapping the multidimensional model user view V2_Usage relationship table and the corresponding table of the half-dimensional model of the present invention;

[0072] FIG15( e ) is a schematic diagram of the mapping between the multidimensional model user view V2_Date entity table and the corresponding table of the half-dimensional model of the present invention. DETAILED DESCRIPTION

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

[0074] Example 1

[0075] like Figure 1 As shown, this embodiment provides a method for automatically building a multidimensional model based on a power grid data warehouse model, comprising the following steps:

[0076] S1: Building a power grid data warehouse model based on DV modeling method DV , which consists of three types of tables: center point, link, and subsidiary. The center point, link, and subsidiary tables store power grid business entities, relationships, and their attribute data, such as the marketing data, production data, and material data of power grid enterprises;

[0077] In this embodiment, the central point table connects the link table and the subsidiary table based on the business key to form a hub-and-spoke architecture. The link table can store many-to-many relationships. If only the cardinality of the relationship changes, the link table will not be affected.

[0078] In this embodiment, a central point or link may have multiple subsidiaries, each of which forms a historical record of different periods of relevant attributes according to timestamps, and the sequence data of relevant attributes of the source power grid business system can be saved under one central point;

[0079] In this embodiment, the three tables of center point, link and subsidiary store the record source attribute, which can store the data integration of all source power grid business systems and also store the inconsistent data caused by multiple source power grid business systems;

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

[0081] S2: The power grid data warehouse model S DV The center point or link table is merged into the auxiliary table, retaining the relationship of the original model to form the user view UV NR , the user view UV presented to the user according to the traditional paradigm model NR Mapping to the power grid data warehouse model S DV , forming a first mapping table map1;

[0082] like Figure 2 As shown, the specific steps include:

[0083] S21: Power grid data warehouse model S DV The user view UV is formed according to the following rules NR :

[0084] S211: Multiple or one subsidiary table are merged with their central point table respectively (if multiple subsidiary tables have overlapping attributes, the user should specify a certain subsidiary table attribute to be used as the reference), and only the timestamp attribute of the subsidiary table is retained to form an entity table with the business key and timestamp as the primary key;

[0085] S212: Multiple or one subsidiary table are respectively merged with their link table (if multiple subsidiary tables have overlapping attributes, the user should specify a certain subsidiary table attribute to be used; if there is no subsidiary, the link table timestamp needs to be saved), and only the timestamp attribute of the subsidiary table is retained to form a relational table with several entity business keys and timestamp as primary keys;

[0086] S213: The entity table establishes a relational connection between the entity table and the relationship table according to the relationship between the original center point and the original link before the merger. If a many-to-one relationship is determined as specified by the user, several foreign keys of the non-key attributes of the relationship table should be adjusted to primary key attributes, and the original primary key should be deleted. Finally, the user view UV is formed. NR .

[0087] S22: Corresponding to user view UV NR An entity table in the power grid data warehouse model S DV A mapping table is established for each power grid business entity and its subsidiary table, and its row is the user view UV NR The attributes of an entity in the grid data warehouse model S DV The attributes of a business entity and its affiliated tables;

[0088] Corresponding user view UV NR A relational table in the , establishes a mapping table for each power grid business relationship and its subsidiary table, whose row is the user view UV NR The attributes of a relational table in the grid data warehouse model S DV The attributes of a business relationship and its affiliated tables;

[0089] S23: assigning values ​​to the mapping table using mapping technology;

[0090] The present invention adopts mapping technology, that is, the model M 1 and M 2 , map(M 1 ,M 2 ) specifically:

[0091] The map row represents M 1 The column represents the attributes of M 2 The properties of<row,col> Indicates mapping behavior. If there is an M in the line 1 Attributes, but there is no M in the column 2 The attribute of M 1 The attribute is not mapped (deleted); if there is no M in the line 1 Attributes, but there is an M in the column 2 The attribute of M 2The attribute of is not mapped (new). If a row directly corresponds to a column, then the row and column<row,col> Clicking "=" indicates direct assignment; if multiple rows correspond to a column, then the multiple rows and the column<row,col> Click to insert its operation formula. For example, if the column "Name" is composed of the rows "Surname" and "First Name", then put "= Surname + First Name" in the columns of these two rows to indicate that the "Name" column is composed of the "Surname" row plus the "First Name" row. Conversely, if a row corresponds to multiple columns, then the multiple columns of the row<row,col> Click to insert its operation formula. For example, if the row "Name" is composed of the columns "Surname" and "First Name", insert "=right(Name,4)" in the column "First Name" of the row to represent the right 4 characters of the "Name" row, and insert "=left(Name,2)" in the column "Surname" of the row to represent the left 2 characters of the "Name" row. Right() and left() indicate that the function takes the right character string and the left character string. In addition,<row,col> You can also add some comments in the point, placing them between " / *" and "* / ".

[0092] S24: Repeat step S23 to assign values ​​to the mapping table, and establish mapping corresponding points between rows and columns for each mapping table.

[0093] S25: Using all the above mapping tables, the user view UV is established. NR To the Grid Data Warehouse Model S DV The first mapping table map1 does not record the corresponding point of the source attribute in the first mapping table map1.

[0094] Power Grid Data Warehouse Model S DV The user view UV is formed according to the following rules NR :

[0095] 1) If Figure 3 As shown in the power grid data warehouse model S DV In the example, the customer hub table Hub_Customer, the contract hub table Hub_Contract and the service hub table Hub_Service are merged with one or more corresponding subsidiary tables, and the timestamp attribute Load_Date of the hub and the record source attribute Rec_Source of the subsidiary table are deleted. The timestamp attribute Load_Date of the main subsidiary table is retained to form three entity tables V1_Customer, V1_Contract and V1_Service, with their respective business keys Cust_Key, Cont_key and Serv_key and timestamp Load_Date as primary keys;

[0096] 2) The two link tables Lnk_Cust-Cont and Lnk_Cont-Serv are merged with their respective subsidiary tables; for the link table Lnk_Cont-Serv which has a subsidiary table, the timestamp attribute of the link table Lnk_Cont-Serv and the record source attribute Rec_Source of its subsidiary table Sat_Cont-Serv are deleted, and the timestamp attribute of the subsidiary table is retained; for the link table Lnk_Cust-Cont, the timestamp attribute of the link table itself is retained; thus, two relational tables V1_Cust-Cont and V1_Cont-Serv are formed, with the entity business key and the link table timestamp as the primary key;

[0097] 3) The entity table establishes a relational connection between the entity table and the relationship table according to the relationship between the original center point and the original link before the merger. If a many-to-one relationship is determined as specified by the user, the foreign keys Cust_key, Cont_key, Serv_key and Load_Date of the subsidiary table's non-key attributes should be adjusted to primary key attributes, and the original primary key Cont_Serv_id should be deleted, such as Figure 4 As shown, the user view UV is finally formed NR .

[0098] like Figure 5 As shown, Figure 4 User View UV NR Create a mapping table for the three entity tables V1_Customer, V1_Contract, and V1_Service. Each row in the mapping table lists the user view UV NR The entity attributes in each column list the power grid data warehouse model S DV The attributes of the entity and its attached tables;

[0099] like Figure 6 As shown, Figure 4 User View UV NR The two relationship tables V1_Cust-Cont and V1_Cont-Serv in the mapping table are used to establish the mapping table. Each row of the mapping table lists UV NR The attributes of the relational tables in the grid data warehouse model S are listed in each column. DV Attributes of business relationships and their subsidiary tables;

[0100] After creating the mapping tables of all the above entity tables and relationship tables, the user view UV is established. NR To the Grid Data Warehouse Model S DV The first mapping table map1.

[0101] S3: Grid data warehouse model S DV According to the evolution of DV model, it is converted into half-dimensional model S SMD, construct the power grid data warehouse model S DV To half-dimensional model S SMD The second mapping table map2;

[0102] like Figure 7 As shown, the specific steps include:

[0103] S31: Power Grid Data Warehouse Model S DV Establish a half-dimensional model S according to the following rules SMD :

[0104] S311: Replication of the power grid data warehouse model S DV All the center point tables, link tables and subsidiary tables in the corresponding half-dimensional model S SMD Table of;

[0105] S312: Check half-dimensional model S SMD For a link without an attached table that connects two central points, if the two business key attributes in the link table meet the functional dependency relationship, that is, a many-to-one relationship, then add the business key of the other party to the primary key of the central table of the many parties, and then delete the link table without an attached table;

[0106] S313: Half-dimensional Model S SMD Add the common dimension date table to the multidimensional model.

[0107] like Figure 8 As shown in the figure, the power grid data warehouse model S DV Establish a half-dimensional model S according to the following rules SMD :

[0108] 1) Copy the power grid data warehouse model S DV The three central point tables Hub_Customer, Hub_Contract and Hub_Service, the two link tables Lnk_Cust-Cont, Lnk_Cont-Serv and the corresponding five subsidiary tables, and continue the power grid data warehouse model S DV The relationship between the tables is used to establish the corresponding power grid data warehouse model S SMD Table of;

[0109] 2) According to the power grid business rules: a customer can have multiple electricity contracts. It can be found that the Cust_key and Cont_key in the link table Lnk_Cust-Cont meet the many-to-one dependency relationship. Therefore, the business key Cust_key is added to the primary key of the Hub_Contract center table, and the link table Lnk_Cust-Cont is deleted.

[0110] 3) In S SMDThe common dimension date table Date of the multidimensional model is added to the model. Its attributes include the business key Date_id, as well as the year, month, and date. Thus, a semi-dimensional model S is formed. SMD .

[0111] S32: Establishing the power grid data warehouse model S DV To half-dimensional model S SMD The mapping table;

[0112] S321: Power grid data warehouse model S DV A mapping table is established for each power grid business entity center point and its subsidiary table, corresponding to the semi-dimensional model S SMD Each grid business entity center point and its subsidiary tables;

[0113] S322: Power grid data warehouse model S DV A mapping table is established for each power grid business relationship link and its subsidiary table in the corresponding semi-dimensional model S SMD Each power grid business relationship link and its subsidiary table;

[0114] S323: For the link table deleted in step S312, the corresponding mapping table has the deleted link table attributes as rows and the center point table attributes added to the business key column as columns;

[0115] S324: For a mapping table, if a row directly corresponds to a column, then the row and column<row,col> Dot-inserting "=" indicates direct assignment;

[0116] S325: Execute step S324 multiple times to establish a mapping method between rows and columns for each mapping table;

[0117] S326: Summarize all the above mapping tables to establish the power grid data warehouse model S DV To half-dimensional model S SMD The second mapping table map2 may not have a corresponding point of the unaffiliated link table in the second mapping table map2.

[0118] like Fig. 9 As shown, this is the power grid data warehouse model S DV The mapping table is established for the three power grid business entity centers and their subsidiary tables, corresponding to the semi-dimensional model S SMD Each grid business entity center point and its subsidiary tables;

[0119] As shown in Figure 10(a), this is the power grid data warehouse model S DV The two power grid business links and their subsidiary tables in the map are used to establish a mapping table corresponding to the semi-dimensional model S SMD Each grid business link and its subsidiary table in;

[0120] As shown in FIG10( b ), for the deleted link table, its corresponding mapping table has the link table Lnk_Cust-Cont attributes as rows and the hub table Hub_Contract attributes as columns;

[0121] By summarizing all the above mapping tables, the power grid data warehouse model S is established. DV To half-dimensional model S SMD The second mapping table map2.

[0122] S4: UV the multi-dimensional model user view MD Mapping to the half-dimensional model S SMD , forming a multi-dimensional model user view and a third mapping table map3;

[0123] like Fig.11 As shown, the specific steps include:

[0124] S41: Build User View UV NR To half-dimensional model S SMD A fourth mapping table map4 and a fifth mapping table map5;

[0125] The first mapping table map1 and the second mapping table map2 are combined into a fourth mapping table map4. The specific implementation steps are: for each corresponding point in the second mapping table map2 <row DV ,col S >, if there is a corresponding point in the first mapping table map1 <row UV ,col DV >, where row DV =col DV , then the corresponding points of the transfer mapping are formed <row UV ,col S >, write into the fourth mapping table map4, otherwise write into the fifth mapping table map5;

[0126] like Fig.12 As shown, the first mapping table map1 and the second mapping table map2 formed above are combined into a fourth mapping table map4, and for each corresponding point in the second mapping table map2 <row DV ,col S >, if there is a corresponding point in the first mapping table map1 <row UV ,col DV >, where row DV =col DV , then the corresponding points of the transfer mapping are formed <row UV ,col S>, write into the fourth mapping table map4. For example, if the mapping point corresponding to Cust_key in the second mapping table map2 is "=", and the corresponding point of Cust_key in the first mapping table map1 is also "=", then write it into the fourth mapping table map4. Otherwise, as shown in Figures 13(a) and 13(b), the mapping that is not transferred is written into the fifth mapping table map5.

[0127] S42: Establishing multi-dimensional model user view UV MD , a third mapping table map3;

[0128] S421: Copy User View UV NR and the mapping in the fourth mapping table map4 to form the initial multi-dimensional model user view UV MD and the initial third mapping table map3;

[0129] S422: Deleting useless mappings: In the fifth mapping table map5, finding out the useless mapping corresponding points between the central table and the subsidiary table and deleting them;

[0130] S423: Add initial multi-dimensional model user view UV MD Relationship: In the fourth mapping table map4, find out the user view UV from the initial multi-dimensional model MD The relational table and semi-dimensional model S SMD The mapping corresponding point between the center point foreign key (the foreign key added in the primary key area in step S312) is placed in the initial multidimensional model user view UV MD In the primary key of the corresponding entity table, a many-to-one relationship is established between the two entity tables;

[0131] S424: Remove transitive dependency: In the initial multi-dimensional model user view UV MD In the example, if a relation table R has a many-to-one relationship with an entity table A, and entity table A also has a many-to-one relationship with another entity table B, then the relation table R transitively depends on entity table B. Then, add a many-to-one relationship to entity table B in the relation table R, delete the many-to-one relationship between entity table A and entity table B (foreign key in the primary key area), and add a mapping from the entity table business key to the corresponding semi-dimensional model S in the third mapping table map3. SMD The mapping point of the central point business key (after adding the entity foreign key in the relationship table R, it must be consistent with S SMD The corresponding central point business key is used to establish a mapping);

[0132] S425: Delete useless entity tables and relationship tables: In the initial multidimensional model user view UV MD In the example, the table without attributes and its mapping (in the third mapping table map3) are deleted;

[0133] S426: Create date dimension: Confirm that the entity table with many-to-one relationship in a certain relationship table R contains timestamp, point the timestamp attribute Load_Data in the relationship table R to the primary key of the public dimension date table (replace it with Data_id foreign key), and then delete the timestamp in these many-to-one relationship entity tables. In addition, add user view UV in the third mapping table map3 MD V2_Date entity to S SMD The mapping corresponding point of the public dimension date table Data;

[0134] S427: At this point, a multi-dimensional model user view UV is formed MD And the third mapping table map3.

[0135] In this embodiment, a multi-dimensional model user view UV is constructed. MD , the specific example of the third mapping table map3 is as follows:

[0136] 1) Copy the user view UV NR The user view and the mapping in the fourth mapping table map4 form the initial multi-dimensional model user view UV MD (To distinguish, the entity name prefix is ​​changed to V2) and the initial third mapping table map3;

[0137] 2) Deleting useless mappings in the initial third mapping table map3: In the fifth mapping table map5 (as shown in FIG. 13( a ) and FIG. 13( b )), find the mapping correspondence points between the three central tables and the five subsidiary tables and delete them;

[0138] 3) Add initial multi-dimensional model user view UV MD In the fourth mapping table map4, the Cust_key of the link table Lnk_Cust-Cont has a mapping corresponding point with the Cust_key of the hub table Hub_Contract (as shown in FIG. 10( b)). Add Cust_key to the initial multi-dimensional model user view UV. MD In the primary key of the entity table Contract;

[0139] 4) Delete transitive dependencies: User view UV of multi-dimensional model MD, there is a many-to-one relationship between the relationship table V2_Cont-Serv and the entity table V2_Contract, and there is also a many-to-one relationship between V2_Contract and the entity table V2_Customer. The relationship table V2_Cont-Serv transitively depends on V2_Customer. Therefore, a many-to-one relationship to V2_Customer is added in V2_Cont-Serv (that is, the primary key Cust_key of V2_Customer is added to the primary key of V2_Cont-Serv), and the many-to-one relationship between V2_Customer and V2_Contract is deleted (that is, the Cust_key in the primary key of V2_Contract is deleted). In map3, the mapping corresponding points of Cust_key of V2_Customer to Cust_key of Hub_Contract and Cust_key of Sat__Contract are added.

[0140] 5) Delete useless entity tables and relationship tables: Initial multidimensional model user view UV MD Since the relationship table V2_Cust-Cont has no corresponding point in the third mapping table map3, it is deleted.

[0141] 6) Establish date dimension: So far, the initial multidimensional model user view UV MD , all three entity tables V2_Customer, V2_Contract and V2_Service in the relationship table V2_Cont-Serv that have many-to-one relationships contain timestamps. You can point the timestamp attribute Load_Data in V2_Cont-Serv to the public dimension date table (replace it with the Data_id foreign key), and then delete the timestamps contained in the three entity tables that have many-to-one relationships. In addition, add UV in the third mapping table map3 MD V2_Date to S SMD The mapping corresponding point of the public dimension date table Data;

[0142] 7) If Fig.14 As shown, the final multi-dimensional model user view UV is formed MD , as shown in Figure 15(a)-(e), a third mapping table map3 is formed.

[0143] The present invention utilizes a unified manipulation method of models and mappings to establish mappings between models and user views, and between models, thereby realizing the process of automatically constructing a power grid multidimensional model from a power grid DV data warehouse model. The method for automatically constructing a multidimensional data model provided by the present invention can realize an efficient and high-quality automatic model evolution method at the data model level, thereby achieving the purpose of improving the design quality of the power grid data warehouse.

[0144] Example 2

[0145] This embodiment provides a system for automatically building a multidimensional model based on a power grid data warehouse model, comprising: a power grid data warehouse model building module, a user view generation module, a first mapping table generation module, a half-dimensional model conversion module, a second mapping table generation module, a multidimensional model user view and a third mapping table generation module;

[0146] In this embodiment, the power grid data warehouse model construction module is used to construct a power grid data warehouse model based on the DV modeling method, using a central point table, a link table, and an auxiliary table to store power grid business entities, relationships, and their attribute data respectively;

[0147] In this embodiment, the user view generation module is used to merge the central point or link table of the power grid data warehouse model into the subsidiary table, retain the relationship of the original model, and form a user view;

[0148] In this embodiment, the first mapping table generation module is used to generate the first mapping table, specifically including: mapping the user view to the power grid data warehouse model, corresponding to the entity table in the user view, establishing a mapping table for each power grid business entity and its subsidiary table in the power grid data warehouse model, corresponding to the relationship table in the user view, establishing a mapping table for each power grid business relationship and its subsidiary table, and forming a first mapping table;

[0149] In this embodiment, the semi-dimensional model conversion module is used to convert the power grid data warehouse model into a semi-dimensional model according to the evolution mode of the DV model.

[0150] In this embodiment, the second mapping table generation module is used to generate a second mapping table, specifically including: establishing a mapping table for each power grid business entity center point and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business entity center point and its subsidiary table in the semi-dimensional model, establishing a mapping table for each power grid business relationship link and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business relationship link and its subsidiary table in the semi-dimensional model, establishing a mapping method between rows and columns for each mapping table, and forming a second mapping table mapped from the power grid data warehouse model to the semi-dimensional model;

[0151] In this embodiment, the multidimensional model user view and the third mapping table generation module is used to use the user view and mapping transfer to establish an initial multidimensional model user view, map the initial multidimensional model user view to the half-dimensional model, and form a final multidimensional model user view and a third mapping table, specifically including:

[0152] The first mapping table and the second mapping table are combined into a fourth mapping table, the mapping that has not been transferred is written into a fifth mapping table, and the mapping of the user view and the fourth mapping table are copied to form an initial multi-dimensional model user view and a third mapping table;

[0153] Delete the useless mapping corresponding points in the fifth mapping table, build a many-to-one relationship between entity tables in the initial multidimensional model user view, delete transitive dependencies, delete useless entity tables and relationship tables, establish a date dimension, and form the final multidimensional model user view and the third mapping table.

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

Claims

1. A method for automatically building a multidimensional model based on a power grid data warehouse model. It is characterized in that The steps include: The power grid data warehouse model is constructed based on the DV modeling method, and the center point table, link table and subsidiary table are used to store the power grid business entities, relationships and their attribute data respectively; Merge the central point or link table of the power grid data warehouse model into the subsidiary table, retain the relationship of the original model, and form a user view; Map the user view to the power grid data warehouse model, corresponding to the entity table in the user view, establish a mapping table for each power grid business entity and its subsidiary table in the power grid data warehouse model, corresponding to the relationship table in the user view, establish a mapping table for each power grid business relationship and its subsidiary table, and form a first mapping table; The power grid data warehouse model is converted into a semi-dimensional model according to the evolution mode of the DV model, and a mapping table is established for each power grid business entity center point and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business entity center point and its subsidiary table in the semi-dimensional model, and a mapping table is established for each power grid business relationship link and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business relationship link and its subsidiary table in the semi-dimensional model, and a mapping mode between rows and columns is established for each mapping table, so as to form a second mapping table mapped from the power grid data warehouse model to the semi-dimensional model; By using the user view and mapping transfer, an initial multidimensional model user view is established, and the initial multidimensional model user view is mapped to the half-dimensional model to form a final multidimensional model user view and a third mapping table, specifically including: The first mapping table and the second mapping table are combined into a fourth mapping table, and the mapping that has not been transferred is written into a fifth mapping table; Copying the mapping of the user view and the fourth mapping table to generate an initial multi-dimensional model user view and a third mapping table; Delete the useless mapping corresponding points in the fifth mapping table, build a many-to-one relationship between entity tables in the initial multidimensional model user view, delete transitive dependencies, delete useless entity tables and relationship tables, establish a date dimension, and form the final multidimensional model user view and the third mapping table.

2. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that The steps of merging the central point or link table of the power grid data warehouse model into the subsidiary table, retaining the relationship of the original model, and forming a user view include: Multiple or one subsidiary table are merged with their central point table, retaining the timestamp attribute of the subsidiary table to form an entity table with the business key and timestamp as the primary key; Multiple or one subsidiary table is merged with its linked table respectively, retaining the timestamp attribute of the subsidiary table to form a relational table with several entity business keys and timestamp as primary keys; The entity table establishes a relational connection between the entity table and the relationship table according to the relationship between the original center point and the original link before the merger. If a many-to-one relationship is determined, several foreign keys of the non-key attributes of the relationship table are adjusted to primary key attributes, and the original primary key is deleted to form a user view.

3. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that Mapping the user view to the power grid data warehouse model to form a first mapping table, the specific steps include: Corresponding to the entity table in the user view, a mapping table is established for each power grid business entity and its subsidiary table in the power grid data warehouse model, wherein the rows are the attributes of the entity in the user view, and the columns are the attributes of the business entity and its subsidiary table in the power grid data warehouse model; Corresponding to the relationship table in the user view, a mapping table is established for each power grid business relationship and its subsidiary table. Its rows are the attributes of the relationship table in the user view, and its columns are the attributes of the business relationship and its subsidiary table in the power grid data warehouse model. The mapping tables are assigned values, and mapping corresponding points between rows and columns are established for each mapping table, so as to form a first mapping table that maps the user view to the power grid data warehouse model.

4. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that The specific steps of converting the power grid data warehouse model into a semi-dimensional model according to the evolution mode of the DV model include: Copy all the center point tables, link tables and subsidiary tables in the power grid data warehouse model, and establish tables corresponding to the semi-dimensional model; Check the links without subsidiary tables that connect two central points in the semi-dimensional model. If the two business key attributes in the link table meet the functional dependency relationship, add the business key of the other party to the primary key of the central table of the multi-party, and delete the current link table without subsidiary tables. The common dimension date table of the multidimensional model is added to the semi-dimensional model to form the final semi-dimensional model.

5. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 4, It is characterized in that A corresponding mapping table is constructed for the deleted link table, with the deleted link table attributes as rows and the center point table attributes added to the business key column as columns.

6. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that Using user views and mapping transfer, the initial multidimensional model user view is established, which specifically includes the following steps: For each corresponding point in the second mapping table <row DV ,col S >, check whether there is a corresponding point in the first mapping table <row UV ,col DV >, if row is determined DV =col DV , then the corresponding points of the transfer mapping are formed <row UV ,col S >, write into the fourth mapping table, otherwise it is determined that the transfer is not achieved, and the mapping that is not transferred is written into the fifth mapping table; The mapping of the user view and the fourth mapping table is copied to generate an initial multi-dimensional model user view and a third mapping table.

7. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that In the initial multidimensional model user view, a many-to-one relationship between entity tables is constructed. The specific steps include: In the fourth mapping table, the mapping correspondence point between the relationship table from the initial multidimensional model user view and the foreign key of the center point of the half-dimensional model is found, and the current foreign key is placed in the primary key of the corresponding entity table in the initial multidimensional model user view to establish a many-to-one relationship between the two entity tables.

8. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that The specific steps to delete transitive dependencies include: In the initial multidimensional model user view, if it is determined that a certain relationship table R has a many-to-one relationship with a certain entity table A, and entity table A also has a many-to-one relationship with another entity table B, then the relationship table R transitively depends on entity table B, a many-to-one relationship to entity table B is added to the relationship table R, and the many-to-one relationship between entity table A and entity table B is deleted, and a mapping corresponding point from the entity table business key to the corresponding half-dimensional model center point business key is added to the third mapping table.

9. The method for automatically constructing a multidimensional model based on a power grid data warehouse model according to claim 1, It is characterized in that The specific steps to create a date dimension include: Determine whether an entity table that has a many-to-one relationship with a certain relationship table R contains a timestamp, point the timestamp attribute in the relationship table R to the primary key of the public dimension date table, and delete the timestamp contained in the many-to-one relationship entity table.

10. An automatic multi-dimensional model building system based on power grid data warehouse model, It is characterized in that include: A power grid data warehouse model building module, a user view generation module, a first mapping table generation module, a semi-dimensional model conversion module, a second mapping table generation module, a multi-dimensional model user view and a third mapping table generation module; The power grid data warehouse model construction module is used to construct a power grid data warehouse model based on the DV modeling method, using a central point table, a link table, and an auxiliary table to store power grid business entities, relationships, and attribute data thereof respectively; The user view generation module is used to merge the central point or link table of the power grid data warehouse model into the subsidiary table, retain the relationship of the original model, and form a user view; The first mapping table generation module is used to generate a first mapping table, specifically including: mapping the user view to the power grid data warehouse model, corresponding to the entity table in the user view, establishing a mapping table for each power grid business entity and its subsidiary table in the power grid data warehouse model, corresponding to the relationship table in the user view, establishing a mapping table for each power grid business relationship and its subsidiary table to form a first mapping table; The semi-dimensional model conversion module is used to convert the power grid data warehouse model into a semi-dimensional model according to the evolution mode of the DV model. The second mapping table generation module is used to generate a second mapping table, specifically including: establishing a mapping table for each power grid business entity center point and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business entity center point and its subsidiary table in the semi-dimensional model, establishing a mapping table for each power grid business relationship link and its subsidiary table in the power grid data warehouse model, corresponding to each power grid business relationship link and its subsidiary table in the semi-dimensional model, establishing a mapping method between rows and columns for each mapping table, and forming a second mapping table mapped from the power grid data warehouse model to the semi-dimensional model; The multidimensional model user view and third mapping table generation module is used to establish an initial multidimensional model user view by using the user view and mapping transfer, and map the initial multidimensional model user view to the half-dimensional model to form a final multidimensional model user view and a third mapping table, specifically including: The first mapping table and the second mapping table are combined into a fourth mapping table, and the mapping that has not been transferred is written into a fifth mapping table; Copying the mapping of the user view and the fourth mapping table to generate an initial multi-dimensional model user view and a third mapping table; Delete the useless mapping corresponding points in the fifth mapping table, build a many-to-one relationship between entity tables in the initial multidimensional model user view, delete transitive dependencies, delete useless entity tables and relationship tables, establish a date dimension, and form the final multidimensional model user view and the third mapping table.

Images