Managing IoT Data using Stream Analytics, Blob Storage and Power BI

IoT stream analytics - wikiazure

Managing IoT Data using Stream Analytics, Blob Storage and Power BI

Dave Rendón Follow
1 Star2 Stars3 Stars4 Stars5 Stars

In this article I will show you how to create an Stream Analytics Job to process the data coming from the previously connected IoT Device. This article is part of the IoT Virtual Bootcamp –  Predict Temperature using Azure IoT & Raspberry Pi3.

  • Execution Time: 30 minutes.
  • Required Hardware:
    • Windows 10 PC
    • IoT Hardware kit: https://www.adafruit.com/product/3605 or similar hardware.
    • Access to a WiFi network
  • Required Operating System:
    • Windows 10
  • Other Requirements:
    • Active Azure Subscription

Managing IoT Data using Stream Analytics, Blob Storage and Power BI

Stream Analytics is a fully managed event-processing engine that will help us to connect to external data sources, enabling our application to integrate certain data into the application flow, or to update an external database with processed information.

  1. Go to the Azure Portal and create a new Stream Analytics Job, this job also specifies a transformation; how to look for data, patterns, or relationships :

Stream Analytics Job

2. Once the Stream Analytics Job is created, go to the Input option:

Inputs - stream analytics job- wikiazure

3. Now we will prepare the input for the data that we will be receiving form the IoT hardware. This input specifies from where the data comes. Click on Add and provide the proper parameter for your input:

parameters stream analytics job - wikiazure

 

4. Now we will prepare the output from the data we are processing through stream analytics. Click on Outputs:

 

output- stream analytics job - wikiazure

5. Now Click on Add and provide the proper parameters for your desired output as shown in the image below:

 

new output stream analytics job - wikiazure

 

In this case I´m using CSV as Event Serialization Format:

output serialization format stream analytics - wikiazure

 

6. Now we have provided to Stream analytics our input and output. Stream Analytics offers a SQL-like query language for performing transformations and computations over streams of events. In this case we will modify our query as shown below:
The image below shows the default query that you should be looking at:

Stream analytics query - wikiazure

Now we will modify the query as follows:

**Note that you have to have a previously IoTHub deployed – please refer to Create an IoT Hub and connect your IoT Device

Create query stream analytics wikiazure

 

Enter the following query:

 

SELECT
    AVG(temperature),
    iothub.iothub.connectiondeviceid as deviceName,
    DATEPART(minute, System.TimeStamp) as minute,
    DATEPART(hour, System.TimeStamp) as hour,
    DATENAME(weekday, System.TimeStamp) as day,
    DATENAME(month, System.TimeStamp) as month,
    DATENAME(year, System.TimeStamp) as year
INTO
    powerBi
FROM
    iothub
Group By 
    TumblingWindow (minute, 1), iothub.iothub.connectiondeviceid

SELECT
	*
INTO
	blob
FROM
	iothub

 

 

7. Now click on Start Job Now.

 

start stream analytics job -wikiazure

This could take couple of seconds:

 

starting stream analytics job - wikiazure

8. Now let´s move on to integrate Power BI. To achieve Power Bi integration you must create a second output and specify PowerBI as desired output Alias and Authorize PowerBI on your associated account as shown below:

output stream analytics job power bi - wikiazure

PowerBI output stream analytics job - wikiazure

 

Go to http://powerbi.com/, and click Sign in then select “My Workspace”. Under Datasets, click Streaming dataset and then click the Create Report on the table “HandsonLab”.  Then  create a Line chart, using deviceName as the Legend and minute, hour day, month and year as the Axis. Lastly, change the val to Average of avg. Holding your hand over the sensor for over a minute will show the difference seen below:

 

 

9. Once you properly authorized Power BI through your account you should be able to go to Power Bi and review the data coming from your IoT Device:

 

PowerBI report stream analytics job - wikiazure

 

This completes the Lab on Managing IoT Data using Stream Analytics, Blob Storage and Power BI. Please refer to the  IoT Virtual Bootcamp –  Predict Temperature using Azure IoT & Raspberry Pi3.

2 Comments

John Liu

July 20, 2018 at 4:33 am

Hi,Dave,
I try to repeat your lab. In the query: INTO powerBi FROM iothub
The “iothub” should be “iot hub”, the IoT hub you define in the previous lesson?
Thanks,
John

 Reply

Dave Rendon

July 20, 2018 at 1:49 pm

Hi John,

First and foremost, thanks for reviewing and follow this lab, indeed the iothub is defined in the previous lesson. It all depends on the name you provided to your IoT Hub service. Please reach me out in case you have any further question, thanks!

 Reply

Leave a Reply