Basic Azure IoT Flow : From Event Hub via Stream Analytics to Power Bi

Introduction

A few weeks back I posted a blog post on how you can leverage “serverless” components for IoT. Today I’ll show you what it would mean if we replace the Azure Functions component in that post by Azure Stream Analytics.

 

Flow

So the flow between device and event hub is untouched. Though we’ll replace the functions part with Azure Stream Analytics. So how will the end result look?

2017-01-03-11_11_49-job-diagram-microsoft-azure

We’ll be using one Stream Analytics job to trigger three flows. One will store the data into an Azure Table Storage, another on will store it as a JSON file on to Azure Blob Storage and another one will stream it directly into a PowerBi dataset.

So let’s take a look at all the components from within this Stream Analytics Flow we’ll be using…

2017-01-03-11_23_08-inputs-microsoft-azure

 

Input : EventHub

We’ll only be using one input, being our event hub.

2017-01-03-11_17_32-program-manager

The messages onto the quere are serialized in a “json”-format. Now onto our three output…

2017-01-03-11_17_50-output-details-microsoft-azure

 

Output : Power Bi

Our first output will be the one for Power Bi.

2017-01-03-11_18_01-output-details-microsoft-azure

You can authorize the connection and specify the dataset & table name.

 

Output : Table Storage

Next up is the table storage, here you specify the storage account settings. In addtion, I’ve set up the partition key to be my “hostname” and the row key to be my “timestamp”.

2017-01-03-11_18_30-output-details-microsoft-azure

I could increase the batch size. As my poc environment is pretty “modest”, I’m keeping this at “1”.

 

Output : Blob Storage

For archival purposes, I want to keep a history of all my telemetry (data sent by my devices. Why do I want this? If at a given point I gain new insights (go figure?!?), then I’m able to replay the data I gathered earlier on.

2017-01-03-11_18_17-output-details-microsoft-azure

Do notice that I specified a path pattern. This will save the telemetry data per year/month/day/hour…

2017-01-03-11_30_01-program-manager

So I could even go back quite granular without doing anything fancy!

 

Query

Now let’s take a look at the query…

2017-01-03-11_19_21-kvaes-iot-telemetry-microsoft-azure

This is a rather “modest” query. There are two non basic treats in there… One is that I’m overriding the timestamp (that is normally given by the event queue) by the one included in my message (as defined by my device) and that there are three queries…

SELECT
    hostname ,
    timestamp ,
    Humidity ,
    Temperature
INTO
    [storageTableTelemetry]
FROM
    [ehQueueTelemetry] TIMESTAMP BY Timestamp


SELECT
    *
INTO
    [pbiDatasetTelemetry]
FROM
    [ehQueueTelemetry] TIMESTAMP BY Timestamp


SELECT
    *
INTO
    [storageBlobTelemetryJSON]
FROM
    [ehQueueTelemetry] TIMESTAMP BY Timestamp

For the table storage, I’m doing  limited select, where for power bi & blob storage, I’m just redirecting everything.

 

Testing

Now, let’s test this… Select the three dots of your input, and either upload sample data, or you can capture it from the input. I’ll be doing that one here.

2017-01-03-11_19_36-program-manager

It’s processing…

2017-01-03-11_19_50-kvaes-iot-telemetry-microsoft-azure

… and done!

2017-01-03-11_20_33-kvaes-iot-telemetry-microsoft-azure

Now you’ll be shown the results for each of your outputs. This one being table storage

2017-01-03-11_20_50-kvaes-iot-telemetry-microsoft-azure

And this one being the power bi.

2017-01-03-11_21_06-kvaes-iot-telemetry-microsoft-azureThis looks good! So we’ll run that query…


Monitoring
Once a job is running, you monitor the metrics… If you see the blue line spiking, then you are receiving errors.
2017-01-03-11_18_54-metric-microsoft-azure

Visualization : Power BI

You promised me power bi too! Yeah, I did… 🙂 So let’s a look at that one. Bare in mind that I’m using the preview look & feel. So your screen might look a bit different.

2017-01-03-11_38_33-power-bi

As you can see, the dataset was generated as expected… and I’ve already created a report for my telemetry data.

2017-01-03-11_39_23-power-bi

When we take a look at the dataset, we can see this matches the one we specified in our Stream Analytics job.

2017-01-03-11_39_35-program-manager

And I can now create visualizations for my data… Here is an example for the temperature of this room…

2017-01-03-11_40_08-program-manager

… and for the humidity.

2017-01-03-11_40_25-program-manager

 

Costs

How much did this flow cost me?

  • Event Hub : As I’m only using one consumer group, I can limit myself to the basic variant (~9€/month), where the standard variant would set me back about ~19€/month.
  • Stream Analytics : The 24×7 running job is about 19€/month, with a bit extra (0,0008€/GB) for my (rather modest) bandwidth.
  • Storage : With 2 euro cent / GB, I’m hardly noticing this cost. The transactions (IOPS) will cost me a bit more, but still nothing in compared to the above (which is still not that big a deal).
  • Bandwidth : All bandwidth is “ingress” (from outside to Azure), so that doesn’t cost me anything.
  • Power Bi : The free tier is sufficient. In my previous flow I had to use the pro tier if I wanted my data to be refreshed more than once a day (up to 8 times per day). Now I have “near online” information as Stream Analytics is feeding the data directly into Power Bi.

 

Serverless

In this flow, I’m not using “Azure Functions” anymore… So one might say I’m not having a “serverless”-approach anymore. As I’m still using “PaaS/SaaS”-only building blocks, we are still running “Serverless”. The semantic is mainly oriented towards not using the typical IaaS Compute components.

 

Closing Thoughts

Azure provides a lot of services, and it’s sometimes a though job to find the right fit for your workload. Here I must say the move from Azure Functions to Azure Stream Analytics worked out wonderful for my workload. I’ve upgraded the visibility of my telemetry towards “near online”, whilst keeping the costs still manageable. The architecture I have here can scale far beyond that one single IoT device I currently have, and that for a cost… that’s still very modest!

Advertisements

2 thoughts on “Basic Azure IoT Flow : From Event Hub via Stream Analytics to Power Bi

  1. Nice post. Stream analytics needs a Visual Studio plugin it’s a bit of pain dev’ing queries in the portal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s