Thursday, April 25, 2019

Case Study: Improving Power BI Premium Load Times with Custom Analysis Tools



Key Challenges

   Determine the cause of slow Power BI Premium load times.
   Create a list of steps to improve report load times.
   Educate the client on how to assess future Power BI Premium load time inefficiencies.

Power BI Premium Expertise

Fast report load times are essential for all businesses that use Power BI. Load times make the difference between a report that quickly delivers useful insights and a report that no one wants to use. Through our close relationship with the Power BI development team and our expertise in building enterprise-grade Power BI Premium implementations, we’ve developed deep technical knowledge of DAX and tabular models. This knowledge—along with multiple in-house tools we’ve created—enables us to evaluate Power BI implementations by pinpointing inefficiencies that hinder load times. We use our knowledge to provide recommendations to clients on how they can improve report performance.

Assessing Our Client’s Report Load Times

Recently, a client contacted us seeking recommendations on how to improve the load times of their Power BI Premium implementation. Power BI Premium offers the ability to share data with anyone without purchasing per-user licenses, but it also adds configuration complexities beyond those associated with standard Power BI implementations. The client, a medium-sized airline, was using Power BI Premium to track passenger and flight statistics. They came to us concerned that their reports had been running slow for the previous six months.

Determining the cause of the slow load times required some investigation. We organized multiple rounds of discussion with the client. Before our first round of discussion, we performed an analysis of all dedicated capacities. While there can be numerous causes for slow load times, we often find that report design needs to be improved. Reports with many visuals and slicers take much longer to load. Data refresh times also need to be considered. Are scheduled refreshes occurring during peak traffic times? How do user queries interact with scheduled refreshes? In this case, the client’s DAX queries were not properly written, creating significant load times when multiple queries were executed. 

After identifying the primary bottleneck in the reports, we dove deeper, examining the client’s actual partner reports and models. This allowed us to identify specific problems and propose specific solutions. We found that load times were significantly reduced by disabling the auto date and time function, using out-of-the-box visuals when possible, using a collapsed drill-down hierarchy in matrix visuals, reducing report-level filters, reducing the number of slicers, checking for query folding, and fixing erroneous measures.

Empowering the Client to Address Future Challenges

We presented our findings to the client in the form of a report. Our intention, however, was not to simply give the client a laundry list of optimizations. Instead, we worked with the client to educate them about how they could take better advantage of their Power BI Premium implementation in the future. We reviewed Power BI Premium behavior and capacity metrics and explained how the client could use the metrics to identify further load issues in the future.

Ultimately, the client’s report performance was improved by 57%. But perhaps more importantly, the client gained the ability to identify and improve performance issues on their own. Feedback from the client was positive: “Thanks again for all the support. It was very helpful for us, and I think we have managed to optimize our reports to work better.”