Distributed data warehouse construction method for health monitoring of hydroelectric power production equipment

Abstract

The present application relates to a distributed data warehouse construction method for hydropower production equipment health monitoring, comprising: analyzing hydropower equipment data application requirements to determine the analysis theme of the hydropower equipment; constructing a data warehouse layered architecture comprising an original data layer ODS, a detailed data layer DWD, a service data layer DWS, a theme data layer DWT, and a data application layer ADS; establishing a hydropower production equipment data model; and constructing a hydropower production data warehouse physical model comprising dimension tables and fact tables. The present application realizes unified extraction, conversion and loading of various system data sources of the hydropower plant, guarantees data quality, and breaks data silos; realizes centralized and unified management of data, utilizes data backup, data security control and access permission management functions of the data warehouse to ensure data security and reliability; reduces manual workload for data collection, arrangement and management, and improves data processing and analysis efficiency.

Description

Technical Field

[0001] This invention belongs to the field of data processing, storage and analysis, and specifically relates to a method for constructing a distributed data warehouse for health monitoring of hydropower production equipment. Background Technology

[0002] In recent years, with the continuous development of informatization in hydropower enterprises, these companies have accumulated a large amount of structured and unstructured data, while also possessing a potentially massive amount of real-time data that can be collected. Today, data is an intangible asset for enterprises, and their need to leverage data to drive development is extremely urgent. After data collection and storage, data analysis can improve operational efficiency, provide decision-making support, and support corporate strategy, thereby driving enterprise development.

[0003] Currently, most hydropower companies use traditional architectures when building data centers, which have the following drawbacks:

[0004] (1) Poor scalability, high cost, and limited support for a single data type make it difficult to meet the storage requirements of rapid growth in all types of data;

[0005] (2) There is a “data silo” phenomenon. Hydropower data comes from various systems, resulting in low data processing efficiency and difficulty in meeting the high-speed processing needs of large data volumes.

[0006] (3) The lack of a data management system and poor data quality make it difficult to meet the storage and computing needs of all types of data in the big data environment and to provide support for the in-depth utilization of enterprise data assets. Summary of the Invention

[0007] The purpose of this invention is to address the above-mentioned problems by providing a distributed data warehouse construction method for health monitoring of hydropower production equipment. This method aggregates data from the online monitoring systems of various production equipment through data extraction and designs the table structure of the data warehouse according to the theme of equipment health monitoring, thereby solving the problem that the long-term accumulated data of hydropower plants is difficult to apply in depth.

[0008] The technical solution of this invention is a method for constructing a distributed data warehouse for health monitoring of hydropower production equipment, comprising the following steps:

[0009] Step 1: Analyze the data application requirements of hydropower equipment and determine the analysis topic of hydropower equipment;

[0010] The analysis topics for the hydropower equipment include turbine units, generators, and GIS;

[0011] Step 2: Construct a layered data warehouse architecture comprising the raw data layer ODS, detailed data layer DWD, service data layer DWS, thematic data layer DWT, and data application layer ADS;

[0012] Step 3: Establish a data model for hydropower production equipment;

[0013] Step 3.1: Establish the mapping between the corresponding tables of the source and target database systems, and determine the storage structure of various data sources in the data warehouse based on the definition of each dimension table in the metadata;

[0014] Step 3.2: Design the logical table structure of the snowflake dimension model from multiple dimensions, including time, operation cycle, and maintenance.

[0015] Step 4: Construct a physical model of the hydropower production data warehouse, including dimension tables and fact tables;

[0016] Step 4.1: Design and construct a dimension table to group related measurement points of the same device at the same frequency into the same super table. The columns of the super table are divided into two parts: dynamic and static. The dynamic data is the collected data, with the first column being the timestamp and the other columns being the collected physical quantities; the static part is the label column.

[0017] Step 4.2: Design and build a fact table to store the metrics of business events.

[0018] Preferably, the distributed data warehouse construction method establishes a data warehouse data management mode based on the characteristics of hydropower production data; the data management mode includes metadata management, generation and retrieval of dormant data, generation and cleaning of dirty data, and monitoring of dormant and dirty data.

