Near Real-Time Data Updates to Power BI Dashboard
This is a continuation of my previous article. In this article, I would like to give a little intro on how to create the data ingestion and reporting pipeline with Lamda Architecture for real-time data updates in a Power BI dashboard.
Real-time data updates are very essential in today’s marketplace, which lets us remotely access an aggregated view of the business or organization’s performance, across all locations.
Understand and obtain current and historical data on the business to make quick and confident decisions based on facts and data, to improve the performance of the business as a whole.
Use Case
Assume that we already have a Sales Dashboard that updates daily and the use case is to add a new page to the Sales Dashboard to show an hourly sales summary.
Below is the architecture that accomplishes that:
This article assumes that you are already familiar with the batch layer, so I am not covering that part here.
For the near real-time updates, we need to create a composite model by connecting the operational data store to the existing Power BI model. In this example, the ODS that I am using will get updated every 30 minutes.
So let’s get into the action
As explained in my previous article, enable the preview features, and make the current model a local model. Now click on the Get Data -> SQL Server ( Operational Data Store) to it.
Enter the Server and Database details and select the Data Connectivity Mode as ‘Direct Query’
Select the tables that you need from the list and click Load
Click on ‘Model->Manage Relationships’ and create a relationship with the existing dataset
We are almost there …
Now you should see the tables from the new sources in the composite model. Now the new dataset is available, let’s add a new page to the existing Sales Dashboard to show the Hourly Sales Summary.
For this example, I created a sample page from the new dataset for the hourly sales updates.
Now let’s make some changes in the Operational Data Store and see how the dashboard works.
I made chnages in the Sales Order table for product category ‘Touring Bikes’
update [SalesLT].[SalesOrderDetail] set unitprice = 70000 where [productid] = 954
let’s go back to our dashboard and see the update ….WAIT do we need to set a data refresh in Power BI Services for this?
The answer is NO because it’s a Direct Query to the ODS, we don’t need to set the data refresh in the Power BI portal.
let’s make another change in the ODS
update [SalesLT].[SalesOrderDetail] set unitprice = 400 where [ProductID] = 954
BOOM!!, the updates are so instantaneous and the end-users will get a nice view of real-time sales details with historical sales, which will empower them to perform a more accurate analysis of the sales data.
I hope you enjoyed reading this article, see you in my next article until then stay safe and have a happy new year
