Last month, we released a set of features in preview designed to make it easier than ever to display real-time streaming data in your Power BI dashboards. Check out the announcement if you haven’t already. Today, I want to show you just how easy this can be.
Many of you have expressed interest in using Power BI to display real-time sensor data. In this tutorial, we will walk you step-by-step through the entire process of setting up a Raspberry Pi weather station, and showing the resulting temperature and humidity data in real-time with Power BI. Here’s a sneak peek of the end result:
Have other use cases in mind? Don’t have access to the materials below? No sweat. The lessons and sample code in this tutorial are broadly applicable for a wide range of IoT scenarios. And, as always, please let us know if you have any questions, either in the comments below, or in our community forums.
With that in mind, let’s get started! For this tutorial, we’ll be using the following materials:
- Raspberry Pi 3 w/ Rapsbian Jessie installed
- DHT22 temperature sensor
Using Windows 10 IoT instead? No problem. Most of the steps below will apply to you as well, and we’ll call out the differences where necessary.
Preparing the streaming dataset in Power BI
Streaming datasets in Power BI represent streams of incoming data. In this tutorial, the constant stream of temperature and humidity data from our weather station will be represented as a streaming dataset. In this step, we’ll create an API streaming dataset. This exposes an API endpoint that our Raspberry Pi can call to push data to Power BI.
To create a streaming dataset, expand the navigation bar at the left, and click on the “Streaming datasets” button at under the Datasets tab.
From there, click the “Add streaming dataset” button at the top right.
Select the API option.
Give your new streaming dataset a name, and add three fields: timestamp, temperature and humidity. In this step, we’re specifying that the data stream has three fields:
1.timestamp: when the weather measurements were taken
2.temperature: the temperature at that point time
3.humidity: the humidity at that point in time
Important: to use the sample code as is, it is important that your data fields (e.g. “temperature”) and their types (e.g. “Number”) exactly match the ones in the screenshot below!
Select “Create” to confirm the creation of the streaming dataset. On the following screen, copy the Push URL and set that aside – we’ll need it for later.
We’ve now successfully created a streaming dataset.
Hooking up the Raspberry Pi to collect temperature and humidity data
In this step, we’ll hook up the temperature sensor to the Raspberry Pi, and write a script to have the Raspberry Pi send the data to Power BI.
First, we’ll want to wire the temperature sensor to the Raspberry Pi. We won’t go into the details of wiring the Raspberry Pi in this tutorial, Adafruit has a great wiring schematic here. Hook up your Raspberry Pi to the DHT22 according to that schematic.
Reading the data and sending it to Power BI
Next, we’ll write a Python script to read the temperature and humidity from the DHT22 sensor, and to send that data to Power BI.
First, some setup: assuming you have Raspbian installed and connected to the internet, you’ll want to run the following commands in the bash terminal. This sets up your development environment. The first two lines make sure that your system is ready for Python extensions. The last line installs the Adafruit_DHT Python libraries which accompany the DHT22 sensor, which will allow us to easily access temperature data.
> sudo apt-get update
> sudo apt-get install build-essential python-dev
> git clone https://github.com/adafruit/Adafruit_Python_DHT.git && cd Adafruit_Python_DHT && sudo python setup.py installNow, go here and download the uploadWeatherData Python script. Open it, and set the REST_API_ENDPOINT variable to the Push URL that you copied down above.
# REST API endpoint, given to you when you create an API streaming dataset
# Will be of the format: https://api.powerbi.com/beta/<tenant id>/datasets/< dataset id>/rows?key=<key id>
REST_API_URL = ' *** Your Push API URL goes here *** 'Using Windows 10 IoT? The library above may not work for your purposes. Take a look at this guide for a step-by-step walkthrough of reading from the DHT22 sensor.
Before we run the script, let’s dive in and understand what the script is doing. The script executes indefinitely, so all our logic is wrapped in a while True loop. In the loop body, we first use the Adafruit_DHT library to grab the humidity and temperature readings from the DHT22 sensor. The library abstracts all the complicated logic into one line! Then, just for fun, we print out the current readings.
# read and print out humidity and temperature from sensor
humidity,temp = dht.read_retry(SENSOR, PIN)
print 'Temp={0:0.1f}*C Humidity={1:0.1f}%'.format(temp, humidity)Next, we prepare the data that we’re going to send to Power BI. As shown above, the API endpoint is expecting the data to come in the following format:
[ {
“timestamp” : “2016-09-10T01:26:45.030Z”,
“temperature” : 98.6,
“humidity” : 98.6
} ]The next lines massage our data into that format:
# ensure that timestamp string is formatted properly
now = datetime.strftime(datetime.now(), "%Y-%m-%dT%H:%M:%S%Z")
# data that we're sending to Power BI REST API
data = '[{{ "timestamp": "{0}", "temperature": "{1:0.1f}", "humidity": "{2:0.1f}" }}]'.format(now, temp, humidity)
Tip: no matter what application you’re building, we recommend sending a timestamp to Power BI as part of your streaming dataset. When sending timestamp, try to match the sample format as closely as possible: “2016-09-10T01:26:45.030Z” – feel free to use the code snippet above for that purpose.
Using Windows 10 IoT? Check out our sample code here to see how to make a sample request to the Power BI REST API Streaming endpoint.
With our data payload ready for sending, we use Python’s built-in networking libraries to make the HTTP POST request to Power BI.
# make HTTP POST request to Power BI REST API
req = urllib2.Request(REST_API_URL, data)
response = urllib2.urlopen(req)Afterwards, we wait one second before starting over again.
time.sleep(1)And there we have it! Now that we understand what the script is doing, let’s run it. In the terminal, navigate to the directory where you downloaded the script, and run it:
> python uploadDHT22Data.pyYou should see the temperature and humidity data populate, the POST requests being sent, and a 200 response. You’re now successfully uploading data to Power BI.
Troubleshooting Tips
Something go wrong? Here are some diagnosis tips for common issues.
- My temperature and/or humidity readings are wrong
First, double-check your wiring. Also, the script assumes that you’re using Pin 4 to measure the temperature readings. If that’s not the case for your wiring setup, be sure to make the appropriate change in the script. - I get a 404 or 500 error from the service
First, be sure to read the error message that is returned by the service – we try to make these as informative as possible. If that doesn’t work, you should double-check: did you paste your push URL into the REST_API_URL field? Do the fields in line 13 (“timestamp”, “temperature”, “humidity”) match exactly with what’s in your streaming dataset?
Putting it all together: visualizing the data in Power BI
With the data being pushed to Power BI, we can now visualize the tiles on a Power BI dashboard with streaming tiles. First, create a new dashboard by clicking the “+” icon in the left navigation bar.
Within the new dashboard, click “Add tile” on the top right.
Select “Custom Streaming Data” and then Next.
On the next screen, select the dataset that you created in the first step, and click Next.
We’ll start by creating a card that displays the most recently received value. In the next screen, under Field, select temperature.
Click next, and you’ll be given the option to customize the title and other similar options. Finalize your tile by clicking “Apply” – you should see the tile appear in your new dashboard, and it should be automatically updating with your temperature data.
And there we have it! In a few easy steps, we’ve taken a stream of raw data and visualized it in Power BI.
Next we’ll create a line chart, so that we can see the changes in the data over time. Go back to “add tile” experience, and this time select “Line chart” under the “Visualization type.” From there, set Axis to timestamp, and value to temperature.
You can also repeat the same steps with humidity data that’s flowing into the streaming dataset. And voila! We’ve successfully built a rudimentary real-time dashboard representing the data from our mini-weather station.
Fun thing to try: gently blow on the humidity sensor – the moisture from your breath will cause the humidity to spike, and then gradually decline.
What’s next?
- Stay tuned for part two of our tutorial, where we’ll be covering more advanced use cases, including using Azure Stream Analytics to perform time series analysis on your data
- The steps above should be broadly applicable to the wide variety of sensors available to Raspberry Pis – e.g. motion, sound, pressure, and many more. Build something cool? Let us know in the comments or in the forums! Your work could be showcased in a future blog post.
- Check out the full REST API documentation.
- Have a feature request for real-time? Let us know in UserVoice.