Generator set data warehouse construction method and system based on operation state

Abstract

The invention discloses a generator set data warehouse construction method and system based on an operation state, and relates to the field of data warehouses, and the method comprises the steps: collecting the operation parameters of a generator set in real time through a sensor and a monitoring device; preprocessing operation including cleaning, de-noising and standardizing is carried out on the collected operation parameters so as to improve the data quality; effective characteristic quantities are extracted from the preprocessed data to be used for describing the operation state of the generator set; converting the extracted characteristic quantity into a format conforming to a data warehouse mode; and loading the converted data into a data warehouse, and establishing a target index and a materialized view to optimize query performance. By means of the scheme, high-performance storage and management of the power generation data can be achieved, and the flexible data retrieval requirement is met.

Description

Technical Field

[0001] The present application relates to the field of data warehouses, and in particular to a method and system for constructing a generator set data warehouse based on operating status. Background Art

[0002] The data storage method of generator sets based on the operating status refers to the use of technical means such as sensors and monitoring equipment to collect and record various operating parameter data of generator sets in real time, and manage and analyze them through specific data storage methods. As an important part of the power system, the operating status of the generator set directly affects the stability and safety of the power. Therefore, accurately recording and analyzing the operating data of the generator set is of great significance for improving power generation efficiency, reducing maintenance costs and preventing failures. Traditionally, the operating data storage methods of generator sets mainly include manual recording, regular inspection, and recording through a dedicated monitoring system. The manual recording method involves the operation and maintenance personnel to manually record various parameters of the generator set on a regular basis. This method is not only time-consuming and labor-intensive, but also prone to data omissions and recording errors. The regular inspection method improves the accuracy of the data by regularly conducting a comprehensive inspection and data collection of the generator set. However, due to the low frequency of data collection, it is impossible to monitor the operating status of the equipment in real time, and it is difficult to detect and prevent failures in time. The dedicated monitoring system collects data in real time through sensors installed on the generator set, and transmits the data to the central control system for storage and analysis. This method improves the real-time and accuracy of the data, but the data storage method of the traditional monitoring system is mostly centralized storage, which is easily affected by single point failures, and the data storage capacity is limited, making it difficult to process massive data.

[0003] At present, in order to solve the shortcomings of traditional data storage methods, the industry has proposed a variety of improvement methods. For example, the data storage method based on cloud computing uses the distributed storage technology of the cloud platform to achieve efficient storage and management of data. The cloud platform not only provides a large-capacity storage space, but also supports high-concurrency data access and processing, which can effectively cope with the storage needs of massive data. In addition, the data storage method based on edge computing achieves local storage and processing of data by deploying edge nodes close to the data source, reducing the delay of data transmission and improving the real-time performance of data processing. Although these methods have solved the shortcomings of traditional data storage methods to a certain extent, there are still some defects. For example, the data storage method based on cloud computing relies on network connection. When the network connection is unstable, the transmission and storage of data may be affected; while the data storage method based on edge computing improves the real-time performance of data processing, it is difficult to cope with the storage needs of large-scale data due to the limited computing and storage resources of edge nodes.

[0004] Therefore, there is an urgent need for a technical solution that can achieve high-performance storage and management of power generation data and meet flexible data retrieval requirements. Summary of the invention

[0005] In order to solve the deficiencies of the prior art, the embodiment of the present application provides a method and system for constructing a generator set data warehouse based on operating status. The present application solves the technical problems that the storage and management performance of power generation data in the prior art is low and cannot meet the flexible data retrieval requirements.

[0006] The embodiment of the present application provides a method for constructing a generator set data warehouse based on operating status, including: collecting operating parameters of the generator set in real time through sensors and monitoring equipment; performing preprocessing operations including cleaning, denoising and standardization on the collected operating parameters to improve data quality; extracting effective feature quantities from the preprocessed data to describe the operating status of the generator set; converting the extracted feature quantities into a format that conforms to the data warehouse model; and loading the converted data into the data warehouse, and establishing target indexes and materialized views to optimize query performance.

[0007] In one possible implementation, the operating parameters of the generator set are collected in real time through sensors and monitoring equipment, including: monitoring the temperature of key parts of the generator set to obtain temperature data; the parts include generators, transformers and cooling systems; measuring the pressure of steam, water or oil media to obtain pressure data reflecting the operating status and safety level of the equipment; recording the flow rate of water, oil or gas fluid to obtain flow data for calculating efficiency and monitoring potential leakage or blockage; and real-time monitoring of electrical parameters to obtain power data for evaluating power output and grid stability.

[0008] In a possible implementation, the collected operating parameters are subjected to preprocessing operations including cleaning, denoising and standardization to improve data quality, including: S1, describing the dynamic characteristics of the operating parameters based on a first-order Markov process to establish a state transfer equation; S2, combining the linear relationship between the measured value and the true value to establish an observation equation; S3, setting the initial operating parameter estimate and the initial estimate error covariance matrix; S4, predicting the operating parameters at the current moment based on the operating parameter estimate and the state transfer equation at the previous moment; S5, correcting the predicted value using the measured value at the current moment to obtain an updated operating parameter estimate; and iteratively repeating steps S4 and S5 to predict and update the operating parameter estimate until the data processing is completed.

[0009] In a possible implementation, extracting effective feature quantities from preprocessed data for describing the operating status of a generator set includes: acquiring sampling data of the preprocessed target information in the time domain to obtain a discrete time domain sampling sequence; performing an FFT transform on the discrete time domain sampling sequence to obtain a target spectrum; based on the target spectrum, calculating the frequency value corresponding to the frequency component with the largest energy to obtain the main frequency component; based on the target spectrum, calculating the maximum value of the spectrum amplitude within the target frequency band to obtain the strength of the signal within the frequency band; and based on the target spectrum, calculating the accumulated value of the square of the spectrum value within the frequency band to obtain the severity of vibration within the frequency band.

[0010] In a possible implementation, converting the extracted feature quantity into a format that conforms to a data warehouse model includes: constructing a data warehouse of a star model or a snowflake model using an ETL tool based on the extracted feature quantity.

[0011] In a possible implementation, the transformed data is loaded into a data warehouse, and a target index and a materialized view are established to optimize query performance, including: using an incremental loading method to update only the changed data.

[0012] In a possible implementation, after loading the converted data into a data warehouse and establishing target indexes and materialized views to optimize query performance, it also includes: using the data in the data warehouse to perform multidimensional analysis and data mining to explore the laws and trends of the operating status of the generator set; wherein the multidimensional analysis and data mining include using an association rule mining algorithm to find typical patterns that lead to generator set failures.

[0013] In one possible implementation, using an association rule mining algorithm to find typical patterns that lead to generator set failures includes: using an Apriori association rule mining algorithm to find frequently occurring parameter combinations in a data warehouse to obtain frequent item sets; and generating association rules based on the frequent item sets, wherein the association rules are used to calculate support and confidence for evaluation and interpretation.

[0014] The embodiment of the present application also provides a system for constructing a generator set data warehouse based on operating status, including: a collection unit, used to collect the operating parameters of the generator set in real time through sensors and monitoring equipment; a preprocessing unit, used to perform preprocessing operations including cleaning, denoising and standardization on the collected operating parameters to improve data quality; a feature extraction unit, used to extract effective feature quantities from the preprocessed data to describe the operating status of the generator set; a conversion unit, used to convert the extracted feature quantities into a format that conforms to the data warehouse model; and a loading unit, used to load the converted data into the data warehouse, and establish target indexes and materialized views to optimize query performance.

[0015] An embodiment of the present application also provides a device for constructing a generator set data warehouse based on operating status, including: a processor, a memory, and a system bus; wherein the processor and the memory are connected via the system bus; the memory is used to store one or more programs, and the one or more programs include instructions, and when the instructions are executed by the processor, the processor executes the construction method described in the above embodiment.

[0016] Through the method, system and equipment for constructing a generator set data warehouse based on operating status as provided above, the embodiment of the present application can achieve high-performance storage and management of power generation data and meet flexible data retrieval requirements by introducing a data warehouse to store motor data in real time. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0018] Figure 1 A schematic flow chart of a method for constructing a generator set data warehouse based on operating status provided in an embodiment of the present application;

[0019] Figure 2 A schematic diagram of a system for constructing a generator set data warehouse based on operating status provided in an embodiment of the present application. DETAILED DESCRIPTION

[0020] Various exemplary embodiments of the present application will now be described in detail with reference to the accompanying drawings. It should be noted that unless otherwise specifically stated, the relative arrangement of components and steps, numerical expressions and numerical values ​​set forth in these embodiments do not limit the scope of the present application.

[0021] Those skilled in the art will appreciate that the terms "first", "second" and the like in the embodiments of the present application are only used to distinguish different steps, devices or modules, etc., and do not represent any specific technical meaning, nor do they represent the necessary logical order between them. It should also be understood that in the embodiments of the present application, "multiple" may refer to two or more, and "at least one" may refer to one, two or more. It should also be understood that for any component, data or structure mentioned in the embodiments of the present application, in the absence of explicit limitation or contrary revelation given in the context, it can generally be understood as one or more. In addition, the term "and / or" in the present application is only a kind of association relationship describing the associated objects, indicating that there can be three kinds of relationships, for example, A and / or B, which can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in the present application generally indicates that the associated objects before and after are an "or" relationship. It should also be understood that the description of each embodiment in the present application emphasizes the differences between the embodiments, and the same or similar parts can refer to each other. For the sake of brevity, they will not be repeated one by one.

[0022] At the same time, it should be understood that, for ease of description, the sizes of the various parts shown in the drawings are not drawn according to the actual proportional relationship. The following description of at least one exemplary embodiment is actually only illustrative and is by no means intended to limit the present application and its application or use. The techniques, methods and devices known to ordinary technicians in the relevant fields may not be discussed in detail, but where appropriate, the techniques, methods and devices should be considered as part of the specification. It should be noted that similar numbers and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further discussed in subsequent drawings.

