Level up your Power BI model using DAX Studio – Part 2

Welcome back to part two of how to level up your workflow and Power BI model using DAX Studio! I’m showing you my go to scenarios on when to use Power BI together with DAX Studio.

In the first part of this post I started with an introduction to what DAX Studio is. I discussed why you should use it when working with Power BI or tabular models in general and I explained a use case on how to troubleshoot the performance of a Power BI visual containing multiple measures.

In this post I will go through how to view and analyze your model using model metrics, how to benchmark your DAX measures performance and how to run traces on your DAX queries (which we touched briefly in part 1).

View model metrics (Vertipaq analyzer)

Vertipaq analyzer is an open source project, initially created by Marco Russo from SQLBI. It is a tool that allows you to view the metrics of any tabular model in Power BI, PowerPivot or Analysis Services Tabular.

Vertipaq Analyzer is a must have tool for any data professional working with tabular models such as Power BI datasets. It’s a simple way to get a full overview of your tabular model metrics related to the health and performance of your model.

Since version 2.x, Vertipaq Analyzer is a feature in DAX Studio. To access it, go to Advanced tab and click View Metrics

An image showing how to navigate to the view metrics feature in DAX Studio
View model metrics(Vertipaq Analyzer) using Dax Studio

This opens the VertiPaq Analyzer Metrics pane. Vertipaq Analyzer lets you analyze the metrics of a model on Table, Column, Relationship and Partition level. A summary view of the model is also available.

An image showing an example of the results from VertiPaq Analyzers metrics pane
VertiPaq Analyzer Metrics pane

Understanding these metrics can give you a huge advantage in not only understanding your model, as already mentioned. It is a great tool both for authoring and maintaining a tabular model. As a model grows and increases in complexity it becomes more technically difficult to maintain.
By looking at these metrics you can quickly get an overview and discover things such as:

  • Which parts of your model that is using the most resources. Such as table and column size and the percentage of total.
  • Identifying high cardinality (Number of unique values) columns in a model. High cardinality columns can have a negative impact on your models performance.
  • How data is encoded and stored. If you want to read more about how Power BI stores it’s data, read my post about Vertipaq compression techniques
  • The size of your model in memory. This is not the same as the size stored on disk.
  • Column datatypes
  • Referential Integrity violations and missing keys
  • Hidden date tables created from the Auto date/time feature in Power BI, which could potentially bloat your model.

And much more…

More information about Vertipaq Analyzer can be found on SQLBI

Benchmark your DAX measures

Benchmarking your DAX query is a great way to to get an overview over your DAX performance. Adding this to your development or debugging workflow lets you be more precise when for example comparing performance between different measures or testing different versions of the same measure.
DAX Studio’s benchmark functionality allows you to run x number of executions using both cold and warm cache.

Let’s take a look at an example. I’m using the same modified DAX query used in part 1 when troubleshooting a DAX query generated from a Power BI visual.

This is a modified query originally generated from a matrix visual in Power BI. The query has 6 measures grouped by customer name. The DAX logic of the measures are not important in this demonstration. But you can see that I have defined the measure Sales as part of the query.

An image shoing an example DAX query
A DAX query in DAX Studio

Let’s benchmark this query using DAX Studio. To open the benchmark function, go to the Advanced tab and click Run Benchmark.

An image showing how to start a benchmark in DAX Studio
Start a benchmark in DAX Studio

This opens up the Benchmark window. You can set the number of cold and warm cache executions for the current benchmark session. Let’s go ahead with the default setting of 5 cold and 5 warm cache executions for this demonstration.

An image showing the available settings when starting a benchmark in DAX Studio
Benchmark settings

The query executes a total of 10 times. It is possible to see the executions in DAX Studio’s output window. A Summary and Details view shows the results of the benchmark, once it has finished.

An image showing the results from the summary view after running a benchmark
Benchmark summary view

Let’s analyze the results. The summary view presents a number of interesting information. This allows you to see the Average, Standard deviation, Min and Max values for both the total query duration as well as the Storage engine. The values are split by Cold and Warm cache.

So, what does this tell us? For the cold cache executions, this query runs in 1052 milliseconds, in average. We see that the values range from 1022 to 1086 ms, which is around the same value. The Warm cache average value is 773,4 ms, which is almost 30% faster than the cold cache execution. By performing this test we see that the performance improves using the cache. This is an important aspect to understand, even if it is not always the case.

The Details view offers you even more details about each execution of the query.

Using the benchmarking functionality enables you to get a great overview of the performance of your measures. When running queries, the execution times can vary depending on many things. For example the current load on the model or the system which the model is running on. By using the benchmarking tool you can get more accurate and even performance metrics, without worrying as much about spikes in your RAM usage.

Run server timing traces on your query

Running a Server Timings Trace in DAX Studio allows you to see a lot of very important information for your query. Such as total query execution time, formula engine execution time, storage engine execution times as well as the number of SE(storage engine) queries and cache hits. Full documentation is available on DAX Studio’s site.
DAX Studio also displays a SQL like query, called xmSQL. This is only a textual representation of the requests sent to the Storage engine. Nonetheless, it can prove to be extremely valuable when troubleshooting the logic and operations performed by the storage engine.

Start a Server Timings trace in DAX Studio by pressing the Server Timings button located on the Home tab. This will immediately start a trace in DAX Studio.

An image showing how to start a Server Timings trace in DAX Studio
Run a Server Timings trace in DAX Studio

A message in the output pane will confirm the trace by showing the message “Query Trace Started”. After the trace has started you are able to go ahead and execute you query.

After the query has finished you are able to see the result in the Server Timings pane as seen below.

In this example the query took 984 milliseconds to execute. 762 milliseconds was spent in Formula Engine (FE) and 222 milliseconds in the Storage Engine (SE).
Understanding the roles of the formula and storage engine, as well as understanding these metrics, is an important step in troubleshooting and understanding the performance of your DAX query.

If you want to read more about the storage engine and formula engine in DAX, SQLBI has a great in depth article about the topic.

An image showing the results from a Server Timings Trace
Results from a Server Timings Trace

It is also possible to see the number of Storage Engine queries generated from the executed query. This query generated three SE queries.

Looking at the individual query metrics as well as the xmSQL queries can also give you valuable information in your investigation. Such as data retrieved, duration for each query and number of rows returned. The number of queries, as well as the actual query content (xmSQL), can also provide valuable information in search for poor DAX query performance.

Thank you for reading

That all for now. I hope that you learned why you should use DAX Studio to level up your workflow when working with data models in Power BI.
What are your thoughts? Want to share something? Please leave a comment below!

Leave a Comment

%d bloggers like this: