Idil Dikbas on , updated
Grafana dashboards are extremely performant, but they can slow down when multiple panels make separate requests at the same time. Using Shared Queries allows panels to reuse the same data, improving both performance and ease of maintenance. Additionally, features like “Repeat by Variable” help create dynamic dashboards, reducing manual effort needed and ensuring consistency.
In this article, we are going to discover the benefits of Grafana’s Shared Queries and its potential use cases such as calculating OEE metrics and performing batch-to-batch comparisons.
In Grafana every panel in a dashboard has its own individual query to its respective datasource. Every time a dashboard is refreshed, those queries will be executed all at once and fetch data from the datasource specified for that query.
When a dashboard contains many panels, this can lead to low performance. This is mainly because of the large amount of requests sent to the datasource(s) for fetching the desired data. To put it simply, the more panels a dashboard contains, the less performant it becomes.
Redundant queries in a dashboard are one of the contributors in increasing the load which eventually can cause a decrease in the performance of the dashboard. Moreover, maintaining these redundant queries is prone to mistakes, as they all need to be modified manually one by one.
Shared Queries and “Repeat by Variable” are approaches that will help us to:
You can take a look at the Grafana documentation for Shared Queries and Repeat by Variable.
Shared Queries in Grafana offer the user the ability to define a query once and reuse it across multiple panels in a dashboard.
Shared Queries play an important role in dataset preparation, allowing the use of Grafana filters such as time range and variables. A shared query can be used as a base query for creating different panels and then small desired transforms can be applied on top of the base query to achieve the desired result for each individual panel.
The main benefits of using Shared Queries are:
In a world of manufacturing and production, understanding and improving Overall Equipment Effectiveness (OEE) is crucial for optimising operations and maximising efficiency. OEE provides a measure of metrics quality, performance, and availability of a production process. Therefore, visualising OEE metrics is essential.
Grafana’s Shared Queries can be quite handy when it comes to visualising OEE metrics.
By leveraging the advantages of Shared Queries that are mentioned previously in this article, organisations can obtain deeper insights into their equipment effectiveness, ensure continuous improvement, and ultimately achieve higher efficiency.
This is particularly useful because, in typical OEE monitoring, multiple panels are required to display all the necessary data. For instance, three metrics that are mentioned previously have their individual panels. To have an insight of OEE metrics, another panel is usually used for showing the details of the data that is used for calculations of OEE metrics. Despite the need for separate panels, all of them utilise the same dataset.
Incorporating Shared Queries into batch-to-batch comparisons of OEE metrics in Grafana offers numerous advantages, from ensuring consistency and accuracy to improving performance.
The OEE metrics of different batches can be compared easily with the help of Shared Queries. Since Shared Queries ensure that the same calculation methods and data sources are used across different batches, it provides a consistent basis for comparison. This standardisation is important for maintaining the integrity of comparisons, as it ensures that any observed differences are due to actual performance variations rather than differences in data handling.
We have utilised Shared Queries to prepare a dashboard for one of our clients for visualising their OEE metrics, batches and steps of their batches. Using this dashboard they are able to investigate thoroughly and pinpoint what has affected their production whether that is a specific batch or a specific step. Thus, they are able to improve their production processes to have optimal results.
Grafana’s Shared Queries are a built-in feature. Therefore, it does not require any action before it can be used.
The variables in the WHERE clause are filled automatically with values that are picked with the time range picker.
After setting up the new panel using the result of another panel, some transforms and/or overrides can be implemented in order to achieve the desired result.
For this panel, the aim was to calculate the product produced in the last production step. To achieve the desired result, we have performed two transformations which are “Organise fields by name” to filter out the columns we do not need and “Add field from calculation” to calculate total cakes produced.
Dashboards can be further simplified by using the “Repeat by Variable” option in panel options. “Repeat by Variable” option enables the user to create a dynamic dashboard based on a dashboard variable. This is particularly useful when the same query needs to be used for visualising multiple entities, such as event properties, in a dashboard. This option requires a variable in the dashboard which has values that will be used for repeating a panel.
“Repeat by Variable” option can be used for creating dynamic dashboards that contain panels for parameters, such as temperature, pressure and speed, which have the same configuration.
After setting up the configuration, the variable can be used to repeat panels in your dashboard.
Unfortunately Shared Queries are not the answer to all our problems, there are a few limitations we need to keep in mind:
Shared Queries and “Repeat by Variable” features can be very beneficial in terms of ensuring consistency in our data, minimising errors in our queries and improving the overall performance of our dashboard. Moreover, these tools simplify the process of setting up and maintaining dashboards, saving time and effort.
Ultimately, they help to ensure reliable and consistent data while keeping the performance of the dashboard as optimal as possible.
For any modern production facility, staying competitive requires a focus on operational efficiency and data-driven decision-making. How we store, process, and share data plays a major role in achieving this. Find out why we chose Parquet as a format for exporting data from Factry Historian, and how it delivers significant benefits in both data storage and processing speed.
Steam management has always been an essential aspect of industries such as food and beverage, paper production, chemical processing, and energy generation. With Factry Historian v7.1, steam functions are now fully integrated, making steam calculations straightforward, reliable, and accessible. Here’s how it works.
Is open source software (OSS) the right decision for your production company? In contrast to proprietary software, the possibilities in terms of flexibility and scalability are endless. Yet, not all open source applications are alike.
