Stream Analytics and Power BI Join Forces to Real-time Dashboard

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-09-12_19h56_38

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.

2016-09-12_14h47_57

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

2016-09-12_14h57_36

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.

2016-09-12_15h02_17After 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.

2016-09-12_15h05_39

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.

2016-09-12_15h07_55

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

2016-09-12_15h10_34

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.

2016-09-12_15h33_19

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

2016-09-12_15h34_45

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

2016-09-12_15h47_14

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.

2016-09-12_15h49_56

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.

2016-09-12_16h13_15

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.

2016-09-12_16h17_26

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.

2016-09-12_16h21_42

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;

2016-09-12_16h25_25

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.

2016-09-12_16h31_45

 

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.

2016-09-12_16h34_37

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

2016-09-12_16h36_07

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

2016-09-12_16h38_07

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.

2016-09-12_16h50_12

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.

2016-09-12_17h05_05

Under RootManageSharedAccessKey you’ll find Primary Key;

2016-09-12_19h23_11

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.

2016-09-12_19h26_25

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

2016-09-12_19h27_56

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

Also add code below under Main method;

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.

2016-09-12_19h38_31

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.

2016-09-12_19h46_30

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).

2016-09-12_19h47_57

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.

2016-09-12_19h50_43

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

2016-09-12_19h53_36

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

2016-09-12_19h55_16

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.

2016-09-12_19h56_38

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

13 Comments

  • Hello :)
    At first I wanted to mention that this is a great tutorial. Perfectly done!
    My Question is, if the dashboard is really updating in real-time (as in your Power BI Rest API example) or is it updating in an intervall like 15 minutes ?
    Because for me it does not update in live time

    Thanks man :)

    • Thanks Tino for your kind feedback.
      Yes, it is updating real-time exactly similar as REST API example. There will be a lag of few seconds because it takes time for application to send message to Event Hub, and then to Stream Analytics and then Power BI. I’ll say a lag of about 4, 5 seconds.

      Cheers,
      Reza

      • Sry man, but it is not working for me unfortunately …
        And I don’t know why. I tried to reproduce your example and wrote an endless loop, which is doing some requests, but it is not updating in live time. When I press F5 it updates correctly. Maybe you have some idea ?
        Thanks man

        • Hi Tino,

          You mean pressing F5 on browser will show you the new results, but dashboard doesn’t refresh by itself?
          It normally takes few seconds to refresh, have you waited a bit to see does it refresh?
          Is is a REPORT you are testing or DASHBOARD? Please note that report doesn’t refresh automatically, but dashboard does.

          Cheers,
          Reza

          • Hey,
            I noticed that dashboard only doing refreshes, not reports.
            I’ve waited more than 5 minutes and it does not refresh.
            Maybe this is a Power BI issue ?
            greets Tino

          • That is strange. I can look at the issue if it helps. but I do need access to your Azure portal and Power BI account then. If you want so, send me details to my email address as reza at radacad dot com.
            it is working as expected in my side. so I doubt it be Power BI issue.

            Cheers,
            Reza

  • Hi Reza,
    Great post. I’ve been using Power BI for a while and now I’m starting to test Stream Analytics for real time dashboarding. Rather than using Visual Studio (like you did in your post), I just deployed the Cortana Intelligence Solution available at this site, https://gallery.cortanaintelligence.com/Solution/Stream-Analysis-with-Azure-ML-1
    It basically creates all the necessary Event Hub, Stream Analytics, etc. automatically. This works perfectly for me because I am not a developer.
    I was able to push Stream Analytics to my Power BI and the dataset does show in My Workspace. However, I’m not able to have my dashboard update in real time. In your post, you mention that while the dashboard is open, execute the Console Application… what does this mean? Is it something I can turn on or execute in Azure? Webjob?

    • Hi P,
      In my example I’ve used a .NET console application to send messages to Azure Event hub. In the example you’ve got from Cortana Gallery there is probably another source which you need to run to see real-time results in Power BI. I cannot tell to you what to do about that, In the Cortana Gallery there should be some documentation about that example. If you want to follow my example you would need .NET application.
      Cheers
      Reza

  • While creating output for data stream and point to PowerBI why the create button is disabled. I am facing the issue where create button for stream job output is disabled.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">