Power BI streaming dataset example for lazy people

[wpvideo FBeZpgm3]If you don’t have much programming experience or you are lazy and you just want to see, what you can do with Power BI and streaming dataset,  this post is for you.

You will need a Linux installation with PHP.

Let’s get to the point – fast!

Create a streaming dataset in Power BI.

1. Log in to Power BI.
2. Choose your workspace.
3. Click Create icon on the top right corner and choose Streaming dataset.
4. Next choose API and click Next.
5. Give your dataset a name and start typing the values.  Make sure to tab on the historical data.
   
6. Copy and paste the Push URL and the contents of the Raw field to your clipboard.
   

You are all set.

Create an API REST call

1. Copy and paste the following PHP script.
2. You might want to randomise your own numbers
3. Save the file, E.g. yourscript.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 = “https://api.powerbi.com/beta/blaah”;

/* you can change the numbers to whatever */
$temp = rand(0,40);
$humi = rand(30,70);
$voc = rand(1,100);
$pressure = rand(337,1070);
$light = rand(1,5);

$arr = array(‘temperature’ => $temp, ‘humidity’ => $humi, ‘voc’ => $voc, ‘light’ => $light, ‘pressure’ => $pressure, ‘ts’ => date(“Y-m-d h:i:s”));

$json = json_encode($arr);

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

You are almost done.

Create a dashboard in Power BI

1. Click dashboards
2. Click the Create icon on the top right corner
3. Choose to create a dashboard
4. Name your dashboard
5. Next, choose to create a tile from the upper menu bar
6. Choose Custom Streaming Data
   
7. Choose the dataset you created few minutes ago, click Next
   
8. Continue creating a line chart
9. Choose temperature and Next
   
10. Give your tile a title
11. Click apply

That’s it for now.

At this point you should test your script. Run your script at shell by typing

php yourscript.php

At the same time, open your dashboard at the background. Now run your script many times. You should see, that the report is reacting to your input. Great!

Now you probably guessed the rest. We need to automate the script to run every 15 seconds so that we can “demonstrate” the real-time data in dashboard.  Also your fingers will probably be numb after few minutes of running the script, so we’ll use crontab.

Note! Crontab was not meant for running script more ofter than once in a minute. We assume that this script is quick to run, because if it takes longer than 15 seconds, your “timer” will not work as intended. Here you are going to run the script every 15 seconds.

Open your crontab by typing the following command:

crontab -e

Type the following into the file:

* * * * * /foo/bar/your_script.php
* * * * * sleep 15; /foo/bar/your_script.php
* * * * * sleep 30; /foo/bar/your_script.php
* * * * * sleep 45; /foo/bar/your_script.php

Now your report is updated with “realtime data”.

Now, go ahead and create more tiles. Have fun!

The report will have new data every 15 seconds. However, our data is pure crap. You should replace the randomised values with something reasonable.

When you are done, you should have something like this:

[wpvideo FBeZpgm3]