D'après un article publié sur
http://blogs.msdn.com/powerpivot/Remerciements à
Howie Dickermann [MSFT]
DAX est un langage basé sur la syntaxe des formules d'EXCEL. Il est capable d'adresser plusieurs tables et comprend des fonctions permettant d'invoquer des bases relationnelles. Il comprend également des fonctionnalités permettant de créer dynamiquement des aggrégations, ce qui permet d'envisager d'afficher des valeurs calculées dans un TCD. Au grand bonheur des utilisateurs d'EXCEL, DAX reprend une partie des bibliothèques de fonctions d'EXCEL et beaucoup de fonctions sont identiques.
Quelques exemples:
= [First Name] & “ “ & [Last Name] formule de concaténation de texte classique
= SUM(Sales[Amount]) la fonction SUM somme sur une colonne plutôt que sur une plage de cellules
= RELATED (Product[Cost]) permet de référer la table produit dans un tableau basé sur la table SaleOrderDetails par exemple
Quand utiliser DAX ?
En partant du principe que l'utilsateur a chargé grâce à POWERPIVOT un certain nombre de tables, il est probable qu'il ne dispose pas de toutes les valeurs nécessaires dans ces tables.
Il peut être alors nécessaire de faire appel à 2 types d'objet de calcul pour poursuivre l'analyse:
les
colonnes calculées et les
mesures (ce vocabulaire est propre à la sémantique de l'analyse multidimensionnelle).
Colonnes calculées:Elles sont identiques à celles d'EXCEL et il n'y a pas de nouveau concept sous jacent
Lorsque vous entrez une formule pour définir une colonne calculée, la formule est évaluée pour chaque cellule et vous disposez immédiatement du résultat.
Par exemple, si vous avez une colonne [Quantite] et une colonne [Prix], vous pouvez créer une colonne [Montant] qui est simplement [Quantite]*[Prix]
Les valeurs ainsi crées dans des colonnes calculées peuvent être exploitées dans le TCD de diverses façons. Une colonne calculée peut contenir:
- Des valeurs numériques qui peuvent être agrégées dans la zone "Valeurs" de votre TCD
- Des valeurs qui peuvent à leur tour constituer les lignes ou les colonnes de votre TCD
- Des valeurs qui peuvent être utilisées dans un "slicer" pour découper le résultat de votre TCD
Mesures:
Les mesures dans Powerpivot sont des formules qui sont placées dans la zone "Valeurs" de votre TCD. Elles sont évaluées pour chaque cellule de la zone "Valeur". Chaque évaluation est le résultat résultant de la combinaison unique d'un colonne, d'un ligne et d'un filtre associé à cette cellule.
Il y a 2 types de mesures dans PowerPivot: les mesures "implicites" et les mesures "DAX".
Mesures "implicites: si vous cochez une valeur numérique dans la liste des champs disponibles de votre TCD, Powerpivot va automatiquement créer une mesure "somme de MonChamp" et cette mesure sera automatiquement placée dans la zone de valeur du TCD (voir diapo). Vous pouvez bien sûr changer le type d'agrégation en COUNT, AVERAGE, MIN ou MAX. Nous les appelerons les mesures "implicites" car il n'est pas nécessaire de sélectionner un champ (colonne) et un type d'agrégation. La formule associée à ce type de mesure est implicite et sera toujours une simple agrégation à partir d'un croisement de lignes et de colonnes.
Mesures DAX: quand vous créez une mesure en utilisant la boîte de dialogue "Nouvelle mesure", vous pouvez nommer la mesure et lui attribuer la formule DAX souhaitée. Vous n'êtes cette fois plus limité à une simple aggrégation de colonnes mais vous pouvez vous livrer à des traitements un peu plus sophistiqués.
Il faut cependant bien comprendre que lorsque vous saisissez une formule pour définir une mesure, celle ci n'est pas évaluée immédiatement. C'est lorsque votre mesure sera déposée dans la zone de valeurs de votre TCD qu'elle sera évaluée.
Bien sûr, que vous cochiez un champs pour en faire une mesure implicite ou que vous choisissiez "Nouvelle mesure" et entriez une formule DAX, votre mesure sera ajoutée à votre TCD.
Quelque exemples de colonnes calculées comprenant des fonctions DAX basiques
Le schéma de Sales dans AdvetureWorks
A partir de la table Sales, on définit une colonne [Amount] = [OrderQty] * [UnitPrice]
Vue dans Powepivot
vue dans SQL SERVER Management studio
A partir de la table Product, il est possible de récupérer le UnitCost pour l'intégrer dans notre tableau en utilisant la fonction RELATED().
UnitCost= RELATED('Product'[StandardCost]))
vue dans SQL SERVER Management studio
Il
Vue dans Powerpivot
Il existe également une fonction RELATEDTABLE() qui permet de permet de renvoyer l'ensemble des lignes liées à la table d'origine. Par exemple, à partir d'un tableau basé sur la table Product, il est possible de récupérer l'ensemble des ventes des produits:
=SUMX(RELATEDTABLE([SalesOrderDetails],SalesOrderDetails[UnitPrice])
Vue dans Powerpivot
Notons dans cet exemple que nous avans utilisé la fonction SUMX plutôt que la fonction SUM. La fonction SUMX prend 2 arguments: la table dans laquelle nous allons chercher les valeurs et une expression à retourner pour chaque ligne de cette table.
Dans notre exemple, nous retournons dans notre tableau le montant des ventes par produit dans notre TCD.
DAX ne se réfère pas aux emplacement des cellules pour référencer des données
Dans DAX, il n'existe pas de notion d'adressage de cellule pour une données. Les données ne sont pas identifiés par leur emplacement dans la feuille de calcul. DAX ne se réfère qu'aux colonnes dans la table. Plutot que de considérer une plage de cellules, il faut concevoir les données comme un jeu de tables constituées de colonnes et de lignes
Fonctions d'agrégation DAX
DAX inclut de nombreuses fonctions d'agrégations d'EXCEL dont les fonctions SUM, AVERAGE, MIN, MAX, COUNT mais ces fonctions prennent pour arguments des colonnes. Il existe également de nouvelles fonctions d'agrégation capable d'agréger n'importe quel expression à partir de lignes d'une table:
- SUMX (Table, Expression)
- AVERAGEX (Table, Expression)
- COUNTAX (Table, Expression)
- MINX (Table, Expression)
- MAXX (Table, Expression)
Fonctions de table DAXDAX ne dispose pas de fonctions permettant de retourner une table. La formule qui définit la valeur d'une colonne calculée doit renvoyer une valeur scalaire afin de pouvoir être placée en colonne. Mais disposer de fonctions qui permettent de retourner des tables de données est très utiles surtout si vous souhaitez utiliser ces résultats intermédiaires pur les passer en arguments à d'autres fonctions. L'exemple ci dessus illustre ce mécanisme où l'on renvoie l'ensemble des lignes de la table SalesOrderDetails et qu'elles sont agrégées via la fonction SUMX pour être enfin renvoyées à la table Product.
Cette notion de fonctions renvoyant des tables peut être nouvelle pour un utilisateur d'EXCEL mais c'est un concept facile à assimiler. Quelques exemples de fonctions renvoyant des tables:
- RelatedTable (Table) renvoie la table contenant les données liées
- Filter (Table, Condition) renvoie la table filtrée par la condition
- Distinct (Column) renvoie une colonne de la table par valeur distincte
En conclusion, les fonctionnalités puissantes mis en oeuvre grâce à DAX pourront dérouter les utilisateurs d'EXCEL mais ceux ci pourront rapidement en tirer bénéfice en terme de performance mais également ( et surtout) en terme de clarté et de maintenabilité de leurs multiples sources de données. Finies les macros alambiqués pour tenter d'improbables et de périlleuses jointures entre leurs feuilles de calcul. Fini le joyeux mélange entre données stockées et calculées. Fini les hasardeuses mises à jour en cascade de formules entre feuilles et fichiers. Désormais, toutes les données résident en tables dans Powerpivot avec une gestion relationnelle propre. L'interface classique d'EXCEL 2010 devient uniquement le client du fournisseur de données Powerpivot et peut être utilisé à plein régime pour l'analyse. Nul doute qu'à terme analystes et spécialistes IT s'y retrouvent.
_1257.gif){kind=small}
Messages
