�ݺ�ߣ

#sqlsat589February 25th, 2017
Optimizing DAX
Marco Pozzan

Sponsor

Organizers

Chi sono
 Community Lead 1nn0va (www.innovazionefvg.net)
 Consulente Business Intelligence per (www.beantech.it)
 Docente ITS all’Università di Pordenone
 Partecipo agli eventi community
 info@marcopozzan.it
 @marcopozzan.it
 www.marcopozzan.it
 http://www.scoop.it/u/marco-pozzan
 http://paper.li/marcopozzan/1422524394

Agenda
 La differenza tra FE ed SE
 Tipi di query plan
 Tool per ottimizzare DAX
 Recap su Vertipaq
 Ottimizzazioni di DAX
 Conclusioni

 In Vertipaq ci sono due motori completamente diversi come
caratteristiche, sono come due fratellini che assieme cercano di
risolvere la query DAX
 Storage engine
 Formula engine
 Lo storage engine va sullo storage scandisce il contenuto delle tabelle e
restituisce sempre tabelle come risultato
 Le tabelle che restituisce lo storage engine sono utilizzate dal formula
engine per fare ulteriori calcoli
(SE) e (FE)

 Storage engine (SE):
 è incredibilmente veloce
 è multi-thread quindi lavora su più core ed è in grado di parallelizzare moltissimo
 non sa fare molte operazioni: select, matematica di bassissimo livello, applica
un filtro where e segue le relazioni per join
 Formula engine (FE):
 Tutte le altre operazioni vengono risolte da formula engine
 Single-thread
 Espressività
 Considerazioni: bisogna codificare le query DAX in modo che si ottimizzi
l’uso di SE e si riduca il l’uso di FE perchè così si ha più velocità
(SE) e (FE)

 Ogni task eseguito da SE va in cache e quindi produrranno un risultato
veloce la seconda volta che viene eseguito
 Ogni compito eseguito da FE non va in cache e viene ripetuto di nuovo
 Per creare delle buone ottimizzazioni analizziamo come vertipaq lavora
con e senza cache. Per svuotare la chace si utilizza il seguente script
 Considerazioni: fare in modo che le parti delle nostre query utilizzino il
meno possibile FE in quanto sono parti che vengono ogni volta
ricalcolate
Cosa viene messo nella cache

 Il «DAX Query Plan» indica ciò che Vertipaq farà. Ci sono due tipi di
query plan:
 Logical query plan: praticamente inutile perchè descrive la definizione
dell’algoritmo della query DAX e a patto che la query non sia molto complessa
non contiene informazioni importanti
 Fisical query plan: molto utile e ci dice quello che effettivamente ha fatto la
query.
 Il query plan in tabular non è come sql che mostra frecce e colori ma è
una stringa di testo
Query plan

