Sassenheim, 6 June 2019
In the PowerBI service you can define a streaming dataset. With this dataset you can show real time data on a dashboard and create reports based on this real time dataset. But how can you use this if you don’t have a streaming datasource? The easy answer is to create one. But then, how do you get data from a static database to stream to this PowerBI dataset? This is where Microsoft Flow comes into play! In a few easy steps you are able bring this static data to life, also known as ‘data in rest to data in motion’.
Before going into Flow you first need to define a streaming dataset in the Power BI service. View the first few minutes of this Guy in a Cube video on how to do this.
Microsoft flow allows you to connect to several source and exchange data between them. It all start with a trigger (something happens), normally an event that occurs, but it can also be a schedules recurrence (start every x sec/min/hours). You can use a recurrence of every 60 seconds (you need a Pro licence for this or else you are limited to every 5 minutes). Following the trigger you can define several actions. In this case you need to select data and push it to a streaming data set.
There is one more thing you need to consider. You don’t want to select all data everytime the flow starts, you only want to select the data which was added after the last records processed. To do this You need to save the datetime of the last processed record. You need to have a datetime column to select data and save the last processed datetime to be able to make this work, a date column won’t work (a workaround could be to use an identity column, but I prefer a datetime column). Every time the flow start you read this LastProcessedDate and select only the data which has a higher order datetime. You can add this setting to a table in a database but you can also use a file on a filesystem.
In short the workflow will be:
- Start every 60 seconds
- Select last processed datetime
- Select all orders after last processed datetime
- Push every selected record to the Power BI streaming dataset (flow will create the apply to each flow automatically)
- Save the datetime of the last processed record
The steps of the flow would look something like this:
Microsoft Flow allows you to easily bring data in rest into motion again and visualize this in a report or dashboard. This is very usefull when a real stream of data is not available and where some delays in showing the data are not a problem.