[0023] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0024] Figure 1 The flowchart of the method for constructing the generator set data warehouse based on the operating status provided in the embodiment of the present application is as follows. Figure 1As shown, at step S101, the operating parameters of the generator set are collected in real time through sensors and monitoring equipment. In one embodiment, it includes: monitoring the temperature of each key part of the generator set to obtain temperature data; the parts include generators, transformers and cooling systems; measuring the pressure of steam, water or oil medium to obtain pressure data reflecting the operating status and safety level of the equipment; recording the flow of water, oil or gas fluid to obtain flow data for calculating efficiency and monitoring potential leakage or blockage; and real-time monitoring of electrical parameters to obtain power data for evaluating power output and grid stability.

[0025] Furthermore, temperature data is one of the important indicators of the operating status of the generator set. By placing temperature sensors in key parts such as generators, transformers and cooling systems, the temperature data of each part can be accurately obtained. These data can be used to determine whether the equipment is within the normal operating temperature range, thereby preventing the risk of equipment damage caused by overheating. For example, inside the generator, the winding temperature is a key parameter. Excessive temperature may cause aging or even burning of the winding insulation material; in the transformer, the oil temperature and the core temperature are parameters that need to be monitored. Excessive oil temperature may reduce the service life of the transformer.

[0026] Pressure data is also an important parameter that reflects the operating status of the generator set. By arranging pressure sensors in steam pipes, water cooling systems and oil circulation systems, the pressure data of these media can be obtained in real time. These data can reflect the load and operating status of the equipment. For example, too high steam pressure may cause pipe rupture or safety valve action, and too low pressure in the water cooling system may affect the cooling effect and cause the equipment to overheat.

[0027] Flow data is a key parameter used to evaluate the operating efficiency of generator sets and identify potential problems. By installing flow meters on water, oil or gas fluid pipelines, the flow data of these media can be recorded in real time. This data can be used to calculate the energy conversion efficiency of the system and monitor the operating status of the system. For example, a reduction in cooling water flow may indicate a blockage in the cooling system, and an abnormal oil flow may indicate a leak or blockage in the oil circuit.

[0028] Electrical parameters are important indicators for evaluating the power output of generator sets and the stability of power grids. Voltage and current sensors can be used to monitor the voltage, current, power factor and other parameters of generator sets in real time. These data can reflect the load of generator sets, power quality and the operating status of power grids. For example, voltage fluctuations may affect the stability of the power grid, and abnormal power factor may lead to increased power loss.

[0029] At step S102, the collected operating parameters are subjected to preprocessing operations including cleaning, denoising and standardization to improve data quality. It should be understood that the purpose of data cleaning is to remove noise and outliers in the data to ensure the accuracy and consistency of the data. During the data collection process, the collected data may contain noise and outliers due to sensor errors, environmental interference and other reasons. For example, a temperature sensor may be subject to electromagnetic interference, resulting in a sudden change in temperature readings; a pressure sensor may produce abnormally high pressure values ​​due to instantaneous impacts of the equipment. Therefore, these noises and outliers need to be processed through data cleaning technology. Commonly used data cleaning methods include, but are not limited to: filtering, interpolation, statistical analysis, etc.

[0030] On the basis of data cleaning, the data is further denoised to eliminate random noise in the data. Common denoising methods include low-pass filtering, Kalman filtering, wavelet transform, etc. Low-pass filtering reduces high-frequency noise in the data through smoothing; Kalman filtering is based on the state space model and suppresses noise by predicting and correcting the data at the current moment; wavelet transform removes noise components in the high-frequency band by decomposing the data into different frequency bands.

[0031] The purpose of data standardization is to convert data of different dimensions to the same scale for comparison and analysis. Common data standardization methods include normalization and standard deviation standardization. Normalization scales the data to the interval [0, 1], which is suitable for situations where the data range is relatively fixed; standard deviation standardization converts the data into a standard normal distribution with a mean of 0 and a standard deviation of 1, which is suitable for situations where the data range varies greatly.

[0032] Further, in one embodiment:

[0033] S1. Describe the dynamic characteristics of the operating parameters based on the first-order Markov process to establish the state transfer equation: The first-order Markov process assumes that the operating parameters at the current moment are only related to the operating parameters at the previous moment, and are independent of the parameters at earlier moments. This assumption simplifies the complexity of the model and facilitates practical application. The state transfer equation is usually expressed in linear form, that is, Xt = A*Xt-1+wt, where Xt represents the operating parameter vector at the current moment, A is the state transfer matrix, and wt is the process noise.

[0034] S2. Combine the linear relationship between the measured value and the true value to establish the observation equation; the observation equation describes the relationship between the sensor measurement value and the actual operating parameters. It is usually expressed in a linear model, that is, Yt = H*Xt + vt, where Yt represents the measurement value vector at the current moment, H is the observation matrix, and vt is the measurement noise. The observation equation can link the measured value with the true value and provide a basis for subsequent parameter correction.

[0035] S3, set the initial operating parameter estimates and the initial estimation error covariance matrix; the initial operating parameter estimates X t0 It can be set based on historical data or empirical values, and the initial estimation error covariance matrix P0 reflects the uncertainty of the initial estimation value.

[0036] S4, predict the operating parameters at the current moment based on the estimated operating parameter values ​​at the previous moment and the state transfer equation; based on the state transfer equation, convert the estimated operating parameter values ​​X at the previous moment t-1 and the estimated error covariance matrix P t-1 The prediction is performed through the state transfer equation to obtain the predicted operating parameter value Xt- and the prediction error covariance matrix Pt- at the current moment. The prediction formula is as follows: Xt-=A*Xt-1, Pt-=A*Pt-1*A^T+Q, where Q is the process noise covariance matrix, which reflects the uncertainty in the state transfer process.