Query plan Optimization Flow
Build DAX Expression Tree
Build DAX Logical Plan
Simplify DAX Logical Plan
Build DAX Physical Plan
Execute DAX Physical Plan
Fire Logical Plan Event
Fire Physical Plan Event

 Profiler SQL Server
 DAX Studio (https://daxstudio.codeplex.com/)
Tool per le ottimizzazioni

 Query End: E’ un evento che viene generato quando si cocnlude la query e riporta il
tempo di esecuzione totale delle query e il tempo di lavoro della CPU
 Dax Query Plan: è un evento che viene generato quando viene creato il query plan e
genera la forma testuale del query plan
 Vertipaq SE Query Cache Match: questo evento accade quando una query Vertipaq
viene eseguita usando la cache
 Vertipaq SE Query End: è l’evento che si genera quando viene eseguita una query dallo
Storage Engine di Vertipaq per ritornare il risultato
Eventi che catturano i tool

 Vediamo come viene risolta questa query
 SQL Profiler
 DAX Studio
Come lavorano?

DAX query plan (logical plan)
AddColumns : RelLogOp DependOnCols()() 0-0 Parametri Operatori
RequiredCols(0)(''[Result])
Sum_Vertipaq: ScaLogOp DependOnCols()() Tipo Operatore
Currency DominantValue=BLANK
Scan_Vertipaq: RelLogOp
DependOnCols()() 0-135
RequiredCols(126)('Internet
Sales Big'[Sales Amount])
'Internet Sales'[Sales Amount]: ScaLogOp DependOnCols(126)('Internet
Sales Big'[Sales Amount]) Currency
DominantValue=NONE
EVALUATE
row ("Result",
SUM(
'Internet Sales Big'[Sales
Amount]
))Operatore
Operazioni
eseguite
Da Vertipaq
SQL Profiler DAX Studio

 Column List (Elenco numero di colonna) (elenco nomi di colonna). RequiredCols (0, 1) ( 'Data'
[CalendarYear], ‘Internet Sales Big' [SalesAmount]) o DependOnCols () ()
 La ScaLogOp indica una dipendenza sul ramo sinistro DependOnCols(126) e ritorna uno scalare
 sotto RelLogOp ci sono le Vertipaq query ritorna una tabella
 «Crea una tabella con una colonna result che sarà riempita dalla SUM (SUM_Vertipaq) sulla colonna
[Sales Amount] leggendo tutta la tabella (Scan_Vertipaq) Internet Sales Big»
DAX query plan (logical plan)
AddColumns : RelLogOp DependOnCols()() 0-0
RequiredCols(0)(''[Result])
Sum_Vertipaq: ScaLogOp DependOnCols()()
Currency DominantValue=BLANK
Scan_Vertipaq: RelLogOp
DependOnCols()() 0-135
RequiredCols(126)('Internet
Sales Big'[Sales Amount])
'Internet Sales'[Sales Amount]: ScaLogOp
DependOnCols(126)('Internet Sales Big'[Sales Amount]) Currency DominantValue=NONE
AddColumns
RelLog
Op
SubTree
ScaLog
OpVertipaq Query
1
2

 SinglestonTable è una tabella con una riga che rappresenta il comando ROW = [Result]
 SpoolLookup i dati per result li cerca nella datacache che si chiama “ProjectFusion<Sum>“
 ProjectionSpool nuova versione di AggrgationSpool contiene il risultato della VertipaqResult
importante l’etichetta #Records che sono il numero di righe in cache che sono usate
 VertipaqResult risultato di una query xmlSQL. Ma non si sa quale query 
 #ValueCols n° di colonne numeriche e #FieldCols n° di colonne di altro tipo
DAX query plan (physical plan)
AddColumns: IterPhyOp LogOp=AddColumns IterCols(0)(''[Result])
SingletonTable: IterPhyOp LogOp=AddColumns IterCols(0)(''[Result])
SpoolLookup: LookupPhyOp LogOp=Sum_Vertipaq Currency
#Records=1 #KeyCols=258 #ValueCols=1
DominantValue=BLANK
ProjectionSpool<ProjectFusion<Sum>>: SpoolPhyOp #Records=1
VertipaqResult: IterPhyOp #FieldCols=0 #ValueCols=1

Vertipaq SE query
 l’ FE genera una serie di operazioni che vengono inviate all’ SE
 Quello che è eseguito da SE nel profiler è il «vertipaq SE query» (xmSQL).
 Ci sono sempre 2 Vertipaq SE query.
 vertipaq scan è quello che chiede FE all’ SE
 vertipaq scan internal è quello che viene effettivamente eseguito da SE
SQL Profiler
DAX Studio

Query End
 Query End ha una durata (Duration) e (CPUTime) in millisecondi
 CPUTime = SE CPU =tempo eseguito per query (x tanti utenti devo ridurlo)
 Duration = Total = tempo di attesa del risultato (diviso per n° core) (se ho
pochi utenti devo ridurlo e non me ne frega che la CPU sia 100%)
 Se ho due thred in parallelo uno per core la Duration <= CPUTime
 123 ms di SE (sommatoria dei Vertipaq Scan), 125-123 = 2 FE
 Quando i due sono sotto i 10-15 millesecondi non ha senso ottimizzare

Compressione in Vertipaq
 Vertipaq (simile compressione di pagina in SQL Server)
 Identifica parti uguali nell’aria di memoria
 Crea una struttura per rappresentare le parti uguali e ottiene la struttura
compressa della colonna
 Più efficiente di SQL perché si ragiona solo su una colonna con pochi
valori distinti rispetto alla pagina di SQL in cui ho righe con più colonne e
con meno valori distinti .
Vediamo come Vertipaq esegue la compressione

Run Length Encoding (RLE) - 1 livello
Potrei anche decidere di
togliere la colonna inizio
e tenere solo la fine
Children
1
1
1
1
1
...
2
2
2
2
2
2
2
2
....
Children Inizio Lunghezza
1 1 200
2 201 400
FirstName
Larry
Larry
Larry
...
Geoffrey
Geoffrey
Geoffrey
...
Alexa
Alexa
Alexa
...
Colleen
Colleen
...
FirstName Lunghezza
Larry 400
Geoffrey 400
Alexa 100
Colleen 100
BirthDate
13/04/1977
13/05/1977
13/06/1977
....
15/04/1980
16/04/1947
13/04/1976
...
13/04/1976
13/04/1976
13/04/1976
...
13/04/1990
13/04/1934
...
BirthDate lunghezza
13/04/1977 1
13/05/1977 1
13/06/1977 1
....
15/04/1980 1
16/04/1947 1
13/04/1976 1
...
13/04/1976 1
13/04/1976 1
13/04/1976 1
...
13/04/1990 1
13/04/1934 1
...
Le date cambiano così di frequente che se
provassi a comprimerla avrei su lunghezza
tutti 1 e otterrei una tabella più grande
dell’originale. Vertipaq lascia l’originale.

Run Length Encoding (RLE) - 1 livello
 Vertipaq non usa mai più memoria rispetto alla colonna sorgente…se non
riesce a comprimerla la lascia come è
 Vertipaq durante il processing di un tabella
 Divide la tabella in colonne
 Comprime ogni colonna con RLE
 Obbiettivo 1 L’ordinamento delle colonne è il punto più importante per
RLE (se ne occupa vertipaq ) buon ordinamento = buona
compressione
 Obbiettivo 2 : Se la dimensione della colonna in memoria è bassa lo
scan sarà più veloce

Dictionary encoding - 2 livello
Conoscendo i possibili valori della
stringa utilizzo il numero minimo di
bit per rappresentarla. In questo
caso 4 possibili valori bastano 2 bit.
Creo il
dizionario
Quarter
Q1
Q4
Q1
...
Q2
Q3
Q1
...
Q3
Q3
Q2
...
Q1
Q1
....
DISTINCT
Indice Quarter
1 Q1
2 Q2
3 Q3
4 Q4
Quarter
1
1
1
...
2
2
2
...
3
3
3
...
4
4
....
SOSTITUISCI
RLE
Quarter Count Lunghezza
1 1 400
2 400 400
3 800 100
4 900 100
xVelocity storage
Con il dictionary encoding Vertipaq è datatyping
independent. Non ha nessuna importanza il tipo dei campi
che si utilizzano nelle viste per popolare il modello
Indice Quarter
1 Q1
2 Q2
3 Q3
4 Q4
Versionecompressa
Dizionario

Conclusioni su RLE e Dictionary encoding
 Una stringa nella tabella dei fatti (osceno) non ha più nessun
prezzo grazie al dictionary encoding
 Obbiettivo 1: Importa solo il numero di valori distinti delle colonne
 Tanti valori distinti occupano più spazio (+ RAM) ed più lungo a
fare analisi
 Pochi valori distinti occupano poco spazio (- RAM) e tutte
operazioni ridotte

 Long Scan Time: Spesso per aggregazioni semplici le query DAX fanno
lo scan su una o piu colonne. Il costo di questa scan dipende dalla
dimensione delle colonne che dipende dal numero di valori distinti e
loro distribuzione
 Large Cardinality: Un largo numero di valori univoci in una colonna può
dare noia alla DISTINCTCOUNT
 Alta frequenza di CallbackDataID: Un largo numero di chiamate dallo
storage engine al formula engine possono influire pesantemente sulle
performance
 Large Materializzation: Se uno Storage Engine produce una grande
datacache la sua generazione richiede tempo (allocamento di RAM)
Cause dei bottlenecks nello storage engine

Demo 0

Demo 0
 La sum è sensibile alla dimensione perchè deve fare lo scan della
tabella quindi deve fare lo scan di tutte le colonne
 La distinctcount dipende dalla cardinalità
 N.B. Ridurre il numero di valori distinti velocizza le query 
Colonna Memoria (MB) Valori distinti SUM DISTINCTCOUNT
Sales Amount 1,855,587,152 40,000 809 2023
TimeKey 1,237,296,936 86,400 526 2556

Demo 1
 Eseguiamo la seguente formula DAX
 Genera un query plan che con due scansioni due coppie di Vertipaq Scan

Demo 1 – Perchè due query SE?
 1° Vertiaq Scan estrae CalendarYear da Date e la somma di Sales
Amount da Internet Sales. Usa solo SE perchè sono operazioni che sa fare
 Se la prima query risolve tutto perchè c’è una 2° Internal Vertiaq Scan?
 La seconda restituisce gli anni e la prima le vendite e anni e poi metto
assieme con FE

Demo 1 – Considerazioni sull’ottimizzazione
 Recupera gli anni dalla tabella dei fatti
 Che senso ha fare la scansione della tabella dei fatti per gli anni?
 Secondo voi quale è la maniera più veloce per recuperare tutte le date?
 Analizzando la tabella dei fatti (77.309.440 rows)
 Analizzando la tabella della dimensione Date (3652 rows)

Demo 2 – SUMMARIZE vs ADDCOLUMNS
 Con la nuova query si azzera il tempo per recuperare l’anno

Demo 2 – SUMMARIZE vs ADDCOLUMNS
 Con la SUMMURIZE al posto di VALUE peggioro le performance ma scrivo
meno DAX
 E se uso SUMMARIZECOLUMNS (Dax 2015) ?

 Una summarize dove aggreghiamo sia per anno che per colore.
 2° Vertipaq Scan per recuperare anno e colore fa uno scan su tabella fatti
Demo 3 – Problema SUMMARIZE con più colonne

Demo 3 – Soluzione
 Utilizzamo la ADDCOLUMNS con CROSSJOIN per ottimizzare la formula
 Le Vertipaq Scan aumentano da 2 a 3 perchè viene eseguita una
Internal Vertipaq Scan secca sulla tabella prodotti e calendario

 Con ROLLUP possiamo ottenere il venduto per anno e colore , il totale
per anno e colore e il grand total per anno.
Ci sono due modi di operare:
 Generazione di più query SE per recuperare i sottototali e per il grand total
(l’unica possibile)
 Recuperare il dettaglio e poi aggregare all’interno di FE per (questo solo se sono
sicuro che sono additive)
Demo 4 – ROLLUP

Demo 4 – ROLLUP
 Come si vede il numero di query SE è aumentato di molto
 la ROLLUP aumenta la complessità di (2N + 1) volte (N n° parametri)
(subtotali su 2 livelli =>2n+1=5 scansioni, 3 livelli =>3n+1=7 scansioni...)

 Esaminiamo la seguente query DAX
 I callbackDataID sono richieste di aiuto di SE a FE in
quanto non sa fare IF  e chiede a FE di eseguirlo.
Funziona ma ci sono due problemi:
 uno è multi-thread e l’altro no
 si parla di elaborazione di FE e quindi non si usa la cache
Demo 5 – CallbackDataID

 Anche se uso DIVIDE ho sempre la callbackID anche se è un pò più
veloce

 Riscriviamo la formula per levare la CallBackDataID

 La SUMX non si comporta come una iterazione perchè viene eseguita
direttamente da Vertipaq in quanto la sa gestire
Demo 5 – CallbackDataID non avviene con le interazioni
singole

 doppia iterazione genera un CallbackDataID
Demo 5 – CallbackDataID avviene con le interazioni annidate

 Vogliamo ottenere i 50 prodotti che hanno vendite maggiori
Demo 6 – Riduciamo la materializzazione
Dobbiamo ridurre
la dimensione della
datacache che in
questo caso è di 79
KB

 Dobbiamo ridurre la dimensione della Datacache
Demo 6 – Riduciamo la materializzazione

 Capire come funzionano SE e FE
 Ridurre FE, aumentare SE
 Leggere i query plan rende possibile l’ottimizzazione
 DAX non è facile da ottimizzare.... Ma è meglio che con MDX
 Libro di riferimento da cui partire
 https://www.sqlbi.com/books/the-definitive-guide-to-dax/
 Link di riferimento per approfondire
 http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf
 http://www.sqlbi.com/wp-content/uploads/Understanding-Distinct-Count-in-
DAX-Query-Plans.pdf
Conclusioni

THANKS! Q&A
#sqlsat589

�ݺ�ߣ

Optimizing dax

Recommended

More Related Content

Similar to Optimizing dax (20)

More from Marco Pozzan (20)

Optimizing dax