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:
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:
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:
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)':
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:
(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:
Hope this helps! Any questions or comments please feel free to leave below, or alternatively you can contact us at info@reportsimple.com.au