[0037] S5. Correct the predicted value using the current measurement value to obtain the updated estimated value of the operating parameter. Based on the observation equation and the current measurement value Yt, the predicted value is corrected by the Kalman gain K to obtain the updated estimated value of the operating parameter Xt and the updated estimated error covariance matrix Pt. The correction formula is as follows: Kt = Pt-*H^T*(H*Pt-*H^T+R)^-1, Xt = Xt-+Kt*(Yt-H*Xt-), Pt = (I-Kt*H)*Pt-. Among them, R is the measurement noise covariance matrix, which reflects the uncertainty in the measurement process, and I is the unit matrix.

[0038] Iterate and repeat steps S4 and S5 to predict and update the estimated values ​​of the operating parameters until the data processing is completed. By continuously iterating the prediction and correction steps, dynamic tracking and real-time estimation of the operating parameters can be achieved. The result of each iteration will further correct the previous estimate, thereby improving the accuracy and robustness of the estimate. In addition, in order to improve the efficiency and reliability of data preprocessing, modern big data processing technology and machine learning algorithms can be combined. For example, big data platforms such as Hadoop or Spark can be used to achieve distributed storage and processing of massive data; machine learning frameworks such as TensorFlow or PyTorch can be used to achieve automatic cleaning, denoising and standardization of data. The combination of these technologies can not only improve the efficiency of data preprocessing, but also improve the level of intelligence in data processing, and further improve data quality and analysis accuracy.

[0039] At step S103, effective feature quantities are extracted from the preprocessed data to describe the operating status of the generator set. In one embodiment, the sampling data of the preprocessed target information in the time domain is obtained to obtain a discrete time domain sampling sequence. The sampling data of the target information in the time domain is obtained from the preprocessed operating parameter data. The time domain sampling data refers to the continuous measurement results of the equipment operating parameters by the sensor within a specific time interval. The time domain data reflects the changes of the equipment operating parameters over time, such as temperature, pressure, flow and electrical parameters. These time domain data can be recorded at a certain sampling frequency by a data acquisition system to form a discrete time domain sampling sequence.

[0040] Perform an FFT transform on the discrete time domain sampling sequence to obtain a target spectrum. Perform a fast Fourier transform (FFT) on the discrete time domain sampling sequence to obtain a target spectrum. FFT transform is a mathematical tool that converts time domain signals to frequency domain. By performing an FFT transform on the time domain sampling data, the amplitude and phase distribution of the signal in the frequency domain can be obtained. Frequency domain signals are usually represented by a spectrum diagram, in which the horizontal axis is the frequency and the vertical axis is the amplitude corresponding to the frequency. The spectrum diagram can be used to observe the energy distribution of different frequency components in the signal, thereby analyzing the vibration characteristics and spectrum characteristics of the equipment.

[0041] Based on the target spectrum, the frequency value corresponding to the frequency component with the largest energy is calculated to obtain the main frequency component. The main frequency component refers to the frequency component with the largest amplitude in the spectrum diagram, which usually reflects the main vibration mode of the equipment during operation. For example, when the generator set is operating normally, its main frequency component may correspond to the rotation frequency or mechanical vibration frequency of the equipment. If the main frequency component changes or abnormal frequency components appear, it may indicate that the equipment is faulty or the operating status is abnormal.

[0042] Based on the target spectrum, the maximum value of the spectrum amplitude in the target frequency band is calculated to obtain the strength of the signal in the frequency band. The strength of the signal in the frequency band refers to the maximum amplitude of the signal in a specific frequency range. By selecting different frequency bands, the vibration characteristics of the equipment in different frequency ranges can be analyzed. For example, you can select the low frequency band, the medium frequency band, and the high frequency band, and calculate the strength of the signal in each frequency band respectively, so as to evaluate the vibration of the equipment in different frequency ranges. The change of the signal strength in the frequency band can reflect the change of the operating status of the equipment, such as increased vibration, increased wear, etc.

[0043] And based on the target spectrum, the cumulative value of the square of the spectrum value in the frequency band is calculated to obtain the severity of the vibration in the frequency band. The severity of the vibration in the frequency band is measured by calculating the cumulative value of the square of the spectrum value in the frequency band (i.e., the spectrum energy). The spectrum energy reflects the total energy of the signal in a specific frequency band and can effectively describe the vibration intensity and vibration energy distribution of the equipment. For example, in mechanical equipment, an increase in vibration energy may indicate problems such as bearing wear and poor gear meshing. By analyzing the spectrum energy, potential faults and abnormal operating conditions of the equipment can be identified.

[0044] At step S104, the extracted feature quantities are converted into a format that conforms to the data warehouse model. In one embodiment, based on the extracted feature quantities, an ETL tool is used to construct a data warehouse of a star model or a snowflake model. Specifically, data conversion includes the following aspects:

[0045] Extracted feature quantities: Feature quantities are key indicators extracted from the original operating parameter data, which are used to describe the operating status and performance of the generator set. For example, feature quantities such as frequency components, signal strength within the frequency band, and vibration severity. In order to store these feature quantities in the data warehouse, they need to be formatted and structured.