[0019] Furthermore, source data is obtained from the monitoring systems of various equipment in the hydropower station and a raw data layer (ODS) is constructed to store the unprocessed raw data in the data warehouse. The raw data layer is structurally consistent with the source system and serves as the data preparation area for the data warehouse. A unified standard and specification data ETL process is established for the data sources of the hydropower plant's computer monitoring system, water situation and dispatch system, protection fault information system, and online monitoring system to achieve unified data extraction, transformation, and loading.

[0020] Preferably, a sub-table is designed and constructed for each data acquisition point. All sub-tables corresponding to data acquisition points belong to the same super-table. The super-table contains dynamic attributes and static attributes. The dynamic attributes of the super-table include timestamp and values ​​of various physical quantities of the acquisition point. The static attributes of the super-table include source of the acquisition point and description of the acquisition point.

[0021] In step 2, the health monitoring business of hydropower generation equipment is used as the modeling driver to construct the detailed data layer (DWD). The original data layer (ODS) is cleaned, dimension degraded, and desensitized, and a dimensional model is constructed. For the turbine unit operation-related data, the source data is split into several dimensional tables and fact tables of turbine unit operation status, including vibration power generation status table and vibration shutdown status table, according to the unit's operating condition.

[0022] Based on the streaming computing capabilities of the TDengine time series database, a function is designed and provided to aggregate and query operating conditions based on time period window segmentation. The window includes an equal interval window and a status window.

[0023] The simulation data of current, voltage, reactive power, and active power are divided into time windows with equal time intervals. Within each window, the average, maximum, and minimum values ​​of the aggregated streaming calculation are calculated. The real-time results of the streaming calculation are stored in a new sub-table, and a timestamp alignment function is provided.

[0024] The status and unit status switch data of circuit breakers are aggregated using status windows. The average and maximum values ​​of the collected values ​​in each status window are aggregated and calculated using streaming, and the duration and number of data records are counted. The real-time results of the streaming calculation are stored in a new sub-table, providing data statistics functions under a unified status.

[0025] Furthermore, a service data layer (DWS) is constructed based on the detailed data layer (DWD), using relevant subject objects for health analysis of hydropower generation equipment as the modeling driver, and summarizing them according to the unit's operating cycle.

[0026] The power generation efficiency index, operation vibration index, and bearing temperature index are summarized on a daily, monthly, or turbine unit operating cycle basis.

[0027] Furthermore, a topic data layer DWT is constructed based on the service data layer DWS, and the runtime data of different topics in the service data layer DWS are further summarized to obtain a full data table for each topic;

[0028] Models for vibration and sway analysis, shutdown analysis, status analysis, operation analysis, maintenance analysis, and life analysis of hydro-turbine equipment were established. Data and information in the DWS layer were further integrated, and business process-related data were redundantly processed based on themes to facilitate the use of data by upper-layer services.

[0029] Furthermore, an application data layer (ADS) is constructed based on the topic data layer (DWT) to calculate and store personalized statistical indicator data for health analysis of hydropower generation equipment.

[0030] Data on equipment health assessment indicators, deterioration rate, and remaining life of the turbine generator unit are stored to facilitate the analysis and decision-making regarding the turbine generator unit's operating status and production.

[0031] Compared to existing technologies, the advantages of this invention are that it provides a data warehouse cluster for hydropower production built on the time-series database TDengine. This cluster unifies the extraction, transformation, and loading of various system data sources, such as hydropower plant computer monitoring systems, water situation and dispatch systems, protection fault information systems, and online monitoring systems, ensuring data quality and breaking down data silos. After calculation and aggregation, the data is stored in pre-designed fact tables at different levels, achieving centralized and unified data management. Utilizing the data warehouse's data backup, data security control, and access permission management functions, data security and reliability are guaranteed. This reduces the manual workload of data collection, organization, and management, improves the efficiency of data processing and analysis, and simultaneously reduces enterprise costs and increases efficiency. Attached Figure Description

[0032] The present invention will be further described below with reference to the accompanying drawings and embodiments.

[0033] Figure 1 This is a flowchart illustrating the distributed data warehouse construction method according to an embodiment of the present invention.

[0034] Figure 2 This is a schematic diagram of the data warehouse layered architecture according to an embodiment of the present invention.

