Microsoft Power BI + Particle = Awesome Custom Dashboards!

RWB · August 13, 2016, 10:18pm

In my search for a highly customizable dashboard for displaying data harvested from connected Particle Photon & Electron devices I have come across many different solutions but none of them provide the flexibility Microsoft Power BI does

Thanks to @gusgonnet for bringing my attention to this service

You can pull in data from tons of different sources and use that data in your custom dashboards specific to your particular needs. There are over 60 different platforms you can currently pull data from and this list is growing quickly since Microsoft has a team of over 500 employees working on Power BI.

Here is a list of services that you can quickly pull data from so that you know:

Now another huge plus from my point of view is that you or your clients can quickly see the custom dashboards you create on any of the mobile platforms by just downloading the iOS, Android, or Windows Mobile app and then logging in to their account. You do not need to create a custom iOS, Android, or Windows Mobile app or deal with keeping it up to date which is a major time consumer.

Once you create a dashboard, you can easily share that dashboard online via a web page or blog post using their simple share button which creates an iFrame code that you can embed. That is very nice! You can then track how that link is begin used via their built-in analytics.

Today Gustavo posted a link from PubNub’s latest Blog post that announced that they are working with Microsoft to make it easy to get data that is in a PubNub account to show up in the Power BI dashboard so you can use and view it.

That blog post is here: https://www.pubnub.com/blog/2016-08-11-microsoft-power-bi-integrates-pubnub-for-realtime-data-visualization/

In that article, there is a link to a tutorial from Microsoft showing how to add a PubNub data stream to Microsoft Power BI so you can display it however you want within a Power BI dashboard.

Here is the link to the tutorial that Microsoft BI created:

Now in this tutorial I learned that you could use the Power BI REST API to capture and log streaming data sent by an IOT device. Below is a snippet directly from this tutorial about getting data into Power BI.

So I think this is great since it sounds like we should be able to send data directly from the Particle Photon to Microsoft Power BI by using their API functions. Then Power BI will display that data live and also store that sensor data into a database so you can see historical data also.

The Microsoft Power BI, REST API documentation, is here: http://docs.powerbi.apiary.io/#reference/datasets

I’m clueless on how to work with REST API’s, but I’m posting all this in case somebody smarter than me is interested in exploring this and creating some code that works

I know that we can send data from the Photon to the PubNub service and get this working, but since Power BI can accept data directly from a Photon if I understand everything correctly then it is probably the most efficient way to get data over to Power BI. Let me know if I’m off base here or not.

I shared this because I’m excited about being able to create beautiful custom dashboards for my products exactly how I have been envisioning them.

I don’t know if pushing data directly into an Azure database is better or cheaper, but I would like to explore driving data directly into Power BI using the REST API if others are willing to help figure out how to do that.

I would also love to hear what @Dave @zach @BDub @will @zachary think about the Power BI platform and if it’s something Particle would be interested in supporting via a Plugin or something.

Thanks for your time!

gusgonnet · August 14, 2016, 8:42pm

Hey, I took a look at that API, and I think you cannot post data using it. You can manage your dashboards and lists and other things but no data.
Have you found this?

Else, the way to go here might just be Particle -> pubNub -> powerBI dashoard.

Gustavo.

RWB · August 14, 2016, 9:06pm

Thanks for taking a look at it.

I now have a custom Power BI dashboard setup displaying data from the PubNub demo account that they provide in the Microsoft PubNub setup tutorial and that was super simple to do after you sign up for the free Power BI account.

Then I used the Particle + PubNub tutorial I linked to in the AWS thread to get a Photon sending data to PubNub successfully so that works also and is enough to get you up and running which is nice.

It seems that Power BI is just subscribing to publish events from PubNub and displaying them and or saving them as they come in also. I see no reason why they cant do the same for the Particle devices also.

I’m going to try to get somebody from Microsoft Power BI on here and see if they would be interested in providing a solution for the Particle ecosystem.

I’m impressed with the dashboard and what I have been able to do with it and it’s features. It looks great on my phone and desktop. I like that in landscape mode on your phone the desktop looks exactly like it does on the laptop with no differences.

It feels like PubNub’s publish and subscribe methods are almost identical to what Particles cloud does and we really do not need PubNub in the middle but I’m not 100% sure on that.

PubNub does have some extra services that Particle does not like data logging and other action services so maybe it is a good service for a IOT product.

