Optimizing measures, relationships, and visuals – Optimizing Model Performance

Optimizing measures, relationships, and visuals

Measures in Power BI are driven by DAX queries. To optimize DAX queries (and measures), we must understand how Power BI uses both a Formula Engine (FE) and an SE, which make up the backend technology. Simply put, the SE is where imported data is stored in the highly compressed columnar VertiPaq format, and the FE is where data can be calculated based on the requirements in DAX queries.

The SE provides a single interface for the FE to query and retrieve data. It functions to store data or provide a conduit for underlying data stores in cases where data is connected using DirectQuery. The SE is built into Power BI Desktop and is also part of the underlying technology used in SQL Server Analysis Services and Azure Analysis Services.

The FE is the query processor that takes DAX queries as input, interacts with the SE, and then returns data to Power BI visuals in reports and dashboards. The FE does not have direct access to every kind of data source that Power BI supports; instead, the FE only interacts with the SE. It’s the SE that has both the ability to store data in-memory in VertiPaq storage and the myriad of different data stores supported by Power BI. The FE serves the purpose of understanding DAX queries, converting to xmSQL (the SQL dialect used by VertiPaq), and performing calculations on data received from the SE to get to the end result as directed by the DAX query.

When optimizing measures, you will want to use an open source tool called DAX Studio (available for free from https://daxstudio.org). DAX Studio is an open source DAX client tool that allows you to do an in-depth analysis of DAX queries and how they are executed in the Power BI environment:

  1. To use DAX Studio, you will need the DAX query used by the specific operation of the report in Power BI Desktop. You can acquire this query easily using the Performance analyzer pane in Power BI Desktop, accessed from under the View menu on the ribbon. Click Performance Analyzer and then click Refresh visuals, select a slicer, or apply a filter. Power BI will generate and run the DAX query (or queries) to generate the requested data for each of the visuals on the report being analyzed.

Figure 8.3 – Results of Performance analyzer after refreshing visuals

2. After the operation has completed, you will be able to click Copy query in the Performance analyzer pane. Copy the query and then open DAX Studio.

3. Open DAX Studio, and ensure you connect to the PBI/SSDT model that is currently running in Power BI Desktop (the name of your Power BI file will show under PBI / SSDT Model).

Figure 8.4 – Connection screen in DAX Studio

4. Copy your query into the main or largest query pane shown in the DAX Studio interface, where it shows Start by typing your query in this area..

Figure 8.5 – DAX Studio window showing various panes, including the main query pane

5. After pasting your query, be sure to select Server Timings and then Clear Cache and then Run under the Run menu shown under Home on the ribbon.

6. Once these settings have been selected, click Run (which should run Clear Cache and then Run), and then click the Server Timings tab on the bottom pane. The bottom pane should now show something like this:

Figure 8.6 – Server Timings tab showing details of the DAX query

From this screen, we can see information about our DAX query that tells us the following:

  • How much time was spent in total to run the DAX query (4 ms)
  • How much time was used by the FE to run the DAX query (3 ms)
  • How much time was used by the SE to run the DAX query (1 ms)
  • How many SE queries were generated by the FE (1)
  • What the SE query looks like (shown in the right-hand side subpane)

This information will help us determine where we can spend time optimizing the DAX query used by the measure. In the previous example, we can see that the majority of the 4 ms time is spent in the FE, so it would make the most sense to optimize that as the remaining portion (1 ms consumed by the SE) will have less time to optimize. Ideally, you want to see more time spent in the FE or equally balanced between the FE and SE.

Here is an example of a DAX query used by a visual that contains two SE queries and is relatively balanced between FE and SE time:

Figure 8.7 – DAX query with two SE queries

SE queries are composed of xmSQL and are viewable by clicking each one in the middle-bottom pane. In this case, the SE query from line 2 is as follows:

SET DC_KIND=”AUTO”;

SELECT’States'[State],MAX ( ‘States'[Winter Avg ° F] )

FROM ‘States’

WHERE ‘States'[R/B] = ‘Blue’;

‘Estimated size ( volume, marshalling bytes ) : 54, 864’

The SE query from line 4 is as follows:

SET DC_KIND=”AUTO”;

WITH $Expr0 := [CallbackDataID ( MAX ( ‘States'[Winter Avg ° F]] )  ) ] ( PFDATAID ( ‘States'[State] )  )

SELECT

MAX ( @$Expr0 )

FROM ‘States’

WHERE ‘States'[R/B] = ‘Blue’;

‘Estimated size ( volume, marshalling bytes ) : 1, 16’

The PL-300 exam will not require in-depth knowledge of tuning FE or ST queries (or SE queries that get pushed down to DirectQuery data sources) but it is important to understand the dynamics of how DAX is executed (the relationship between the FE and the SE and how to troubleshoot these operations to increase performance).

Recommended reading for in-depth study on this topic is the book The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel by Marco Russo and Alberto Ferrari.

Leave a Reply

Your email address will not be published. Required fields are marked *

Shopping cart

close