This post is a loose follow-up to the From IoT Hub to SMS article I posted on December. Let's start with a question: "Do we need Stream Analytics do get data from IoT Hub to Power BI?" The answer is as usual: "sometimes you do, sometimes you don't..."

For those not familiar, the traditional approach to IoT in Microsoft Azure is to connect the IoT device to IoT Hub, then let data flow through Stream Analytics and finally dump it anywhere you want to use it (SQL Database, CosmosDB, PowerBI etc.).

In most cases this is OK, because you can use the power of Stream Analytics to transform and aggregate the data, combine it with other information etc. But there are cases when you don't need all this and you're okay with just storing the data as they come.

Scenario

Imagine a school classroom. They have a temperature and CO2 sensor, controlled by a field gateway, which is connected to the internet. The gateway device sends measurements regularly to IoT Hub and the goal is to provide a Power BI dashboard so that people responsible for air conditions in the school can monitor the air quality.

Architecture

As there's no need to display streaming datasets and we are interested only in historical data, we can bypass Stream Analytics and store data to Storage Table directly.

Ingredients

You will need to provision the following services:

• IoT Hub
• Function App
• Storage Account (blob, table, queue)

In this article I assume you already have your way of getting data from the device to IoT Hub in a format that suits your needs. I will focus on the rest of this chain - saving data to Storage and displaying in Power BI.

Set up Azure Function

First thing is to connect your Function App to IoT Hub. The process is similar to the one described here. Once you have your function ready and triggered by the IoT Hub, return here and we will continue.

Storage Table as Output

The output in this case will be Azure Storage - Storage Table to be precise.

1. Go to the Integrate tab of your function.

2. Select + New Output in Outputs.

3. Choose Azure Table Storage.

4. Change the Table name to a meaningful name of the table where you want to store your data (such as tempdata).

5. Click new next to the Storage account connection field.

6. Select the Storage Account you have created earlier.

Good, now the function will know where to store your IoT Hub messages.

Function Code

Click your function’s name to get to the code editor.

Copy/paste this code:

#r "Microsoft.WindowsAzure.Storage"

using System;
using Microsoft.WindowsAzure.Storage.Table;

public static void Run(DataPoint myIoTHubMessage, out DataPoint outputTable, TraceWriter log)
{
    log.Info($"C# IoT Hub trigger function processed a message: {myIoTHubMessage}");

    myIoTHubMessage.PartitionKey = DateTime.Now.Year.ToString();
    myIoTHubMessage.RowKey = DateTime.Now.ToString("yyyy-MM-ddTH:mm:ss");

    outputTable = myIoTHubMessage;
}

public class DataPoint : TableEntity
{
    public string DeviceId { get; set; }
    public double Temperature { get; set; }
    public double Co2 { get; set; }    
}

Note that we can strongly-type the input parameter myIoTHubMessage to DataPoint and have it deserialized automatically from JSON by the Azure Functions runtime.

Also see how the data point is altered to adhere to Storage Tables format - PartitionKey and RowKey properties are mandatory. They can contain any string you find suitable, but together have to be unique (they form the primary key).

Choosing the right Partition and Row key is crutial to the performance of the table. Here I chose the year as Partition and date with time as Row, but you can go with greater/lesser granularity - depending on how you want to query your data.

Keep in mind that querying for any other column (other than ParitionKey and RowKey) can be very costly for tables with large amounts of rows.

If everything went correctly, you should see no errors in the function app's logs.

You can now try and send a message to the IoT Hub. This function expects payload like this:

 {
     "deviceId":"tester",
     "temperature": 21.2,
     "co2": 920.1
 }

The log indicates whether the function run was successful:

And you can check your Storage Account to see if the record was added to the table:

Hint: I'm using Azure Storage Explorer to browse my storage accounts.

Power BI

We will use Power BI Desktop to load dashboard from Azure Table Storage.

Btw: Power BI Desktop is now available from Microsoft Store on WIndows 10!

1. Start Power BI Desktop

2. Click Get data.

3. Find Azure Table Storage in the list of providers.

   

4. Enter your Storage Account name:

5. Enter that Storage Account's access key:

6. Power BI should get a list of tables in this Storage Accounts. Select table and click Edit.

7. Click the expander button shown in the Content header.

8. Select all columns and uncheck the checkbox (it's actually up to you, I prefer it unchecked).

Voilá, the data from IoT device is now in Power BI. You should now change the data type of Temperature and Co2 to be double and Timestamp to be datetime.

And finally, click Close and apply and start building some cool visualizations!

Summary

This article demonstrated how to ingest data from IoT Hub using Azure Functions and load it in Power BI Desktop using Azure Storage Tables. The main goal was reached - we have omitted Stream Analytics. Now it's up to you to adjust the process and/or code to suit your specific needs.

And yes... my Power BI Desktop is in Czech :)