This tutorial will discuss about setting up DAX Studio and measuring query performance in Power BI. These information will help detect bottlenecks in the query so that you can eliminate them and optimize your DAX.
Setting Up DAX Studio
Open the DAX Studio tool and connect your model. In this example, the EDNA Optimizing DAX Course will be used and connected.
Next, turn on the Query Plan and Server Timings. The Server Timings is the storage engine query while the latter is the logical and physical query plan.
Change the options depending on how you’re going to use DAX Studio. You can adjust the Standard, Key Bindings, and Advanced settings to what you prefer in your DAX query’s performance.
In the Standard settings, you can modify the Timeouts, Editor, Results view, DAX formatter, and other basic settings as seen in the image below.
You can assign commands like Run Query and Format Query on your keys in the Key Bindings Settings.
You have to turn on all the settings on the Advanced tab settings to get all the information you need.
Viewing Query Performance
Once you’ve finished setting your options, go back to the studio and click Advanced. Click View Metrics to see the VertiPaq Analyzer Metrics.
The VertiPaq Analyzer Metrics was discussed in earlier blogs. You can drill down and do various analysis from the different tabs inside the metric. So if you want to refresh your memory, you may review the earlier posts on the VertiPaq Analyzer Metric.
Now, this is a sample query. It uses the ROW and SUMX functions to get the product of the FactSales’ Current Price and Quantity.
If you run the query, you can see how much time it spent in the formula engine and storage engine. You can also see how many storage engine queries were generated in the execution. The xmSQL statement also shows how your query performs the DAX codes from behind the scenes.
In the Results tab below, you can see a one-row result of the Total Sales provided by the DAX query.
If you go to the Query Plan tab, you can see the Physical and Logical Plans. This is where the formula engine is working to produce your valuable data.
***** Related Links *****
What Is DAX Studio In Power BI Desktop
DAX Tutorial: Context & The Calculation Engine
VertiPaq Analyzer In DAX Studio | Power BI Tutorial
Conclusion
It’s important to have your DAX Studio properly setup according to your preferences. It helps you to be comfortable in your workspace in Power BI. It will also help you easily navigate and monitor your DAX’s performance with ease.
You can learn more about how to get your DAX ready and how to start collecting data by checking out the other blog posts related to this topic.
Nick
