Create a real-time streaming dataset in Power BI using Microsoft Flow

Power BI allows real-time streaming. This feature makes it possible to update dashboards in real-time. In this blog, we explain how you can set up real-time streaming within your Power BI environment using Microsoft Flow.


Step 1: SQL Server Database

For this scenario, we created a dummy SQL Server Express table and script adding a new row to a table every 5 seconds. This will be used to simulate real-time changes to an SQL database:

SQL looping script

Step 2: Install an on-premises data gateway

In our scenario, the SQL server express is located on-premise. To make the data available to Microsoft Flow and Power BI, an on-premises data gateway is required:

On-premises data gateway named 'SQL Express Gateway'

Step 3: Create a Streaming Dataset in Power BI Service

Within Power BI Service, create a new streaming dataset and choose for the API option. We configured our streaming dataset as followed:

Configuration of the streaming dataset

If Historic data analysis is turned on, the streaming dataset automatically becomes both a streaming dataset and a push dataset. This allows you to stream real-time data while also performing historical analysis on the same dataset.


More information between the different datasets can be found here:


Step 4: Configure Microsoft Flow

After you configure the connection between your SQL Server using the on-premises data gateway you will be able to connect and configure an automated workflow within Microsoft Flow. We used the template 'Add rows to a dataset in Power BI when an item is created in SQL Server (Express)':

Standard SQL Server and Power BI Flow App

Note: A special Microsoft flow license is required to access on-premises data using on-premises data gateway.


We set-up the workflow as followed using the appropriate SQL table and Power BI naming:

It's a requirement to use table "RealTimeData" within your streaming dataset in Power BI Service. In Flow, a mapping was made between the column names in SQL and Power BI (ID for ID and current_date_time for current_date_time in the last two fields of the above Power BI box).


To finalise the configuration, test whether the flow is working:

Flow is running successfully!

(Optional): Limit number of flow runs

To limit the number or flow requests and stay below the maximum number or runs, the scheduler (Delay) tool could be used: This helps reducing the number of flow executions and hence the Microsoft Flow cost. This step needs to be added at the start of the flow.


Step 5: Create your real-time streaming dashboard and reports in Power BI Service

Play time! Now everything has been set-up, you can create steaming tile visuals & reports in Power BI Service dashboards to stream live data:

Historical analysis (left graph) & streamed value (right card)

Hope this helps! Any questions or comments please feel free to leave below, or alternatively you can contact us at info@reportsimple.com.au