------- OmnIoT SoftHub to Microsoft Azure Power BI Dashboard -------
This is the Microsoft Azure Power BI specific guide that builds on the base example found HERE. As mentioned in the base example, we assume you are familiar with the OmnIoT SoftHub and Power BI platforms, and have an understanding of MQTT in general. In this guide we'll modify the base example to publish our packet data to a cloud based Azure Power BI dashboard. We'll also illustrate how to build the full set of Azure IoT connection parameters required to connect to an Azure IoT Hub. Like the base example, the video below is done essentially in "real time". As you will see, to modify our base example to graphically display our sensor data on a Power BI dashboard takes only a few minutes from start to finish.
------- EXAMPLE VIDEO -------
------- VIDEO RECAP -------
To visualize our sensor data on our Power BI dashboard, we only needed to change the Publish Data action object. Microsoft's Azure IoT Hub provides multiple options for specifying your MQTT connection parameters. In our example we illustrated how to create a fully encrypted SSL MQTT connection. To create our connection we needed to specify the Message Topic, Server User Name, Server Client ID, Server Address, Trusted Store File, and the Server Password fields in for Publish Data object. Referencing the Microsoft's "IoT Hub MQTT support" webpage (link below), we can create the required Message Topic, Server User Name, Server Client ID, and Server Address fields by using the following set of template strings -
Message Topic: devices/<<HubDevId>>/messages/events/
Server User Name: <<IotHubName>>.azure-devices.net/<<HubDevId>>/?api-version=2018-06-30
Server Client ID: <<HubDevId>>
Server Address: ssl://<<IotHubName>>.azure-devices.net:8883
where you will need to replace each occurrence of <<IoTHubName>> with your target Azure IoT Hub's Name string, and each occurrence of <<HubDevId>> with the IoT Hub Device Id you've created as a sub-component of the associated Azure IoT Hub.
For the Trusted Store File you will need to download a copy of the current DigiCert Baltimore Root certificate and place it in a directory on your Raspberry Pi. You will then need to provide the full path and filename of the file you've downloaded as the Trusted Store File value. To verify the certificate file contents reference the c program file linked below.
Lastly, you will need to create a Microsoft Signed Access Signature (SAS) token you will need to provide via the Server Password field. There are multiple ways to generate these tokens however in the video we downloaded and ran the Azure IoT Hub "Device Explorer" app (link below). Note that when using this app your SAS tokens will have a maximum "Time To Live" of one year, and thus will no longer be valid to connect beyond that date (and so would need to be updated).
In the video we also looked at a sample JSON packet that the SoftHub generated and we captured in the base example -
--- BEGIN PACKET ---
{
"OmnIotHubPacket": {
"OriginDeviceId": 555,
"PacketGeneratedTimestamp": "2019-08-06T23:47:56.769023Z",
"SequenceNumber": 21,
"PacketType": "Raw BLE",
"PacketContents": {
"SensorType": "TI CC2650 SensorTag",
"SensorMacAddress": "42303A42343A",
"PacketDataField": [{
"FieldName": "TI HDC1000 Ambient Celsius",
"FieldValue": 26.537781,
"FieldValueHex": "00000000AC893A40"
}, {
"FieldName": "TI HDC1000 Relative Humidity",
"FieldValue": 69.671631,
"FieldValueHex": "00000000FC6A5140"
}]
}
}
}
--- END PACKET ---
and from that packet we derive the Azure IoT Analytics job query required to extract the data suitably for input to the Power BI dashboard -
--- BEGIN QUERY ---
SELECT
CAST(GetArrayElement(OmnIotHubPacket.PacketContents.PacketDataField, 0).FieldValue AS Float) As Temperature,
CAST(GetArrayElement(OmnIotHubPacket.PacketContents.PacketDataField, 1).FieldValue AS Float) As Humidity,
CAST(OmnIotHubPacket.PacketGeneratedTimestamp AS datetime) AS timestamp
INTO
AzureDemo
FROM
OmnIoTStreamInput Timestamp by OmnIotHubPacket.PacketGeneratedTimestamp
--- END QUERY ---
This query extracts three values, Temperature, Humidity, and a timestamp from the designated input stream, "OmnIoTStreamInput", and writes them to a streaming output we'd created named "AzureDemo". The AzureDemo input stream is then in turn referenced in our Power BI dashboard as input to our dashboard widgets.
See Microsoft's "IoT Hub MQTT support" webpage, which details Azure's MQTT interface HERE.
See Microsoft's github page referencing the current DigiCert Baltimore Root certificate HERE.
Read about Microsoft's Azure IoT Hub "Device Explorer" app HERE.
You can download the files used in the example video HERE.
------- SUMMARY -------
In this video we made all the required changes to get our data displaying on a Microsoft Azure Power BI dashboard in just a couple of minutes.