Power BI Reports using Azure Event Hubs and Cosmos Db

Stream sensor data using Event Hubs to Cosmos Db to build custom Power BI dashboards.

Background

Integrate Azure services using myDevices native integration to stream IoT sensor data to Azure Event Hubs, and build custom Power BI dashboards and reports. In this article, we'll go over the steps needed to capture sensor data, performs a small transformation, store the telemetrics in Cosmos DB and visuallize dashboards with Power BI.

Requirements

This tutorials assumes you have an existing Azure account and familiarity with Azure services.

Table of Content

1. Setup an Event Hub Integration
2. Create a Cosmos DB database and collection
3. Setup an Stream Analytics Job
4. Setup Stream Analytics Input (Event Hub)
5. Setup Stream Analytics Output (Cosmos DB)
6. Visualize data with Power BI

Stream analytics input SQL

SELECT
    i.EventId as Id,
    i.event_data.device_id as DeviceID,
    i.event_data.hardware_id as HardwareID,
    System.Timestamp() as Timestamp,
    i.event_data.application_id as ApplicationID,
    SensorPayload.ArrayValue.type as SensorDataType,
    SensorPayload.ArrayValue.unit as SensorUnit,
    SensorPayload.ArrayValue.channel as SensorChannel,
    SensorPayload.ArrayValue.value as SensorValue,
    i.event_data.user_id as UserID,
    i.device_type.id as DeviceTypeID,
    i.device_type.name as DeviceTypeName,
    i.device_type.manufacturer DeviceManufacturer,
    i.device_type.model as DeviceModel,
    i.company.id as CompanyID,
    i.company.name as CompanyName,    
    i.company.address as CompanyAddress,
    i.company.city as CompanyCity,
    i.company.state as CompanyState,
    i.company.timezone as CompanyTimezone,
    i.location.id as LocationID,
    i.location.name as LocationName,
    i.location.address as LocationAddress,
    i.location.city as LocationCity,
    i.location.state as LocationState
INTO
    [airquality]
FROM
    [tina-source] i
CROSS APPLY GetArrayElements(event_data.payload) AS SensorPayload