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