Any thoughts on this?

RWB · August 14, 2016, 9:59pm

Here is the tutorial I used to get a Photon to send data to PubNub:

Here is the dashboard I created so far after following the Microsoft tutorial that is linked here:

Here is a screen shot of a simple dashboard:

There is a free Power BI Windows application you use to create custom visualization templates they offer online, but I have not figured out how to get access to the PubNub stream from the desktop application yet.

The tiles that are available on the web based are limited to 5 different options, with the rest of them being accessible via the desktop Power BI application which pushes them to the web-based dashboard after you create them and then pin them to whatever dashboard you choose.

RWB · August 15, 2016, 3:15am

@gusgonnet I found the documentation that shows you how to setup an Azure EventHubs to capture data sent by a Photon or Electron webhook and then place that data into an Azure Database.

It’s part of this weather station tutorial which is doing a lot of different things that are not required if all your wanting to do is push data into an Azure Database.

They are also using a service called Stream Analytics to read the incoming data, and then trigger actions based on your desires. This Stream Analytics is a service that cost money, so if you do not want to use it then, you can just skip setting it up as described in the tutorial.

So the main loop code they use for the Photon weather shield project is in the link below so you can look at that code and see exactly how they are sending the webhook data.

There is a video that shows how to setup the Event Hub + Azure Database + Event Stream Analytic services which should be helpful.

The video looks like this, and it’s half way down the webpage at the link above.

And here is the step by step setup guide that they are following in the video. I’m just focusing on getting the data into a database personally. :

github.com

BretStateham/connectthedots/blob/master/HOLs/Azure/AzurePrep/README.md

# "Azure Prep" Hands-On Lab #

---

## Overview ##

