Quand il s'agit d'élaborer des tableaux de bord sur Excel, les tableaux croisés dynamiques sont quasiment obligatoires, et très utiles. Quand les tableaux croisés dynamiques montrent des limites sur Excel, notamment pour faire des calculs bien précis, ou encore des calculs qui impliquent plusieurs tableaux, on passe à Power Pivot ou Power BI.

Intéressons-nous à Power Pivot : que faut-il pratiquer en particulier pour pouvoir utiliser cet outil à son plein potentiel ?

‍

1. Des tables structurées

‍

Pour ceux qui ont utilisé des tableaux croisés dynamiques, vous aurez probablement remarqué qu'il s'agit principalement de faire glisser les colonnes du tableau source vers les champs ligne, colonne ou valeur, pour faire apparaître les calculs que nous cherchons à visualiser. Seulement voilà : cela suppose que nous avons déjà un tableau bien structuré et « prêt à l'emploi ». Dans la vie quotidienne, ce n'est pas si simple, et nous avons souvent besoin d'aller chercher les données à leur source, les transformer pour leur donner la structure en colonnes, et les envoyer vers Power Pivot. L'outil qui permet de faire tout cela est Power Query.

Le but et le fonctionnement de Power Query : il est capable de repérer les données d'un fichier, d'un dossier, d'une base de données en lignes, etc., tant que celui-ci est accessible par l'ordinateur. À partir de là, il est capable de les importer. Puis, c'est à nous d'exécuter les manipulations nécessaires afin de retravailler la structure des données, pour qu'elles deviennent utilisables par la suite sur Power Pivot. C'est bien là la partie de Power Query à pratiquer, afin d'utiliser Power Pivot dans les meilleures conditions. Ce n'est pas Power Pivot en soi, mais impossible d'utiliser Power Pivot avec des tables non structurées.

‍

2. Les relations entre les tables

‍

Sur Power Pivot, il y a deux types de relations possibles entre deux tables différentes, les autres étant tout bonnement refusées par le logiciel. Celles-ci sont les relations 1–1 (lire « un vers un ») et 1–* (lire « un vers plusieurs »).

Que représentent ces chiffres ? On considère qu'une table est reliée à l'autre via une colonne de chaque côté. Dans au moins un des côtés de cette relation, la colonne ne doit comporter aucun doublon. C'est donc dans ce contexte où on peut dire que la table du côté du 1 sert de dimension à la table du côté du plusieurs.

C'est bien grâce à ces liens que plus tard, on peut créer des mesures et des tableaux croisés dynamiques qui peuvent prendre en compte plusieurs tables simultanément.

