Wednesday, July 10, 2019

Case Study: Improve Power BI Premium Report Performance and Eliminate Data Refresh Failures



Key Challenges

   Improve report performance.
   Eliminate data refresh failures.
   Improve architecture design.

Achieving More with Power BI Premium

Our client, an internationally recognized humanitarian organization, needed to improve report load times and data refreshes. One critical report was not loading quickly, and data refreshes were not working properly. We worked with our client’s IT department and business users to better utilize Power BI Premium capabilities.

Power BI Premium provides dedicated capacity, dedicated capacity nodes, and viewing access without the need for additional user licensing. Dedicated capacity allows users to avoid the published restrictions of Power BI. Dedicated capacity nodes ensure provisioned computing power is customized to client needs. Viewing access without Power BI user licenses allows business users to view customized reports without incurring additional costs. 

Improving Report Performance

To improve report load time, we first analyzed our client’s report. We identified the report elements responsible for the slow load time. We then provided our client with recommendations to improve report performance. First, we removed the dates from dimension date tables for which data was not present in fact tables. We also removed all unnecessary filter interactions. Finally, we turned off the auto date and time features. Turning off the auto date and time feature resulted in a 40% reduction in the database size. The database reduction significantly improved the report’s performance.

Eliminating Data Refresh Failures

To eliminate data refresh failures, we first investigated our client’s data refresh schedule. We discovered the data refresh failures were due to parallel refreshes and a high number of queries. To separate the parallel refreshes and improve data refresh times, we changed the refresh schedule.

Improving Architecture Design

As we investigated the data refresh failures, we also discovered the size of the database was partly responsible for the failures. Our client’s database was sizable, and refreshing the database required a substantial amount of our client’s capacity. With our client’s permission, we scaled out our client’s architecture, increasing capacity. We also improved gateway performance and provided gateway troubleshooting documents.

Understanding Power BI Premium

As the engagement progressed, our client turned to us for additional guidance about how they could better utilize Power BI Premium. To provide our client’s business users with a technical understanding of Power BI Premium, we provided a walk-through of the following capacity metrics:

   System Usage
   Refreshes
   Evictions
   Query Duration
   Query Waits
   Model Size

Our client’s improved understanding of capacity metrics enabled further learning opportunities. Following the walk-through, we explained how to use capacity metrics to move databases across capacities for load balancing. We also shared and explained capacity planning suggestions. To ensure our capacity plan suggestions met client needs, we analyzed our client’s Power BI audit logs. The audit logs allowed us to identify the reports accessed during peaks in capacity metrics. We then discussed the findings of the audit logs to ensure our client understood the significance of their usage patterns. The engagement ended when our client was confident they could maximize performance using the recommended capacity.

Concluding the Engagement

The Power BI Premium engagement was a great success. Through the engagement, our client reduced the page load time of a critical report and eliminated data refresh failures. More importantly, our client’s business users gained a technical understanding of Power BI Premium. The business users’ improved understanding of capacity metrics allowed them to approve Premium decisions with confidence.