[0046] Data warehouse model selection: The choice of data warehouse model depends on the complexity of the data and query requirements. Star model and snowflake model are two commonly used data warehouse models. Star model: In the star model, the data warehouse consists of a fact table and multiple dimension tables. The fact table stores business events or transaction data, while the dimension table stores the dimensional information that describes the business events. The star model has a simple structure and high query performance, and is suitable for scenarios with simpler data relationships. Snowflake model: In the snowflake model, the dimension table is further normalized and split into multiple sub-tables. In this way, the structure of the snowflake model is more complex, but it can reduce data redundancy and improve data consistency. The snowflake model is suitable for scenarios with complex data relationships and more dimensional levels.

[0047] Use ETL tools for data conversion: ETL (Extract, Transform, Load) tools are used to extract data from the source system, clean and transform it, and then load it into the data warehouse. ETL tools can automate a large number of data conversion tasks to ensure data consistency and accuracy. Commonly used ETL tools include Informatica, Talend, Microsoft SQL Server Integration Services (SSIS), etc.

[0048] The specific implementation steps are as follows:

[0049] Extract data: Extract the pre-processed operating parameter characteristics from the data source system. The data source can be a real-time data acquisition system, a sensor network, a log file, etc. The extracted data includes frequency components, signal strength within the frequency band, vibration severity and other characteristics.

[0050] Data transformation: Use ETL tools to transform the extracted data and format it into a structure that conforms to the data warehouse schema. For example, frequency components can be transformed into a frequency table, signal strength can be transformed into a signal strength table, and vibration severity can be transformed into a vibration table. Each table contains relevant dimensional information such as time, equipment, location, etc.

[0051] Data loading: Load the converted data into the data warehouse. The loading process needs to ensure the consistency and integrity of the data. The tables in the data warehouse can be designed and organized according to the star model or the snowflake model. Star model: Build a fact table that contains all the operating parameter characteristics and establish multiple dimension tables. For example, a fact table can include fields such as time, equipment, location, frequency component, signal strength, and vibration severity. Dimension tables can include time dimension tables, equipment dimension tables, location dimension tables, and so on. Snowflake model: Further normalize the dimension table and split it into multiple sub-tables. For example, the equipment dimension table can be split into equipment type table, equipment model table, equipment location table, and so on. In this way, the hierarchy of the dimension table is more refined, but the query and analysis of the data may be more complicated.

[0052] Data indexing and optimization: To improve the performance of data queries, you can create indexes and optimize data structures in the data warehouse. For example, you can create indexes on key fields in the fact table, such as time fields, equipment fields, etc. You can also use partitioning technology to divide data into multiple subsets to improve query efficiency.

[0053] Data verification and monitoring: After data loading is completed, the data needs to be verified and monitored to ensure its accuracy and completeness. Data verification rules and monitoring tools can be used to regularly check and verify the data in the data warehouse. For example, the uniqueness, completeness, and consistency of the data can be verified.

[0054] In an implementation scenario, a data warehouse is built to monitor the operating status of a generator set. A star model is selected as the data warehouse model. The data warehouse consists of a fact table and multiple dimension tables.

[0055] Fact table name: fact_generator

[0056] Fact table fields: time_id (time ID), device_id (device ID), location_id (location ID), frequency_component (frequency component), signal_strength (signal strength), vibration_severity (vibration severity)

[0057] Time dimension table name: dim_time

[0058] Time dimension table fields: time_id (time ID), date (date), hour (hour), minute (minute)

[0059] Device dimension table name: dim_device

[0060] Device dimension table fields: device_id (device ID), device_type (device type), device_model (device model)

[0061] Location dimension table name: dim_location

[0062] Location dimension table fields: location_id (location ID), location_name (location name), location_description (location description)

[0063] The specific steps for data transformation and loading using ETL tools are as follows:

[0064] 1. Extract pre-processed operating parameter features from the data source system, such as frequency components, signal strength, and vibration severity. The extracted data includes dimensional information such as time, equipment, and location.

[0065] 2. Use ETL tools to convert the extracted data into the structure of the data warehouse schema. First, convert the time information, map the date, hour, and minute information into the time dimension table, and generate time_id. Second, convert the device information, map the device type and model into the device dimension table, and generate device_id. Then, convert the location information, map the location name and description into the location dimension table, and generate location_id. Finally, map the frequency component, signal strength, and vibration severity into the fact table, and store the generated time_id, device_id, and location_id together in the fact table.

[0066] 3. Load the converted data into the data warehouse. First, load the dimension tables, including the time dimension table, the equipment dimension table, and the location dimension table. Then, load the fact table, including all the operating parameter characteristics and dimension information.

[0067] 4. Create indexes on key fields of the fact table, such as time_id, device_id, and location_id fields, to improve data query performance.

[0068] 5. Verify and monitor the data in the data warehouse to ensure the accuracy and completeness of the data. Regularly check and verify the data through data verification rules and monitoring tools.

