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:
![](https://static.wixstatic.com/media/ddae83_9f82883974954356877dcb43c380184f~mv2.png/v1/fill/w_71,h_22,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_9f82883974954356877dcb43c380184f~mv2.png)
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:
![](https://static.wixstatic.com/media/ddae83_5132b0a753c64ba3bf1580294862567c~mv2.png/v1/fill/w_77,h_62,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_5132b0a753c64ba3bf1580294862567c~mv2.png)
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:
![](https://static.wixstatic.com/media/ddae83_57b7e925236a412aaba86b97ea9cecc2~mv2.png/v1/fill/w_66,h_65,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_57b7e925236a412aaba86b97ea9cecc2~mv2.png)
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)':
![](https://static.wixstatic.com/media/ddae83_f9fbf9ccfd6244ba87c1070cba0bcb2b~mv2.png/v1/fill/w_49,h_27,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_f9fbf9ccfd6244ba87c1070cba0bcb2b~mv2.png)
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:
![](https://static.wixstatic.com/media/ddae83_9d1b0f0ae9fe4f6594072cb888f0a240~mv2.png/v1/fill/w_78,h_69,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_9d1b0f0ae9fe4f6594072cb888f0a240~mv2.png)
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:
![](https://static.wixstatic.com/media/ddae83_ddf5d61e9e4c4b21861b7b559fdf86ba~mv2.png/v1/fill/w_49,h_11,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_ddf5d61e9e4c4b21861b7b559fdf86ba~mv2.png)
(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.
![](https://static.wixstatic.com/media/ddae83_affd5d48f074432a8e51e92a43b57bfa~mv2.png/v1/fill/w_76,h_17,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_affd5d48f074432a8e51e92a43b57bfa~mv2.png)
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:
![](https://static.wixstatic.com/media/ddae83_ed8f05fa4e344eea80e18a7c1cd8a374~mv2.png/v1/fill/w_78,h_36,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/ddae83_ed8f05fa4e344eea80e18a7c1cd8a374~mv2.png)
Hope this helps! Any questions or comments please feel free to leave below, or alternatively you can contact us at info@reportsimple.com.au