Grafana-based industrial field BI method and system

Abstract

The invention discloses an industrial field BI method and system based on Grafana, and belongs to the technical field of industrial data visualization, and the method comprises the following steps: S1, configuring a communication network; s2, data transmission and analysis; s3, carrying out data visual display; according to the industrial field BI system, the industrial manufacturing equipment, the industrial personal computer, the database server and the data processing center can integrate data from multiple sources to a visual interface for display, a user can conveniently check and make a decision, and the deployment cost is low.

Description

Technical Field

[0001] The present invention relates to the technical field of industrial data visualization, and in particular to an industrial field BI method and system based on Grafana. Background Art

[0002] Industrial field BI (Business Intelligence) is an integrated technology specifically applied to the industrial field. It aims to apply data analysis, data visualization, data warehouse technology, and online analysis and processing technology in traditional BI technology to industrial production sites, assist enterprises in completing customized data analysis and display, and avoid the problems of long project cycles, complex programming, high learning costs, and weak visualization capabilities in existing industrial visualization systems, which can accelerate the digital transformation of enterprises. Most of the existing BIs analyze and display business data and provide data support for enterprise management and decision-making. Some BI vendors involved in the industrial field are limited to cloud platform deployment rather than deployment on industrial sites. The real-time nature of data cannot be guaranteed and the cost of use is high. In addition, traditional BI software supports very few industrial protocols, and it is difficult to integrate industrial field data of large quantities and multiple types of equipment. It is necessary to add additional gateways or industrial protocol software, which will increase project costs and increase technical complexity. Now a kind of industrial field BI is needed to solve the above problems.

[0003] The existing patent with announcement number CN116132317B discloses an integrated system for industrial Internet data collection, analysis and visualization and its deployment method. The system includes an ETL data collection component, an industrial data warehouse, a data visualization component, a big data analysis component, a general message middleware and an equipment alarm management component; the ETL data collection component collects and pushes data indicators for IT resources and OT resources; the industrial data warehouse receives and persists the data uploaded by the ETL data collection component; the data visualization component displays industrial data in real time and dynamically; the big data analysis component performs real-time predictive analysis on industrial data; the general message middleware stores the industrial equipment data collected in real time by the ETL data collection component for use by the database in the industrial data warehouse; the equipment alarm management component realizes timely notification and early warning functions. The integrated system for industrial Internet data collection, analysis and visualization and its deployment method support very few industrial protocols, and it is difficult to integrate industrial field data of large quantities and multiple types of equipment. It is necessary to add additional gateways or industrial protocol software, which increases project costs. Summary of the invention

[0004] One of the purposes of the present invention is to provide an industrial field BI method based on Grafana, which solves the problem that the traditional industrial Internet supports very few industrial protocols and it is difficult to integrate industrial field data for large quantities and multiple types of equipment.

[0005] In order to achieve the above-mentioned invention object, the technical solution adopted by the present invention is as follows:

[0006] An industrial field BI method based on Grafana includes the following steps:

[0007] S1. Configure the communication network: The industrial manufacturing equipment at the industrial manufacturing site establishes a physical connection with the industrial computer and configures the communication network. The industrial protocol software in the industrial computer performs handshake communication with the industrial manufacturing equipment to establish a communication connection. The industrial computer accesses the production data stored in the industrial manufacturing equipment by polling or batch reading.

[0008] S2. Data transmission and analysis: The production data is processed and analyzed through the JavaScript code preset in the industrial protocol software, and the production data is temporarily cached. Then the industrial protocol software is connected to the MySQL database software through the SQL driver, and the processed production data is stored in the MySQL database software according to the preset time, completing the data storage operation;

[0009] S3. Data visualization: After the production data is stored in the MySQL database software, use the Grafana software to establish a connection with the MySQL database software through the MySQL data source, read the production data to create a visualization interface, add visualization controls according to the types of production data, and add SQL statements in the visualization controls to query the corresponding types of data, and render and display the query results through the visualization controls;

