DIY home monitoring system – part four

Check previous steps before continuing.

Power BI

Next, we need to pay a visit to Power BI portal. Sign up for Power BI, if you don’t have an account yet. Downloading and using Power BI is free. Also publishing reports to your private workspace is free, but when and if you decide to share a report, you need a license. For the purpose of this post, you don’t need one. Free account will serve you just fine.

Create a streaming data set in Power BI

1. Login to Power BI portal at app.powerbi.com
2. From the upper right corner click the plus sign next to “Create”.
3. Fill in the values from your ruuvi’s. As you can see, Microsoft has defined the structure for capturing data: the name of the parameter and the format of the value. That’s it.
4. One of my tags was named “3”, so to capture the values from my tag number three, I need to fill in the following values (see the pic). Continue this logic for filling in the rest of your tags.
5. Add one value for timestamp, value name “ts” and format “DateTime”.
6. Click Create.

Copy and paste the example JSON format and push URL

1. Copy the Push URL from the next view.
2. You may want to copy and paste the Raw JSON example too.
3. Click Done.

Creating a REST post call to Power BI

Let’s take a look at the database. For each tag you have three rows of data. However Power BI has only one acceptable structure: parameter and value. You have five values for temperature (or as many values as you have tags, for me, it’s five). We need to convert the parameters and their values to match to whatever Power BI is accepting. We need to differentiate the values based on the tag id, so temperature for tag named “3”, will became a parameter called “temperature_3”. You probably saw this coming when you created the streaming dataset in Power BI and saw the print screen earlier.

The following code will pick up the values from database and send them to Power BI. However, to be sure, that we are actually sending something, I am actually sending data that is two minutes old. Why? It might take some time for ruuvi’s to send the data via bluetooth, also it might take some time for Raspberry to pick up the data and send it to Power BI. If you pick too short timeframe, like real realtime, your report ends up showing some of the values, not all of them. The result is blinking report and you do not want that.

Save the following code E.g. sendtopowerbi.php.

<?php

function rest_post($url,$what,$format=”application/json”){

if($what==””){

die(“‘$what’ content is empty or non existent\n”);

}

if(is_array($what)) $what = json_encode($what);

$opts = array(

‘http’=>array(

‘method’ => ‘POST’,

‘header’=>”Content-Type: $format; charset=utf-8\r\n”,

‘content’ => $what,

‘timeout’ => 60

)

);

$context = stream_context_create($opts);

$result = file_get_contents($url,false,$context);

return json_decode($result,1);

}

$myUrl = “PUT_YOUR_URL_HERE”;

// mysql connection

$mysqli = new mysqli(“localhost”, “your_userid”, “your_password”, “your_db”);

if ($mysqli->connect_errno) {

printf(“Connect failed: %s\n”, $mysqli->connect_error);

exit();

}

$names = array(); // for storing the new parameter names

$values = array(); // for storing the parameter values

$the = array(); // for creating a json for pushing to pbi

$newTime = strtotime(“-2 minutes”); //pay attention here

$nyt = date(“Y-m-d H:i:s”, $newTime);

// how many values, 3 tags –> 9 values, 5 tags –> 15 values

$query = “SELECT id,tagid,sensor,sensorvalue,ts FROM ruuvidata WHERE ts > ‘” . $nyt . “‘ ORDER BY id DESC LIMIT 15”;

$result = $mysqli->query($query);

$i = 0;

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

if($i<15){

array_push($names,$row[‘sensor’] . “_” . $row[‘tagid’]);

array_push($values,$row[‘sensorvalue’]);

}

$i++;

}

mysqli_free_result($result);

// combine the parameters and their values

$lkm = count($names);

for($z=0;$z<$lkm;$z++){

$the[$names[$z]] = $values[$z];

}

$the[‘ts’] = date(“Y-m-d h:i:s”); //add a timestamp to be sent to PBI

$thejson = array(

$the

);

$thejson = json_encode($thejson);

var_dump($thejson);

$reply = rest_post($myUrl,$thejson,$format=”application/json”);

print_r($reply);

?>

Almost there. Now, whenever you run the python script, your index.php at http://localhost/data/ is picking up the data and storing it to both: a text file and mysql database. The php script above will pick up that data from mysql database and send it to Power BI. You can run the script:

sudo php sendtopowerbi.php

Now your data went through Power BI REST API and landed on your report. You still need to create the report (see next post). However, you don’t want to tire your fingers for long for trying to run both the python script and the php script. You can use crontab for running the scripts in the background. Type the following:

crontab -e

Then add the following lines there

* * * * * python3 /home/pi/yourfile.py > /home/pi/ruuvi.log 2> /home/pi/ruuvi.err

* * * * * php /var/www/html/data/sendtopbi.php

Now, you are running the scripts once in a minute.

Let’s go and create the final part – Power BI dashboard for your data.