We are proud to announce the release of the second edition of the Optimizing DAX video course.

After working for more than a year on this project, we decided that the amount of material produced was already much better than the first edition. It is time to make it available to students, though the production is not complete. Additional sections for optimization strategies specific to DirectQuery and composite models will be released by the end of 2023!

Students that enroll in the course starting today will receive automatic updates as soon as the new sections are available.

Existing students of the first edition of Optimizing DAX have already received information on how to access the new edition if their access is still valid, and how to extend the access if their access has expired.

We are also writing a book titled Optimizing DAX that we will publish once we complete the video course by the end of 2023. The book will be part of the companion content available to the students, but it will also be for sale in printed format once available. However, the video course students already have access to the book’s draft version in PDF!

That’s all for the quick news. If you want to know more about this project, read the following sections!

The project

When we started planning this new video course, we immediately decided that we should also write a book, at least for internal use. After a few months, we considered that the additional work and effort required to create publishable content was worth it because searching for specific topics in the book is something we frequently do. The book forced us to be very accurate in explaining how the DAX engine works, and how different versions of a piece of DAX code that produce the same result may actually generate very different execution plans.

We are convinced that we are now offering the best documentation available about how to read a query plan and how the Formula Engine interacts with the different Storage Engines. These details may seem excessive, but the challenge of DAX is that there are no silver bullets to improve performance. You have no indexes, and aggregations are not useful for imported models and common issues caused by the Formula Engine. Moreover, you see new versions of the DAX engine every month, and new DAX functions are added over time. The only way to develop skills that will last over time is to learn how to identify a bottleneck, explain what is causing a performance issue, and modify the code or the model so that you can improve the execution time. Yes, there are common optimization patterns, which usually correspond to bad DAX coding practices. Still, these cover only a small fraction of the performance issues you can find in real-world models.

We are better able to explain certain things today. We performed an immense amount of in-depth research, investigation, and self-study for this project. We gathered real-world experience through our consulting work, challenges that students brought to our attention in class, discussions we had at conferences, and we collected and organized a massive volume of information. Our goal as always was to best serve your career development, to increase your value in your workplace by enabling you to become the expert who knows how to do everything, from development all the way to optimization. This course holds you by the hand and teaches you things you will not find anywhere else. Even Microsoft has not made anything like this available.

So concretely, we have produced almost 30 hours of video lectures so far, and we will add more to cover DirectQuery and composite models (which are now generally available!). The book’s page count is more complex because it depends on the format we will use for the printed version. However, we are talking about a big book, not the kind you can read on the bus.

The structure

Optimizing DAX is divided into five sections:

  1. Core concepts: after an introduction to the optimization process through several practical examples to give you an overview of the big picture, we describe the internal Tabular architecture, the different Tabular storage engines that share the same Formula Engine, the tools of the trade (Performance Analyzer and DAX Studio), and the different query plans.
  2. The Formula Engine: the logical and physical query plans require a clear understanding of the different operations performed by the formula engine and the interactions with the storage engine. After this section, the first optimizations described are for educational purposes: we need at least one storage engine to get the full picture.
  3. VertiPaq: the storage engine used to import tables in memory is VertiPaq, an in-memory columnar database that performs extremely well in optimal conditions. Complex models and complex DAX code can challenge the efficiency of this engine. To get the best out of VertiPaq, we must understand why compression is important, how it works, and how relationships and additional structures in the Tabular model can affect query performance. Eventually, learning to read xmSQL – the language of VertiPaq – including different join types and callbacks is the most efficient way to figure out how the engine is solving a query rather than analyzing thousands of lines in the query plan.
  4. DirectQuery over SQL (available by end of 2023): the second storage engine we cover is DirectQuery to a relational database source like SQL Server. In a scenario where you have a single data source on a relational database, you can obtain real-time access to the data source, which presents many more challenges for SQL optimization. This section aims to produce more efficient requests to the SQL engine. Optimizing SQL Server (or any other relational database) is not a goal for this section. (not available – expected by the end of 2023).
  5. Composite models (available by end of 2023): when a data model has more than one data source, and at least one is either DirectQuery or a live connection to another Tabular model (Power BI or Analysis Services), then we have a composite model. These are the most challenging models to optimize because you multiply the possible sources of bottlenecks. You must understand how the engine coordinates the requests between the different storage engines to figure out how to improve the performance of reports on composite models.

The delivery

We just released the first three sections of the entire video course. The amount of content is three times the duration of the lectures we had in the first edition, which was limited to VertiPaq and did not cover DirectQuery. Students of the first edition will find that only the explanation of the compression techniques in the VertiPaq engine has a similar length and depth. All the other topics have a completely different structure and are aligned to the latest versions of the engine.


A big difference with the first edition is that this video course has no slides. The companion content is the book in PDF format. We used a few diagrams during the explanation but mainly relied on the whiteboard as a more interactive tool to support code highlighting and describe processes.

The lectures alternate between Alberto and Marco. Each of them has their own style, and they complement each other. The optimization examples usually repeat concepts already described in previous lectures more practically. The change of teacher and the repetition of the concepts in different scenarios is intentional: the goal is to reinforce the learning, which should be completed by practicing with the demos and then by looking for similar issues in your real-world models.

The tools


We waited for DAX Studio 3 to come out in 2022 before capturing the first screenshots for the book. As we were writing, we realized that we needed additional improvements for many activities and demos, so we also spent time adding features in DAX Studio. DAX Studio 3.0.6 is the result of many of these improvements. It is awesome to know that these improvements will be used daily by thousands of users worldwide!

You realize how good DAX Studio is (from a presentation point of view) when you record a video with the analysis of events collected with SQL Server Profiler. Many videos are recorded in 4K with a large font to get the best quality so that in Full HD you do not see pixels when you zoom into an area. DAX Studio shines in those conditions, while other legacy apps just show large pixels.

The demos

We strongly recommend students practice repeating the demos they see in the lectures on their hardware. We provide a set of PBIX files that can run 90% of the demos. For the more advanced users, we also provide the SQL Server backup file to refresh the PBIX files (not required for the demos) and the BIM file to create a large model in SQL Server Analysis Services.

What’s next

We are committed to releasing the remaining sections by the end of 2023. The students will automatically receive access to the new lectures and the updated PDF version of the book. We are also working on new content for SQLBI+. We are going to be very busy for the next few months. As always, stay tuned!

 
Try Optimizing DAX Video Course 2nd Edition now!