Almost a year ago, we announced the release of three sections (core concepts, formula engine, and VertiPaq); in November 2023, we released the DirectQuery over SQL section; now, in April 2024, we release the last section of Optimizing DAX about Composite Models.

What’s new in the Composite Models section

The fifth and last section of Optimizing DAX second edition covers composite and complex models: that is, any scenario where a model has multiple data sources of different types, including other semantic models. This new section adds another 5 hours of lectures in three modules:

  • Understanding composite models: We restricted the definition of a composite model to extending a semantic model into another semantic model. In this module, you learn how the engine manages local and remote tables and how this affects relationships, calculated columns, and calculated tables. We introduce the notion of wholesale and retail calculations so you can understand what to expect in terms of performance depending on the data model defined.
  • Composite model optimization examples: You learn different techniques to analyze and solve performance issues by looking at new examples where the added complexity is the presence of local and remote tables in a composite model. By facing different causes of performance bottlenecks, you learn new modeling and coding techniques specific to this model type.
  • Understanding complex models: By combining multiple data sources that use different storage engines, you are exposed to problems that do not require learning new concepts but, rather, just applying the same techniques already applied to other models. The challenge is to make the right design choices to take advantage of the more efficient technique depending on specific requirements.

State of the art

Compared to our initial plan to release everything by the end of 2023, we are behind. However, we are happy to release a finished product with zero compromises. This course (and its book, which I will describe more later) is the state-of-the-art of optimizing DAX expressions in Power BI, Fabric, and Analysis Services.

When we say we include Fabric, we mean it: when you use Fabric, you are using either DirectQuery, VertiPaq, or both. Indeed, internally, Direct Lake uses the same VertiPaq engine used in Import mode. In terms of DAX optimization, the approach, tools, and techniques are the same. While Direct Lake adds additional challenges to refresh the model and get good compression, these topics are out of scope for this course, which focuses on query performance issues.

With a single source of knowledge (the video course or the book), it is now possible to learn how to analyze and optimize any DAX expression with a guided path and a complete walkthrough of concepts, tools, and best practices.

If the amount of content to learn is intimidating to you (more about that later), you should consider the alternative: investigate and explore many undocumented aspects of the engines yourself and try to collect knowledge from specific articles and blogs.

The book

The Optimizing DAX book will be available to everyone by April 30. Since the first release of the Optimizing DAX video course, registered students have had access to the draft version of the book. Now that we have a final release, we can print the book. We are releasing all the versions simultaneously: printed (B&W and color), PDF, and eBook.

Because the colors of screenshots and samples may be important (we used different background colors for different languages), we created two versions of the printed book: one in black and white and one in color. We wanted to provide both options because the printed color version is expensive.

You can order the book online, but if you are a student taking the Optimizing DAX video course, you already have a free copy of the book in PDF format!

Additional considerations

The video course and the book have 22 chapters grouped into 5 sections. This content fills 40 hours of videos and more than 800 pages. Completing this project required more than two years of research, tests, writing, reviews, video recording, and editing on our end.

While we hope that everyone will purchase the video course or the book, we know this will not happen. Only BI professionals who face complex scenarios will get the maximum return on their investment. However, everyone should consider starting a manual optimization only after having analyzed a model with DAX Optimizer, the tool released to general availability in late March 2024.

Within minutes, DAX Optimizer can find problems that would require hours to be identified by experts who know the content of Optimizing DAX well and have extensive experience. Thus, DAX Optimizer makes an expert more productive and enables all users to solve many bottlenecks in their code without needing help.