Build Real-Time Dashboard in Power BI
Whether it’s a machine learning project or an IoT technical demo, being able to analyse live data (or events as they occur) is becoming increasingly popular.
We at Agile BI has been a pioneer in applying cutting-edge real-time technologies in commercial scenarios. Today, we will show you three different approaches we have used in the past to construct a Real-Time dashboard Power BI.
We will also contrast the pros and cons of each approach so that you have a better idea to which one potentially suits your need best.
(1) Full Cloud Implementation
In this scenario, we first replicate transaction data from our on-prem database to Azure SQL. We developed a Cloud Service application (hosted on an Azure VM) to poll data from Azure SQL every second, and then convert it into JSON blobs for message processing by Event Hub. Lastly, we use Azure Stream Analytics to read Event Hub messages and transform them into a desirable shape that suits our analytical need. All these happen in real-time, or near real-time if we argue the data is polled once per second.
This approach heavily relies on Microsoft’s Azure services. The biggest merit is its fast deployment and low initial set up cost. Such implementation is highly scalable and transferrable. With a few configuration you can have the service up and running in a matter of hours. All you need is “plug-in” the source data to Azure, and it will take care of ‘broadcasting’ them to your Power BI dashboard live.
- Higher running cost – The operation cost of the solution may be higher compared to other means of real-time solutions. After all, every component spinning in Azure blades cost a fortune. From our experience, one can expect a bill of a hundred dollar to a few thousands per month for running the service 24 by 7.
- Data sovereignty – Unless you have a localised data centre, posting your data to the Cloud may or may not imply a legal concern.
(2) Event Hub + Stream Analytics
Your application is often the one that generates or captures data. If the application is capable of communicating with external network and you have control over how it behaves, you may be able to program it to fire data directly at Event Hub. This allows us to bypass the need to ‘stage’ data on Cloud (as seen in scenario 1 above). Reference this article for more detail about sending data (they call it ‘event’) to Event Hub.
In the sample setup, we developed a console program that captures transaction data, and send it off to an Event Hub instance in parallel to writing into a local database for permanent storage. Once data hits Event Hub, the rest is same – i.e. ASA takes care of processing incoming messages and convey results to our Power BI dashboard in real-time.
- This approach has less dependency on Cloud resources and therefore is much more cost efficient during production.
- It eliminates time-lag incurred by polling data from ‘staging’ into Event Hub, therefore realising a truly real-time solution.
- This approach may not scale well – It requires custom coding in your application and this may not always be an option. Even if it is feasible, one must be careful that development cost does not outweigh the benefit of cost savings from comparing to scenario 1.
- It is less flexible – If you wish to introduce any custom logic to process your stream data, you will need to do so in the application. This is a much less flexible option compared to scenario 1.
(3) Power BI REST API
Lastly, Power BI offers REST API for controlling and managing streamed datasets. It allows pushing data directly into Power BI and build visualisations from it. You will need to register your application with Azure AD, get access token and then use it to authenticate your stream data inputs. Read here for more detail about using Power BI REST API.
In the sample set up, we used Power BI REST API to wrap data in JSON strings and push them into Power BI stream dataset. Despite it is fairly straightforward to develop, we found only a handful of visualisations currently support stream dataset. At the time of writing, the following visualisations are available :
- Column Chart
- Line Chart
- Gauge Chart
You also have no control over the streamed data once they hit Power BI. You must pre-process your data and complete transformation at source level. Often this is not a very ideal place to carry out stream data processing logic.
- This approch is most cost effective. There are no cost associated with using Power BI REST API at this stage.
- As REST API is fairly common for streaming data nowadays, you may find development cost very affordable.
- You will be constrained to what is offered by Power BI. You have very limited control over how to handle the streamed data.
- We experienced a number of cases where a functioning stream dataset became invalid after a Power BI update. There are risks to be bore when using Power BI REST API as it continue to undergo critical updates. I’m not saying this is bad though : ) !
Want to Know More ?
Microsoft Azure and Power BI offers a range of technologies for building real-time analytics solution. Depending on your specific envrionment and needs, there are different architectures available to maximise your return of investment.
Talk to us to find out more about building such solutions in a commercial application.