[0035] Figure 3 This is a snowflake model diagram of device timing data in an embodiment of the present invention. Detailed Implementation

[0036] In this embodiment, the data cleaning process is simplified by using hierarchical data management, and the data processing functions of each layer are decoupled from each other to form a data warehouse for hydropower production data. A data monitoring and alarm system is built based on the open-source data visualization system Grafana, which enables the contents of data tables in TDengine to be visualized on the dashboard.

[0037] like Figure 1 As shown, the method for constructing a distributed data warehouse for health monitoring of hydropower production equipment includes the following steps:

[0038] Step 1: Analyze the data application requirements of hydropower equipment;

[0039] Based on a field survey of a hydropower station of a power generation group, the application requirements and data sources of the hydropower production data warehouse were analyzed and sorted out. The requirements were divided into analysis themes for multiple equipment such as turbine units, generators, and GIS. The data comes from front-line systems of hydropower station production, such as computer monitoring systems, water information and dispatching systems, protection fault information systems, and online monitoring systems. In this embodiment, the health status assessment of turbine unit equipment is used as an example to illustrate the data warehouse construction method; the design concept for other equipment is the same as that for turbine unit equipment.

[0040] Step 2: Data warehouse layered architecture design;

[0041] Based on the analysis of business data and application requirements, a five-layered architecture was designed, comprising the raw data layer (ODS), detailed data layer (DWD), service data layer (DWS), thematic data layer (DWT), and data application layer (ADS). Figure 2 As shown.

[0042] In this embodiment, based on the product characteristics of the TDengine time series database, a data model of "one data acquisition point, one sub-table" was designed.

[0043] 1) Raw Data Layer (ODS): Source data is obtained from the monitoring systems of various equipment in the hydropower station and the raw data layer (ODS) is constructed. The raw data is stored in the data warehouse system and its structure is consistent with the source system. It is the data preparation area of ​​the data warehouse.

[0044] Establish a unified standard and specification for data ETL processes for various system data sources such as hydropower plant computer monitoring systems, water information and dispatching systems, protection fault information systems, and online monitoring systems. This will enable unified data extraction, transformation, and loading, ensuring high data quality and providing support for data warehouse applications.

[0045] A data warehouse management model is established based on the characteristics of hydropower production data. This includes tasks such as metadata management, generation and retrieval of dormant data, generation and cleanup of dirty data, and monitoring of dormant and dirty data.

[0046] In this embodiment, the designed database table model consists of more than 17,000 measurement points across the entire plant, which are represented by more than 17,000 sub-tables. The naming convention is set as "ods_abbreviation of plant name_measurement point number". These more than 17,000 sub-tables belong to the same super table. According to the characteristics of the TDengine database, the super table and its sub-tables have the same structure.

[0047] In addition to the dynamic data collected from the corresponding measuring points, i.e., the first column is the timestamp (ts) and the other columns are the collected physical quantities (values), two additional tags are set as static attributes, namely the measuring point source (source) and the measuring point description (description), etc. The super table structure is shown in Table 1.

[0048] Table 1. Structure of the original data layer supertable

[0049]

[0050] 2) Detailed Data Layer (DWD): Based on the original data layer, a detailed data layer is constructed. Taking the health monitoring business of hydropower production equipment as the modeling driver, the detailed data layer (DWD) is constructed. The data of the ODS layer is cleaned, dimension degraded, and desensitized, and a dimensional model is constructed.

[0051] In this embodiment, for the data related to the operation of the turbine unit, the source data is divided into several dimension tables and fact tables for each turbine unit's operating status, such as the vibration power generation state table and the vibration shutdown state table, according to the operating conditions such as power generation state and shutdown state.

[0052] Based on the streaming computing capabilities of the TDengine time-series database, this system enables aggregation and querying of operating conditions by dividing the data into time-segment windows. The windows include two types: equal-interval windows and status windows.

[0053] Simulated data such as current, voltage, reactive power, and active power are divided into time windows with equal time intervals. Within each window, the average, maximum, and minimum values ​​are calculated using aggregating streaming cytometry. The real-time results of the streaming cytometry are then stored in a new sub-table to achieve timestamp alignment.

