Outputting data from an Azure Function to Power BI with PowerShell

Last week I wrote this post that detailed how to use the Azure Table Storage output binding in an Azure PowerShell Function. As part of the same solution I’m working on, I also need to get data/events into Power BI for reporting dashboards. An Azure Function (PowerShell) has the ability to obtain the data but the path to Power BI requires a number of steps that start with using the Azure Function Event Hub output binding.

The end to end process is;

• Azure Function obtains information from an API (Brewery DB)
  • processes the data and;
• Sends the data to an Azure Event Hub
• A Stream Analytics Job picks up the data and puts it into a Power BI Dataset.

This post will detail the process of configuring this process using the same Beer Styles example from the Azure Table Storage Azure Output Binding post here and PowerShell as the documentation doesn’t give a working example for PowerShell.

The inputs and Azure Function are the same. Just the output from the Azure Function to Azure Event Hub is added. You can have multiple output bindings, so this example will add the Event Hub output whilst keeping the Azure Table Service output as well.

Azure Event Hub

Before we can add and configure an Azure Event Hub Output Binding on an Azure Function we need to create an Azure Event Hub Namespace.

From the Azure Portal, create a resource (Event Hub).

Once the Event Hub Namespace has been created, we need to create an Event Hub. Select the Event Hub Namespace => Event Hubs => + Event Hub and give it a name.

Power BI

From the Azure Portal create a resource Power BI Embedded and create a Workspace if you don’t already have one.

Azure Stream Analytics

Now we need to create the Stream Analytics Job that will take the event data from the Event Hub and put it into a Power BI Dataset.

From the Azure Portal create a resource Stream Analytics Job. Give it a name and select create.

Once created select your Stream Analytics Job => Inputs => Add stream input => Event Hub. Provide a job Alias, select your Azure Subscription, the Event Hub Namespace and Event Hub created earlier and select Create.

Select Outputs from your Stream Analytics Job => + Add => Power BI => Authorize to authorise access to Power BI. Provide an output Alias, select your Group workspace and provide a Dataset name and Table name => Save.

Select Query and update the query to copy the input to the output. Select Save. If you weren’t filtering what you wanted into the Power BI Dataset on the Azure Function (or other input to the Event Hub) you could filter it with a query here.

Select the Job Overview and select Start => Now.

Azure Function App Event Hub Output

With all the wiring in place to get our data out to Power BI we can now configure our Azure Function App to output to our Event Hub.

Select your Azure Function => Integrate => Outputs => New Output => Event Hub => New Connection => Event Hub and select your Event Hub Namespace, Event Hub => Select.

Update the Event Hub name to the name of your Event Hub and select Save.

Selecting our Azure Function from the previous blog post adding in the following line will also copy our output object to our Event Hub Output Binding.

 $outputArray | convertTo-json | Out-File $outputEventHubMessage

Processing our Events

Executing our Azure Function sends the events to our Output Bindings.

We can see the progress through looking at our Event Hub Metrics and Stream Analytics Overview (spike in events as they are processed)

After a minute we can see the dataset has been created in Power BI.

We can then create a report from the dataset

using the data that was ingested based off our Azure Function.

Summary

Using the Azure Function Event Hub Output Binding in conjunction with Stream Analytics we can easily get data to Power BI for reporting and dashboards.