Streaming sensor data to Power BI

Digitization, IoT and sensor data are currently on everyone’s lips. So in this post, I want to show you how to create your own small sensor and send its data to Power BI.

The components for the sensor are quite cheap. You get a WeMos D1 mini for less than 7€ and the ultrasound module US-100 for less than 4€.

Of course, you can also use any other ESP8266 / ESP32 board or ultrasound module (e.g., HC-SR04). However, the ultrasound module US-100 has the advantage that it can also be operated with 3.3V and has a temperature sensor.

Create a Power BI Streaming dataset

First, we need to create an endpoint for our sensor data in Power BI. To do this, create a Streaming dataset in your workspace.

Streaming dataset

As the source, select API and click Next.

Streaming dataset API

In the next step you name the streaming dataset and define the data structure. In this example, we will create a DateTime column DateTime, a Distance column of the Number data type, and a Temprature column of the Number data type.

define the data structure

After you choose Create, the streaming dataset is created. In the following dialog you will get the Push URL, which you can use to send data to the endpoint. Copy the URL for later use.

Push URL Streaming dataset

Assembly of the sensor

The connection between the US-100 ultrasound module and the WeMos D1 mini is as follows:

VCC - 3,3 / 5V
GND - GND
D1  - ECHO
D2  - TRIG
Fritzing wiring WeMos US-100

In reality, it should look like this.

wiring WeMos US-100

Then we configure the following Arduino sketch with SSID, PASSWORD and POWER BI API PUSH URL and upload it to the WeMos D1 mini.

#include <SoftwareSerial.h>;
#include <NTPClient.h>
#include <WiFiUdp.h>
#include <ESP8266WiFi.h>
#include <ESP8266WiFiMulti.h>
#include <ESP8266HTTPClient.h>
#include <WiFiClientSecureBearSSL.h>
// Fingerprint Power BI Certificate
const uint8_t fingerprint[20] = {0x23, 0x05, 0x3d, 0x63, 0x7d, 0x4c, 0x86, 0x66, 0x84, 0x17, 0x47, 0xe6, 0x37, 0x6b, 0xed, 0xbb, 0x23, 0x0b, 0x8d, 0xac};

WiFiUDP ntpUDP;
NTPClient timeClient(ntpUDP, "pool.ntp.org", 0, 3600000);

/**********************
 * SET WIFI CREDENTIALS
 **********************/
const char* ssid  = "<SSID>";
const char* password = "<PASSWORD>";

/***************************
 * SET Power BI API Push-URL
 ***************************/
const String pushURL = "<POWER BI API PUSH-URL>";

// defines pins numbers
const int US100_TX = 4;
const int US100_RX = 5;
 
SoftwareSerial US100Serial(US100_RX, US100_TX);
 
unsigned int MSByteDist = 0;
unsigned int LSByteDist = 0;
unsigned int mmDist = 0;
int temp = 0;

String formattedDate;
String jsonString;
 
void setup() {
    Serial.begin(9600);
    US100Serial.begin(9600);

    Serial.print("Connecting to ");
    Serial.println(ssid);
    WiFi.begin(ssid, password);

    while(WiFi.status() != WL_CONNECTED) {
      delay(500);
      Serial.print(".");
    }

    Serial.println("");
    Serial.println("WiFi connected.");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());
  
    Serial.println("Start time client...");
  
    timeClient.begin();
    timeClient.update();
}
 
void loop() {
 
    US100Serial.flush();
    US100Serial.write(0x55); 
 
    delay(500);
 
    if(US100Serial.available() >= 2) 
    {
        MSByteDist = US100Serial.read(); 
        LSByteDist = US100Serial.read();
        mmDist  = MSByteDist * 256 + LSByteDist; 
        if((mmDist > 1) &amp;&amp; (mmDist < 10000)) 
        {
            Serial.print("Distance: ");
            Serial.print(mmDist, DEC);
            Serial.println(" mm");
        }
    }
 
    US100Serial.flush(); 
    US100Serial.write(0x50); 
 
    delay(500);
    if(US100Serial.available() >= 1) 
    {
        temp = US100Serial.read();
        if((temp > 1) &amp;&amp; (temp < 130)) // temprature is in range
        {
            temp -= 45; // correct 45º offset
            Serial.print("Temp: ");
            Serial.print(temp, DEC);
            Serial.println(" ºC.");
        }
    }

    timeClient.update(); 
    formattedDate = timeClient.getFormattedDate();

    Serial.print("DateTime: ");
    Serial.println(formattedDate);    

    jsonString = "[{\"DateTime\": \"" + formattedDate + "\", \"Distance\": " + String(mmDist) + ", \"Temprature\": " + String(temp) + "}]";

    Serial.print("Json: ");
    Serial.println(jsonString);
    Serial.println();

    std::unique_ptr<BearSSL::WiFiClientSecure>client(new BearSSL::WiFiClientSecure);
    
    client->setFingerprint(fingerprint);
    
    HTTPClient https;
  
    Serial.print("[HTTPS] begin...\n");
    // configure traged server and url
    
    https.begin(*client, pushURL);
    https.addHeader("Content-Type", "application/json");
  
    Serial.print("[HTTPS] POST...\n");
    // start connection and send HTTP header
  
    int httpCode = https.POST(jsonString);
  
    // httpCode will be negative on error
    if (httpCode > 0) {
      // HTTP header has been send and Server response header has been handled
      Serial.printf("[HTTPS] POST... code: %d\n", httpCode);
  
      // file found at server
      if (httpCode == HTTP_CODE_OK) {
        String payload = https.getString();
        Serial.println(payload);
      }
    } else {
      Serial.printf("[HTTPS] POST... failed, error: %s\n", https.errorToString(httpCode).c_str());
      String payload = https.getString();
      Serial.println(payload);
    }
  
    https.end();
 
    delay(9000);
}

The “Fingerprint” of the Power BI API certificate can be read out via the lock symbol in the browser bar.

Fingerprint SSL Certificate

In the Arduino board management I loaded the package esp8266 in the version 2.5.0, because I have problems with the SoftwareSerial library in the newer versions.

Arduino board management

As an NTP client I use the custom library “NTP Client library forked by Taranais“. This provides a getFormattedDate() function, which is used to convey the DateTime value.

Creation Sensor Dashboard

Back in the Power BI service, you add a new tile to a new dashboard.

add dashboard title

As the source for the tile, select Custom Streaming Data.

Select your streaming dataset.

Select your streaming dataset.

Choose the visualization type for your streaming data.

Choose the visualization type

Finally, you can define additional Tile details.

Tile details

Now you should see how your data is streamed in Power BI.

Streaming dashboard

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.