Parfois, il est également nécessaire de faire plusieurs liens entre deux tables (exemple classique : relier un calendrier à des factures via leur date d'émission et via leur date de paiement). Dans ce cas-là, il ne peut y avoir qu'une seule relation active à la fois, et donc la deuxième relation sera en pointillés, activable via les mesures avec la fonction USERELATIONSHIP.

C’est donc la cardinalité de la relation qui définit ce que sont les faits et les dimensions.

‍

3. Tables de faits et tables de dimensions

‍

Maintenant, intéressons-nous aux types de tables : sur Power Pivot (et Power BI) il y en a 2, et il est important de les différencier. Ces types sont : les tables de faits et les tables de dimensions. Définissons ces deux types de tables complémentaires :

• Table de faits : ce sont des tables où se trouvent des observations, des événements, des faits de manière générale. Ce sont donc ces tables qui vont être la source et le centre de tous les calculs et les indicateurs que nous souhaitons extraire, voire visualiser. Par exemple, une table avec des factures qui inclut les montants et des dates de facture serait une table de faits.
• Table de dimensions : le rôle de la table de dimensions est d'apporter du contexte à la table de faits, et donc des informations complémentaires aux tables de faits. Une autre utilité pratique de la table des dimensions est aussi de servir de « pont » entre deux tables de faits, qui va donc les relier indirectement (dans 99% des cas, il ne faut jamais relier les tables de faits directement, sous peine de calculer des faux indicateurs). Un exemple très commun de table de dimensions est le calendrier, qui va permettre d'ancrer dans le temps des informations liées à des événements sur une table de faits.

Petite devinette : considérant ce qui vient d'être décrit, laquelle de ces deux tables est une table de faits, et laquelle est une table de dimensions ?

Ici donc, l'intérêt est de bien visualiser quelles tables de données correspondent à quel type de table, pour pouvoir monter un modèle de données fiable et performant.

Une fois les tables définies et reliées, il est temps de commencer à mesurer les indicateurs que nous cherchons à dégager.

‍

4. Les mesures

‍

Nous atteignons probablement le sujet le plus vaste et potentiellement le plus complexe de Power Pivot.

Voici le scénario : nous avons importé toutes les tables, dont les faits et les dimensions en passant par Power Query et nous avons créé tous les liens nécessaires entre les tables. Maintenant, nous souhaitons personnaliser à fond les calculs que nous souhaitons afficher dans nos tableaux croisés dynamiques par la suite. Nous sommes un niveau au-dessus de juste changer les paramètres des champs de valeur pour faire afficher la moyenne plutôt que la somme d'une colonne.

C'est à ce moment-là que nous utilisons le langage DAX (Data Analysis Expressions) pour préparer le calcul de tous nos indicateurs, et faire parler les données. Ces formules que nous créons s'appellent mesures et seront placées par la suite dans le champ valeurs de notre tableau croisé dynamique.

Comme tout langage de formules, il y aura forcément des formules plus souvent utilisées que d'autres, avec en star des fonctions : CALCULATE (voir l'article sur le top 12 des fonctions en DAX). Cette fonction permet notamment de modifier le contexte des calculs (et donc les rendre plus pointus). Cela nous mène au dernier aspect à bien comprendre sur Power Pivot : la notion de contexte.

‍

5. Le contexte

‍

À partir du moment où nous commençons à créer des mesures, notamment celles avec CALCULATE (pas seulement), il faudra apprendre à jongler avec le contexte.

Rapide définition du contexte : c'est l'environnement dans lequel une formule en DAX est évaluée. Il y a, sur Power Pivot, deux environnements qui existent : le contexte de ligne et le contexte (non pas de colonne) de filtre. Ces deux contextes deviennent alors contrôlables et permettent des analyses très pointues.

‍

a. Le contexte de ligne

‍

Commençons par le contexte de ligne : on peut imaginer cela comme la ligne considérée dans une table. Si nous faisons une analogie avec Excel, ce serait comme un calcul qui se répéterait ligne par ligne sur un tableau. Donc, dans le cas où nous créons une colonne calculée, nous créons un contexte de ligne automatiquement, puisqu'un calcul sera refait dans chaque ligne. Le contexte de ligne n'est pas seulement utilisé dans les colonnes calculées, mais aussi dans les mesures, notamment quand nous utilisons des fonctions itératives (qui font des calculs ligne par ligne) dont les fonctions comme SUMX, AVERAGEX, MAXX, FILTER (donc qui vérifient les tables ligne par ligne)… font partie.

‍

b. Le contexte de filtre

‍

Le contexte de filtre ne correspond pas directement aux lignes des tables mais aux filtres qui y sont appliqués. Ces filtres sont appliqués via plusieurs intermédiaires qui peuvent être : un segment sur Excel, le découpage par le champ lignes ou le champ colonnes d'un tableau croisé dynamique ou encore des conditions appliquées via la fonction CALCULATE.

C'est donc, en soi, comment, à partir d'une simple somme (par exemple), les valeurs sont bien découpées selon le champ lignes dans un tableau croisé dynamique, plutôt que d'avoir un total qui se répète à toutes les lignes. Il est donc important de bien visualiser les contextes de filtre et de ligne pour comprendre comment fonctionnent les mesures.

‍

C'est l'heure de l'entraînement !

‍

Ces 5 concepts sont ceux qui régissent Power Pivot, et Power BI jusqu'à un certain point. Si vous souhaitez réaliser d'incroyables tableaux de bord, avec des indicateurs avancés et le tout sur Excel, Power Pivot est un passage incontournable, de la même manière que Power Query. Tout entraînement sur Power Pivot doit donc, pour être complet, chercher à couvrir ces 5 concepts.

Très bon entraînement et nous vous souhaitons les plus beaux tableaux de bord !

‍