DAX Optimization and Performance

DAX can seem simple when just learning but then gets progressively more difficult and it is very hard to master. There are so many subtilties. This makes trying to figure out why your DAX code is underperforming, and fixing it, overwhelming at times. This course attempts to make this process easier.  The course starts with the importance of proper data modeling and why a Star schema is so critical. We then cover the concepts of normalization and denormalization along with the use of dimension and fact tables. We cover in depth the importance of using the VertiPaq Analyzer when exploring the makeup of your data model.

The course then jumps into the VertiPaq engine and the techniques used to compress data. We cover why choosing the correct data types in your model is so critical and the importance of data cardinality in how well data is compressed, which directly affects the performance of all your DAX code.

We then explore the two engines that make up VertiPaq, the formula engine and storage engine. SQL Server Profiler and DAX Studio are introduced as two tools you can use to perform performance monitoring. DAX Studio is used heavily and many DAX Studio examples are used in demonstrating how important the tool is for performance monitoring and evaluation utilizing the server timings feature. We cover xmSQL and how useful it is in getting a handle on what the engines are doing when executing your DAX code. Multiple threads and the data cache are what makes the storage engine blazingly fast and we cover this in detail. The CallbackDataId is extensively covered and why we want to avoid it and how to avoid it.

We wrap up with at least a half dozen common optimization problems / scenarios and how to address them.