Adding the power of BI to your IoT Hub

Once you are using Azure an IoT Hub, you are looking for ways to do something meaningful with the telemetry coming in.

You can try to build your own dashboards in a website eg. but you can also try to show the data using PowerBI.

In this blog, we will look at how to show some nice charts with live data.

This is part 3 of a series of blogs about IoT Hub communication using UWP:

  • Part 1: A very simple complex IoT Hub example
  • Part 2: Using the Visual Studio 2015 IoT Hub Extension and IoT Hub Device explorer
  • Part 3: Adding the power of BI to your IoT Hub
  • Part 4: Closing the Windows IoT Core feedback loop using Azure Functions
  • Part 5: PowerBI showing telemetry of past ten minutes, always!
  • Part 6: Add more flexibility to StreamAnalytics

What do we need?

If you want to work with PowerBI you will need an account at https://powerbi.microsoft.com/en-us/ . Be aware this has to be a “work or school” account at the moment.

Once you have this, remember the credentials. You will need them later on.

As a basis for this blog we build on top of the result of part 2.

Creating an IoT Hub

We create a new IoT Hub, just for the fun of it 🙂

blogtest01

Note: we now have a new (preview) option for device management inside the portal. That’s why I choose North Europe as the region.

I want to point out one  little gem.

It’s wise to give the IoT Hub two consumer groups. The same message is then available for two consumers. I added hot path and cold path.

blogtest02

We will use the hot path for BI. The cold path can be used to put the telemetry also in cold storage like Azure DataLake.

Updating the generated connected service

As seen in part 2, we nog have a nice wizard to connect to an IoT Hub. But this connected service only provided two methods: one for sending and one for receiving.

The method to send a message does not accept parameters. We want to pass real telemetry to be shown in the charts so we will accept some telemetry:


internal static class AzureIoTHub
{
    private const string deviceConnectionString =
      "HostName=xxx;DeviceId=DeviceOne;SharedAccessKey=yyy";

    public static async Task
      SendDeviceToCloudMessageAsync(Telemetry telemetry)
    {
        var deviceClient = DeviceClient.
              CreateFromConnectionString(deviceConnectionString,
                                         TransportType.Amqp);

        var message = new Message(
              Encoding.ASCII.GetBytes(
                Newtonsoft.Json.JsonConvert.
                  SerializeObject(telemetry)));

        await deviceClient.SendEventAsync(message);
    }

    ...

    public class Telemetry
    {
        public DateTime time { get; set; }
        public decimal temperature { get; set; }
    }
}

To pass real telemetry, we call this static method in the main page (see my previous blog for the Xaml page layout and initial code behind):


private Random _random = new Random((int)DateTime.Now.Ticks);

private DispatcherTimer _timer;

public MainPage()
{
    this.InitializeComponent();

    btnSend_Click(null, null);

    _timer = new DispatcherTimer();
    _timer.Interval = new TimeSpan(0, 0, 20);
    _timer.Tick += (sender, e) => { btnSend_Click(null, null); };
    _timer.Start();

    btnSend.IsEnabled = false;
}

private async void btnSend_Click(object sender, RoutedEventArgs e)
{
    await Dispatcher.RunAsync(
      CoreDispatcherPriority.Normal, () =>
      {
          tbReceived.Text = string.Empty;
      });

    var t = new AzureIoTHub.Telemetry
    {
        time = DateTime.Now,
        temperature = 20.5m + _random.Next(-20, 2),
    };

    try
    {
        await AzureIoTHub.SendDeviceToCloudMessageAsync(t);

        await Dispatcher.RunAsync(
          CoreDispatcherPriority.Normal, () =>
          {
              tbReceived.Text = "Message sent";
          });
    }
    catch (Exception ex)
    {
        await Dispatcher.RunAsync(
         CoreDispatcherPriority.Normal, () =>
            {
                tbReceived.Text = ex.Message;
            });
    }
}

So run the app and check the incoming telemetry using the Device explorer to see the data passed:

blogtest03

We use a timer to send a value every 20 seconds. If a shorter time span is chosen, you might get transient errors. This means you are sending too many telemetry items in a short period.

Adding Stream Analytics

To get data out of IoTHub, we need StreamAnalytics. Let’s add one. We use the classic Azure portal, for now. The new Azure portal can create it too but then the IoT hub consumer groups are not supported!

blogtest04

Drawback is that the Stream Analytics is not added to our resource group.

After creation we stay in the classic portal.

A Stream Analytics uses eg IoT Hub as input and it can send it’s output to PowerBI. That’s what we need, so let’s do this!

Adding IoT Hub as input

Adding the IoT Hub as input is fairly straight forward:

Choose Data stream input:

blogtest05

Lol, IoT Hub is still in preview???

blogtest06

Select the correct IoT Hub (if you have multiple). Check the right consumer group:

blogtest07

Keep the serialization settings:

blogtest08

Now we have input:

blogtest09

Selecting PowerBI as output

Stream Analytics needs the output for a job defined. We choose PowerBI:

blogtest10

Next we need to authorize a connection. Fill in the credentials your we asked to remember above:

blogtest11

Now we can enter storage information. Personally, I do not know where this storage?? is done. I do not see it inside in my subscription:

blogtest12

An now the output is available:

blogtest13

Writing a Stream Analytics Query

We have input, we have output and now we need to connect them using a query. A Stream Analytics query looks a lot like SQL queries so let’s add one:

So add this very simple query:

blogtest14

It just passes each message towards PowerBI. It is wise to put some effort in a hopping windows 🙂

We cast the column values to be sure the data passed is in the right format. If PowerBI thinks your numeric data could be text, your can not aggregate the data. This means you have no sum, average, min, max etc.

Now run it!

blogtest15

It takes a few minutes to get the job running. Sit down and rest a little, the end is near.

Note: You can choose the start time of the job. Why? data inside the IoT Hub can be read multiple times, as long as it is still there. So you can test the query multiple times with the same live data…

Receiving data in PowerBI

Go to the PowerBI portal. It should be pretty quiet there…

blogtest16

But wait! If we run the UWP app and send some telemetry, after some time, a datasource is shown. It this the output of the Stream Analytics:

blogtest17

Creating a chart in PowerBI

So now we have data, let’s build a chart.

Select the Line chart at the right. Drag the time column to the “Axis” and the temperature to the “Values”:

blogtest18

Looks’s great, doens’t it?

Now ‘pin’ the chart:

First give the chart a name:

blogtest19

Then add the chart to a (new) dashboard:

blogtest20

And after that, enjoy your new, great dashboard:

blogtest21

And the great thing is, it’s live! you will see the data coming in, every ten or so seconds.

 

Advertenties