Optimizing DAX Video Course


This workshop is a complete video course about optimizing queries and measures in the DAX language.

DAX is the native language of Power Pivot for Excel, Power BI Desktop and SSAS Tabular models in Microsoft SQL Server Analysis Services. This course is aimed at users of Power BI, Power Pivot, and at Analysis Services developers who already know the DAX language and want to learn how to optimize DAX formulas and models for improving performance.

The goal of the course is to enable you to find bottlenecks in DAX calculations using tools such as Profiler and DAX Studio, providing the knowledge to improve the performance by using different DAX formulas or by changing the data model, when required. The course is made of more than 10 hours of lectures, and other 8 hours (estimated) of individual exercises. You can watch the videos at anytime and the system will keep track of your advances. Within the course you can download the material for all the exercises.

Prerequisite: Attendees need a good knowledge of the DAX Language, and a previous experience using either Power Pivot for Excel, or Power BI Desktop, or Analysis Services Tabular. You may acquire this knowledge by following the Mastering DAX Workshop, or having an equivalent real-world experience.


Course Curriculum
  1. Presentation of Optimizing DAX
    •   Presentation of Optimizing DAX FREE
  2. Exercises, slides, and demos
    •   How to download and complete exercises FREE
    •   Exercises download
    •   Demos download
    •   Slides of the video course
  3. VertiPaq storage engine
    •   VertiPaq storage engine FREE
    •   VertiPaq in-memory columnar database
    •   What is VertiPaq? FREE
    •   Run Length Encoding (RLE)
    •   VertiPaq compression
    •   Segmentation
    •   Data memory usage
    •   Materialization in DAX
    •   Storage internals
    •   Relationships
    •   Reduce dictionary size
    •   Hands-on labs FREE
    •   Lab 1 - Exercise 1 - Understanding information about a model
    •   Lab 1 - Exercise 1 - Solution
  4. Measuring performance
    •   Measuring performance FREE
    •   Tabular query architecture
    •   Two engines
    •   SQL Server Profiler
    •   DAX Studio FREE
    •   Gathering counters
    •   Understanding data caches
    •   xmSQL syntax
    •   Filter
    •   Lab 2 - Exercise 1 - Simple grouping
    •   Lab 2 - Exercise 1 - Solution
    •   Lab 2 - Exercise 2 - Simple filters
    •   Lab 2 - Exercise 2 - Solution
    •   Lab 2 - Exercise 3 - Basic time intelligence
    •   Lab 2 - Exercise 3 - Solution
  5. Analyzing query plans
    •   Analyzing query plans FREE
    •   SUMMARIZE
    •   ADDCOLUMNS
    •   Multiple measures
    •   Cache
    •   Storage engine features
    •   CallbackDataID FREE
    •   Measuring MDX
    •   Lab 3 - Exercise 1 - High value countries
    •   Lab 3 - Exercise 1 - Solution
    •   Lab 3 - Exercise 2 - Sum of similar measures
    •   Lab 3 - Exercise 2 - Solution
    •   Lab 3 - Exercise 3 - Context transition
    •   Lab 3 - Exercise 3 - Solution
    •   Lab 3 - Exercise 4 - Counting invoices
    •   Lab 3 - Exercise 4 - Solution
  6. Optimizing large models
    •   Optimizing large models FREE
    •   How many rows do you have?
    •   SUM or SUMX?
    •   Optimizing degenerate dimensions
    •   Dimension bottlenecks
  7. Advanced optimizations
    •   Advanced optimizations FREE
    •   Introduction
    •   Division by zero
    •   Filter materialization
    •   Optimizing IF statements
    •   Column filters vs table filters
    •   Currency conversion
    •   Lab 4 - Exercise 1 - Open orders
    •   Lab 4 - Exercise 1 - Solution
    •   Lab 4 - Exercise 2 - Optimizing if-then-else
    •   Lab 4 - Exercise 2 - Solution
    •   Lab 4 - Exercise 3 - Currency conversion
    •   Lab 4 - Exercise 3 - Solution
    •   Lab 4 - Exercise 4 - New customers
    •   Lab 4 - Exercise 4 - Solution
  8. Optimization examples
    •   Optimization examples FREE
    •   Introduction
    •   Events in progress
    •   New and returning customers
  9. Conclusion
    •   Conclusion


Course access

  This video course is hosted by SQLBI on Vimeo platform, and it will be accessible on desktop and mobile browsers for three years (36 months) after the purchase date.
After registering to the course, you will receive an email containing the credentials to access the training. Please note that these credentials are intended as personal and you should not share them with other people.