Définir des relations entre tables dans Power Pivot

Pour définir des relations entre tables à l’aide de Power Pivot dans Excel, suivez ces étapes pour relier efficacement vos données issues de différentes tables, permettant ainsi des analyses complexes et des rapports croisés dynamiques.

Dans ce scénario, imaginez que vous ayez plusieurs tables de données, comme une table de ventes et une table de produits, et que vous souhaitiez les relier par un identifiant commun, tel que l’ID du produit.

Toutes les données nécessaires pour faire l’exercice se trouvent dans TP Définir des relations entre tables.

À l’issue des manipulations, vous aurez produit le classeur Excel PIB_Lab32.xlsx.

Connecter une source de données

Premièrement, commencez par ouvrir le classeur Excel Ventes.xlsx qui contient les données.

Comme indiqué dans l’objectif, ce fichier se trouve dans le sous-dossier Entrée du dossier TP Définir des relations entre tables

Ce classeur contient 9 onglets : Liste des Ventes, Liste des Clients, Liste des Produits, etc. Dans chaque onglet, il y a une seule table qui porte le nom de l’onglet : Ventes, Clients, Produits, etc. Au total, il y a donc 9 tables.

Ensuite, vérifiez que chaque table dispose d’une colonne pouvant servir de clé pour relier les tables entre elles, comme un identifiant unique d’article.

Activer Power Pivot

Ensuite, activez l’onglet Power Pivot dans le ruban Excel en allant dans le ruban Fichier, puis en cliquant sur le menu Options puis dans le menu Compléments.

Dans le menu déroulant Gérer (en bas), sélectionnez le choix Compléments COM et cliquez sur le bouton Atteindre.

Enfin, cochez la case à côté de Microsoft Office Power Pivot for Excel et cliquez sur le bouton OK.

Ouvrir Power Pivot

Ensuite, une fois l’onglet Power Pivot activé, cliquez dessus pour ouvrir la fenêtre de gestion de Power Pivot.

Puis, dans le groupe Tables, cliquez sur le menu Ajouter au modèle de données pour importer votre table dans le modèle de données Power Pivot.

Recommencez cette manipulation pour importer les 8 autres tables.

Et non, il n’est pas possible d’importer en une fois les 9 tables. Je le regrette aussi.
Toutefois, cela prend moins d’une minute pour le faire.

Afficher Diagramme

Ensuite, après avoir importé toutes vos tables dans Power Pivot, sélectionnez le menu Vue de diagramme dans l’onglet Accueil de Power Pivot.

Cela ouvre une fenêtre où vous pouvez voir les relations existantes, si elles existent, ou en créer de nouvelles.

Astuce. Vous pouvez aussi basculer de l’affichage Grille à l’affichage Diagramme, grâce aux icônes qui sont complétement en bas et à droite.

Agrandir les tables

Afin de mieux comprendre la suite, vous allez agrandir la table Ventes pour voir tous les champs de cette table.

Pour ce faire, avec la souris, tirez légèrement le bas de la table, jusqu’à faire disparaître l’ascenseur vertical. Dès que l’ascenseur a disparu, arrêtez.

Ensuite, répétez cette manipulation pour la table Clients.

Les autres tables ne contiennent pas suffisamment de champs pour être agrandis.

Disposer les tables

La table Ventes joue un rôle pivot car elle contient les valeurs numériques qui vont être analysées.

Elle contient aussi les identifiants qui vont permettre de relier les tables.

Dans le langage de la modélisation de données, cette table Ventes s’appelle la table de faits. Les autres tables s’appellent des tables de dimensions ou des tables de recherche. En effet, elles permettent de rechercher la valeur de l’identifiant conservé dans la table de faits.

Comme la table Ventes joue un rôle pivot, disposez cette table au milieu de la vue Diagramme, à l’aide de la souris.

Ensuite, disposez les 8 autres tables, autour d’elle et en cercle, avec la souris.

Créer des relations

Vous allez commencer à créer une relation entre deux tables.

Le principe est de sélectionner la table et la colonne dans votre première table qui correspond à la clé dans la deuxième table.

Assurez-vous que les colonnes que vous reliez contiennent des données correspondantes et uniques pour garantir l’intégrité de la relation.

Notamment, pour créer une relation entre la table Ventes et la table Clients, glissez le champ Client de la table Ventes sur le champ ID de la table Clients.

Power Pivot crée la relation et vous pouvez voir la relation que vous venez de créer : la relation est symbolisée par un trait orienté.

Par ailleurs, cette relation est quantifiée. Elle est de type 1 à Plusieurs. En effet, pour 1 Client, il existe plusieurs Ventes.

Ensuite, répétez le processus pour d’autres paires de tables, en créant des relations basées sur des clés communes pour établir des connexions entre toutes vos données dans le modèle de données.

Dans la suite des manipulations, le nom de la table est accolé au nom de la colonne qui est entre crochet. Par exemple Ventes[Article] correspond à la colonne Article de la table Ventes.

Notamment, créez les relations suivantes.

• Clients[Type clientèle] avec TypesClients[Code]
• Clients[Ville] avec Pays[Ville]
• Ventes[Article] avec Produits[ID]
• Ventes[Canal] avec Canaux[ID]
• Ventes[Fabricant] avec Fabricants[ID]
• Ventes[Gamme] avec Gammes[ID]
• Ventes[Remise] avec Remises[Code]

