Azure Setup

Prerequisites

• Microsoft Azure Account - http://azure.microsoft.com/en-us/
• Visual Studio 2013 Community Edition - http://www.visualstudio.com/en-us/news/vs2013-community-vs.aspx

Azure Setup

It is preferable to setup all services at the same data center, so as to avoid incurring performance hit and data transfer charges.

Create Website

This can be performed from the Azure Management Portal or from within Visual Studio:-

• From the Azure Management Portal - http://azure.microsoft.com/en-us/documentation/articles/web-sites-create-deploy/
• From within Visual Studio - http://azure.microsoft.com/en-us/documentation/articles/web-sites-dotnet-get-started/

Create SQL Server Tables

Create an Azure SQL Database and then create 2 tables using the below sql statements.

SQL to Create Processed Telemetry Table

CREATE TABLE [dbo].[ProcessedTelemetry]( [MessageType] [nvarchar](50) NULL, [Temp] [float] NULL, [Humidity] [int] NULL, [Location] [nvarchar](50) NULL, [Room] [nvarchar](50) NULL, [Info] [nvarchar](100) NULL, [EventEnqueuedUtcTime] [datetime2](7) NULL )

CREATE CLUSTERED INDEX [ProcessedTelemetry] ON [dbo].[ProcessedTelemetry]([Location] ASC)

SQL to Create Averaged Telemetry Table

CREATE TABLE [dbo].[AverageTelemetry]( [ID] [int] IDENTITY(1,1) NOT NULL, [StartTime] [datetime2](7) NULL, [EndTime] [datetime2](7) NULL, [AverageTemp] [float] NULL, [AverageHumidity] [float] NULL, [LogCount] [int] NULL, CONSTRAINT [PrimaryKey_a97a20a9-3d36-4d59-82af-e5cb075a0b73] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )

Create Event Hub

Create an event hub, making sure you create a Shared access policy with Managed, Send, Listen selected and call it EventHubPublisher.

Create Stream Analytics Jobs

Create 2 Stream Analytics' Jobs with the following Input, Query and Output.

Input

Both the inputs will be the same, they need to obtain data from the Event Hub created in the previous step.

Query

A different query is required for each job.

Processed Telemetry Job Query

SELECT EventEnqueuedUtcTime, MessageType, Location, Room, Temp, Humidity, Info FROM input1

Averaged Telemetry Job Query

SELECT DateAdd(minute,-5,System.TimeStamp) as StartTime, system.TimeStamp as EndTime, Avg(Temp) as AverageTemp, Avg(Humidity) as AverageHumidity, Count(*) as LogCount FROM input2 GROUP BY TumblingWindow(minute, 5)

Output

Both outputs will be to the appropriate SQL Table, either ProcessedTelemetry or AveragedTelemetry tables.