Data Modeling with Microsoft Power BI

Chapter 8. Performance Tuning in the Power BI Data Model

Performance tuning in Power BI is a complex topic with several components involved. Every storage mode has different performance implications, and optimizing the report runtime must be done with approaches specific to the storage mode. You have more than one option for implementing the same calculation in DAX. You can pre-calculate values and physically store them in the data model, or first let calculations be done ad hoc via explicit measures. The in-memory compression is highly dependent on the cardinality of a column—and, therefore, can’t be predicted in a general way.

Performance tuning in Power BI could easily fill a book on its own. All the optimizations have one thing in common, though: scanning less data will speed up query time. Because this book is about data modeling, I limit this chapter to the storage modes and how the variants can be combined in a data model to speed up query time. You can use the Performance Tuning.pbix file to follow along with the examples in this chapter.

Storage Mode

The VertiPaq engine offers different storage modes. Some of them can be set under the Advanced settings in the Properties pane for a table (Figure 8-1).

Figure 8-1. Storage mode of tables

The storage modes in VertiPaq engine include the following:

Import mode

When you import data, the complete table is physically loaded ...