Editions ENI Livres | Vidéos | e-Formations
L’analyse des données avec le langage DAX
Objectif
Ce troisième chapitre permet de se familiariser avec le fonctionnement particulier du langage DAX.
Ses principales fonctions y seront abordées à travers différents exercices d’application.
Les fichiers relatifs à ces exemples sont disponibles en téléchargement.
Il est par contre nécessaire de faire les exercices dans l’ordre proposé car ils constituent une progression logique.
Le langage DAX et l’évolution des tableaux croisés dynamiques
Des millions d’utilisateurs d’Excel sont habitués à utiliser des formules pour effectuer des calculs. Ces opérations peuvent aller de la simple addition à des simulations financières ou scientifiques complexes.
Toutefois, dans tous les cas, ces formules sont construites à l’aide d’une combinaison d’opérateurs de base et de fonctions Excel placés au niveau d’une ou de plusieurs cellules de calcul.
Un changement de paradigme : tableur vs modèle de données
Avec Power Pivot, Excel n’est plus seulement un tableur. Comme nous l’avons vu dans le chapitre précédent, l’application possède désormais certaines des fonctionnalités d’une base de données relationnelle.
Le langage DAX a été développé en 2009 par l’équipe SQL Server Analysis Services de Microsoft dans le cadre d’un projet nommé Gemini. Dès sa conception, il s’agissait donc plutôt d’un outil destiné à travailler avec un modèle de données et non pas avec un tableur.
Le langage a ensuite été intégré dans la première version du complément Power Pivot pour Excel 2010.
Depuis, grâce aux succès des outils de Business Intelligence dans Excel et de Power BI Desktop, DAX est en constante évolution.
À ce titre, depuis la version 2016 d’Excel, Microsoft a incorporé Power Pivot directement dans le ruban et le langage compte aujourd’hui plus de 250 fonctions.
DAX est aussi utilisé dans Power BI Desktop, Azure Analysis Services et dans SSAS (SQL - Server Analysis Services).
Introduction et objectif du langage
DAX (Data Analysis Expressions) est le langage associé au modèle...
Le langage DAX par la pratique : présentation des données
Tout au long de cette section, nous allons utiliser les données du fichier Analyse_commerciale.xlsx.
Les données retracent les ventes de la société Elena sur les exercices 2021 et 2022. La société Elena est une entreprise commerciale spécialisée dans la vente de produits textiles : pulls, bonnets, vestes, gants.
La société comporte quatre magasins situés à Lyon, Mulhouse, Strasbourg et Dijon.
Depuis 2021, la société a investi dans de nombreuses campagnes publicitaires et il serait intéressant d’analyser l’impact de ses investissements sur l’évolution du chiffre d’affaires.
La résolution des différents exemples que nous allons aborder se trouve dans le fichier Analyse_commerciale_résolu.xlsx.
Les données sont décomposées en quatre tables :
-
La table T_Produits retrace les différents produits de la société, il s’agit d’une table de description. La clé primaire de cette table est N° Produit.
-
La table T_Magasins retrace les différents magasins de la société Elena, il s’agit d’une table de description. La clé primaire de cette table est N°_Magasin.
-
La table T_Clients retrace les différents clients de la société, il s’agit d’une table de description. La clé primaire de cette table est N°_Client.
-
Enfin la table T_Ventes retrace l’ensemble des ventes de la société, il s’agit d’une table de fait.
La clé primaire est N°_Facture, les colonnes N° Produit, N° magasin et N°_client sont des clés étrangères. Il s’agit respectivement des clés primaires des tables...
Préparation du modèle de données
Mise en place d’une table de calendrier
Une table de calendrier (appelée aussi table de date) est une table contenant une liste de dates ainsi que divers attributs de ces dates.
Cette table sera utile pour suivre l’évolution des ventes dans le temps. Elle sera notamment nécessaire pour utiliser les fonctions d’intelligence temporelles du langage DAX.
Nous aborderons les fonctions d’intelligence temporelles à la fin de ce chapitre.
La clé primaire de la table calendrier sera la date. Chaque colonne sera un attribut de cette date (mois, année, etc.).
Ouvrez le fichier Analyse_commerciale.xlsx.
Dans le ruban d’Excel, cliquez sur l’onglet Power Pivot, puis cliquez sur le bouton Gérer.
La fenêtre de Power Pivot s’ouvre.
Dans le ruban de Power Pivot, sélectionnez l’onglet Conception puis dans le groupe Calendriers, cliquez sur Table de dates puis sur Nouveau.
Une nouvelle table nommée Calendrier apparaît, elle retrace toutes les dates du 01/01/1950 au 31/12/2022 mais aussi, le Mois, le Numéro du mois, l’Année, le Jour de la semaine et le Numéro du jour de la semaine.
Cependant, les données des ventes de la société vont du 01/01/2021 au 31/12/2022.
Nous allons donc modifier la table pour supprimer les dates antérieures au 01/01/2021.
Dans l’onglet Conception du ruban, au niveau du groupe Calendriers cliquez sur le bouton Table de dates - Mettre à jour la plage (01/01/1950 - 31/12/2021).
La boîte de dialogue Plage de la table de dates apparaît à l’écran.
Au niveau de la Date de début, saisissez la date 01/01/2021.
Attention, la date de début doit être saisie en trois temps : jours, mois, année.
Cliquez sur le bouton OK.
Mise...
Les principes fondamentaux du langage DAX
Il existe deux manières d’utiliser le langage DAX dans Power Pivot :
-
créer des colonnes calculées,
-
créer des mesures (aussi appelées champs calculés).
Les colonnes calculées
Les colonnes calculées permettent d’ajouter de nouvelles colonnes à l’intérieur d’une table à l’aide de formules.
Elles sont établies au niveau de chaque ligne de la table et les valeurs sont stockées en mémoire dans le modèle de données.
Les colonnes calculées prennent donc en compte le contexte de chacune des lignes de la table.
D’expérience, les utilisateurs d’Excel ont tendance à vouloir créer de nombreuses colonnes calculées afin d’avoir un maximum d’informations dans une seule grande table. Cependant, les colonnes calculées sont stockées dans la table et cela consomme de la mémoire. Si les données deviennent trop importantes, des problèmes de lenteur peuvent apparaître.
En règle générale, n’utilisez pas de colonnes calculées pour agréger des données. Pour effectuer cette opération, utilisez plutôt une mesure, comme nous le verrons par la suite dans cette section.
Exemple de colonnes calculées : création d’une colonne conditionnelle
Nous allons créer une colonne conditionnelle au niveau de la table Ventes :
Si la quantité commandée est supérieure à 3, la valeur renvoyée sera grosse commande, sinon, la valeur renvoyée sera petite commande.
Dans Power Pivot, onglet Accueil - groupe Affichage, cliquez sur le bouton Vue de données puis sélectionnez la table Ventes.
Activez la dernière colonne de la table (Ajouter...
Les principales fonctions spécifiques au langage DAX
Certaines fonctions du langage DAX ressemblent aux fonctions d’Excel. On y retrouve certaines des fonctions statistiques, de manipulation de texte, conditionnelles, etc.
Pour vous faire une idée, allez dans l’onglet Conception de la fenêtre Power Pivot.
Dans le groupe Calculs, cliquez sur le bouton Insérer une fonction.
La boîte de dialogue Insérer une fonction apparaît à l’écran :
Parcourez la liste des fonctions disponibles.
Cependant, en langage DAX, les calculs se font toujours sur des colonnes ou des tables entières et fonctionnent dans le cadre d’un modèle de données.
C’est cette dernière spécificité qui fait que certaines des fonctions sont propres au langage DAX, dans cette section nous allons aborder certaines d’entre elles.
La fonction RELATED
La fonction RELATED fonctionne de manière semblable à la fonction RECHERCHEV d’Excel.
Elle utilise les relations définies par le modèle de données, les clés primaires et étrangères pour rechercher les valeurs d’une table dans une nouvelle colonne de la table active.
Sa syntaxe est :
Comme cette fonction nécessite un contexte de ligne, elle ne peut être utilisée que dans le cadre d’une colonne calculée.
Exemple : mise en place de la colonne Prix de vente unitaire dans la table Ventes
Les données relatives au prix de vente par produit se trouvent dans la table Produits :
Nous allons utiliser la fonction RELATED pour créer la colonne Prix de vente unitaire dans la table Ventes.
La recherche va se faire grâce à la clé primaire N°_Produit de la table Produits qui est aussi une clé étrangère de la table Ventes.
Dans la fenêtre...
L’utilisation des variables en langage DAX
Dans le but de rendre le code plus lisible, en DAX comme dans la plupart des langages informatique, il est possible d’utiliser des variables.
Vous retrouverez la résolution de cet exercice dans l’onglet Variables du fichier Analyse_commerciale_résolu.xlsx.
Imaginons que nous souhaitons retrouver le chiffre d’affaires des clients suisse de plus de 40 ans par catégorie de produit.
En DAX, il est possible d’écrire :
=CALCULATE([CA];
Clients[Pays] = "Suisse";
Clients[age] > 40
) La fonction CALCULATE a été utilisée pour filtrer la mesure CA selon deux critères : Clients[Pays] = "Suisse" et Clients[age] > 40.
Créez un nouveau tableau croisé dynamique à partir du modèle de données.
Placez le champ Catégorie de la table Produits dans la zone Lignes.
Créez une nouvelle mesure, affectez-la à la table Ventes et nommez-la CA_suisse_et_age_sup_40.
Dans la liste Catégorie, sélectionnez Nombre, réduisez les Décimales à 0 et cochez la case Utilisez le séparateur de milliers ().
Le résultat est le suivant :
À présent, en utilisant des variables, la même formule serait :
var clients_suisse =FILTER( Clients;Clients[Pays]="suisse")
var clients_sup_40_ans = FILTER(Clients;Clients[age] > 40)
return
CALCULATE([CA];clients_suisse;clients_sup_40_ans) Nous souhaitons retourner le chiffres d’affaire filtré par ces deux variables.
La fonction CALCULATE a été utilisée pour filtrer la mesure CA selon ces deux variables.
Notez qu’en utilisant...
Les fonctions d’intelligence temporelle
Les fonctions DAX d’intelligence temporelle (Time Intelligence) permettent de réaliser des calculs et des analyses qui répondent à un besoin fréquent en business intelligence : manipuler ou comparer des données selon un critère temporel.
Il sera ainsi possible à partir d’un tableau croisé dynamique, de comparer des données sur plusieurs périodes et de mettre en lumière des tendances afin d’établir des prévisions.
Le langage DAX offre un grand nombre de fonctions d’intelligence temporelle.
Dans le cadre de cet ouvrage, nous nous limiterons aux fonctions les plus utilisées.
En pratique, il y a deux grands types d’opérations utilisant ces fonctions :
-
La comparaison entre périodes.
-
Le calcul d’un total cumulé selon un intervalle précis : un total cumulé par mois, par trimestre, etc.
Si les fonctions d’intelligence temporelle permettent d’exécuter facilement des tâches qui étaient laborieuses à réaliser auparavant avec Excel, elles demandent un certain nombre de conditions afin de fonctionner correctement :
-
Le modèle de données doit contenir une table de dates (notre table Calendrier).
-
La clé primaire de la table de dates doit contenir un ensemble de dates contiguës couvrant chacun des jours de la période analysée.
-
Chacune des dates doit exister une et une seule fois dans la table de dates.
-
Vous ne pouvez pas ignorer de dates (par exemple, vous ne pouvez pas ignorer les week-ends ou les jours fériés).
La comparaison entre périodes
Pour réaliser cette opération, nous allons utiliser la fonction SAMEPERIODLASTYEAR.
Comme son nom l’indique, cette fonction permet de réaliser une comparaison sur une période...
Les indicateurs de performance clés (KPI)
Un indicateur de performance (ou KPI pour Key Performance Indicator) est une mesure ou un ensemble de mesures portant sur un aspect critique de la performance globale d’une organisation.
Son but est de donner une idée rapide et claire d’une situation afin d’aider les décideurs.
D’une manière générale, les KPI sont des indicateurs visuels dont le but est de déterminer si un objectif a été atteint ou non.
Power Pivot permet de mettre en place des KPI à l’intérieur d’un tableau croisé dynamique.
Mise en place d’un KPI à partir d’une mesure
La société Elena a fait de gros investissements publicitaires afin de mieux faire connaître ses produits. La responsable commerciale souhaiterait savoir si ces investissements ont été fructueux ou non. L’augmentation de chiffre d’affaires 2022 escompté devrait se situer entre +10 % et +20 % par rapport à l’exercice 2021.
L’idéal serait de pouvoir ventiler l’évolution du chiffre d’affaires par ville et par produit afin d’effectuer une analyse fine.
La résolution de ce cas pratique se trouve dans l’onglet KPI du fichier Analyse_commerciale_résolu.xlsx.
Première étape : mise en place du tableau croisé dynamique
Créez un rapport de tableau croisé dynamique vierge à partir du modèle de données dans une nouvelle feuille de calcul.
Dans le volet Champs de tableau croisé dynamique, dans la table Calendrier, cliquez sur Plus de champs, faites glisser le champ Année dans la zone Filtres.
Dans la table Magasins, cochez le champ Ville.
Dans la table Produits, cochez le champ Catégorie.
Dans la table Ventes, cochez...