[0010] S4. Data publishing and viewing: Use Grafana to publish the visualization interface and the visualization controls on the interface to a local or cloud-based Web server. Remote or local users use the HTTP protocol to establish a connection with the Web server through the Web client software. By accessing the visualization interface on the Web server, local or remote viewing of production data is achieved. Data from multiple sources can be integrated into the visualization interface for display, which is convenient for users to view and make decisions, and the deployment cost is low.

[0011] Furthermore, in S2, JavaScript deletion code is pre-installed in the industrial protocol software to periodically delete historical data in the MySQL database software to release storage resources in the industrial computer for storing the latest data, thereby improving the utilization of hardware storage resources and reducing deployment costs.

[0012] Preferably, the Web client software in S4 includes Chrome, Edge and IE browsers, which are accessed by common browsers and are convenient for users to use.

[0013] Preferably, in S1, the communication protocols between the on-site industrial manufacturing equipment and the industrial computer include Modbus protocol, OPC UA protocol, Siemens S7 protocol and EtherNet / IP. Different communication protocols are used according to different industrial manufacturing equipment. There is no need to modify the existing equipment, and the deployment cost is low.

[0014] Preferably, in S3, the visualization controls include curve graphs, pie charts, histograms, bar charts, dashboards, historical status diagrams, Gantt charts, funnel charts, heat maps, scatter plots, multi-function tables and / or maps. The visualization controls are in various forms to facilitate user comparison and viewing.

[0015] Preferably, in S2, the preset JavaScript processing codes include energy consumption analysis code, OEE analysis code, production progress analysis code, and fault statistics code, which are used to improve production data processing efficiency.

[0016] Preferably, in S4, the user accesses the visualization interface through a computer, tablet or mobile communication device, and the cost of accessing with a general device is low.

[0017] Furthermore, in S3, when Grafana software reads production data through MySQL to create a visualization interface, it can compare production data with preset data, and alarm for the part that exceeds the preset data, so as to promptly inform users.

[0018] Furthermore, in S3, the alarm methods of Grafana software include publishing alarm icons to visualization controls and sending alarm emails through the server, using a common channel to transmit information with low usage costs.

[0019] The second purpose of the present invention is to provide an industrial field BI system based on Grafana, which solves the problem of difficulty in integrating multiple technical routes of traditional industrial Internet systems.

[0020] In order to achieve the above-mentioned invention object, the technical solution adopted by the present invention is as follows:

[0021] A Grafana-based industrial field BI system is used to implement the Grafana-based industrial field BI method, including industrial manufacturing equipment, an industrial computer, a database server and a data processing center. The industrial manufacturing equipment has multiple built-in sensors for collecting equipment operation status information. The industrial computer provides a CPU, memory, solid-state hard disk, input / output devices and a data transmission interface to provide an operating environment for industrial protocol software, MySQL database software and Grafana software. The database server is used to store data, and the data processing center is used for data analysis. Different types of data on different devices can be integrated to facilitate users to monitor and make decisions in a timely manner.

[0022] The beneficial effects of the present invention are:

[0023] (1) The Grafana-based industrial site BI method applies the data analysis and visualization advantages of Grafana software to the industrial production site. The industrial equipment collects production information and stores it in the database. The Grafana software calls the production data and processes it to create a visualization interface. The visualization control renders and displays the visualization interface, which is convenient for users to call and make decisions. The visualization interface can be published through the Web server, and users can access and read it locally or remotely through the Web client software. It solves the problems of long project cycle, complex program writing, high learning cost, and limited visualization capabilities of traditional industrial visualization systems. At the same time, it avoids the problems of high cost, cloud platform deployment, poor industrial protocol compatibility, and insufficient data real-time performance of traditional BI software, improves the analysis and visualization efficiency of industrial site equipment data, saves enterprise costs, and accelerates the digital transformation of enterprises.

[0024] (2) The Grafana-based industrial field BI method can store data cyclically in the database by presetting deletion codes in the industrial protocol software, thereby achieving the purpose of cyclic use of storage and reducing the layout cost. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 The architecture of the industrial field BI system based on Grafana provided by the present invention;

[0026] Figure 2 The present invention provides a data transmission path for the industrial field BI method based on Grafana. DETAILED DESCRIPTION

[0027] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments in the application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0028] Embodiment 1

[0029] like Figure 1-Figure 2 As shown, this embodiment discloses an industrial field BI method based on Grafana, comprising the following steps:

[0030] S1. Configure the communication network: The industrial manufacturing equipment at the industrial manufacturing site establishes a physical connection with the industrial computer and configures the communication network. The industrial protocol software in the industrial computer performs handshake communication with the industrial manufacturing equipment to establish a communication connection. The industrial computer accesses the production data stored in the industrial manufacturing equipment by polling or batch reading.

[0031] S2. Data transmission and analysis: The production data is processed and analyzed through the JavaScript code preset in the industrial protocol software, and the production data is temporarily cached. Then the industrial protocol software is connected to the MySQL database software through the SQL driver, and the processed production data is stored in the MySQL database software according to the preset time to complete the data storage operation. The MySQL database software is used to store data and improve the speed of data extraction by external programs;

[0032] S3. Data visualization display: After the production data is stored in the MySQL database software, use the Grafana software to establish a connection with the MySQL database software through the MySQL data source, read the production data to create a visualization interface, add visualization controls according to the types of production data, and add SQL statements in the visualization controls to query the corresponding types of data. The query results are rendered and displayed through the visualization controls. The Grafana software can read multiple database software at the same time and then integrate multiple data into the same visualization control for display, and has a strong ability to process complex data;

[0033] S4. Data publishing and viewing: Use Grafana to publish the visualization interface and the visualization controls on the interface to a local or cloud-based Web server. Remote or local users establish a connection with the Web server through the HTTP protocol via the Web client software. By accessing the visualization interface on the Web server, local or remote viewing of production data is achieved. Users can view data without space and time restrictions and can make timely adjustment strategies based on production data, thereby improving production efficiency and yield rate.

[0034] Among them, Grafana is an open source data visualization and monitoring tool, which is widely used in data analysis and system monitoring. It can connect to multiple data sources, display data in real time, and allow users to create interactive dashboards. It has: Flexibility of data sources: Grafana supports multiple data sources, such as Prometheus, log tools, CloudWatch and MySQL database, and can collect and store data from different data sources; Visualization function: Grafana provides a variety of graphic types and plug-ins, can flexibly create charts and dashboards, and supports dynamic adjustment and reuse of charts; Alert function: Grafana can define alert rules according to preset indicators, and send notifications through Slack, email or screen display to ensure that users can take timely actions and make decisions; Open source and flexibility: Grafana is open source, users can run it on their own infrastructure, or choose a fully managed service, support hybrid data sources, allow data from different data sources to be mixed in the same chart, increase the complexity of data processing, and use it here to integrate and display data from different data routes.

[0035] In addition, Grafana users can customize panels through advanced query and transformation functions to create visualization content that meets their individual needs.

[0036] Furthermore, in S2, JavaScript deletion code is pre-installed in the industrial protocol software to periodically delete historical data in the MySQL database software to release storage resources in the industrial computer for storing the latest data, reduce the demand for storage hardware, and reduce deployment costs.

[0037] Furthermore, in S4, users transmit data information to Grafana software through the Web client software, and Grafana software integrates the data into the original visualization interface for personalized data analysis.

[0038] Preferably, in S4, the Web client software includes Chrome, Edge, IE browser, Safari and 360 security browser, and the visual interface is viewed by a common browser without adding additional costs on the user side. It is easy to use and has low deployment costs.

