Few weeks ago, I’ve written how REST API can be used for real-time dashboard in Power BI. Another method for real-time dashboard is using Azure Stream Analytics. This can be combined with Azure IOT and Event Hub to get data from IOT devices. In this post I’ll explain to you all steps necessary to push output from a .NET application through Azure Event Hub, and Azure Stream Analytics to Power BI. You will see how to create a real-time dashboard with this method. If you want to learn more about Power BI; read Power BI online book; from Rookie to Rock Star.

Prerequisite

You need an Azure account for running sample of this post. Azure account can be created with a free trial 1 month of credit. To create an Azure Account go to Portal.Azure.com.

You also need Visual Studio 2015 professional or Community, because a .NET application will be used as the source of the data.

Architecture

Diagram below shows the architecture of this sample.

IoT Devices or Applications can pass their data to Azure Event Hub, and Azure Event hub can be used as an input to Azure Stream Analytics (which is a data streaming Azure service). Then Azure stream analytics can pass the data from input based on queries to outputs. If Power BI be used as an output then a dataset in Power BI will be generated that can be used for real-time dashboard.

As a result anytime a new data point from application or IoT device comes through Event hubs, and then Stream Analytics, Power BI dashboard will automatically update with new information.

Azure Event Hub

Azure Event Hub is the entry point of the data from applications. Start by creating an Event Hub by going to Portal.Azure.com

In Azure portal, create a New Event Hub from New, and then Data + Analytics

Event Hub needs a Service Bus, this first step here creates the namespace for service bus. Set a namespace in the new blade. for example PBIRadacad. choose a pricing tier. for this example you can choose Basic. Select a resource group (or create if you don’t have one), and set a location.

After creating you will be redirected to Event Hubs Namespace Blade. Your Event Hubs namespace can include multiple Event Hubs. Create your first Event Hub here, by Adding an Event Hub.

For the new Event Hub just set a name. like pbi for example. and leave other settings as is. You can change these settings if you want to change partitions, and number of days to retention.

After creation the Event Hub should be listed under PBIRadacad Event Hubs (or whatever name you set for the namespace).

For simplicity we keep all configuration and setting as is. So far, you have created the Event Hub which can be used as the data entry input point. Now Let’s create an Azure Stream Analytics Job.

Azure Stream Analytics

Stream Analytics is a data streaming technology under Azure list of services. With this technology you can pass data from input (such as Event Hub) to one or more outputs. Note that Stream Analytics is not a data storage. If you want the data to be stored you have to pass that to an storage output, such as Azure Blob Storage, or Azure SQL Database or Azure SQL Data Warehouse.

Create an Azure Stream Analytics Job from Azure portal under New, and Data + Analytics.

Set a Name, Resource Group, and Location for Stream Analytics Job.

After creating the job, you will be redirected to the job’s blade.

Job above named as DeviceTempratures, yours might be named something else.

Input from Event Hub

From Job Topology click on Inputs, and then Add an Input in the new blade. In the New Input blade, set a name. set Source to Event Hub, and choose the service bus namespace as the namespace you entered earlier in this post (PBIRadacad). and select the event hub as pbi.

Leave event serialization format as JSON and encoding as UTF-8. With setting above, now Stream Analytics reads the input from Event Hub.

Output to Power BI

Go to Stream Analytics main blade and create an Output this time. Set a name such as PBIOutput, and set Sink to Power BI. This means Stream Analytics will output to Power BI as a data set.

Then Click on Authorize to map the Power BI account that you want to use here. You will be prompted to login with your Power BI username and password. This account can be different from your Azure account.

After authorization, you will see list of all groups in the Power BI account, and you can choose where the data set will be published to. (by default it goes to My Workspace, but you can change it to other groups). If you want to learn more about groups in Power BI, read this post. Set a name for data set, for example StreamAnalyticsDS. Notice that if a data set with this name exists, data set will be overwritten. set a name for table, for example myTable. and then create the output.

After creating output and input you will see them both under Stream Analytics blade. but you can’t start the job yet. there is one step more.

Stream Analytics Query

Stream Analytics passes the data from input to output through query. Without a query you cannot start Stream Analytics job. For this example we use a very simple query which select everything from input and pass that to output. Click on Query item in the Stream Analytics main blade (as numbered 1 in above screenshot).

