Next Gen Data Learning – Amplify Your Skills

Blog Home

Blog

Query Performance And DAX Studio Setup

by | DAX

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.

query performance

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.

query performance

You can assign commands like Run Query and Format Query on your keys in the Key Bindings Settings.

query performance

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.

query performance

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.

query performance

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

Related Posts