StreamAnalytics

Stream Analytics Lab - Send data to Power BI

Prerequisites: Power BI account

In this lab, we are going to create an Azure Stream Analytics job that will take the telemetry data from the Raspberry Pi and feed it to PowerBI as the output. From PowerBI, we will use the user friendly interface to drag and drop values onto a BI canvas and create some simple reports so that we can visualize the data coming into Azure from our device.

Stop the device simulators

1. Log into the Azure portal

2. Go to the "App Services" blade

3. Click on the App services associated with your preconfigured solution

4. Go To the "WebJobs" blade

5. Right click on the "DeviceSimulator-Webjob", and click "Stop"

   

Create Azure Stream Analytics (ASA) Job

1. Log into the Azure portal
2. Add an Azure Stream Analytics (ASA) Job

• Click on "+ New"

  

• In the "Search the marketplace" file, type in "Stream Analytics". Click on the "Stream Analytics job" option that shows up.

  

  1. Click on the "Stream Analytics job" that shows up in the results. Click "Create".

     

  2. Enter a name for your job. eg. "HandsOnLab-PowerBI"

  3. Choose your subscription.

  4. Choose a Resource Group. Use the existing Resource Group that was created previously. This will make it easier to delete all the resources when you are done with the lab.

• Choose a Location. eg. West US

• Click "Create". Feel free to click the "Pin to dashboard" check box. This will add the newly created ASA service to the main Azure portal dashboard.

  

• Wait for the job to be created. You will see a notification banner that will pop up in the top right corner of the Azure portal to indicate the status of the job. This banner will disappear automatically. If you wish to see all the past notifications, click the bell icon.

  

• Next, you will add an Input for the Stream Analytics job.

  1. If you pinned the ASA service to the dashboard, you will see the ASA tile on the main Azure portal page. Click it.

     

     If not, click "Resource Groups" -> Your resource group name -> Your ASA name

     

  • Under the "Job Topology" category, click on "Inputs".

  • Click "+ Add".

    

  • In the "New Input" blade that appears, fill in the fields:

    • Alias: Free form text name for the input. eg. "IoTHub"
    • Source Type: Data Stream
    • Source: IoT Hub
    • Subscription: Use IoT Hub from current subscription
    • IoT Hub: Choose the IoT Hub you have been using for the lab
    • Endpoint: Messaging
    • Shared Access Policy Name: iothubowner
    • Consumer Group: asa (we created this earlier)
    • Event Serialization Format: JSON
    • Encoding: UTF-8
    • Click "Create" and wait for the input to be created.

    

• Next, add an Output for the Stream Analytics job.

  1. Under the "Job Topology" category, click on "Outputs".

     

  2. Click "+ Add" in the blade to the right

  3. Fill out the values in the "New Output" blade.

  • Enter in any free form text for the "Output alias". eg. "PowerBI"
  • For the Sink, select "Power BI".
  • Click "Authorize".

  

  • A new window will open requiring credentials to authorize the connection to PowerBI. Enter your credentials and click "Sign in".

  

  • Enter a Dataset name. A dataset is a collection of data tables. eg. Raspberry Pi Dataset
  • Enter a Table Name. eg. Raspberry Pi Data Table
  • Click "Create"

  

  1. Wait for the input and output to be created. Check the Notifications in the portal for a successful connection test.

• Create an ASA Query.

  1. Under the "Job Topology" category, click on "Query". The inline query editing tool will already have some stub code inserted. You will make some modifications to the query.
  2. Enter the following query:

  SELECT * INTO
  [PowerBI] FROM [IoTHub]

  1. Click "Save".

  2. Click "Test"

     

• Start the ASA Job

  1. Click on "Overview"

  2. Click "Start"

     

  3. For the "Job output start time", click "Now"

  4. Click "Start"

     

View Data in Power BI

1. Open Power BI in a web browser - https://powerbi.microsoft.com

2. Sign in

3. Go to the bottom of the bar on the left. Expand "My Workspace" and select the dataset that you configured in Azure Stream Analytics (eg. Raspberry Pi Dataset)

   

4. In the "Fields" bar (far right), select EventEnqueuedUtcTime and Temperature

   

5. Select "Line Chart" from the visualizations. You now see your Pi data in a line chart

   

6. Save your report.

7. Click on the "Pin Visual" button on the chart

   

8. Click on "New Dashboard"

9. Type in a name for your dashboard. eg. "Raspberry Dashboard"

10. Your trend is now viewable as a dashboard. You can also view the dashboard from a mobile app

Back to Main HOL Instructions