Step diagnostics

To generate step diagnostics tables, select the step under Applied Steps of the Query Settings pane in Power Query. Then, click Diagnose Step under the Tools tab of the ribbon. Once the diagnostics have executed, you’ll see new diagnostic tables created for just this step in the Queries pane list, under a new group called Diagnostics.

Understanding query diagnostics

When reviewing or analyzing the details or aggregations of query diagnostics data, it’s important to understand what each step is doing and which ones are happening inside Power BI and which ones might be steps where Power BI is going back to underlying data sources (which is typical for data refreshes even for import scenarios). Some of the important columns to look at include the following:

• query (so you know which query or table the operations pertain to)
• operation (to distinguish between opening a connection, sending a query, or evaluating, among other things)
• start time
• end time
• exclusive duration

The last few columns will help you understand which operations are taking the longest time during the query execution.

Once you know where the time is being spent, then you will be able to focus on that operation. For example, you may find that the data refresh is taking a long time because Power Query is waiting for the underlying database to return data to a query – so you’ll end up with better performance in Power BI if you scale up the underlying database to make a better-performing data refresh in Power BI.

Summary

In this chapter, we learned about optimizing model performance. We learned how some of the easiest ways to increase data model performance include only keeping data that is necessary for reports and either removing the additional rows and columns using Power Query, aggregating the data to reduce it, or simply removing the data in the underlying data store (sometimes accomplished with a view in a database). We also learned how to use tools such as DAX Studio to investigate the inner workings of measures to best optimize those components of our data models for the best performance. We learned how we can optimize relationships and visuals and how we can use the query diagnostics tools in Power BI to better understand the operations that take place in Power Query.

In the next chapter, we will learn about creating dynamic and engaging reports using Power BI Desktop.