Speaker : Alberto Ferrari – Niveau : 300 – Durée : 90mn
Objectif de la sesssion
Introduction au DAX comme langage de requêtage
Comprendre le moteur xVelocity
Introduction
Le DAX a été créé pour être simple
Dans PowerPivot 2010 DAX n’est qu’un simple langage de calcul
Avec PowerPivot 2012 et SQL Server 2012 le DAX est devenu un langage de requêtage, qu’on peut monitorer avec le SQL Server Profiler et donc optimiser
DAX as a query Language
Un seul statement : EVALUATE
Quelques exemples avec les fonctions basiques
- Un select sur une table : EVALUATE <table>
- Trier le resultset : ORDER BY
- Filtrer : FILTER ou CALCULATE TALBE
- Ajouter une colonne calculée : EVALUATE ADDCOLUMNS (…)
- Group by => Summarize
- Group by avec ROLLUP : EVALUATE SUMMARIZE(table,ROLLUP(table[col],alias,SUM(table[col]))
- Cross join : CROSS JOIN
- Défintion de mesures calculées : c’est l’équivalent du WITH en MDX
DEFINE
MEASURE Table[nom mesure calculée] = …
EVALUATE
…
- Génération d’une table issue du produit cartésien de chaque lignes d’une table tbl1 évaluant dans son contexte d’exécution les lignes d’une autre table (tlb2) GENERATE(tbl1,tbl2)
Optimisation du DAX
Le modèle tabulaire est très simple, il n’existe pas beaucoup de possibilité de l’optimiser.
La suel réponse est le DAX, vous pouvez l’optimiser à partir du plan d’exécution (cela n’est pas possible en MDX)
DAX Query Architecture
Petite overview du Formula et du Storage Engine.
Optimisation en DirectQuery
En direct Query on attaque directement la base SQL => la seule façon d’optimiser le DAX est donc d’optimiser la bdd
Lors de la démo on lance le SQL Profiler sur la db et on execute la requête DAX). On récupère ainsi la requête SQL qui a été générée par le moteur.
On procède ensuite comme pour une optimisation SQL classique, en analysant le plan d’execution et en utilisant le query optimizer.
Je vous rappelle que vous n’avez pas la main sur la requête, et que par conséquent les seules optimisations seront à faire de la création d’index. Good luck.
N.B : on voit au passage qu’en DAX des requêtes très simples se révèlent être bien plus compliquées en SQL, ce qui confirme la simplicité du langage.
Optimisation en In-Memory Tabular
Le moteur xVeolovity est composé de 2 moteurs le Formula Engine (desing pour l’expressivity : pouvoir implémenter des formules riches et complexes et le Storage Engine (trés rapide pour calculer et effectuer des opérations en parallèle)
DAX Query Evaluation Flow
Permet de voir toutes les étapes effectuées par le moteur lors de l’execution d’une requête DAX
SQL Server Profiler
Sur SSAS Tabular, le profiler permet de tracer les évènements suivants :
- Queries Events
- Query Processing
On passe sur une démo, on lance le profiler en cochant les évènement suivants : Query End, Vertipaq Query Cache Match, Vertipaq Se Query End, DAX Query Plan
On met en relation la requête DAX et le plan logique d’execution (ce dernier est similaire à la requête) puis on descnde sur le plan physique d’exécution et les requêtes Vertirpaq.
L’analyse d’une simple requête montre : 1 seul plan logique d’exécution, 1 seul plan physique d’exécution, et 4 requêtes Vertipaq (2 pour préparer l’exécution, 2 pour l’exécution)
Petit teasing pour la session de Marco de cet après-midi Pour on ira plus loin avec la session de Marco cette après-midi.
Quelques axes d’optimisations
- Eviter le SUMX. Ce dernier utilise le CallbackDataID qui n’arrête pas de faire des aller/retour ligne à ligne entre le vertipaq scan et le formula engine (évaluation itérative)
=> Préférer le SUM. On s’appuit sur une démo permettant de faire de la conversion de devise avec une formule contenant 2 SUMX, pour l’optimiser on passe on utilise le SUM avec un CROSS JOIN.
- Filtrer les données le plus tôt possible (ex sur un filter placés en fin de traitement et un filter placé avec le calcul des mesures.
- Utiliser plutôt le ADDCOLUMNS au SUMMARIZE
- Eviter les calculs complexes dans les mesures
- Eviter d’utiliser la gestion d’erreur (c’est vraiment trop lent, ce n’est bon que sur les mesures)
- Construire un data model simple, relationnel et claire (éviter les junk dimensions et rappeler vous que les join sont couteux)
Démos
- distinct count : montre des calculs rapides
- calcul sur les niveaux aggrégés et niveaux feuilles : pas de différence de perf
- utilisation des m2m : pas de pb de perf
- l’utilisation du cache (parfois le storage engine permet d’avoir plus de performance que le storage engine, parfois non).
Conclusion
Une très bonne session de la part d’Alberto qui me montre enfin les avantages tant ventés du modèle Tabular avec pas mal de démos.
Mais côté optimisation, cela reste du Teasing pour la session de Marco de cet après-midi.
Vivement les ppt et la vidéo de cette session.
Frédéric