Thursday, October 15, 2020

Reduce Data Latency in Power BI with Power Automate


Business Case:

Our client, the customer support team at a tech company, focuses on improving after-sales services across a wide array of products. The team keeps track of customer support data on a Power BI workspace that consists of 50-60 reports. The workspace consolidates data from various channels.

Previously, our client would manually monitor workspace data refresh. As report volume increased, it became challenging to align back-end jobs with front-end data refresh. Manually initiating data set refresh also increased overall refresh time.

Power BI offers scheduling capabilities that enable users to set a specific time for data refresh. The challenge our client faced was a mismatch between the scheduled refresh time and actual back-end refreshes. If the back end didn’t refresh in time for the Power BI scheduled refresh, the data presented in the report would be out of date. With multiple reports in the dashboard, a total data refresh overwhelms the Power BI server and slows page load time (PLT). In addition, our client struggled to track failures in data refresh.

Our client needed a solution that responded dynamically to back-end data refresh and allowed them to track refresh performance across numerous reports. As the size of the reporting solution grew overtime, the manual process for monitoring the Power BI dataset refresh and aligning it with the backend jobs became challenging. Additionally, these manual interventions added to the refresh cycle time.

Key Challenges:

  Align Power BI data refresh with back-end data refresh  
  Enable users to monitor Power BI data refreshes, including refresh failures, across multiple reports  
  Reduce the impact of parallel refresh on the Power BI server  

Our Solution:

We used Microsoft Power Automate flows to automatically trigger Power BI data refresh based on the status of back-end data. 

Figure 1: Solution Architecture

All back-end data is grouped by relevance and refreshed in parallel by group. We developed a list in SharePoint that establishes group refresh priority based on size, criticality, and service-level agreement (SLA).  

We used two Power Automate flows to implement our solution: 

1.  Back-end status: Checks the status of back-end data in the SQL database to ensure successful refresh.  
2.  Intermediary (back end to Power BI): Uses Power BI APIs to trigger a data refresh in Power BI once the first flow confirms a successful back-end refresh. This flow establishes the order of data set refreshes by pulling information from the SharePoint list. In the case of refresh failure, the Power Automate flow sends an alert to the development team. Once all refreshes are complete, the flow sends a status email with the most recent refresh dates for all data sets in the workspace.  

Business Outcomes:

Our Power Automate solution eliminated the delay caused by manual interventions in Power BI data refreshes. The solution reacts to event-based triggers rather than time-based triggers, meaning data is refreshed in Power BI as soon as it is refreshed in the back end.  

In addition, the solution’s automated email alerts mean our client’s development team can quickly respond to any refresh failures. Instead of having to search through the entire Power BI workspace for refresh errors and the latest refresh dates, the team automatically receives an email that consolidates all relevant information.  

By setting a precedence for data refresh based on group, we optimized Power BI’s service capacity and reduced refresh time. Our client’s customer support team can now quickly access and act on critical customer support insights.  

Highlights:

    Developed a Power Automate flow that automatically triggers Power BI data refresh based on the status of back-end data refresh
    Reduced data refresh time, enabling the customer support team to access critical insights in near real time
    Enabled easy tracking of data refresh times and failures