Building Real Time Dashboards with Power BI - Streaming Data

Hey Folks,

So this blog is not exactly about Power BI but building real time Dashboards with them which can refresh in every 5 minutes ( this is the minimum time frame which can be put to refresh. Come-on this could be said real time).

So the problem with Power BI is that there are only 8 automatic refreshes (in Pro License) and max 48 refreshes ( in Premium License) of Data Sets that is allowed.

If you think that Dashboards & Reports can be refreshed by a browser refresh then its not the case.Until the Data Sets are refreshed, dashboards & reports will not be refreshed as they are build on top of these data sets.

Note: The term 'Automatic Refresh' means refreshing the data set by any other means than manual refresh ( by clicking the 'Refresh Now' link on Data set).

So In order to solve this issue Power BI REST API's can be used to do this but still pose the same issues of Maximum Refresh Limit on the account.

Now what's the permanent solution to this. I had a thought that if Power BI is built on a platform to do reporting & create such elegant dashboards then 'real-time' feature must be there.

And then I fund 'Streaming Data Sets' which worked wonder.In the below section I will explain step - by - step to create just dashboards which will refresh in real time. There are some prerequisites to it beforehand:

• Knowledge of Power Shell.
• Back-end Data Source ( in my case I used REST API's)
• A dashboard design which you want to create.

Now there are 2 parts to this. We can create 2 types of Streaming Data Sets:

a) Push Data Sets ( with Historical Data ) : In this one you have a table on which you can build any kind of Visual.Also called Hybrid Data Sets

b) Streaming Data Sets ( with Historical Data 'turned off'): There are limited no of Visualizations to it. Also called as 'Streaming' Data Sets.

Visualizations can be directly created on Dashboard by creating a Tile and selecting option 'Custom Streaming' and post to that selecting Data set and Visualization and so forth:

Lets start the process to create push data sets with Historical Data:

Step 1: Go to 'My Work space' & Create a Data Set with types of fields which can come in the visualization as follows:

Now after clicking 'Done' API details can be retrieved ( our interest will be in Power Shell code):

The above screen can also be displayed by clicking the info ( 'i') icon on Data Sets Tab.

Once the Data Set is created then it can be viewed under Data Sets tab under the work space:

Step 2: Now the report has to be created from this DataSet which is a normal process is to be done in Report Designer. Report can also be directly built on this DatSet by clicking the 'Create Report' icon

So Creating Report is already known and will not be explained during this article. The final report can contain any visualization and is displayed as follows:

Tip: As this is a Historical data and it keeps on piling up so the data from our last API call has to be rendered in the visual. Hence to do this we are passing Time Stamp value whenever we make the API call in our Power Shell script and have to perform the 'Visual Level Filter' on report by Top 1 of TimeStamp and take as Latest TimeStamp. This is the trick else all the historical data will be rendered in the Visual.

Step 3: Final step would be to call the back-end API to retrieve the data and push that data into the Power BI Service via the PowerShell Script. So open a PowerShell Editor ( google it !! Okay ....Open 'Run' and type 'powershell ise') and write the code as copied in Step 1.

There will be 2 parts to your powershell API calls:

1. Call the Back-end Data Service to retrieve data.
2. Call the PowerShell code of Power BI to push Data.( If there are many records then it has to be performed in a loop.But remember to keep the timestamp in a variable outside and pass all records in same timestamp only)

Your Final Powershell will look something like this:

Remember: Call all API's in one PowerShell file for the same Dashboard as all of the data will be refreshed at one instance.

Step 4: Now the data is flowing to & fro between the Back-end and your Power BI Service. However this is just on-demand on executing the Power Shell script. Our final trick would be to schedule this power Shell script for every 5 minutes.

This can be achieved by the following blog: Schedule PowerShell through Windows Task Scheduler

Hence our Dashboard will be refreshed in real time without any quick fixes to refresh the browser & overcoming the automatic refreshes limit of Power API.

Leave a feedback on below touch ups as how was this blog because "Learning never stops!!". Bye and take care.

Email: [email protected]

Buzz: +1-419-902-3590

WhatsApp: +91-9873300475