Power BI: Create a real-time dashboard of sensor data
When I talked about just a few of the things Power BI can do, I got a lot of responses even though I didn’t talk much about technical details. So I would like to introduce how I am using Power BI with specific instructions on how to do it.
What is Power BI?
In a nutshell, it is a BI tool provided by Microsoft.
What is a BI tool?
BI (Business Intelligence) tool is a tool that analyzes and visualizes the following information, for example, that a company owns for business purposes.
- Various databases
- Excel sheet with sales records
- User activity logs
However, its use is not limited to business, and I have found it useful in my graduate research as well, as it allows for super-easy visualization of various data. For example, it is very easy to graph the results of performance measurements of short-range wireless communications conducted as part of my scientific study.
Which Power BI should I use?
This is an excellent question, and I didn’t understand this at first either.
Power BI has a standalone application called Power BI Desktop, which is installed on a PC, and an online version called Power BI Service, which can do different things.
Power BI Desktop
This is a software for Windows and is completely free to download and use.
If you watch this video, even if only for the first five minutes, you will understand what Power BI Desktop can do, rather than me writing a lengthy article.
The Desktop version is completely free as long as you use it for personal use, so you can continue to use it for free forever unless you have a particular motivation to share the dashboard within your company.
Power BI Service
Simply put, it is an online version of Power BI.
Reports created in Power BI Desktop can be easily published to a Power BI Service dashboard and shared within the company.
You can also do things like create and visualize reports and dashboards that can be completed only online, using Microsoft Azure services as the data source. In this entry, I would like to show you a small part of these functionalities.
Power BI Service is a fee-based service, and the fee structure is mainly a monthly fee based on the number of users, but some companies that use Microsoft 365 company-wide may have a contract together, so please contact your IT admin.
Create a dashboard of sensor data with Power BI
If you watched the Power BI Desktop video earlier, you may have a rough idea of what Power BI can do. As a very common scenario here, I would like to create a dashboard that allows real-time monitoring of sensor data. The details are as follows.
- Temperature data is recorded every minute at locations around the world.
- The data is in JSON format as follows, which at this point is not time-series data and simply continues to be stored in the inexpensive Data Lake.
- We would like to visualize this in a way that makes it easy to understand temperature changes from place to place.
{
"place": "Antarctica",
"temperature": -10,
"dateTime": "2022-10-16 05:05"
}The completed image would look like this.
How to collect and store sensor data
From an engineering point of view, how to collect and store sensor data is a major concern. For example, will it be stored in a relational database, a document database, or will it be stored as a structured data file in a Data Lake?
These days, people often save whatever data they can get for the time being, throw it into an inexpensive Data Lake without thinking carefully, and then use an ETL tool to make good use of it later for machine learning or other purposes. In this entry, I assume a scenario in which sensor data at any given moment is accumulated in JSON format in the Data Lake (Blob storage/ADLS Gen2).
By the way, if many people are interested in the know-how related to the data storage part of this project, I plan to write a separate article about it.
Creating Streams with Stream Analytics
I’ll get to the conclusion first. Power BI makes it easy to create line chart dashboards that are updated in real time using stream data as the data source. Currently, our sensor data is only stored in JSON at a certain moment in time, so we need to convert this into a time series stream. Azure Stream Analytics makes this easy.
From Microsoft Azure Portal, select “Stream Analytics job” and create a new job.
Next, from “Add stream input,” select “Blob storage/ADLS Gen2,” which is the Data Lake storing the sensor data this time.
Next, select “Inputs” from “Job topology”. This is where settings are made regarding data input sources.
Select the Storage account where the data is stored and specify the Container name.
Next, match the path pattern and date format of the data stored in the Container with your own; Stream Analytics uses this information to find the JSON file.
Finally, select JSON as the file format. In this way, each property can be queried.
Next, from “Outputs,” set the output destination for the data. Here, you configure the settings to use time series data as a data source in Power BI.
Specify the Workspace of the Power BI Service that you have previously created, and set the names of the Dataset and Table that this stream will produce.
Next, select “Query” and assemble a query to collect JSON from the input source, Data Lake, to create a time series stream.
This query is the biggest key. First, I will introduce the query as a whole. After that, I will explain the meaning one by one.
First, looking over the whole thing, we can see that the query is constructed like a typical SQL.
SELECT data from “DataLakeInput” where JSON is stored using the FROM clause. In doing so, the “dateTime” field of the JSON is used as a TIMESTAMP. TUMBLINGWINDOW is a kind of sliding window, which here means creating a stream of temperature data grouped by location at 5-minute intervals. The interval can be adjusted arbitrarily according to the granularity of the data and the frequency of updates. The window function is detailed in the following link.
Various aggregate functions are available in SELECT. For example, avg(temperature) takes the average of the temperatures summarized in the TUMBLINGWINDOW. Also, max(dateTime) takes the last value of each time interval, which is the time of the break in the window (interval).
Finally, the INTO [PowerBIOutput] section is set up to make the results of this query execution available as a data source in Power BI.
Finally, select “Start” from “Overview” in Stream Analytics to start this job.
Now the sensor data stored as JSON in Data Lake can be converted to a time series stream.
Creating a real-time dashboard with Power BI (1)
Let’s use this time-series stream to create a real-time dashboard. It’s surprisingly easy to do…!
First, log in to the Power BI Service at app.powerbi.com and select the workspace that we used from Stream Analytics.
Next, select “+ Add a tile” from “Edit” on the dashboard.
Next, select “REAL-TIME DATA”.
Next, select “RealtimeWorldTemperature” created earlier in Stream Analytics from “YOUR DATASETS” as the data source.
Select “Line chart” for “Visualization Type”.
Next, select “WindowEnd” as the Axis (X axis).
Also, select “place” as Legend.
Finally, select “averageTemperature” as Values (Y axis).
Finally, set the number of minutes to be displayed as latest information.
That’s all it takes to create a dashboard that reflects updates to the data source in real time as they occur!
Now, there is one problem with this dashboard. It does not allow any narrowing down of the data. Right now there are only three legends, so we can manage to see them, but it is not difficult to imagine that if this number were increased to 100–200, it would be almost unusable without the ability to narrow down the legends. Let’s create a dashboard for it in the next section.
Creating a real-time dashboard with Power BI (2)
Earlier, we created a dashboard that easily visualizes real-time data sources by simply making a few selections from the Power BI Service screen, but it had the disadvantage of not allowing for fine control.
However, Power BI Service can also create reports like Power BI Desktop, which was introduced at the beginning of this entry. This approach is more time-consuming to create, but has the advantage of allowing fine control.
Select “Create report” under “Datasets” from the workspace.
Then, as shown in the figure below, a report can be created using the time series stream as the data source. If you were able to watch the video at the beginning of this article all the way to the end, you will have noticed that you can use this data source to create any visualization exactly the same way as in Power BI Desktop.
Since we want to create a line chart here, select “Line Chart” from “Visualizations”.
As with the dashboard we just created, select “WindowEnd” for “X-axis,” “averageTemperature” for “Y-axis,” and “place” for “Legend”.
Next, from “Filters”, select “Relative time” for “Filter type” of “WindowEnd” to make it relative to the latest time.
Next, once all charts are deselected, select “Visualizations” and change the “Page refresh” setting.
Unlike the real-time dashboard described earlier, Power BI’s regular reports unfortunately do not have the ability to dynamically refresh the screen in response to data source updates. However, by automatically refreshing the page at a specified time, it is possible to simulate real-time updates.
Next, select “Slicer” from “Visualizations” to achieve the ability to filter by location.
Select “place” for “Field” under “Slicer”.
At this point, the search by location is achieved. In addition, by selecting “Search” from “…”, the search function by text string can be added.
Finally, let’s finish the dashboard creation by pinning this report with hitting “Pin to a dashboard”.
The dashboard consists of a main screen that is automatically updated at specified intervals and a narrowing down function in the lower right corner. If the screen is left unattended, it will update periodically with new values, and by selecting a location from the Slicer in the lower right corner, only the results that have been narrowed down can be drawn.
Conclusion
In this entry, we used Azure Stream Analytics to convert sensor data that was collected in the Data Lake into time-series data, which was then converted into an online dashboard using the Power BI Service. In doing so, we used the built-in real-time dashboard functionality to create a dashboard that dynamically reflects any changes to the data source, and we also showed how Slicer can be combined with regular reporting functionality similar to Power BI Desktop for fine-tuning.
This time, we happened to introduce an example using Azure and Power BI Service, but as long as you use it as Power BI Desktop, it is completely free, so you can start with a small visualization using an Excel sheet or CSV file at hand. Also, the knowledge of Power BI Desktop can be utilized when creating reports and dashboards with Power BI Service.
The features introduced here are only a small part of Power BI’s potential (really!). So, if this entry looks interesting to you, I hope you will try out the various features and find useful ways to use Power BI. Thanks!
