Data Modeling with Microsoft Power BI

Chapter 20. Performance Tuning the Data Model with SQL

In Chapters 4 and 8, you learned several options for optimizing query response time through decisions about the storage mode of the tables in your Power BI data model. Physics mandates that you can obtain faster query response times by using more disk space—and the other way around. This is not only true for Power BI and Analysis Services but for all database systems, including relational databases.

In this chapter, I describe the options you have in relational databases to exchange storage space for query response time, and what you can do in the relational world to support query speed inside Power BI and Analysis Services.

Storage Modes

I title this section after Power BI’s term storage modes to parallel other parts of the book even though it’s typically not used when talking about relational databases. In relational databases, people usually talk about either persisting data in the database, or not. Persisting means to actually use disk space to store information in a certain form and shape, as opposed to only storing a query string. If the data is already stored in the right shape on disk, it only has to be transferred from the disk into memory and then sent over the network to the client. If only a query string is stored, everything I just mentioned has to be done as well, but on top of that, you need resources (CPU, memory, disk IO) to fetch the data from different tables, join it, and apply the one or another transformation ...