Change the default query to;

SELECT
    *
INTO
    [PBIOutput]
FROM
    [PBIEventHub]

Notice that the query select everything from the input. so you have to put your input name after FROM clause. and it will insert result into output. So you need to put your output name after INTO clause. if names doesn’t match you get failure error message at the time of starting the job.

Don’t test the query. Just Save it and Start the job. It will take few minutes for job to start. Wait and check if the job started successfully. Otherwise failure might be related to input, output, or the query. You have to see job in RUNNING state as below.

Push Data Application

As I mentioned earlier in this post, I’ll be using a .NET C# console application to pass the data to Event Hub. Let’s create the sample application by opening Visual Studio 2015 Professional. Create a New C# Console Application.

Name the application something, and when it is opened in Solution Explorer, go to Referencces, right click and select Manage NuGet Packages

Click on Browse, Search for Azure Service Bus, and install it.

After installation, open App.Config file and at the end of the file search for appSettings. The connection string to Azure Service Bus is in the value beneath this section.

The highlighted line should be changed to your service bus connection string. To find your service bus connection string go to Azure Portal, find the service bus (we’ve created it in Event Hub section) under All Resources.

Under RootManageSharedAccessKey you’ll find Primary Key;

Copy the value of primary key here and replace the value in app.Config file with it. Save the App.Config after change and close it.

Then right click on the references folder in solution explorer and add System.Configuration reference.

Now open Program.CS file and add these two namespaces using;

using System.Configuration;
using Microsoft.ServiceBus.Messaging;

Also add code below under Main method;

string ConnectionString = ConfigurationManager.AppSettings["Microsoft.ServiceBus.ConnectionString"];
            EventHubClient client = EventHubClient.CreateFromConnectionString(ConnectionString, "pbi");

            // Declare values that we're about to send
            Int32 unixTimestamp = (Int32)(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
            Random r = new Random();
            int currentValue = r.Next(0, 100);

            // Generate string in JSON format
            string postData = String.Format("[{{ \"ts\": {0}, \"value\":{1} }}]", unixTimestamp, currentValue);
            
            // Send the Message
            client.Send(new EventData(Encoding.UTF8.GetBytes(postData)));

            Console.WriteLine("Message sent. Press Enter to Continue.");
            Console.ReadLine();

First Line in the code reads the connection string from app.config. Second line create an instance of EventHubClient with the connection string. Note that in this line connection created to Event Hub with name “pbi”. if you named your Event Hub differently enter your own name.

Next three lines are creating random integer value with time stamp. and line with String.Format will generate a JSON formatted string. and Client.Send send the string in UTF-8 encoding to Service Bus (Event Hub).

Execution of this application will pass only one data point to Event Hub. For adding another entry you have to run it again. Now the application is ready to run. Let’s run it for the first time.

After the execution we expect Power BI to show something. Let’s see.

Power BI Real-time Dashboard

Login to Power BI service with the account you have used in the Stream Analytics Authorization section. In My Workspace (or any other group you’ve selected in Stream Analyitcs Output section) you’ll find the new data set. if you followed the naming I suggested it should be named StreamAnalyticsDS.

Open the data set and you’ll see the table under data set with two fields we’ve passed (ts, and value) plus other fields from Event Hub (EventEnqueuedUtcTime, EventProcessedUtcTime, and PartitionId).

As you can see the structure can be anything sent from Event Hub and Stream Analytics. Now create two simple visualizations. One Card Visualization with distinct count of ts. This will show number of times we send data point (execute the console application) to Event Hub.

Also a Column chart with TS at Axis, and Value as the Value of chart.

Save this report, and Pin these two visualizations into a new dashboard.

Now go to the dashboard. While dashboard is open execute the console application. after few seconds of execution and sending message you will see the dashboard shows result. Fantastic, You’ve build your first Azure Stream Analytics and Power BI real-time dashboard now.

Think Further

Real-time dashboard with Power BI, Azure Stream Analytics, and Event Hub open the path to many possible solutions. You can analyze Twitter data real-time. Or you can pass data from an IoT device with IoT Hub into a real-time dashboard. Come with ideas of what real-time dashboard with Stream Analytics can do? I like to hear your ideas.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.