Azure Stream Analytics and Power BI

Introduction

Completed 100 XP

• 2 minutes

Microsoft Power BI is used by organizations all around the world to create dynamic, interactive data visualizations that reveal insights on which important business decisions are based. Access to timely data can be the difference between failure and success, so the ability to capture and visualize data in real-time, or as near as possible, is critical in many scenarios.

Azure Stream Analytics provides a way to process a stream of real-time data from an input such as Azure Event Hubs, and direct the results to an output. One possible output is a Power BI dataset, from which dashboards can consume data for real-time visualization.

In this module, we’ll examine how to use Azure Stream Analytics to process a stream of real-time data, and send the results to a Power BI dataset for visualization.

Use a Power BI output in Azure Stream Analytics

Completed 100 XP

• 6 minutes

All Azure Stream Analytics jobs include at least one input and output. In most cases, inputs reference sources of streaming data (though you can also define inputs for static reference data to augment the streamed event data). Outputs determine where the results of the stream processing query will be sent. To support real-time data visualization, you can use a Power BI output.

Streaming data inputs

Inputs for streaming data consumed by Azure Stream Analytics can include:

• Azure Event Hubs
• Azure IoT Hubs
• Azure Blob or Data Lake Gen 2 Storage

Depending on the specific input type, the data for each streamed event includes the event’s data fields and input-specific metadata fields. For example, data consumed from an Azure Event Hubs input includes an EventEnqueuedUtcTime field indicating the time when the event was received in the event hub.

Note

For more information about streaming inputs, see Stream data as input into Stream Analytics in the Azure Stream Analytics documentation.

Power BI outputs

You can use a Power BI output to write the results of a Stream Analytics query to a table in a Power BI streaming dataset, from where it can be visualized in a dashboard. When adding a Power BI output to a Stream Analytics job, you need to specify the following properties:

• Output alias: A name for the output that can be used in a query.
• Group workspace: The Power BI workspace in which you want to create the resulting dataset.
• Dataset name: The name of the dataset to be generated by the output. You shouldn’t pre-create this dataset as it will be created automatically (replacing any existing dataset with the same name).
• Table name: The name of the table to be created in the dataset.
• Authorize connection: You must authenticate the connection to your Power BI tenant so that the Stream Analytics job can write data to the workspace.

Create a query for real-time visualization

Completed 100 XP

• 6 minutes

To send streaming data to Power BI, your Azure Stream Analytics job uses a query that writes its results to a Power BI output. A simple query that forwards event data from an event hub directly to Power BI might look something like this:

SQL

SELECT
    EventEnqueuedUtcTime AS ReadingTime,
    SensorID,
    ReadingValue
INTO
    [powerbi-output]
FROM
    [eventhub-input] TIMESTAMP BY EventEnqueuedUtcTime

The results of the query determine the schema of the table in the output dataset in Power BI.

Alternatively, you might use your query to filter and/or aggregate the data, sending only relevant or summarized data to the Power BI dataset. For example, the following query calculates the maximum reading for each sensor other than sensor 0 for each consecutive minute in which an event occurs.

SQL

SELECT
    DateAdd(second, -60, System.TimeStamp) AS StartTime,
    System.TimeStamp AS EndTime,
    SensorID,
    MAX(ReadingValue) AS MaxReading
INTO
    [powerbi-output]
FROM
    [eventhub-input] TIMESTAMP BY EventEnqueuedUtcTime
WHERE SensorID <> 0
GROUP BY SensorID, TumblingWindow(second, 60)
HAVING COUNT(*) > 1

When working with window functions (such as the TumblingWindow function in the previous example), consider that Power BI is capable of handling a call every second. Additionally, streaming visualizations support packets with a maximum size of 15 KB. As a general rule, use window functions to ensure data is sent to Power BI no more frequently than every second, and minimize the fields included in the results to optimize the size of the data load.

Create real-time data visualizations in Power BI

Completed 100 XP

• 5 minutes

When you successfully run an Azure Stream Analytics job that sends results to a Power BI output, a streaming dataset containing a single table is created in the Power BI workspace specified for the output. The table contains the data produced by the Stream Analytics query.

Creating real-time visualizations in a dashboard

To visualize data in real-time, you can create a dashboard with a real-time visualization tile. Real-time visualizations on a dashboard show data from a streaming dataset, and are updated dynamically as new data flows into the dataset.