Vous remarquerez que toutes les relations sont de type 1 à Plusieurs. C’est normal.

Il est extrêmement fréquent que les relations entre la table de faits et les tables de dimensions, soient de type 1 à Plusieurs.

Vérifier les relations

Pour vérifier les relations, cliquez sur le ruban Conception de Power Pivot.

Dans le groupe Relations, cliquez sur le menu Gérer les relations : la fenêtre des relations s’ouvre.

Dans cette fenêtre, vérifiez que vous avez les relations suivantes :

Active	Table 1	Cardinalité	Direction du filtre	Table 2
Oui	Clients[Type clientèle]	*:1	vers Clients	TypesClients[Code]
Oui	Clients[Ville]	*:1	vers Clients	Pays[Ville]
Oui	Ventes[Article]	*:1	vers Ventes	Produits[ID]
Oui	Ventes[Canal]	*:1	vers Ventes	Canaux[ID]
Oui	Ventes[Clients]	*:1	vers Ventes	Clients[ID]
Oui	Ventes[Fabricant]	*:1	vers Ventes	Fabricants[ID]
Oui	Ventes[Gamme]	*:1	vers Ventes	Gammes[ID]
Oui	Ventes[Remise]	*:1	vers Ventes	Remises[Code]

Puis, cliquez sur le bouton Fermer.

Enfin, fermez Power Pivot, en cliquant sur la croix en haut et à droite.

Intérêts des relations

Une fois que vous avez fini d’établir toutes les relations nécessaires, vous pouvez utiliser ces relations pour créer un tableau croisé dynamique, un tableau croisé dynamique aplati, et des visualisations dans Excel qui tirent parti de votre modèle de données relationnel.

Un tableau croisé dynamique (Pivot Table) est un outil d’analyse de données dans Excel qui permet de résumer, analyser, explorer et présenter des données sous forme de tableau dynamique interactif.

Vous pouvez faire pivoter les champs de données pour visualiser les données sous différents angles et ainsi obtenir des perspectives différentes.

Par ailleurs, un tableau croisé dynamique aplati (Flattened Pivot Table) est un tableau croisé dynamique qui a été aplati pour le convertir en une disposition de données tabulaire standard.

Contrairement au tableau croisé dynamique traditionnel, qui a une structure hiérarchique où les champs sont organisés dans des colonnes et des lignes imbriquées, un tableau croisé dynamique aplati présente les données dans une disposition tabulaire simple sans structure hiérarchique.

Cela peut être utile pour certains types d’analyses ou de présentations de données où une structure tabulaire simple est préférée.

Dans la section suivante, vous allez voir un cas d’usage concret.

Utiliser les relations

L’objectif principal est de créer un tableau croisé dynamique qui répartit la somme des montants hors taxe selon les canaux de vente et les gammes de produits.

Pour utiliser les relations établies dans un tableau croisé dynamique, accédez au menu Gérer dans le groupe Modèle de données. Cela vous permet d’accéder au modèle de données.

Par ailleurs, dans Power Pivot, plusieurs menus sont divisés en deux parties : une partie supérieure et une partie inférieure avec une petite flèche pointant vers le bas.

Sur le ruban Accueil, cliquez sur la partie inférieure du menu Tableau croisé dynamique pour ouvrir un sous-menu offrant plusieurs options.

Puis, sélectionnez l’option Tableau croisé dynamique.

Dans la boîte de dialogue qui s’ouvre, conservez les paramètres par défaut, puis cliquez sur le bouton OK.

La feuille Feuil1 s’affiche.

Dans le volet des Champs de tableau croisé dynamique à droite, développez la table Ventes en cliquant sur la petite flèche à gauche et cochez le champ Montant HT.

Formatez ce champ en format monétaire en cliquant sur sa petite flèche, puis sur Paramètres des champs de valeurs, et enfin sur Format de nombre. Sélectionnez Monétaire et validez par OK deux fois pour obtenir le total général des montants hors taxe.

Ensuite, répartissez ce total par canaux de vente en cochant le champ Canal (sous Canaux) et le champ Gamme (sous Gammes) dans le volet des Champs de tableau croisé dynamique.

Puis, déplacez le champ Gamme dans la section Colonnes.

Vous obtiendrez ainsi un tableau croisé dynamique ventilant les montants hors taxe par canaux et gammes.

Il convient de noter que dans cet exemple pédagogique, toutes les relations établies n’ont pas été exploitées, ouvrant ainsi la voie à d’autres scénarios possibles.

Par exemple, il serait facile de généraliser cette approche pour ventiler les montants par clients, fabricants, remises, etc.

Apport des relations

Vous constaterez que vous n’auriez pas pu obtenir ce tableau directement dans Excel sans recourir à Power Pivot.

En effet, si vous aviez créé un tableau croisé dynamique directement à partir de la table des ventes sans utiliser Power Pivot, vous n’auriez pas eu accès aux relations entre les tables et, par conséquent, aux valeurs détaillées des tables de dimension.

En conséquence, ce tableau croisé dynamique aurait affiché les montants hors taxe répartis par les identifiants des canaux (comme V0001, V0002, etc.) et des gammes de produits (comme GS0121, etc.) sans montrer leurs valeurs correspondantes.

Enregistrer son travail

Puis, enregistrez votre classeur Excel avec le nom PIB_Lab32.xlsx

Enfin, fermez le classeur Excel PIB_Lab32.xlsx

La manipulation est terminée.