[0054] Status windows are used to aggregate switch data such as circuit breaker status and unit status. When a certain status field has the same value within a certain duration, this duration is divided into the same status window. For example, from 5:00 to 6:00, when the circuit breaker status is open, the status state = 1, and 5:00 to 6:00 constitutes one status time window. The average and maximum values ​​of the collected values ​​within each window are aggregated and calculated using streaming methods. The duration and number of recorded data entries can also be counted. The real-time results of the streaming calculation are stored in a new sub-table, achieving data statistics under a unified state.

[0055] 3) Service Data Layer (DWS): The Service Data Layer (DWS) is constructed based on the detailed data layer. Several relevant subject objects for health analysis of hydropower generation equipment are used as the modeling driver, and a light summary is performed according to the unit operation cycle.

[0056] In this embodiment, a subject object for the health assessment of a turbine unit is set as the modeling driver. Based on the designed equipment power generation efficiency index, operation vibration index, bearing temperature index and other index models, a light summary is performed on a daily, monthly and turbine unit operation cycle basis.

[0057] 4) Theme Data Layer (DWT): The theme data layer (DWT) is constructed based on the service data layer. The DWT layer is a wide table layer for themes. It further summarizes the runtime data of different themes in the DWS layer to obtain the full data table for each theme.

[0058] In this embodiment, multiple analysis models are established for turbine equipment, including vibration analysis, shutdown analysis, status analysis, operation analysis, maintenance analysis, and life analysis. Data and information from the DWS layer are further joined and integrated, and business process-related data are redundantly processed based on themes, thereby facilitating the use of data by upper-layer services.

[0059] 5) Data Application Layer (ADS): Based on the topic data layer, the data application layer (ADS) is constructed to calculate and store personalized statistical indicator data designed for the health analysis of hydropower production equipment.

[0060] In this embodiment, statistical data on equipment health assessment indicators, deterioration degree, and remaining life estimation of the turbine generator set are stored to facilitate turbine generator set operation status and production analysis and decision-making.

[0061] Step 3: Establish a data model for hydropower production equipment;

[0062] Design and implementation are independent, expressive conceptual models. The conceptual model design is based on a dimensional model to describe the attribute mapping from data sources to data warehouse objects in the ETL process. In this embodiment, starting with the current data storage status of multiple systems such as computer monitoring systems, water resources management systems, protection fault information systems, and online monitoring systems, the range of data sources is selected according to the theme and objectives. Mappings of the corresponding tables in the source and target database systems are established, and the storage structure of various data sources in the data warehouse is determined by the definitions of each dimension table in the metadata.

[0063] Based on the conceptual model, a logical model is constructed, outlining the enterprise's business information blueprint through entities, attributes, and relationships. In the embodiment, a logical table structure for the snowflake dimension model is designed from multiple dimensions, including time, operating cycle, and maintenance.

[0064] Currently, the mainstream logical modeling methods mainly include relational modeling and dimensional modeling. Relational modeling uses the entity-relationship model as its foundation and requires a relational database for data storage. Dimensional modeling uses two types of data as its foundation: facts and dimensions. A fact refers to a single measurement that occurs during the business process, while a dimension refers to the context in which this measurement occurs. A single measurement, i.e., a row in the fact table, can contain multiple measurement results and multiple associated dimensional information. In hydropower production, equipment time-series data from computer monitoring systems is a typical example of fact data: sampling from multiple sensors generates multiple test measurement data, such as temperature, vibration, and sway. Simultaneously, this fact data will have at least two dimensions—timestamp and measurement point ID. Considering that equipment maintenance records are also important criteria for judging equipment health, the implementation example designs multiple dimensions such as time dimension, operating cycle dimension, and maintenance dimension. Figure 3 The diagram shows the logical table structure of the snowflake dimension model. The snowflake model is an extension of the star schema, allowing for further relationships between dimension tables. In other words, the fact table can obtain indirect dimensions through the relationships between dimensions.

[0065] Step 4: Design of the Physical Model for Hydropower Production Data Warehouse Storage: Based on the logical data model, and considering various specific technical implementation factors, the database architecture is designed to truly realize the storage of data in the database. The design of table storage, key joins, indexes, and partitions is based on the TDengine time-series database cluster design, with a focus on topics such as turbine unit health assessment. Specific table structures for fact tables and dimension tables are designed.

