Connected Field Service to PowerBI in Real Time - Part 1
Who would have thought that 8 little lines would have made life this easy? As part of an update to PowerBI, you can now in real time push data to PowerBI from Azure Stream Analytics. And doing so takes just 8 lines in Azure Stream Analytics.
This is part one of two parts. The second part will cover taking the data and making a PowerBI report with the data. I have stolen both the code and concepts for this directly from my two friends, Murray Fife and Jack Bender.
It's really that simple. To make all of this work, here are the steps required to make this work.
Step One - Create Azure Stream Analytics job
- Select the Resource Group where Connected Field Service is installed.
- Click Add in the Resource Group
- Search for Azure Stream
- Select Stream Analytics job
- Click Create
- Give the Job Name of "IoT2PowerBI"
- In Resource Group, Select "Use existing" and select the Resource Group
- Click Create
Step Two - Create Input from IoT Hub
- Open your newly created Azure Stream Analytics job
- Click on Inputs
- Click on Add stream input
- Select IoT Hub
- In the Input Alias type "IoTStream"
- Select the IoT Hub inside the Resource Group you are using.
- Click Save
Step Three - Create Output to PowerBI
- Click on Outputs
- Click on Add
- Select PowerBI
- In Output Alias "PowerBI"
- BEFORE you select the Group Workspace, Dataset name or Table name, click Authorize.
- Click Authorize
- Enter your PowerBI Credentials
- Select your Group workspace "My Workspace" is fine if it only for you
- Give your Dataset a name "IoTDeviceData"
- Give your Table name a name "DeviceData"
- Click Save
Step Four - Add Query and Start Stream Analytics
- On the Azure Stream Analytics Screen, right above the query, click Edit query.
- Use the following Query:
SELECT
IoTHub.ConnectionDeviceId as Device,
IoTHub.EnqueuedTime as Time,
*
INTO
PowerBI
FROM
IoTStream
- Click Save
- Click Start on the Stream Analytics job. This may take a few moments.
That's it. You have now successfully started pushing the device data directly to PowerBI.
Currently, Power BI can be called roughly once per second. Streaming visuals support packets of 15 KB. Beyond that, streaming visuals fail (but push continues to work). Because of these limitations, Power BI lends itself most naturally to cases where Azure Stream Analytics does a significant data load reduction. We recommend using a Tumbling window or Hopping window to ensure that data push is at most one push per second, and that your query lands within the throughput requirements.