Name: DAX Optimization and Performance
Rating: 4.6 (196 reviews)

Udemy Business

Enseigner sur Udemy

Transformez vos connaissances en véritable opportunité et touchez des millions de personnes du monde entier.

Votre panier est vide.

Continuer vos achats

Se connecter

S’inscrire

Créé parRandy Minder

Dernière mise à jour : 09/2023

Anglais

Ce que vous apprendrez

The Importance of Proper Data Modeling and Star Schemas
Dimension and Fact Tables (Normalization and Denormalization)
The VertiPaq Engine and Compression Techniques
Formula Engine, Storage Engine and xmSQL
Using DAX Studio for Performance Tuning using a 100M Row Fact Table
Data Caches
The Dreaded CallbackDataId
Common Optimization Scenarios and Techniques
Practice Exercises and a Quiz

Contenu du cours

6 sections • 33 sessions • 5 h 43 min de durée totale

Introduction2:16
Introduction
Important! - Before Getting Started3:50
Please view this before getting started. It is important.

Introduction9:42
An introduction to the VertiPaq engine
Compression - Value Encoding6:01
In this lecture we talk about how the VertiPaq engines achieves compression via value encoding and why this matters.
Compression - Hash Encoding5:11
In this lecture we talk about hash encoding to achieve compression and better performance
Compression - Run Length Encoding9:06
In this lecture we discuss run length encoding and why it's important.

We're Working with Two Engines10:00
All your DAX queries are executed by one of two engines. We discuss those engines in this lecture.
SQL Server Profiler18:17
In this lecture we briefly discuss the roll of SQL Profiler in DAX performance monitoring
DAX Studio22:59
In this lecture we discuss the DAX Studio and how important it is to every DAX developer and performance monitoring.
The VertiPaq Analyzer25:02
In this lecture we will be discussing all the incredibly useful information which can be found in a VPAX file to help you optimize your data model and DAX code.
VertiPaq Analyzer - A Practical Use Case20:08
In this lecture we discuss a practical use case for using the VertiPaq Analyzer using a large sales database.
Storage Engine Queries and xmSQL20:12
In this lecture we talk about xmSQL and the types of queries the storage engine can process
Data Cache8:10
An explanation of the data cache, what is stored there and what uses it
CallbackDataID11:04
In this lecture we discuss the CallbackDataId and why it can cause havoc with the performance of your queries.

Should I use SUM or SUMX?7:49
The question of using SUM vs. SUMX is discussed in this lecture, which also has an impact on your data model design.
Checking for Divide by Zero12:49
You wouldn't think that attempting to prevent a divide by zero could cause performance problems, but it can if it's done the wrong way.
More Complex IF Statements9:43
We have already seen why we want to remove IF statements from our DAX expressions and how to remove them in simple cases. In this lecture we covert scenarios that are a bit more complex.
CALCULATE Does not Need a FILTER Function (Usually)9:11
In this lecture we correct two bad practices in a query that is using the CALCULATE function with a FILTER.
Use Variables to Improve Performance and Maintainability9:36
The use of variables almost always makes your DAX code easier to understand and maintain. And, sometimes, it can also improve performance.
Be Careful with SUMMARIZE6:20
SUMMARIZE can be a bit problematic to use at times. If not used correctly it can lead to very bad performance.
Performance Analyzer9:23
In this lecture we talk about the Power BI performance analyzer

Prérequis

Some experience with DAX development will be very helpful
It will be helpful to already have DAX Studio on your machine or be able to install it

Description

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.

À qui ce cours s'adresse-t-il ?

Advance beginner, intermediate and experienced DAX developers

Ce que vous apprendrez

Découvrir les sujets associés

Contenu du cours

Introduction2 sessions • 6 min

It All Starts With Data Modeling and the Star Schema4 sessions • 38 min

The VertiPaq Engine4 sessions • 30 min

Understanding and Measuring Performance8 sessions • 2 h 16 min

Common Optimization Scenarios7 sessions • 1 h 5 min

Exercises and Quiz8 sessions • 1 h 9 min

Prérequis

Description

À qui ce cours s'adresse-t-il ?