[0066] TDengine's time-series database is a columnar storage database. Its primary characteristic is that each acquisition point corresponds to a single table, ensuring data storage continuity and significantly reducing random read operations. Simultaneously, each table corresponds to a single signal, avoiding the repeated storage of fixed static information such as signal names. Furthermore, the numerical range of corresponding engineering signals is generally fixed within a certain approximate range. The columnar storage structure and compression algorithms not only improve data compression rates but also optimize the retrieval efficiency, addressing the problem of low read efficiency caused by row-by-row reading in scenarios where queries focus on a specific column or a few columns. This significantly improves retrieval efficiency.

[0067] The second major feature implemented in this embodiment using the TDengine time-series database is the efficient aggregation of multiple data collection points using a super table. Static labels are stored separately as key-value pairs. During multidimensional aggregation analysis, labels are filtered first to significantly reduce the amount of aggregated data. Efficient multidimensional retrieval is performed based on static labels. Furthermore, the super table structure can be inherited to quickly create ordinary tables, simplifying the complexity of creating new tables and establishing a lineage relationship.

[0068] Step 4.1: Design and build the dimension table;

[0069] Based on the characteristics and usage rules of the TDengine database product's storage structure, a special dimension table structure was designed. First, related measurement points from the same device at the same frequency are grouped into a single super table. The columns of the super table are divided into two parts: a dynamic part and a static part. The dynamic data is the collected data; the first column is the timestamp, and the other columns are the collected physical quantities. The collected physical quantities contained in a super table must have been collected simultaneously, meaning the timestamps must be identical. For a given type of device, there may be multiple sets of physical quantities, and each set of physical quantities may not have been collected simultaneously; therefore, a separate super table needs to be created for each set of physical quantities. The columns in the static part are also called tags. A super table can contain multiple tags, and measurement point dimension information, device dimension information, etc., can be added as tags. Since the dimension table belongs to the DIM layer, the tag name is named "dim_field meaning" as the dimension field, as shown in Tables 2 and 3.

[0070] Table 2 Measurement Point Dimension Table

[0071]

[0072] Table 3 Equipment Dimension Table

[0073]

[0074] Step 4.2: Design and build the fact table;

[0075] "Facts" represent the metrics of business events, such as the vibration value of a turbine unit at a certain moment. The table records the transaction time information generated during the business process, with the finest granularity of time. It is not aggregated or rolled up according to any dimension, which ensures that it can meet various business statistical analysis scenarios to the greatest extent. Moreover, this fact data will continue to grow over time, resulting in a large data volume. The fact table is stored in the DWD layer of the data warehouse, so the naming convention for fact tables is "dwd_field meaning", as shown in Tables 4 and 5.

[0076] Table 4 Fact Table of Equipment Running Sequence Data

[0077]

[0078] Table 5 Equipment Maintenance Fact Sheet

[0079]

[0080] In summary, a hydropower production data warehouse was constructed based on the time-series database TDengine. This warehouse unifies the extraction, transformation, and loading of various system data sources, including hydropower plant computer monitoring systems, water situation and dispatch systems, protection fault information systems, and online monitoring systems. After calculation and aggregation, the data is stored in pre-designed fact tables at different levels, achieving centralized and unified data management. The data warehouse's data backup, data security control, and access control functions ensure data security and reliability. This reduces the manual workload of data collection, organization, and management, improves the efficiency of data processing and analysis, and simultaneously reduces enterprise costs and increases efficiency.

Claims

