locked
From the IoT Hub to the Database RRS feed

  • Question

  • I have an experimental IoT Hub running with an device placing events on the the event hub.

    I've implemented a StoreEventProcessor class to consume the messages, filter the informations and place the data in various tables in a Azure SQL database.  Everything runs fine from my development machine.  What I don't understand yet is how to get the StoreEventProcessor class running automatically in Azure together with the Iot Hub?

    I had a look at Azure Stream Analytics and will use it for alerts but what technology should I use for data sorting, filtering and storage?

    Looking forward to any suggestions

    Friday, December 11, 2015 6:06 AM

Answers

  • Take a look at the Azure IoT Suite remote monitoring preconfigured solution. You can find the docs here: https://azure.microsoft.com/solutions/iot-suite/#documentation. And you can find the source here: https://github.com/Azure/azure-iot-remote-monitoring.

    This solution illustrates a complete end-to-end solution deployed to Azure that uses an IoT hub.


    Dominic Betts

    Friday, December 11, 2015 7:10 AM
  • Hi EmbeddedGuyDan

    Re Storing and filtering and only really displaying the data you need have you looked at Power BI?

    Power BI is a great way to use the information received from the connected device/s is to get near real-time analysis using the Microsoft Power BI tool.

    Setting up a Power BI account

    If you don't have a Power BI account already, you will need to create one (a free account is enough to complete this lab). If you already have an account set you can skip this step.

    1. Go to the Power BI website and follow the sign-up process.

    2. You will be asked to enter your email address. Then a confirmation email will be sent. After following the confirmation link, a form to enter your personal information will be displayed. Complete the form and click Start.

      The preparation of your account will take several minutes, and when it's ready you will see an screen similar to the following:

      Power BI Welcome screen

      Power BI welcome screen

    Now that your account is set, you are ready to set up the data source that will feed the Power BI dashboard.

    Create a Service Bus Consumer Group

    In order to allow several consumer applications to read data from the Event Hub independently at their own pace a Consumer Group must be configured for each one. If all of the consumer applications (the Console application, Stream Analytics / Power BI, the Web site you will configure in the next section) read the data from the default consumer group, one application will block the others.

    To create a new Consumer Group for the Event Hub that will be used by the Stream Analytics job you are about to configure, follow these steps:

    • Open the Azure Management Portal, and select Service Bus
    • Select the Namespace you used for your solution
    • From the top menu, select Event Hubs
    • From the left menu, select the Event Hub
    • From the top menu, select Consumer Groups
    • Select "+" Create at the bottom to create a new Consumer Group
    • Give it the name "CG4PBI" and click OK

    Create Consumer Group

    Setting the data source

    In order to feed the Power BI reports with the information gathered by the hats and to get that information in near real-time, Power BI supports Azure Stream Analytics outputs as data source. The following section will show how to configure the Stream Analytics job created in the Setup section to take the input from the Event Hub and push that summarized information to Power BI.

    Stream Analytics Input Setup

    Before the information can be delivered to Power BI, it must be processed by a Stream Analytics Job. To do so, an input for that job must be provided. As the Raspberry devices are sending information to an Event Hub, it will be set as the input for the job.

    1. Go to the Azure management portal and select the Stream Analytics service. There you will find the Stream Analytics job created during the Azure services setup. Click on the job to enter the Stream Analytics configuration screen.

      Stream Analytics configuration

      Stream Analytics Configuration

    2. As you can see, the Start button is disabled since the job is not configured yet. To set the job input click on the INPUTS tab and then in the Add an input button.

    3. In the Add an input to your job popup, select the Data Stream option and click Next. In the following step, select the option Event Hub and click Next. Lastly, in the Event Hub Settings screen, provide the following information:

      • Input Alias: TelemetryHub
      • Subscription: Use Event Hub from Current Subscription (you can use an Event Hub from another subscription too by selecting the other option)
      • Choose a Namespace: Windows10IoT-ns (or the namespace name selected during the Event Hub creation)
      • Choose an Event Hub: Windows10IoT (or the name used during the Event Hub creation)
      • Event Hub Policy Name: RootManageSharedAccessKey
      • Choose a Consumer Group: cg4pbi

      Stream Analytics Input configuration

      Stream Analytics Input Configuration

    4. Click Next, and then Complete (leave the Serialization settings as they are).

    Stream Analytics Output Setup

    The output of the Stream Analytics job will be Power BI.

    1. To set up the output, go to the Stream Analytics Job's OUTPUTS tab, and click the ADD AN INPUT link.

    2. In the Add an output to your job popup, select the POWER BI option and the click the Next button.

    3. In the following screen you will setup the credentials of your Power BI account in order to allow the job to connect and send data to it. Click the Authorize Now link.

      Stream Analytics Output configuration

      Stream Analytics Output Configuration

      You will be redirected to the Microsoft login page.

    4. Enter your Power BI account email and click Continue, then select your account type (Work, School account, or Microsoft account) and then enter your password. If the authorization is successful, you will be redirected back to the Microsoft Power BI Settings screen.

    5. In this screen you will enter the following information:

      • Output Alias: PowerBI
      • Dataset Name: Raspberry
      • Table Name: Telemetry
      • Group Name: My Workspace

      Power BI Settings

      Power BI Settings

    6. Click the checkmark button to create the output.

    Stream Analytics Query configuration

    Now that the job's inputs and outputs are already configured, the Stream Analytics Job needs to know how to transform the input data into the output data source. To do so, you will create a new Query.

    1. Go to the Stream Analytics Job QUERY tab and replace the query with the following statement:

      SELECT
          displayname,
          location,
          guid,
          measurename,
          unitofmeasure,
          Max(timecreated) timecreated,
          Avg(value) AvgValue
      INTO
          [PowerBI]
      FROM
          [TelemetryHUB] TIMESTAMP by timecreated
      GROUP BY
          displayname, location, guid, measurename, unitofmeasure,
          TumblingWindow(Second, 10)

      The query takes the data from the input (using the alias defined when the input was created TelemetryHUB) and inserts into the output (PowerBI, the alias of the output) after grouping it using 10 seconds chunks.

    2. Click on the SAVE button and YES in the confirmation dialog.

    Starting the Stream Analytics Job

    Now that the job is configured, the START button is enabled. Click the button to start the job and then select the JOB START TIME option in the START OUTPUT popup. After clicking OK the job will be started.

    Once the job starts it creates the Power BI datasource associated with the given subscription.

    Setting up the Power BI dashboard

    1. Now that the datasource is created, go back to your Power BI session, and go to My Workspace by clicking the Power BI link.

      After some minutes of the job running you will see that the dataset that you configured as an output for the Job, is now displayed in the Power BI workspace Datasets section.

      Power BI new datasource

      Power BI: New Datasource

      Once the datasource becomes available you can start creating reports. To create a new Report click on the Raspberry datasource:

    2. Power BI Report Designer

      Power BI: Report Designer

      The Report designer will be opened showing the list of fields available for the selected datasource and the different visualizations supported by the tool.

    3. To create the Average Light by time report, select the following fields:

      • avgvalue
      • timecreated

      As you can see the avgvalue field is automatically set to the Value field and the timecreated is inserted as an axis. Now change the chart type to a Line Chart:

      Select Line Chart

      Selecting the Line Chart

    4. Then you will set a filter to show only the Light sensor data. To do so drag the measurename field to the Filters section and then select the Light value:

      Select Report FilterSelect Light sensor values

      Selecting the Report Filters

    5. Now the report is almost ready. Click the SAVE button and set Light by Time as the name for the report.

      Light by Time Report

      Light by Time Report

    6. Now you will create a new Dashboard, and pin this report to it. Click the plus sign (+) next to the Dashboards section to create a new dashboard. Set Raspberry Telemetry as the Title and press Enter. Now, go back to your report and click the pin icon to add the report to the recently created dashboard.

      Pin a Report to the Dashboard

      Pinning a Report to the Dashboard

    7. To create a second chart with the information of the average Temperature follow these steps:

      1. Click on the Raspberry datasource to create a new report.
      2. Select the avgvalue field
      3. Drag the measurename field to the filters section and select Temperature
      4. Now change the visualization to a gauge chart:

        Change Visualization to Gauge

        Gauge visualization

      5. Change the Value from Sum to Average

        Change Value to Average

        Change Value to Average

        Now the Report is ready:

        Gauge Report

        Gauge Report

      6. Save and then Pin it to the Dashboard.

    8. Following the same directions, create a Temperature report and add it to the dashboard.

    9. Lastly, edit the reports name in the dashboard by clicking the pencil icon next to each report.

      Edit Report Title

      Editing the Report Title

      After renaming both reports you will get a dashboard similar to the one in the following screenshot, which will be automatically refreshed as new data arrives.

      Final Power BI Dashboard

      Final Power BI Dashboard

    Hope you find this of use.

    • Proposed as answer by grauwolf Friday, March 31, 2017 6:03 AM
    • Marked as answer by EmbeddedGuyDan Friday, March 31, 2017 8:20 AM
    Monday, January 25, 2016 10:55 AM

All replies

  • Take a look at the Azure IoT Suite remote monitoring preconfigured solution. You can find the docs here: https://azure.microsoft.com/solutions/iot-suite/#documentation. And you can find the source here: https://github.com/Azure/azure-iot-remote-monitoring.

    This solution illustrates a complete end-to-end solution deployed to Azure that uses an IoT hub.


    Dominic Betts

    Friday, December 11, 2015 7:10 AM
  • Hi EmbeddedGuyDan

    Re Storing and filtering and only really displaying the data you need have you looked at Power BI?

    Power BI is a great way to use the information received from the connected device/s is to get near real-time analysis using the Microsoft Power BI tool.

    Setting up a Power BI account

    If you don't have a Power BI account already, you will need to create one (a free account is enough to complete this lab). If you already have an account set you can skip this step.

    1. Go to the Power BI website and follow the sign-up process.

    2. You will be asked to enter your email address. Then a confirmation email will be sent. After following the confirmation link, a form to enter your personal information will be displayed. Complete the form and click Start.

      The preparation of your account will take several minutes, and when it's ready you will see an screen similar to the following:

      Power BI Welcome screen

      Power BI welcome screen

    Now that your account is set, you are ready to set up the data source that will feed the Power BI dashboard.

    Create a Service Bus Consumer Group

    In order to allow several consumer applications to read data from the Event Hub independently at their own pace a Consumer Group must be configured for each one. If all of the consumer applications (the Console application, Stream Analytics / Power BI, the Web site you will configure in the next section) read the data from the default consumer group, one application will block the others.

    To create a new Consumer Group for the Event Hub that will be used by the Stream Analytics job you are about to configure, follow these steps:

    • Open the Azure Management Portal, and select Service Bus
    • Select the Namespace you used for your solution
    • From the top menu, select Event Hubs
    • From the left menu, select the Event Hub
    • From the top menu, select Consumer Groups
    • Select "+" Create at the bottom to create a new Consumer Group
    • Give it the name "CG4PBI" and click OK

    Create Consumer Group

    Setting the data source

    In order to feed the Power BI reports with the information gathered by the hats and to get that information in near real-time, Power BI supports Azure Stream Analytics outputs as data source. The following section will show how to configure the Stream Analytics job created in the Setup section to take the input from the Event Hub and push that summarized information to Power BI.

    Stream Analytics Input Setup

    Before the information can be delivered to Power BI, it must be processed by a Stream Analytics Job. To do so, an input for that job must be provided. As the Raspberry devices are sending information to an Event Hub, it will be set as the input for the job.

    1. Go to the Azure management portal and select the Stream Analytics service. There you will find the Stream Analytics job created during the Azure services setup. Click on the job to enter the Stream Analytics configuration screen.

      Stream Analytics configuration

      Stream Analytics Configuration

    2. As you can see, the Start button is disabled since the job is not configured yet. To set the job input click on the INPUTS tab and then in the Add an input button.

    3. In the Add an input to your job popup, select the Data Stream option and click Next. In the following step, select the option Event Hub and click Next. Lastly, in the Event Hub Settings screen, provide the following information:

      • Input Alias: TelemetryHub
      • Subscription: Use Event Hub from Current Subscription (you can use an Event Hub from another subscription too by selecting the other option)
      • Choose a Namespace: Windows10IoT-ns (or the namespace name selected during the Event Hub creation)
      • Choose an Event Hub: Windows10IoT (or the name used during the Event Hub creation)
      • Event Hub Policy Name: RootManageSharedAccessKey
      • Choose a Consumer Group: cg4pbi

      Stream Analytics Input configuration

      Stream Analytics Input Configuration

    4. Click Next, and then Complete (leave the Serialization settings as they are).

    Stream Analytics Output Setup

    The output of the Stream Analytics job will be Power BI.

    1. To set up the output, go to the Stream Analytics Job's OUTPUTS tab, and click the ADD AN INPUT link.

    2. In the Add an output to your job popup, select the POWER BI option and the click the Next button.

    3. In the following screen you will setup the credentials of your Power BI account in order to allow the job to connect and send data to it. Click the Authorize Now link.

      Stream Analytics Output configuration

      Stream Analytics Output Configuration

      You will be redirected to the Microsoft login page.

    4. Enter your Power BI account email and click Continue, then select your account type (Work, School account, or Microsoft account) and then enter your password. If the authorization is successful, you will be redirected back to the Microsoft Power BI Settings screen.

    5. In this screen you will enter the following information:

      • Output Alias: PowerBI
      • Dataset Name: Raspberry
      • Table Name: Telemetry
      • Group Name: My Workspace

      Power BI Settings

      Power BI Settings

    6. Click the checkmark button to create the output.

    Stream Analytics Query configuration

    Now that the job's inputs and outputs are already configured, the Stream Analytics Job needs to know how to transform the input data into the output data source. To do so, you will create a new Query.

    1. Go to the Stream Analytics Job QUERY tab and replace the query with the following statement:

      SELECT
          displayname,
          location,
          guid,
          measurename,
          unitofmeasure,
          Max(timecreated) timecreated,
          Avg(value) AvgValue
      INTO
          [PowerBI]
      FROM
          [TelemetryHUB] TIMESTAMP by timecreated
      GROUP BY
          displayname, location, guid, measurename, unitofmeasure,
          TumblingWindow(Second, 10)

      The query takes the data from the input (using the alias defined when the input was created TelemetryHUB) and inserts into the output (PowerBI, the alias of the output) after grouping it using 10 seconds chunks.

    2. Click on the SAVE button and YES in the confirmation dialog.

    Starting the Stream Analytics Job

    Now that the job is configured, the START button is enabled. Click the button to start the job and then select the JOB START TIME option in the START OUTPUT popup. After clicking OK the job will be started.

    Once the job starts it creates the Power BI datasource associated with the given subscription.

    Setting up the Power BI dashboard

    1. Now that the datasource is created, go back to your Power BI session, and go to My Workspace by clicking the Power BI link.

      After some minutes of the job running you will see that the dataset that you configured as an output for the Job, is now displayed in the Power BI workspace Datasets section.

      Power BI new datasource

      Power BI: New Datasource

      Once the datasource becomes available you can start creating reports. To create a new Report click on the Raspberry datasource:

    2. Power BI Report Designer

      Power BI: Report Designer

      The Report designer will be opened showing the list of fields available for the selected datasource and the different visualizations supported by the tool.

    3. To create the Average Light by time report, select the following fields:

      • avgvalue
      • timecreated

      As you can see the avgvalue field is automatically set to the Value field and the timecreated is inserted as an axis. Now change the chart type to a Line Chart:

      Select Line Chart

      Selecting the Line Chart

    4. Then you will set a filter to show only the Light sensor data. To do so drag the measurename field to the Filters section and then select the Light value:

      Select Report FilterSelect Light sensor values

      Selecting the Report Filters

    5. Now the report is almost ready. Click the SAVE button and set Light by Time as the name for the report.

      Light by Time Report

      Light by Time Report

    6. Now you will create a new Dashboard, and pin this report to it. Click the plus sign (+) next to the Dashboards section to create a new dashboard. Set Raspberry Telemetry as the Title and press Enter. Now, go back to your report and click the pin icon to add the report to the recently created dashboard.

      Pin a Report to the Dashboard

      Pinning a Report to the Dashboard

    7. To create a second chart with the information of the average Temperature follow these steps:

      1. Click on the Raspberry datasource to create a new report.
      2. Select the avgvalue field
      3. Drag the measurename field to the filters section and select Temperature
      4. Now change the visualization to a gauge chart:

        Change Visualization to Gauge

        Gauge visualization

      5. Change the Value from Sum to Average

        Change Value to Average

        Change Value to Average

        Now the Report is ready:

        Gauge Report

        Gauge Report

      6. Save and then Pin it to the Dashboard.

    8. Following the same directions, create a Temperature report and add it to the dashboard.

    9. Lastly, edit the reports name in the dashboard by clicking the pencil icon next to each report.

      Edit Report Title

      Editing the Report Title

      After renaming both reports you will get a dashboard similar to the one in the following screenshot, which will be automatically refreshed as new data arrives.

      Final Power BI Dashboard

      Final Power BI Dashboard

    Hope you find this of use.

    • Proposed as answer by grauwolf Friday, March 31, 2017 6:03 AM
    • Marked as answer by EmbeddedGuyDan Friday, March 31, 2017 8:20 AM
    Monday, January 25, 2016 10:55 AM
  • Thanks Lee, nice detailed suggestion. 

    I made this:

    Tuesday, January 26, 2016 9:33 AM
  • EmeddedGuyDan

    Excellent, PowerBI is pretty awesome, so did using PowerBI answer your question?

    If my suggested above helped please make it as an answer.

    Thanks

    Lee

    Tuesday, January 26, 2016 1:46 PM