Monday, July 8, 2019

Case Study: Over 50% Faster Load Time with Power BI Premium Optimization



Key Challenges

   Optimize report design.
   Load test Power BI reports.
   Implement DAX optimizations.

Leveraging Power BI Premium

As part of our Power BI Premium engagements, we work with our clients’ IT departments to better leverage Power BI Premium capabilities. We optimize report design, provide modeling suggestions, implement DAX optimizations, review Premium configuration, review architecture design, and provide recommendations on capacity planning.

Reducing Page Load Times

Our client, a global provider of water, hygiene, and energy services, needed to improve their Power BI Premium performance. Before we optimized their report design, the default Any Measure view required 10 seconds to load. Drilldown also required significant amounts of time. Drilling down to the Any Measure subarea required 15 seconds. Drilling down to the Sold To view required one minute. Drilling down to the Program view required two and half minutes, and the Solution view required four and a half minutes.

To reduce page load times, we optimized our client’s report design. We removed redundant visuals from pages. We replaced the custom visuals with out-of-box visuals. We also implemented drill through and drilldown options. To eliminate unnecessary interactions between visuals, we removed interactions between slicers. We used bookmarks to hide visuals during the default load. Lastly, we used the New Filter Experience. New Filter Experience moved multi-select slicers to filters and enabled the apply filters option.

Our report design optimizations substantially reduced page load times. Following the optimizations, the Any Measure default view loaded instantly. Drilldown load times also improved. Overall, our report design optimizations reduced page load times by 57%.

Stress Testing Power BI Reports

Our Power BI Premium engagement did not end with report design optimizations. To improve data modeling, we removed the bidirectional filter. We also implemented DAX optimizations for variable caching and the calculate and if functions. Finally, we performed load testing using a PowerShell script and Visual Studio.

Load testing our client’s Power BI reports presented a unique challenge. Because powerbi.com is load balanced, we could not directly stress test our client’s Power BI reports. To evaluate how the reports would perform under stress, we load tested our client’s backend. We fired 1,500 queries at the backend server. The test provided our client with a general understanding of report performance.

Results of a Successful Engagement

Our Power BI Premium engagement resulted in multiple benefits for our client. Our report design optimizations reduced page load times by 57%. The backend load test demonstrated how Power BI reports performed under stress. The entire engagement provided our client’s IT department with further insight into the capabilities of Power BI Premium.