[0069] Through the above steps, based on the extracted feature quantities, the data warehouse of the star model or snowflake model built by the ETL tool can effectively store and manage the operating parameter data of the generator set and support efficient data query and analysis. The construction of the data warehouse not only improves the efficiency of data organization and management, but also provides important data support and technical guarantee for the operation monitoring and fault diagnosis of the generator set.

[0070] At step S105, the converted data is loaded into the data warehouse, and a target index and a materialized view are established to optimize query performance. Specifically, this may include: using an incremental loading method to update only the changed data.

[0071] It should be noted that the choice of data loading strategy depends on the frequency of data changes and business requirements. Common data loading strategies include full loading and incremental loading. Full loading: Full loading means reloading all data into the data warehouse each time it is loaded. The advantage of full loading is high data consistency, but the disadvantage is that when the amount of data is large, the loading time is long and the system resource consumption is large. It is usually suitable for scenarios with small data volume or infrequent changes. Incremental loading: Incremental loading means loading only the changed data each time it is loaded. The advantages of incremental loading are short loading time and low system resource consumption. It is suitable for scenarios with large data volume and frequent changes. Incremental loading requires detection and recording of data changes, which is usually achieved through mechanisms such as timestamps, version numbers, or change logs.

[0072] In order to achieve incremental loading, data changes need to be detected and extracted. Timestamp: By adding a timestamp field in the data table, the last update time of the data is recorded. Each time you load, only the data updated after the last load is extracted. Version number: By adding a version number field in the data table, the version information of the data is recorded. Each time you load, only the data with a version number greater than the last loaded version number is extracted. Change log: By recording a change log of the data in the data table, each data insertion, update, and deletion operation is recorded. Each time you load, only the changed data recorded in the change log is extracted.

[0073] After extracting incremental data, the data needs to be converted and formatted to conform to the data warehouse model. For example, the timestamp can be formatted into a standard time format, and the version number can be converted to a numeric type. Data conversion and formatting can be automated using ETL tools to ensure data consistency and accuracy.

[0074] Load the converted incremental data into the data warehouse and merge it with the existing data. Insert operation: For newly added data, directly insert it into the corresponding table of the data warehouse. Update operation: For updated data, find and update the corresponding records in the data warehouse. You can match it through the primary key or unique index, and use the SQL UPDATE statement to update. Delete operation: For deleted data, find and delete the corresponding records in the data warehouse. You can match it through the primary key or unique index, and use the SQL DELETE statement to delete it.

[0075] In order to optimize query performance, you can create indexes and materialized views in the data warehouse. Index: Index is a technology that speeds up data query. By creating indexes on key fields of the data table, you can increase the query speed. For example, you can create indexes on the time field, device field, and location field of the fact table to speed up queries based on time, device, and location. Materialized view: Materialized view is a technology that pre-calculates and stores query results. By pre-calculating and storing the results of complex queries, you can greatly improve the query response speed. For example, you can create a materialized view based on the fact table and pre-calculate commonly used aggregate query results, such as average, maximum, minimum, etc., to speed up the query response speed.

[0076] In an implementation scenario, the specific implementation steps combined with SQL language are as follows:

[0077] Incremental data extraction: Incremental data extraction is achieved through the timestamp mechanism. For example, a last_update_time field is added to the data table to record the last update time of the data. Each time the data is loaded, the data with last_update_time after the last load time is extracted.

[0078] Extract SQL example:

[0079] SELECT*FROM source_table WHERE last_update_time>'2023-01-0100:00:00';

[0080] Data conversion and formatting: Use ETL tools to convert and format the extracted incremental data. For example, convert timestamps to standard time formats, convert text types to numeric types, etc. You can use the conversion function of ETL tools to achieve automated data processing.

[0081] Data conversion example:

[0082] SELECT CAST(last_update_time AS TIMESTAMP)AS update_time,

[0083] CAST(text_field AS INTEGER)AS number_field

[0084] FROM source_table;

[0085] Data loading and merging: Load the converted incremental data into the data warehouse and merge it with the existing data. For example, for newly added data, use the INSERT statement to insert; for updated data, use the UPDATE statement to update; for deleted data, use the DELETE statement to delete.

[0086] Insert operation example:

[0087] INSERT INTO target_table(field1, field2, update_time)

[0088] SELECT field1, field2, update_time

[0089] FROM source_table

[0090] WHERE last_update_time>'2023-01-01 00:00:00';

[0091] Update operation example:

[0092] UPDATE target_table

[0093] SET field1=source.field1, field2=source.field2, update_time=source.update_time

[0094] FROM source_table source

[0095] WHERE target_table.id=source.id

[0096] AND source.last_update_time>'2023-01-01 00:00:00';

[0097] Delete operation example:

[0098] DELETE FROM target_table

[0099] WHERE id IN(SELECT id FROM source_table WHERE is_deleted=true);

[0100] Index creation: Create indexes on key fields in the data warehouse to improve query performance. For example, create indexes on the time field, equipment field, and location field in the fact table.

[0101] Index creation example:

[0102] CREATE INDEX idx_time ON fact_table(time_id);

[0103] CREATE INDEX idx_device ON fact_table(device_id);

[0104] CREATE INDEX idx_location ON fact_table(location_id);

[0105] Materialized view creation: Create a materialized view to pre-calculate and store the results of common queries to speed up query response. For example, create a materialized view to pre-calculate the average, maximum, and minimum values ​​of device operating parameters.

