Indoor Temperature and Humidity Data Collection and Analysis using Azure IoT Hub, Azure SQL Database and Power BI

5 min readAug 7, 2020

Before I jump into the article, I would like to THANK Microsoft for sending me the IoT DevKit (MxCHIP IoT Device) as a gesture of appreciation for participating in the Azure skill challenge.

Now let’s jump into the article and see how we can collect the sensor data (indoor temperature and humidity) and analyze, the below illustration shows the steps and services involved in data collection and analysis

Step -1: Connect your IoT Device to Azure IoT Hub

In this article, I used MxCHIP IoT Device, and here are the instructions that I followed to connect my IoT Device to IoT Hub. So follow the instruction and connect your device to IoT Hub, If your device is connected to IoT Hub then you see T: temperature and H: humidity value in the display screen with message count sent to Azure IoT Hub.

you can check the telemetry data sent to IoT Hub by running the below command in Azure Cloud Shell:

az iot hub monitor-events --hub-name IoTHubName --output table

Step — 2: Provision Stream Analytics Job

Azure Stream Analytics is PaaS service provided by Microsoft, it’s an event-processing engine that is designed to analyze and process high volumes of fast streaming data from multiple sources simultaneously.

Create Stream Analytics Job in the Azure portal

Enter the required details and click on the create, to create the streaming job.

Step -3: Provision Azure SQL Database to store the IoT data

Create Azure SQL Database in Azure portal and add your client IP to the firewall rules to connect it from your client PC

Connect to the Azure SQL Database from your client PC and create a table to store the IoT temperature and humidity data.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[iot_temp_humidity](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [messageid] [varchar](3) NULL,
    [temperature] [nvarchar](200) NULL,
    [humidity] [nvarchar](200) NULL,
    [EventProcessedUtcTime] [datetime2](7) NULL,
    [EventEnqueuedUtcTime] [datetime2](7) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[iot_temp_humidity] ADD  CONSTRAINT [PK_iottemphum] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Now we are ready with all the resources that we need to process the IoT telemetry data, below are the resources that you should see in your resource group after this step

Step-4: create Stream Analytics Job to process the real-time IoT data

Open the Stream Analytics resource that we created in Step -2 and click on the overview to set up the input and output stream details of the Job

We are going to use the IoT Hub that we created in Step-1 as streaming input, so click on Input and Add IoT Stream Input as “IoT Hub”, enter the IoT Hub details and Save it

Now click on the output and Add Output as “SQL Database”, enter the SQL DB details and save it.

So the Input and output for the Stream Analytics Job has been set, now change the query to pull the required data elements by clicking on the Edit Query

Edit the query and click on Test Query to see the test results, if everything looks good then save the query. We are all set to kick-off our newly created Stream Analytics Job, so go back to Stream Analytics Job and click on Overview and click on ‘Start’ to kick-off the streaming job.

The streaming job will collect the data from IoT Hub and process it to SQL database , So let’s connect to the SQL Database and quey the IoT Data, use the below SQL query to test the data from your table.

SELECT [messageid]
      ,[temperature]
      ,[humidity]
      ,[EventProcessedUtcTime]
      ,[EventEnqueuedUtcTime]FROM [dbo].[iot_temp_humidity]

Step-5: Connect SQL Database from Power BI and Analyse the data

Now its time for data analysis using Power BI, let’s connect to our “iot_teperature” SQL database from Power BI Desktop and create a report.

Thanks for visiting this page and I hope you enjoyed reading the article. See you in my next article until then stay safe and healthy