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.
As a Data Engineer at Factry, I often face the challenge of guiding industrial companies along their Overall Equipment Effectiveness (OEE) journey. These projects have provided me with some interesting experiences and valuable insights, and I’m eager to share what I’ve learned so far.
Being part of Factry’s Data Engineering team means we’re spending a lot of time providing customer support and fixing specific issues in our clients’ setup. We’ve noticed that we’re frequently being asked similar questions and spend quite a bit of time helping users fix similar errors. In this blogpost, we’ll be going over some common OPC-UA and time synchronization errors and how to fix them.
When information is trapped in isolated systems across the production floor, getting valuable insights is increasingly difficult, and integration becomes fragile. The result? Wasted time, missed opportunities, and inefficiencies that eat away at the bottom line. In this article, we’ll explore the real costs of data fragmentation and how Factry Historian can bring clarity and efficiency to your operations.