[0039] Preferably, in S1, the communication protocols between on-site industrial manufacturing equipment and the industrial computer include Modbus protocol, OPC UA protocol, Siemens S7 protocol and EtherNet / IP, and are also compatible with Modbus RTU, Modbus TCP, MQTT, BACnet, KNX, TCP, UDP, NMEA0183, and PLC communication protocols of electrical control manufacturers Siemens, AB, Schneider, ABB, Mitsubishi, Omron, Beckhoff, CODESYS, GE, Panasonic, Emerson, Delta, Hitachi, Bosch Rexroth, and Keyence. Different communication protocols are used according to different usage environments, which reduces deployment costs and improves data integration capabilities.

[0040] Preferably, in S3, the visualization controls include curve graphs, pie charts, histograms, bar charts, dashboards, historical status diagrams, Gantt charts, funnel charts, heat maps, scatter plots, multi-function tables and / or maps. The visualization controls can also update the displayed graphs in real time to improve the timeliness of decision-making based on data.

[0041] Preferably, in S2, the preset JavaScript processing code includes energy consumption analysis code, OEE analysis code, production progress analysis code, and fault statistics code, which are used to improve the processing rate of the processing software and reduce energy consumption.

[0042] More preferably, in S4, the user accesses the visualization interface through a computer, tablet or mobile communication device, using a general device for access, thereby reducing the deployment cost.

[0043] Furthermore, in S3, when Grafana software reads production data through MySQL to create a visualization interface, it can compare production data with preset data and issue an alarm for the part that exceeds the preset data. Grafana software issues warning information through the visualization interface, making it convenient for users to process key information in a timely manner and reduce the failure rate of industrial production.

[0044] Furthermore, RTSP video stream is integrated in Grafana. Through the camera on the industrial manufacturing equipment, the production process of the equipment can be displayed in the visualization interface through the RTSP video stream. Users can obtain intuitive industrial manufacturing equipment status images to facilitate fault handling.

[0045] Preferably, in S3, the alarm mode of the Grafana software includes publishing an alarm icon to a visualization control and sending an alarm email to the outside through a server.

[0046] By being compatible with multiple industrial protocols in the industrial protocol software, it collects data from on-site industrial equipment, performs high-level data analysis, and then stores the processed data in the MySQL database; Grafana calls the MySQL database data, renders and displays it through the visualization interface and the visualization controls on the interface, and publishes it to the Web server; users use the browser commonly used by the Web client software to establish a connection with the Web server through the HTTP protocol for access, overcoming the problems of complex programming of traditional industrial visualization systems, poor user interface usability, few supported industrial protocols, and insufficient real-time performance.

[0047] Embodiment 2

[0048] The present embodiment also discloses an industrial field BI system based on Grafana, which is used to implement the industrial field BI method based on Grafana described in any one of claims 1-9, including industrial manufacturing equipment, an industrial computer, a database server and a data processing center. The industrial manufacturing equipment has multiple built-in sensors for collecting the operating status information of the equipment. The industrial computer provides a CPU, memory, a solid-state hard disk, input / output devices and a data transmission interface to provide an operating environment for industrial protocol software, MySQL database software and Grafana software. The database server is used to store data, and the data processing center is used for data analysis.

[0049] The specific working process of the industrial field BI system based on Grafana is as follows:

[0050] On industrial manufacturing equipment or temperature and humidity sensors in workshops, the industrial protocol software collects temperature and humidity data from the temperature and humidity sensors through Modbus RTU, calculates the average, maximum, minimum and median values ​​per hour through JavaScript scripts, and stores the processed data in the MySQL database table through the local MySQL interface; Grafana establishes a connection with the MySQL database through its own MySQL data source; creates a visualization interface in Grafana, adds a Time series visualization control, and adds a SELECT SQL statement in the visualization control to call the temperature and humidity data in the MySQL database; Grafana publishes the visualization interface and its internal visualization controls to the Web server, and the user uses a Web browser to establish a connection with the Web server through the HTTP protocol. The visualization control renders the data called by the SELECT SQL statement into a visual image and displays it in the user's Web browser.

