Stream SmartThings data to Cosmos DB and PowerBI Part 3 – Reporting

So far we’ve sent our SmartThings data to event hub, collected this data using Stream Analytics and then stored it in Cosmos DB. In this part were finally going to use that data to create some reports in PowerBI that show SmartThings sensor data over time. We’re going to focus on the example I started with, showing temperature change over time. This will give you the information you need to go on and report on whatever data you want to.

The first thing I want to make clear is that I am very much a PowerBI novice. I’ll show you how I managed to do things, these are based on internet research, but I am very aware that there may be better ways to do it. If you do know any, please let me know! Given that caveat, let’s get started.

Accessing PowerBI

To create PowerBI reports, you need access to PowerBI. There are two ways to create reports, either online through a web browser or using the desktop tool. The web designer is very limited in what data sets you can work with. This does not include Cosmos DB, so we need to work in the desktop tool, which you can download here for free.

You will be able to do 90% of what is in this tutorial using the desktop tool only. You only need access to a web-based workspace if you want to publish the report so you can access it online. If you want to use the online components, you will need to sign up. It’s not part of your Azure subscription (at least for Power BI Pro). There is a 60-day free trial available here.

Creating a Report and Data Set

Once you have downloaded and installed the PowerBI desktop tool we can start creating a report. There isn’t a “new report” button, instead, you select a data source to use which will then create the report. On the PowerBI splash screen, click the “Get Data” button.

Go to the Azure section and then select “Cosmos DB (Beta)” and then click “connect”. A window will open and it needs 3 pieces of information which we can get from the Azure portal:

• The URI for the Cosmos DB account, you can find this on the overview page of your Cosmos account, it will be in the format of https:///documents.azure.com:443
• The Database Name, this is what we created in the previous tutorial, you can see it under data explorer if you forget
• The collection name, also available under data explorer, underneath the database name.

Enter these 3 items and then click OK.

This will open a preview window that will look a bit odd, it will have lots of rows in a single column called “document. We need to expand the documents so we have access to the data inside them, so click on the edit button at the bottom.

You’ll see a similar window with again lots of rows of documents. Click on the small double arrow button at the top right of the column header.

This will open a window asking you to select which columns you want to use. They should all be selected currently, leave it like that and click OK.

You’ll now get a preview that shows you all the data inside the Cosmos DB documents.

Click the “Save and Apply” button at the top left. It will take a little while to import the data (especially if you have already collected lots of data). Once complete we have now connected PowerBI to our data and we have a blank report ready for our charts.

Transforming Data

Before we can create a chart we need to resolve a slight issue. The data we are sending to Cosmos DB has a value field, which is what we are going to use in our charts. But, the values we are sending can either be a string, for things like a motion sensor, where it sends “Motion Detected”, or an integer for things like temperature. This mismatch means that we store this data as a string. This is no good for our chart, as we need the temperature data in numerical format.

There is an argument to amend sending the data to be all numeric. For now, we are going to transform this data in PowerBI instead.

To create the transformation to the ribbon and click the “Edit Queries” button. This will open the data editor page. In here, go to the “Add Column” tab and then click on “Custom Column”. This will open a query editor window to allow us to enter a query for the new column. First, enter a name for the column, I went with “valueInt”. Then in the “Custom column formula” box, you should enter:

=try Number.From([Document.value])otherwise 0 

This will take the data from the Value column and try to cast it to an Int. If it can it uses that value and if it cannot then it sets it to 0. The last thing we need to do is make sure to set that column as an integer one. In the column heading click the icon to the left of the name then in the drop-down select “Decimal Number”.

We also need to change the columns that contain dates to be the data time data type. Select the same button on the “Document.EventProcessedUtcTime” and Document.EventEnquedUtcTime” columns and change to the “Date/Time” option. Go to file, save, then if it asks you to apply pending changes select “Apply”. If you haven’t already it will also ask you to save the file at the same time.

Creating a Chart

Now we have imported the data, we are ready to create a chart to display it. We’re going to look to chart the temperature sensor readings from many sensors. For this, we are going to use a line chart. The Y axis will be the temperature, the X axis being time. Each sensor is a separate line.

To create the chart, click on the line chart icon on the visualizations toolbox on the right. This will create a blank chart on the canvas. You can resize this as you wish.

The chart is now ready for us to assign some data to it. Select the chart, and you will see on the right-hand menu we now have boxes to fill in for things like “Axis”, “Legend” and “Values”.

Further to the right, you should also see the “Fields” section, and inside that the query we created (if you took the default name it is called “Query1”.

Expand this and you should see the fields that contain our data.

Make sure you have the chart selected, then drag the following fields over to match what you see below:

Once you do this, you will see that there is now data on the graph, but you’ll note it contains all our sensors, not just temperature ones. We need to make some adjustments to get the data we want. You may have noticed that when you added the fields it also added some values to the “Filters” section further down the page.

We want to filter by Sensor Type, rather than name. In the right hand “Fields” column you should see a filed called “Document.sensorType”, select this and drag it into the “visual level filters, underneath the one that says “Document.sensorName (All)”. You will then see a list of sensor types, select the one called “temperature”.

Next, we want to make sure we are showing the average temperature value if there are multiple for a selected time and not the sum. To do this, select the drop-down arrow next to the values box and select “Average”

You should now see a chart that is showing your temperature data as a line for each sensor.

Filtering Time

This is great, but as you can see from this screenshot when you get a lot of data, it will attempt to show it all and it doesn’t lead to a very readable chart. You need to be able to filter it to show the required period.

For some reason, PowerBI does not come with a time slicing tool built in, but there is one available on the marketplace here.

Download the file it provides, then in PowerBI desktop go to the visualisation toolbox and click the … button. Select “import from file”, accept the warning then point it at the file you downloaded. Once done the time slice visualisation will be available in the toolbox, select it to add it to your canvas. Once added, you need to tie it to your time value to allow it to filter. Select the time slice object on the canvas, then drag the “Document.EventProcessedUtcTime” filed into the time box. It should then look like this:

You can then select the period you want to look at and get a much more readable chart.

Formatting

Now we have a chart showing the data we want, we can customise things like chart titles, axis etc. Select the chart then select the paint roller icon on the right to see the visual options.

Publishing

At this point, you have a report that you can run in the desktop application and load up and refresh the data whenever you want. You can even send the .pbix file around to others who want access.

If you want to publish this to the online PowerBI workspace then there are a couple of extra steps. First, we need to publish it, go to the file menu and click publish. Sign in with your PowerBI account and then select a workspace to publish to. Your report will get published and will be ready to view online in a few minutes.

Your chart is now available in a browser and you can share this with other PowerBI users. But, in its current state, the data will not refresh automatically, you will need to hit the refresh button. To arrange an automatic refresh, go to the dataset section on the left and locate the dataset you created. Click on the … button and go to “Schedule Refresh”. Expand the “Scheduled Refresh” section and change the “keep your data up to date” box to On, then you can select how often you want to refresh. The minimum you can select is daily, but you can multiple times in the day, so you can refresh up to 6 times a day.

More Charts

Hopefully, this has given you a good introduction to building charts with SmartThings data. You can use this approach to build charts for any of the data we’ve been collecting. I’d be really interested to see what visulations people are building, so please do feel free to comment with details!