1. A method for constructing a distributed data warehouse for health monitoring of hydropower production equipment, characterized in that, Includes the following steps: Step 1: Analyze the data application requirements of hydropower equipment and determine the analysis topic of hydropower equipment; The analysis topics for the hydropower equipment include turbine units, generators, and GIS; Step 2: Construct a layered data warehouse architecture comprising the raw data layer ODS, detailed data layer DWD, service data layer DWS, thematic data layer DWT, and data application layer ADS; Source data is obtained from the monitoring systems of various equipment in the hydropower station and a raw data layer (ODS) is constructed. Unprocessed raw data is stored in the data warehouse. The raw data layer is structurally consistent with the source system and serves as the data preparation area for the data warehouse. Establish a unified standard and specification for the data ETL process of the data sources of the computer monitoring system, water information and dispatching system, protection fault information system and online monitoring system of hydropower plants, so as to realize the unified extraction, transformation and loading of data; Design and build a sub-table for each data acquisition point. All sub-tables corresponding to data acquisition points belong to the same super table. The super table contains dynamic attributes and static attributes. The dynamic attributes of the super table include timestamp and values ​​of each physical quantity of the acquisition point. The static attributes of the super table include source of the measurement point and description of the measurement point. Step 3: Establish a data model for hydropower production equipment; Step 3.1: Establish the mapping between the tables corresponding to the source and target database systems, and determine the storage structure of various data sources in the data warehouse based on the definitions of each dimension table in the metadata; Step 3.2: Design the logical table structure of the snowflake dimension model from multiple dimensions, including time, operation cycle, and maintenance. Step 4: Construct a physical model of the hydropower production data warehouse, including dimension tables and fact tables; Step 4.1: Design and construct a dimension table to group related measurement points of the same device at the same frequency into the same super table. The columns of the super table are divided into dynamic and static parts. The dynamic part is the collected data, the first column is the timestamp, and the other columns are the collected physical quantities. The static part is the label column; Step 4.2: Design and build a fact table to store the metrics of business events.

2. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 1, characterized in that, The method for constructing a distributed data warehouse establishes a data warehouse data management model based on the characteristics of hydropower production data. The data management model includes metadata management, generation and retrieval of dormant data, generation and cleanup of dirty data, and monitoring of dormant and dirty data.

3. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 1, characterized in that, In step 2, the health monitoring business of hydropower production equipment is used as the modeling driver to construct the detailed data layer DWD, and the original data layer ODS is cleaned, dimension degraded, and desensitized, and a dimensional model is constructed. For the data related to the operation of the hydro-turbine unit, the source data is divided into several dimension tables and fact tables of the hydro-turbine unit operation status, including a vibration power generation state table and a vibration shutdown state table, based on the unit's operating condition.

4. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 3, characterized in that, Based on the streaming computing capabilities of the TDengine time series database, a function is designed and provided to aggregate and query the results of operating conditions by dividing the data into time-period windows. The windows include equally spaced windows and status windows. The simulation data of current, voltage, reactive power, and active power are divided into time windows with equal time intervals. The average, maximum, and minimum values ​​of the aggregated streaming calculation are calculated within each window. The real-time results of the streaming calculation are stored in a new sub-table, and a timestamp alignment function is provided. The status and unit status switch data of circuit breakers are aggregated using status windows. The average and maximum values ​​of the collected values ​​in each status window are aggregated and calculated using streaming, and the duration and number of data records are counted. The real-time results of the streaming calculation are stored in a new sub-table, providing data statistics functions under a unified status.

5. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 4, characterized in that, In step 2, the service data layer DWS is constructed based on the detailed data layer DWD, using the relevant subject objects of health analysis of hydropower generation equipment as the modeling driver, and summarizing them according to the unit operation cycle; The power generation efficiency index, operation vibration index, and bearing temperature index are summarized on a daily, monthly, or turbine unit operating cycle basis.

6. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 5, characterized in that, In step 2, the topic data layer DWT is constructed based on the service data layer DWS, and the runtime data of different topics of the service data layer DWS are further summarized to obtain the full data table of each topic. Establish models for vibration and sway analysis, shutdown analysis, status analysis, operation analysis, maintenance analysis, and life analysis of hydro-turbine equipment. Further integrate the data and information of the DWS service data layer and perform redundancy processing on business process-related data based on themes, thereby facilitating the use of data by upper-layer services.

7. The method for constructing a distributed data warehouse for health monitoring of hydropower production equipment according to claim 6, characterized in that, In step 2, the data application layer ADS is constructed based on the topic data layer DWT to calculate and store personalized statistical indicator data for health analysis of hydropower generation equipment. Data on equipment health assessment indicators, deterioration rate, and remaining life of the turbine generator unit are stored to facilitate the analysis and decision-making regarding the turbine generator unit's operating status and production.

Images