In this lab you will prepare your Azure subscription with the Service Bus namespace, Event Hubs, Storage Account and Stream Analytics jobs as required by the [ConnectTheDots.io](http://aka.ms/bsctd) architecture:

![Connect the Dots](./images/00010AzurePrepLabArchitecture.png)

In this "Azure Prep" HOL, we'll focus on the Azure Service Bus, Event Hubs, and Stream Analytics components. The sample web site and device specific implementations are covered in other labs.  The components we create here are:

| Name                                   | Type                  | Use | 
| ---                                    | ---                   | --- |
| "***&lt;name&gt;*-ns**"                | Service Bus Namespace | Contains the Event Hubs | 
| "**ehdevices**"                        | Event Hub             | Receives messages from devices as well as the "***&lt;name&gt;*Aggregates**" Stream Analytics Job.  Messages sent to this event hub are later received by the sample website and displayed graphically in charts|
| "**ehalerts**"                         | Event Hub             | Receives alert messages generated by the alert Stream Analaytics jobs.  Those messages are then read by the sample website and used to display alerts about various sensor values | 
| "***&lt;name&gt;*Aggregates**"         | Stream Analytics Job  | Reads temperature messages from "**ehdevices**", averages them over 10 second windows, and outputs the resulting averages back into "**ehdevices**".  Those messages are later displayed in the sample website along with the detail temperature data. 
| "***&lt;name&gt;*&nbsp;&#42;&nbsp;**"  | Stream Analytics Jobs | Various jobs to read messages from "**ehdevices**" then generate alerts or other data to be output into the "**ehalerts**" event hub.  Those messages will then be used later by the sample website (or other downstream clients like Power BI) to display alerts data. 
| "***&lt;name&gt;*storage**"            | Storage Account       | Used by the Stream Analytics jobs for monitoring and logging purposes.  This is known as the "Regional Monitoring" storage account |

This file has been truncated. show original

Once the data is being dropped into an Azure database, you will be able to pull it into Power BI and create beautiful dashboards using the Power BI desktop design software and then push it to your online dashboard.

I’m going to give it a shot and see how it goes Hopefully all goes well.

If anybody else tries’s this out, please do let us know how it works out

gusgonnet · August 15, 2016, 11:37am

great recompilation of information!
Good luck on making this work
Gustavo.

will · August 15, 2016, 4:26pm

This looks really interesting, and would be a great candidate for an “official” Particle integration. Please post your progress, @RWB!

RWB · August 16, 2016, 1:36am

@gusgonnet I figured out how to import Google Sheets into the Power BI platform using Power BI Desktop.

I just tried this, and it works.

I also looked up the Google Sheet limitations as far as size goes which is not that big but could work fine for some applications, see below:

Here is instructions on how to add a Google Sheet to Power BI:

And then:

With the free Power BI account, the data is updated from the Google Sheet every day. With the Power BI Pro account, you can set it to update the Google Sheet data every hour. So this is a good way to add another data source to include in your overall dashboard of relevant info.

I’ll keep adding more info here as I learn more.

bjagpal · October 17, 2016, 5:35pm

Hi there, I believe I have a similar setup to yours, and I am now also experimenting with the Power BI software. Specifically, I am sending data from my Photon -> Azure Event Hub -> Azure Table Storage.

Using the Power BI Desktop application, it is easy to pull data from my Azure Table Storage account and create nice dashboards. These dashboards can then be published to the Power BI Web application where they are displayed (within your own account). Unfortunately these dashboards are “frozen in time”, or in other words they do not refresh automatically so they don’t provide real-time info about your device.

I am interested in having access to real time data and there is a way to use Azure Stream Analytics to send data from the Event Hub directly to a Power BI account. The instructions are found at this link, (which was initially located within that Hackster Weathershield project):

github.com

BretStateham/connectthedots/blob/master/Azure/PowerBI/PBI_setup.md

The instructions below will help you setup a Power BI dashboard in the Connect The Dots starter solution, but they can be adapted as necessary for other scenarios. This document assumes you have already deployed the starter solution with at least one device set up pushing data to an Azure Event Hub. The documentation below uses the names and fields you would have if you had set up the starter solution with an Arduino Weather Shield, but can be modified as needed if you have a different sensor or named your fields and hubs differently.

## Prerequisites ##

Make sure you have a working starter solution, with data showing in your Azure website. In addition, you will need a Power BI account, for which you can sign up for the Public Preview at [PowerBI.com](http://www.PowerBI.com).

## Create a Service Bus Consumer Group ##

To make sure you do not exceed the maximum number of readers on your Connect The Dots Event Hub, create a Consumer Group first.

* Open the Azure Management Portal, and select Service Bus
* Select the Namespace you used for your solution
* From the top menu, select Event Hubs
* From the left menu, select ehdevices
* From the top menu, select Consumer Groups
* Select "+" Create at the bottom to create a new Consumer Group
* Give it the name "CG4PBI" and click OK


## Create an Azure Stream Analytics (ASA) job ##

This file has been truncated. show original

I was able to complete the tutorial above, and it does provide auto-refreshing real-time data. However, although I was able to show the data in a regular chart format, I couldn’t get the data into a graph format as shown in the tutorial. I think it’s because the temperature data is being interpreted as a string rather than a number.

Using the Power BI Desktop application, you can easily change the data type from a string to a number. But the Power BI Web application (the one required to take the streaming data from Azure) doesn’t seem to allow for this data type conversion.

Can anyone shed some more light on using Power BI in this manner?

RWB · October 17, 2016, 5:59pm

@bjagpal You have gotten further than me at this point which is good and I would also like to know how to accomplish the live data graphing your looking to accomplish also.

It looks like you setup a different stream analytics job to push the incoming data from a Photon > Stream Analytics > Power BI Live Dashboard tile.

PubNub has this live data feature added in and I’m hoping maybe these links about that setup may help with your search for a solution for showing live data in a graph format.

See if anything useful is in these links:

https://github.com/Microsoft/PowerBI-CSharp/tree/master/samples/consoleapp/getting-started-for-dotnet-real-time

bjagpal · October 17, 2016, 8:17pm

Yes, I had to set up a separate stream analytics job as outlined in that tutorial. I also came across these two links which basically outline the same process:

1 - https://azure.microsoft.com/en-us/documentation/articles/stream-analytics-power-bi-dashboard/
2 - http://radacad.com/stream-analytics-and-power-bi-join-forces-to-real-time-dashboard

I found these links on a Power BI community forum that was discussing this very topic. Essentially there are two ways that Power BI can ingest streaming data from Azure. The first way is as I’ve done it, using a stream analytics service. The second (better) way is to set up a “streaming dataset” from within Power BI. This is basically an integration on par with the Pubnub integration already present. It isn’t quite ready yet, but apparently they are working on it.

Here’s the a link to that discussion:

RWB · October 17, 2016, 9:07pm

@bjagpal I’m glad you posted that up on their forum so hopefully, we can get some help or a time frame for when this feature will be added.

The only way around it right now that I can think of is to push the data to PubNub also and then displays that live data with a max window of 15 mins, and then send that same data to Azure table storage where it will update all stored data every 15 mins. This way you have a window with the live data stream back 15 mins and all historical data will be in another window where Power BI can be set to update/refresh every 15 mins.

Make sense? If you’re using the Electron then sending to PubNub would cost extra since you would have to push that data to their source also to get the live updates to happen. Sucks but hopefully they get it worked out SOON

grillo · January 4, 2017, 9:02pm

@bjagpal Hi, I also completed the tutorial and have got my data passing through Stream Analytics, confirmed by checking the ‘output’ blob storage.

However I can’t get the data to input in PowerBI - did you succeed in this ?

In PowerBI i can see the streaming dataset, but when I create a tile or report there is nothing there… I have four fields to use but cannot work out how to use them; displayname, measurename, timecreated, value.

Any help would be awesome. cheers

RWB · January 4, 2017, 10:31pm

@Grillo That is a service that is still in preview at Microsoft so you will have to ask to have this function added to your Power BI Account to get it working.

Email this guy: Sirui Sun sirsu@microsoft.com

If you push your data into a Azure Table Database then you will be able to access that database from Power BI now without problems.

bjagpal · January 4, 2017, 10:59pm

@Grillo, as I mentioned a few posts above, there are two different ways to get data from Azure Stream Analytics (ASA) into Power BI (PBI). The first method is to simply setup PBI as an output on your ASA and just pass the data through. This is the method outlined at the following links:

1 - https://azure.microsoft.com/en-us/documentation/articles/stream-analytics-power-bi-dashboard/2
2 - http://radacad.com/stream-analytics-and-power-bi-join-forces-to-real-time-dashboard1

The second method is to setup a ‘streaming dataset’, however, this function is in private preview right now so unless you signed up for that private preview, you shouldn’t actually see any ‘streaming datasets’, but you will see a dataset displayed below the “Datasets” heading.

I’m going to assume that you have setup PBI according to method 1 (although my proposed solution should work in both cases).

Now, when you click on the name of your dataset in PBI, it will display your four fields (displayname, measurename, timecreated, value) under the “Fields” heading. Beside your “value” field, you should see a sigma sign. This indicates that that field is an integer. If you do not see this sigma sign, then PBI interprets the data as a character and can’t really do anything with it.

To correct this, you need to explicitly type-cast your data from within your ASA query. Here is an example:

Select
CAST (timecreated AS datetime) as timecreated,
CAST (value AS bigint) as value,
displayname
measurename
INTO
toPowerBI
FROM
IoTinput TIMESTAMP by timecreated

So what I’m doing here is casting my “timecreated” field as a datetime type, casting my “value” field as an integer, and passing through the “displayname” and “measurename” fields without any casting since they are of the character type and can stay that way.
“toPowerBI” is the name of my ASA output, and “IoTinput” is the name of my ASA input.

So in summary, I think you just need to adjust your ASA query. Let me know if this helps.

RWB · January 4, 2017, 11:39pm

@bjagpal FYI - Dave announced that they are working on changing the way we can format webhooks shortly which will allow us to send data via a webhook directly to a webhook tile without the need to pay for or rely upon Azure Stream Analytics.

This can save people some money since Stream Analytics cost can get high depending on what you’re doing.

bjagpal · January 5, 2017, 3:17am

Interesting. I presume this has to do with sending data directly to PBI using its API? I remember you were looking into that…cool.

RWB · January 5, 2017, 5:16am

Yea, direct from webhook to Power BI using the API endpoint so no Stream Analytics needed.

grillo · January 5, 2017, 4:50pm

Thanks @RWB !

Actually I did sign up for the preview so can access the steaming dataset in PBI.

I think the problem is I am not proficient enough to do the query. Am following @bjagpal instructions below. Will post results.

cheers

grillo · January 5, 2017, 4:55pm

And it works…! thanks v much ! got the data plotting out perfectly with your instructions.

Topic		Replies	Views
Need help with cloud visualisation and filtering of data Cloud	1	989	February 4, 2017
Announcing the new and improved Particle Console! Particle Updates	7	2993	August 5, 2016
Particle Dashboard? Cloud	3	400	January 15, 2020
Past Console Usage reports now available via the Console and the API Particle Updates	2	535	April 28, 2022
Dashboard and analytics Project Share	0	1017	August 23, 2015

Related Topics