Visualize Azure Metrics into Power Bi Real-Time Streaming Service

Real-time information is hot and trendy! As part of the operational environment, you want to gather information on the health and performance as soon as possible. Azure Monitoring Service can deliver the data one needs in the blink of an eye. In one of my previous blog posts on Azure Monitoring Service, I explained how to utilize and integrate metrics in the Azure dashboard. Thanks to the metrics’ extensibility, you can stream this information directly to Power Bi. The big advantage? This information now becomes available in the reporting tool Power Bi, typically used to create an application reporting dashboard. Technical and business information can be brought together in Power Bi, thus providing even more analysis insight.

How it’s built

Any Azure service supporting Monitor service metrics can be used to gather information on technical data. Here are some interesting examples:

  • Event Hub: Incoming messages and failed requests
  • Virtual machine: Amount of disk transactions, CPU, memory usage
  • Logic apps: Processed and failed messages or triggers
  • Batch account: Task failures, pool resize events, idle node count, etc.

Azure metrics power bi

A result of my dashboard to display succeeded and failed executions for the last hour.

powerbi graph success and failures logicapps

Measure values with Log metric

Log metric is responsible to track the performance of the resource (such as a VM, website or logic app) by plotting its metrics on a portal chart, and pinning that chart to a dashboard. The resulting data can be shared with other tools by configuring a connection through Event Hubs.

Navigate to the service offering metrics (you might have to enable this first, check out my post on Azure Monitoring service for more information). Next, go to the Diagnostics settings, configure “Stream to an event hub”, and enable Metric.

The captured metric information will now be streamed to Event Hubs, and thus make it available to any third party tool.

azure diagnostic settings

A partial export of the published data:

{
"records":[
{
"count":2,
"total":2,
"average":1,
"resourceId":"/SUBSCRIPTIONS/2DBFAE69-1B47-450F-9668-8F6E28957F6A/RESOURCEGROUPS/GROUP/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/DGLOGICAPP2",
"time":"2016-11-12T18:31:00.0000000Z",
"metricName":"RunsStarted",
"timeGrain":"PT1M"
},
{
"count":2,
"total":2,
"minimum":1,
"maximum":1,
"average":1,
"resourceId":"/SUBSCRIPTIONS/2DBFAE69-1B47-450F-9668-8F6E28957F6A/RESOURCEGROUPS/GROUP/PROVIDERS/MICROSOFT.LOGIC/WORKFLOWS/DGLOGICAPP2",
"time":"2016-11-12T18:31:00.0000000Z",
"metricName":"RunsCompleted",
"timeGrain":"PT1M"
}
...

Query data with Stream Analytics

But in order to be able to display the data, transformation and analytics of the real-time data must be performed first. Stream analytics handles the real-time analytic computations on data streaming within a certain time frame.

Create a stream analytics, and connect the topology:

  • Inputs: connection to the Event Hub, where metric information is available.
  • Outputs: Power Bi account connection to deliver output as streaming data set.

azure stream analytics topology

In this example, I enabled the log metrics on my logical apps services. Being dependable on the Azure service type, the metric data require customized handling to make them available to Power Bi.

  • STEP1 data set collects the information from the available records in one table. This is the first step to transforming data and making them available for usage
  • RAWDATA pivots the dataset into a table
  • The data is afterwards inserted into “InsightsPowerBi”, which represents the data stream to Power Bi.

In the example below, I did not take in account certain metric values, such as TriggersStarted, TriggersFired, and TotalBillableExecutions. Overall, I suggest to cover only the columns that bring an added value to your application, or you would like to have available in the report: covering all metric values would consume unnecessary compute and storage power.

WITH STEP1
AS (SELECT
flattened.arrayindex,flattened.arrayvalue.count,
flattened.arrayvalue.total,flattened.arrayvalue.minimum,
flattened.arrayvalue.maximum,flattened.arrayvalue.average,
flattened.arrayvalue.resourceId,flattened.arrayvalue.time,
flattened.arrayvalue.metricName,flattened.arrayvalue.timeGrain
FROM insightsMetrics CROSS APPLY GetElements(insightsMetrics.records) as flattened),
RAWDATA
AS (SELECT
resourceId,time,
SUM(CASE metricName WHEN 'RunsStarted' THEN count ELSE 0 END) 'RunsStarted',
SUM(CASE metricName WHEN 'RunsCompleted' THEN count ELSE 0 END) 'RunsCompleted',
SUM(CASE metricName WHEN 'RunsSucceeded' THEN count ELSE 0 END) 'RunsSucceded',
(SUM(CASE metricName WHEN 'RunsCompleted' THEN count ELSE 0 END) - SUM(CASE metricName WHEN 'RunsSucceeded' THEN count ELSE 0 END)) 'RunsFailed',
SUM(CASE metricName WHEN 'ActionsStarted' THEN total ELSE 0 END) 'ActionsStarted',
SUM(CASE metricName WHEN 'ActionsCompleted' THEN total ELSE 0 END) 'ActionsCompleted',
SUM(CASE metricName WHEN 'ActionsSucceeded' THEN count ELSE 0 END) 'ActionsSucceeded',
SUM(CASE metricName WHEN 'ActionLatency' THEN average ELSE 0 END) 'AvgActionLatency'
FROM STEP1
GROUP BY resourceid, time, TumblingWindow(MINUTE,1))
SELECT *
INTO InsightsPowerBi
FROM RAWDATA

Create dashboard in Power Bi

Shortly following the activation of the previous configuration, a streaming data set should be appearing in the Power Bi account.

powerbi streaming dataset

To select the information coming from Stream Analytics, add the title “Custom Streaming Data” as a source in your dashboard.

powerbi add custom streaming data

Next, select the streaming data set.

powerbi add custom streaming dataset

Configure the chart properties. In this particular case, I am interested to see the completed runs from the last hours in a bar chart.

powerbi clustered bar chart

Enjoy your freedom!

Compared to the effort required to create a custom solution, this way of working takes but a couple of minutes to set up a technical scalable real-time dashboard. Amazing!

If you are looking for a more technical solution, integrated with other Azure diagnostics tools, I’d recommend checking out OMS workspace.

This example gives you a taste of how you can integrate Azure Monitor metrics with Power Bi by using the components Event Hub and Stream Analytics. Additionally, you can make the information available as a source if you enable historical data. This makes the full-blown functionality of Power Bi to create any type of reports on data sets possible!

I noticed that Microsoft is building extensively on Power Bi streaming features. Therefore, I hope to soon see more chart types and settings that we can change to customize a real-time dashboard.

Stay tuned!