We are eager to try out our beer cooling solution and we want to see it in action. We want to visualize our data and keep eye on the temperature of the cooling beer. Before doing anything more complex like building a web or mobile app, we'll take a quick shortcut and bring our data to web, desktop, and mobile. This post is about Power BI in action.
What is Power BI?
Power BI is online data visualization platform by Microsoft. It works on desktop, tablets, and mobile devices.
Microsoft: Microsoft Power BI transforms your company’s data into rich visuals for you to collect and organize so you can focus on what matters to you. Stay in the know, spot trends as they happen, and push your business further.
With Power PI we can get temperatures on desktop and mobile with one app. To get started, you have to create a Power PI account. It’s free until you have small demands like what we have here.
Getting Data to Power BI
In previous Beer IoT post Using Stream Analytics to save data from IoT Hub to SQL database we created Stream Analytics job to move sensors data from Azure IoT Hub to SQL Azure database. Our SQL Azure database was just one output source for Stream Analytics job. Same way we may add other types of output sources and among supported types we find also Power BI.
NB! Adding and configuring Power BI output source is currently possible only in Azure management portal. New portal doesn’t support it yet.
Now let’s make Stream Analytics job to pump data to Power BI. Go through the following steps to make it happen.
- Open old management portal and move to Open Stream Analytics job we created in previous post.
- Click on “Outputs” menu and then on “Add output” button.
- Select “Power BI” and click arrow to move to next page.
- Under “Existing Microsoft Power BI” section click on “Auhtorize now” link.
- Log in to Power BI.
- Fill required fields:Output alias – name of output source, Dataset name — name of Power BI dataset (for example: Beer IoT), Table name — Measurement, Workspace — leave it like it is.
- Click on the check button to save data.
Next, we have to modify our query and make it insert data to the Power BI output source. Here is the query:
SELECT batchKey as BatchKey, MAX(CAST([timeStamp] AS datetime)) as Time, AVG(beerTemp) as BeerTemp, AVG(ambientTemp) as AmbientTemp INTO [SqlAzureBeerIoT] FROM [FromIoTHub] GROUP BY batchKey, TumblingWindow(minute, 5) SELECT batchKey as BatchKey, MAX(CAST([timeStamp] AS datetime)) as Time, AVG(beerTemp) as BeerTemp, AVG(ambientTemp) as AmbientTemp INTO [PowerBI] FROM [FromIoTHub] GROUP BY batchKey, TumblingWindow(minute, 5)
Duplicating is not a nice or polite solution, but I couldn't find a better way to accomplish our goal right now.
If you run Stream Analytics and the beer cooling solution, then after five minutes you should see a new dataset appearing in the Power BI interface.
NB! It’s easier to build reports and dashboards in Power BI if you have some data already waiting there. To get data, run a Stream Analytics job and the beer cooling solution, and let the solution report measurements for around 20-30 minutes.
Building the Beer Cooling Report
Now let’s build the beer cooling report. Log in to Power BI through your browser and under the Datasets section on left, select BeerIoT (this must be the name you gave to the dataset when you added a new output source to the Stream Analytics job).
Select line chart under visualizations, drag it wider, and configure it like shown on the screenshot below.
Click on somewhere in the empty space near the chart to get it out of focus and click on "table" under visualizations. Configure the table like shown in the following screenshot:
Now save your report and name it “Beer cooling”, for example. Under the reports menu on the left, select the report you just created. Click on the pin button on the line chart and add it to the Beer IoT dashboard.
If you click on "dashboard" you will see the beer cooling chart there. When the beer cooling solution is running, then the chart will be updated in real-time. I also added the measurings table to the dashboard, and changed their sizes to make the dashboard look better.
Power BI On Your Desktop
On Windows 10, open store from the taskbar and search for Power BI. Install the Power BI application to your machine and run it. Log in if needed and open your beer IoT dashboard.
You can click on the chart and table to see them enlarged. Also, you can open your beer IoT report and see it on your desktop. In report view, you can also use filters if you defined some.
Now take your Windows Phone 10, open store, and search for Power BI. Install the Power BI app to your phone and run it. Log in to Power BI when asked and open the beer IoT dashboard you created before.
We see almost same thing also on the Power BI mobile app. If you'd like, you can pin this dashboard to the home screen of your Windows Phone.
This was the first time we visualized our measurements data. We added a new output source to the Stream Analytics job to send aggregated measurement data to Power BI. On Power BI, we created a beer cooling report and dashboard. After this, we can monitor beer cooling through the web browser and through Power BI desktop and mobile apps. We wanted to get our data visualized and we did so with minimal time and effort.