[0106] Example of creating a materialized view:

[0107] CREATE MATERIALIZEDVIEW mv_device_stats AS

[0108] SELECT device_id, AVG(parameter)AS avg_param, MAX(parameter)AS max_param, MIN(parameter)AS min_param

[0109] FROM fact_table

[0110] GROUP BY device_id;

[0111] Through the above steps, based on the incremental loading method, the converted data can be efficiently loaded into the data warehouse, and the query performance can be optimized by establishing indexes and materialized views. Incremental loading not only reduces the time of data loading and the consumption of system resources, but also improves the update efficiency and response speed of the data warehouse. The establishment of indexes and materialized views further optimizes the performance of data queries and ensures the timeliness and accuracy of data analysis.

[0112] By using technical means such as incremental loading, indexing and materialized views, the data warehouse can efficiently store and manage large amounts of operating parameter data, support complex data query and analysis, and provide strong data support and technical guarantee for the operation monitoring and fault diagnosis of generator sets.

[0113] Among them, after loading the converted data into the data warehouse and establishing the target index and materialized view to optimize the query performance, it also includes: using the data in the data warehouse to perform multidimensional analysis and data mining to mine the laws and trends of the operating status of the generator set; wherein the multidimensional analysis and data mining include using the association rule mining algorithm to find the typical pattern that causes the generator set failure. Specifically, the Apriori association rule mining algorithm can be used to find the frequently occurring parameter combinations in the data warehouse to obtain the frequent item sets; and the association rules are generated based on the frequent item sets, and the association rules are used to calculate the support and confidence for evaluation and interpretation.

[0114] In one implementation scenario, multi-dimensional analysis and association rule mining are performed on the operating data of the generator set to identify typical patterns that may lead to failures. You can choose to use the Apriori algorithm for association rule mining. The steps are as follows:

[0115] Extract the operating data of the generator set from the data warehouse, including parameters such as temperature, pressure, and vibration. Clean and convert the data to discretize the continuous data. For example, divide the temperature into three intervals: "low temperature", "medium temperature", and "high temperature", divide the pressure into three intervals: "low pressure", "medium pressure", and "high pressure", and divide the vibration into three intervals: "low vibration", "medium vibration", and "high vibration".

[0116] Use OLAP technology to perform multi-dimensional analysis on data. Select the time dimension and equipment dimension, summarize the data of different equipment types by month, and analyze the changing trends and abnormalities of operating parameters. For example, it is found that the temperature and vibration parameters of certain months and certain equipment types often show abnormalities.

[0117] Perform association rule mining: Frequent item set mining: Use the Apriori algorithm to find frequently occurring parameter combinations in the data. For example, it is found that "high temperature" and "high vibration" often appear at the same time. Association rule generation: Generate association rules based on frequent item sets. For example, "if the temperature is high temperature and the vibration is high vibration, then the pressure is high pressure". Calculate the support and confidence, and filter out the rules with high support and confidence.

[0118] Verify and explain the selected association rules. Verify the accuracy and stability of the rules through backtesting of historical data. Explain the physical and practical meanings of the rules, and understand the operating mechanisms and failure causes behind them.

[0119] According to the association rules, formulate strategies for preventive maintenance and optimized operation. For example, when the temperature is high and the vibration is high, perform equipment inspection and maintenance in advance to prevent failures caused by excessive pressure. Optimize the settings of temperature and vibration parameters to avoid parameter combinations that lead to high pressure, and improve the operating efficiency and reliability of the equipment.

[0120] Figure 2 A schematic diagram of a system for constructing a generator set data warehouse based on operating status provided in an embodiment of the present application. It should be understood that the system shown in the figure is exemplary and not restrictive. This means that the system architecture involved is not limited to a specific form or design, but is presented as an example. In other words, the architecture shown in the figure can be regarded as a way of expression to clearly describe related concepts and relationships, and does not exclude other forms of architecture. Therefore, when interpreting the architecture in the picture, it should be understood that the model is flexible and diverse, and its purpose is to provide an exemplary description rather than a restrictive provision on a specific form.

[0121] Specifically, a knowledge graph-driven large-model business intelligence decision-making question-answering system in an embodiment of the present application includes: a collection unit 201, used to collect the operating parameters of the generator set in real time through sensors and monitoring equipment; a preprocessing unit 202, used to perform preprocessing operations including cleaning, denoising and standardization on the collected operating parameters to improve data quality; a feature extraction unit 203, used to extract effective feature quantities from the preprocessed data to describe the operating status of the generator set; a conversion unit 204, used to convert the extracted feature quantities into a format that conforms to the data warehouse model; and a loading unit 205, used to load the converted data into the data warehouse, and establish a target index and materialized view to optimize query performance.

[0122] Furthermore, an embodiment of the present application also provides a device for constructing a generator set data warehouse based on operating status, including: a processor, a memory, and a system bus; the processor and the memory are connected via the system bus; the memory is used to store one or more programs, and the one or more programs include instructions, which, when executed by the processor, enable the processor to execute any of the above-mentioned construction methods.

[0123] Furthermore, an embodiment of the present application also provides a computer program product, which, when executed on a terminal device, enables the terminal device to execute any one of the above-mentioned construction methods.

