Power BI Performance Tuning

Introducing DAX Performance Tuning

In order to know the main options to make a Power BI report run more performant, you must first know what happens during the execution of a query. That is the focus of this module.

• A Day in the Life of a DAX Query
• Formula Engine
• Storage Engine
• DataCache
• LAB: Capturing Query Plans and Server Timings

Monitoring DAX Query Execution

Regular programmers have breakpoints and print statements to help understand what their code is doing. Power BI works slightly different, but with EVALUATEANDLOG DAX programmers get something comparable. This module shows how to use this feature in DAX Studio, and compares it with other techniques to debug DAX.

• Tracking DAX queries with Query Rewrites and Variables
• Tracking DAX queries with EVALUATEANDLOG
• Using EVALUATEANDLOG in DAX Studio
• LAB: Using EVALUATEANDLOG in DAX Studio

Formula Engine

The work of executing a DAX query is spread over the Formula engine (which focusses mainly on the transformations needed) and the Storage engine, which mainly retrieves the necessary data. In this module we zoom in on the Formula engine, and its interaction with the Storage engine.

• Monitoring Materialization
• Callbacks: Advantages and Disadvantages
• Vertical and Horizontal Fusion
• LAB: Monitoring the Formula Engine

Vertipaq Storage Engine

When using Import mode to store your data, you can help Power BI reduce the storage cost. But you must also keep the CPU cost into account. In this module you learn how Power BI stores data in import mode, and how you can help the system reduce this storage.

• Columnstore Storage
• Hash versus Value Encoding
• Run Length Encoding (RLE)
• Encoding Relationships
• Influence of Hierarchies on Storage
• LAB: Influencing Vertipaq Storage

Relational Storage Engine

When using DirectQuery, Power BI no longer works on its own copy of the data, but fetches data from a relational database (SQL Server, Oracle,...). This has some consequences for data refresh and performance. This module explains the details of DirectQuery, and optimizations possible to improve performance.

• From Import mode to DirectQuery mode
• Tracking Latency
• Is DirectQuery always Live Data?
• Work around Calculated Tables
• Optimizing Relationships
• LAB: Optimizing DirectQuery

Combining Multiple Storage Engines

Power BI does not force you into pure Import mode, or pure DirectQuery: You can mix and match. But these composite models complicate the queries required, and the performance concerns. This model explains what performance concerns to pay attention to when building composite models.

• Working with Composite models
• Regular versus Limited (weak) Relationships
• DirectQuery on Analysis Services