Today we are very lucky to have with us Spyros Sakellariadis, a Director with the Microsoft Open Tech team. He is doing some amazing work with the ConnectTheDots project. Now he’s integrated Power BI with ConnectTheDots! Would love to see you give it a try and let us know what you think.
By Spyros Sakellariadis
In a previous post, we discussed using solutions like Temboo to monitor and visualize real-time sensor data. Another option is to ‘roll-your-own’ Internet of Things infrastructure, to connect your devices to Microsoft Azure, and perform monitoring, analysis, and visualization with the many available tools. In this blog post we’ll talk in more depth about how to use Azure with Power BI to visualize data on Power BI dashboards in real-time, using code we created in an open source project we call ConnectTheDots.
Within the ConnectTheDots project, we show you how to:
1. Collect data from sensors measuring anything from temperature and humidity using Arduino weather shield, to heart rate and motion using a Microsoft Band, to soil conductivity and salinity using various hydrology sensors
2. Package and transmit that data over the Internet to a Microsoft Azure Event Hub
3. Generate alerts and perform various computations on the data streams using Microsoft’s Stream Analytics
4. Show real-time charts using a simple Microsoft Azure website
5. Produce real-time charts, averages, alerts, etc. using Power BI.
All of the instructions for rolling your own infrastructure are published here, including code samples and scripts to automatically provision gateway and Azure components.
Let’s have a look at a few examples of dashboards that you can easily create.
Monitoring weather data
It seems that everyone at some point of time in their exploration of IoT creates a weather station. Don’t know why, but they do, as you can see from a quick Internet Search on “Internet of Things.” A simple way to do this is to attach some weather sensors to an Arduino board connected to a Raspberry Pi, and send the data somewhere you can see it such as Power BI. All of the instructions are on the ConnectTheDots site, with the Power BI instructions, for example, at https://github.com/MSOpenTech/connectthedots/tree/master/Azure/PowerBI.Here is an example of what a PowerBI dashboard might look like if you had temperature and humidity sensors in four different locations:
There are many ways to do this, but the way we chose was to format the data leaving the sensors as JSON, with some prescribed fields:
{
"guid" : "string",
"organization" : "string",
"displayname" : "string",
"location" : "string",
"measurename" : "string",
"unitofmeasure" : "string",
"value" : double/float/integer
}
The temperature sensor in our example sends displayname =”Room B9/1508”, measurename = “Temperature”, and values such as 76.43, in quick succession – about once a second – to an Azure Event Hub defined in a subscription we own. We then have a Stream Analytics job that queries the Event Hub, with a simple query like the one following, and sends it to Power BI.
Select measurename, displayname, timecreated, value from DevicesInput TIMESTAMP by timecreated
When opening Power BI to create a new dashboard, a list of new data sources that are linked to this stream of data from Stream Analytics. We can now create a series of charts to place on the dashboard. To create the dashboard above, we added the temperature and humidity fields (“measurename”) to the charts, filtered by sensor (“displayname”), and displayed the data (“values”). Finally we chose to display a couple of real-time charts, and a few gauges with averages and min/max data. Voila. And to give you a sense of timing, it might take you 15 minutes to replicate such a Power BI dashboard for your weather data following the instructions on ConnectTheDots.
Monitoring sound
While creating a weather station is a popular first IoT project, and Power BI makes a great dashboard to display weather data, a weather dashboard is not a very exciting use of Power BI. It’s like watching paint dry – after all, how frequently does weather data change? An example that shows off some of the power of Power BI is to monitor the ambient sound in a room. Here is a chart showing a snippet of the Opera Turandot by Puccini playing in the background:
This dashboard leverages the exact same infrastructure as the weather station sample, yet with a Wensn Sound Level Meter as the sensor and some Python code reading the data off the device. Again, once the basic networking and Azure infrastructure is in place, it should take about 10 minutes to connect and configure the sound sensor and another 15 to configure the Power BI dashboard.
Monitoring heart rate
Both of the above examples are motionless. That is, the sensors sit in one place, connected to a field gateway, such as a Raspberry Pi, that is hard-wired to the network.A final IoT example I will present here is a dashboard to monitor data from Microsoft’s fitness monitoring device, the Microsoft Band. This device, which goes around your wrist and goes everywhere you go, has many sensors: a heart rate monitor, an accelerometer/gyrometer, GPS, an ambient light sensor, and many others. At the ConnectTheDots project site we have published some simple code samples for a Windows Phone app that reads the data over Bluetooth from the Band and pushes it to the same Azure infrastructure as the two examples above. Here is a simple dashboard that took less than 5 minutes to create that shows the wearer’s heart rate in real-time.
How to create a visual in Power BI
Before closing, let’s look quickly at how to create a simple Power Bi dashboard like the ones above, assuming that you have created a dataset for the data produced by an Arduino weather shield according to the guidance in the ConnectTheDots project. The mouse-click-by-mouse-click instructions can be found here, but the simple summary is the following:
1. Log in to https://app.powerbi.com, then (a) create a dashboard by clicking “+” in the left menu and save, and (b) select the dataset:
2. Once you have selected the dataset, the right pane will be a blank report on which you can draw your first visual. On the right will be a list of fields:
3. Select fields for your first chart, e.g. “arrivaltime” for the x-axis and “displayname” for the sensor. If you had multiple sensors, you would see something like this:
4. To make the chart easier to read, create a filter, e.g. by dragging ‘measurename’ up to the Filters tab, and selecting “Temperature”:
5. Save the report and chart by clicking Save in the top left menu, click on the chart and select “Pin It” to get it to appear on your dashboard, then and click on the name of the dashboard to see the final product.
At this point you have your first Power BI dashboard; embellishing this depends upon your creative talents and the richness of the data you have streamed to Power BI. I added a couple more charts to the report showing averages and came up with the following:
