Disruptive Technologies Disruptive Developers

Power BI

This example will demonstrate how to get temperature data into Power BI using Microsoft Flow and a Data Connector.

The example will demonstrate the use of :

Setup

This example will use Power BI to create a simple demo. Before you can follow the steps below you will need:

  • A working Power BI installation.
  • A working Microsoft Flow installation.
  • A project in Studio where the temperature sensors for your demo resides.

Please note that the solution presented in this guide should only be used for proof-of-concepts where the expected sensor traffic is less than 100 events every 60s, due to throttling limits in Microsoft’s PowerBI Connector.

For a more comprehensive evaluation of using PowerBI with our sensors, please consider using the Getting sensor data into a Microsoft Azure SQL database guide as a spring board to using a SQL database as the PowerBI database instead of setting up a Microsoft Flow.

Creating the streaming dataset in Power BI

Navigate to your desired Power BI Workspace and click the “Create” button in the top right corner and select “Streaming Dataset”. Note that you might have to press the “skip” button first, located at the bottom right corner, to get this view if this is the first time opening Power BI.

You will be provided with three choices, select the one called “API” and click “Next” on the bottom right.

Provide a name for your Dataset (e.g “DT-Temperature”) and provide the following values as “Values from stream” (the order is not important):

Check the checkbox “Historic data analysis” and click the yellow “Create” button.

You should now see a confirmation dialog saying our streaming dataset is created. Click “Done” to proceed.

Three datasets

Repeat the above steps to create one dataset called “DT-Touch”.

Then create one called “DT-ObjectPresent”.

Creating the flow in Microsoft Flow

Navigate to Microsoft Flow and click “My Flows” in the top left navigation bar.

Create a new blank flow by clicking the button “Create from blank” on the top right. Now you might be prompted by a page saying “Start with one of these popular triggers”, ignore this by clicking “Search hundreds of connectors and triggers” on the bottom center.

Search for “Request” in the center search-bar and select the Trigger named “Request - When an HTTP request is received”.

In the “Request Body JSON Schema” box, paste the following JSON example and click “Done”:

{
    "type": "object",
    "properties": {
        "event": {
            "type": "object",
            "properties": {
                "eventId": {
                    "type": "string"
                },
                "targetName": {
                    "type": "string"
                },
                "eventType": {
                    "type": "string"
                },
                "data": {
                    "type": "object",
                    "properties": {
                        "temperature": {
                            "type": "object",
                            "properties": {
                                "value": {
                                    "type": "integer"
                                },
                                "updateTime": {
                                    "type": "string"
                                }
                            }
                        },
                        "touch": {
                            "type": "object",
                            "properties": {
                                "updateTime": {
                                    "type": "string"
                                }
                            }
                        },
                        "objectPresent": {
                            "type": "object",
                            "properties": {
                                "state": {
                                    "type": "string"
                                },
                                "updateTime": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                },
                "timestamp": {
                    "type": "string"
                }
            }
        }
    }
}

Click the “Show advanced options” button on the bottom left and select “POST” in the “method” drop-down. Leave the field “relativePath” blank.

Add a new step by clicking the button “New step” on the bottom center, expand “More” and select “Add a switch case”.

Configure the new Switch statement to switch on “eventType”.

Adding each case

This section should be repeated three times, one for each type of event. The first “case” is already shown, to add the other two, simply press the “+”.

The three different “eventTypes” that we will want to switch on are “temperature”, “touch” and “objectPresent”

For each “case”, press “Add an action”, search for “Power BI” and select the Action called “Power BI - Add rows to a dataset”.

Then

  1. Select the Workspace where we created our Streaming Dataset in our previous step from the “Workspace” drop-down list.
  2. Select our Dataset in the “Dataset” drop-down. This will be different for each “case”. Select “DT-Temperature” for temperature events, “DT-Touch” for touch events and “DT-ObjectPresent” for object present events.
  3. In the “Table” drop-down you should see a table called “RealTimeData”, select this one.

You should now see 3-4 of the follow fields, depending on the dataset: device, temperature, state, eventId and createTime.

Map these to the fields from our incoming HTTP request by clicking in the input field and select the correct field so it matches this setup:

For “objectPresent” events, map “state” to “state”.

For “touch” events there isn’t any specific value data fields. Instead the touch itself (and the timestamp attached) is the event.

Click “Save” on the bottom right to save your flow.

If you scroll up and expand the original HTTP request action, you should now see the “HTTP POST URL” to be populated with an URL. Copy this as we will use this in our next step, creating the Data Connector.

Creating the Data Connector

Navigate to Studio and

  1. Open the project with your sensors that you wish to receive data from.
  2. Go to Data Connectors and click Add to create a new connector
  3. Paste in the URL that you copied from our last step and check uncheck the “Forward All Events” checkbox and check the ones named “Temperature”, “Touch” and “ObjectPresent”.

Now save your Data Connector and then touch one of your sensors.

Hit the “Refresh” button for the “Activity last 24 hours” section on the top. Your “Success” rate should now be at least 1 if everything is set up correctly.

While creating Power BI reports is outside of the scope of this example, the dataset will now be updated in real-time with new sensor updates.