[0051] According to the disclosure and teaching of the above description, those skilled in the art to which the present invention belongs may also change and modify the above embodiments. Therefore, the present invention is not limited to the specific embodiments disclosed and described above, and some modifications and changes of the present invention should also fall within the protection scope of the claims of the present invention. In addition, although some specific terms are used in this specification, these terms are only for the convenience of description and do not constitute any limitation to the present invention.

Claims

1. An industrial field BI method based on Grafana, characterized in that: The following steps are involved: S1. Configure the communication network: The industrial manufacturing equipment at the industrial manufacturing site establishes a physical connection with the industrial computer and configures the communication network. The industrial protocol software in the industrial computer performs handshake communication with the industrial manufacturing equipment to establish a communication connection. The industrial computer accesses the production data stored in the industrial manufacturing equipment by polling or batch reading. S2. Data transmission and analysis: The production data is processed and analyzed through the JavaScript code preset in the industrial protocol software, and the production data is temporarily cached. Then the industrial protocol software is connected to the MySQL database software through the SQL driver, and the processed production data is stored in the MySQL database software according to the preset time, completing the data storage operation; S3. Data visualization: After the production data is stored in the MySQL database software, use the Grafana software to establish a connection with the MySQL database software through the MySQL data source, read the production data to create a visualization interface, add visualization controls according to the types of production data, and add SQL statements in the visualization controls to query the corresponding types of data, and render and display the query results through the visualization controls; S4. Data publishing and viewing: Use Grafana to publish the visualization interface and the visualization controls on the interface to a local or cloud-based Web server. Remote or local users use the HTTP protocol to establish a connection with the Web server through the Web client software, and access the visualization interface on the Web server to view production data locally or remotely.

2. The industrial field BI method based on Grafana according to claim 1, characterized in that: In S2, JavaScript deletion code is preset in the industrial protocol software to periodically delete historical data in the MySQL database software to release storage resources in the industrial computer for storing the latest data.

3. The industrial field BI method based on Grafana according to claim 2, characterized in that: Web client software in S4 includes Chrome, Edge, and IE browsers.

4. The industrial field BI method based on Grafana according to claim 3, characterized in that: In S1, the communication protocols between on-site industrial manufacturing equipment and industrial computers include Modbus protocol, OPC UA protocol, Siemens S7 protocol and EtherNet / IP.

5. The industrial field BI method based on Grafana according to claim 1, characterized in that: In S3, visualization controls include line graphs, pie charts, histograms, bar charts, dashboards, historical status charts, Gantt charts, funnel charts, heat maps, scatter plots, multi-function tables and / or maps.

6. The industrial field BI method based on Grafana according to claim 5, characterized in that: In S2, the preset JavaScript processing codes include energy consumption analysis code, OEE analysis code, production progress analysis code, and fault statistics code.

7. The industrial field BI method based on Grafana according to claim 6, characterized in that: In S4, users access the visualization interface through computers, tablets, or mobile communication devices.

8. The industrial field BI method based on Grafana according to claim 7, characterized in that: In S3, when Grafana software reads production data from MySQL to create a visualization interface, it can compare production data with preset data and issue an alarm for the part that exceeds the preset data.

9. The industrial field BI method based on Grafana according to claim 8, characterized in that: In S3, the Grafana software's alarm methods include publishing alarm icons to visualization controls and sending alarm emails through the server.

10. A Grafana-based industrial field BI system, used to implement the Grafana-based industrial field BI method described in any one of claims 1-9, comprising industrial manufacturing equipment, an industrial computer, a database server and a data processing center, the industrial manufacturing equipment having a plurality of built-in sensors for collecting the operating status information of the equipment, the industrial computer providing a CPU, memory, a solid-state hard disk, input / output devices and a data transmission interface to provide an operating environment for industrial protocol software, MySQL database software and Grafana software, the database server being used to store data, and the data processing center being used for data analysis.

Images