[0124] It can be known from the description of the above implementation mode that those skilled in the art can clearly understand that all or part of the steps in the above-mentioned embodiment method can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a storage medium such as ROM / RAM, a disk, an optical disk, etc., including several instructions for enabling a computer device (which can be a personal computer, a server, or a network communication device such as a media gateway, etc.) to execute the construction method described in each embodiment of the present application or some parts of the embodiments.

[0125] It should be noted that the various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts between the various embodiments can be referred to each other. For the device disclosed in the embodiment, since it corresponds to the method disclosed in the embodiment, the description is relatively simple, and the relevant parts can be referred to the method part description.

[0126] It should also be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0127] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for constructing a generator set data warehouse based on operating status, characterized in that: include: Collect the operating parameters of the generator set in real time through sensors and monitoring equipment; Perform preprocessing operations including cleaning, denoising and standardization on the collected operating parameters to improve data quality; Extracting effective feature quantities from the preprocessed data to describe the operating status of the generator set; Convert the extracted features into a format that conforms to the data warehouse model; as well as Load the transformed data into the data warehouse and build target indexes and materialized views to optimize query performance.

2. The construction method according to claim 1, characterized in that: The operating parameters of the generator set collected in real time through sensors and monitoring equipment include: Monitor the temperature of key parts of the generator set to obtain temperature data; the parts include the generator, transformer and cooling system; Measure the pressure of steam, water or oil media to obtain pressure data reflecting the operating status and safety level of the equipment; Record the flow of water, oil or gas fluids to obtain flow data for calculating efficiency and detecting potential leaks or blockages; and Electrical parameters are monitored in real time to obtain power data for evaluating power output and grid stability.

3. The construction method according to claim 1, characterized in that: The collected operating parameters are preprocessed including cleaning, denoising and standardization to improve data quality, including: S1. Describe the dynamic characteristics of the operating parameters based on the first-order Markov process to establish the state transfer equation; S2, combining the linear relationship between the measured value and the true value to establish the observation equation; S3, setting the initial operating parameter estimates and the initial estimation error covariance matrix; S4, predicting the operating parameters at the current moment based on the estimated values ​​of the operating parameters at the previous moment and the state transfer equation; S5. Correcting the predicted value using the measured value at the current moment to obtain an updated estimated value of the operating parameter; and Steps S4 and S5 are iteratively repeated to predict and update the estimated values ​​of the running parameters until the data processing is completed.

4. The construction method according to claim 1, characterized in that: The effective feature quantities extracted from the preprocessed data to describe the operating status of the generator set include: Acquire the sampling data of the preprocessed target information in the time domain to obtain a discrete time domain sampling sequence; Performing FFT transformation on the discrete time-domain sampling sequence to obtain a target spectrum; Based on the target spectrum, the frequency value corresponding to the frequency component with the largest energy is calculated to obtain the main frequency component; Based on the target spectrum, calculating the maximum value of the spectrum amplitude in the target frequency band to obtain the strength of the signal in the frequency band; and Based on the target spectrum, the cumulative value of the square of the spectrum value in the frequency band is calculated to obtain the severity of the vibration in the frequency band.

5. The construction method according to claim 1, characterized in that: The conversion of the extracted features into a format that conforms to the data warehouse model includes: Based on the extracted features, use ETL tools to build a star model or snowflake model data warehouse.

6. The construction method according to claim 1, characterized in that: The converted data is loaded into the data warehouse and the target index and materialized view are established to optimize the query performance, including: Use incremental loading to update only the changed data.

7. The construction method according to claim 1, characterized in that: in, After loading the transformed data into the data warehouse and building target indexes and materialized views to optimize query performance, it also includes: The data in the data warehouse is used for multi-dimensional analysis and data mining to explore the laws and trends of the operating status of the generator set; wherein the multi-dimensional analysis and data mining include using an association rule mining algorithm to find out the typical patterns that lead to generator set failures.

8. The construction method according to claim 7, characterized in that: The typical patterns that lead to generator failures are found using association rule mining algorithms, including: Use the Apriori association rule mining algorithm to find frequently occurring parameter combinations in the data warehouse to obtain frequent item sets; and Association rules are generated based on frequent itemsets and used to calculate support and confidence for evaluation and interpretation.

9. A system for constructing a generator set data warehouse based on operating status, characterized in that: include: A collection unit is used to collect the operating parameters of the generator set in real time through sensors and monitoring equipment; A preprocessing unit is used to perform preprocessing operations including cleaning, denoising and standardization on the collected operating parameters to improve data quality; A feature extraction unit, used to extract effective feature quantities from the preprocessed data to describe the operating status of the generator set; A conversion unit, used for converting the extracted feature quantity into a format conforming to a data warehouse mode; as well as The loading unit is used to load the transformed data into the data warehouse and build target indexes and materialized views to optimize query performance.

10. A device for constructing a data warehouse of a generator set based on operating status, characterized in that: include: A processor, a memory, and a system bus; wherein the processor and the memory are connected via the system bus; The memory is used to store one or more programs, and the one or more programs include instructions, and when the instructions are executed by the processor, the processor executes the construction method according to any one of claims 1 to 8.

Images