Editions ENI Livres | Vidéos | e-Formations
La création d’un modèle de données avec Power Pivot
Objectif
La troisième partie de cet ouvrage propose dans un premier temps de découvrir et de se familiariser avec l’élaboration d’un modèle de données grâce à Power Pivot dans Excel.
Puis, un cas pratique, la réalisation d’un outil de suivi budgétaire, sera proposé afin d’appliquer de manière concrète les éléments abordés.
Pourquoi créer un modèle de données dans Excel
Power Pivot était dans sa première version un simple complément d’Excel 2010. Bien que cette version offrait moins de possibilités qu’aujourd’hui, pour la première fois, les « power user » ont pu utiliser Excel de la même manière qu’une base de données relationnelle ; c’est-à-dire créer des relations de tables à l’intérieur d’un fichier Excel sans avoir à utiliser un nombre important de fonctions de recherche type RECHERCHEV ou INDEX/EQUIV.
Les anglo-saxons ont un terme assez explicite pour désigner un fichier Excel qui tente de répliquer les fonctionnalités d’une base de données relationnelles à partir de formules : EXCEL HELL
Malheureusement, durant mon expérience professionnelle, je me suis retrouvé dans cette situation à de nombreuses reprises. À chaque fois, au fur et à mesure que le fichier se développe, il devient impossible à maintenir, lent et à vrai dire, au bout d’un moment, plus personne ne comprend à quoi servent les centaines de formules complexes peuplant chacun des onglets.
La faculté de créer un modèle de données afin de fusionner des sources de données disparates contenant des centaines...
Les principes fondamentaux d’un modèle de données
Le modèle de données permet d’organiser les données à la manière d’une base de données relationnelle directement dans Excel. Il s’agit d’une composante de l’outil Power Pivot d’Excel.
Il sera ainsi possible :
-
de gérer et analyser un ensemble de données volumineux qui ne pourrait pas être contenu dans une feuille de calcul Excel traditionnelle ;
-
de créer des relations de tables afin d’afficher et d’agréger les données à la demande ;
-
de créer des tableaux croisés dynamiques non pas à partir d’une table unique mais à partir d’un ensemble de tables organisées et reliées entre elles.
La normalisation
D’une manière générale, la normalisation consiste à organiser les tables et les colonnes dans un modèle de données structuré afin de réduire les redondances et de préserver l’intégrité des données.
Les objectifs de la normalisation sont :
-
d’éliminer les données redondantes pour réduire la taille des tables et améliorer la vitesse et l’efficacité du traitement ;
-
de minimiser les erreurs et les anomalies dues aux modifications de données (insertion, mise à niveau ou suppression d’enregistrement) ;
-
de simplifier la mise en place de requêtes et de structurer la base de données pour une analyse significative.
Dans un modèle de données normalisé, chaque table doit avoir un objectif distinct et spécifique (informations sur les clients ou les fournisseurs, enregistrements d’une transaction, etc.).
Exemple
Vous retrouverez les données de cet exemple dans le fichier modèle_données.xlsx, la résolution de cet exemple se trouve dans le fichier modèle_données_résolu.xlsx.
Dans l’onglet table, le tableau suivant retrace les emprunts de livres de lecteurs d’une bibliothèque :
Les cellules grisées représentent les redondances présentes dans la table.
Cela peut sembler anodin, mais des inefficiences mineures peuvent devenir des problèmes majeurs à mesure que la taille...
Tableau croisé dynamique, modèle de données et contexte de filtre
Le concept de propagation de filtre
Dans un tableau croisé dynamique, les données sont synthétisées en fonction d’un contexte de filtre.
Le contexte de filtre est l’ensemble des filtres (ou "coordonnées") déterminés par les éléments du tableau croisé dynamique (filtres, segments, étiquettes de lignes et étiquettes de colonnes).
Dans un rapport de tableau croisé dynamique basé sur un modèle de données, si vous filtrez une table, cela aura un impact sur la table elle-même mais également sur les tables mises en relation et situées en aval de celle-ci.
Pour illustrer ce principe, nous allons à nouveau travailler avec le fichier modèle_données.xlsx ; la résolution de cet exemple se trouve dans le fichier modèle_données_résolu.xlsx, au niveau de l’onglet tcd.
Exemple
Nous allons construire un tableau croisé dynamique afin de retracer le nombre d’emprunts par titre de livre.
Dans l’onglet Accueil du ruban de Power Pivot, cliquez sur le bouton Tableau croisé dynamique.
La boîte de dialogue Créer un tableau croisé dynamique apparaît à l’écran.
Au niveau de l’emplacement, activez l’option Nouvelle feuille de calcul.
Cliquez sur le bouton OK.
Le volet Champs de tableau croisé dynamique apparaît sur le côté droit de l’écran. Il affiche les trois tables importées précédemment dans le modèle de données :...
Connecter Power Pivot à des données externes
Jusqu’ici nous avons travaillé avec des tables déjà existantes dans un fichier Excel. Cependant, à l’instar de Power Query que nous avons étudié dans le chapitre précédent, Power Pivot peut se connecter à un grand nombre de sources de données.
Cependant, il convient de faire la distinction entre deux cas de figure différents :
-
Les données ont été préalablement préparées grâce à Power Query : il suffit alors de les ajouter au modèle de données.
-
Les données externes sont déjà prêtes à l’emploi : dans ce cas il convient de créer une connexion entre la source de données et Power Pivot.
Bien souvent, dans ce deuxième cas de figure, les données proviennent d’une base de données relationnelle et sont donc déjà nettoyées et normalisées.
Connecter des données externes préparées préalablement avec Power Query
Les données externes préparées grâce à Power Query peuvent être directement importées dans le modèle de données de Power Pivot.
Dans l’éditeur Power Query, onglet Accueil - groupe Fermer, cliquez sur le menu déroulant du bouton Fermer...
L’actualisation des données
Lorsque vous chargez des données d’une source de données externe dans Power Pivot, vous créez une copie « statique » de la source de données. Si cette source est amenée à évoluer, il peut être opportun d’actualiser les données chargées dans Power Pivot.
Il existe plusieurs options pour actualiser les données.
Pour illustrer les différentes modalités d’actualisation des données, nous allons continuer avec la base de données Access modèle_données_access.accdb que nous avons importée dans Power Pivot dans la section précédente.
L’actualisation manuelle
Pour actualiser manuellement les données, dans l’onglet Accueil du ruban de Power Pivot, cliquez sur la liste déroulante du bouton Actualiser.
Deux options sont alors possibles :
-
Actualiser : actualise uniquement les données de la table active.
-
Actualiser tout : actualise toutes les tables chargées dans le modèle de données.
L’actualisation automatique
Vous pouvez configurer l’actualisation des données afin que celle-ci se fasse automatiquement lors de l’ouverture du fichier et/ou à intervalle régulier.
La mise en place d’une actualisation automatique se fait au niveau du ruban d’Excel....
Cas pratique : le suivi budgétaire
Présentation du cas pratique
Une bibliothèque municipale souhaite mettre en place un système de suivi budgétaire. Chaque année, un budget prévisionnel est alloué à l’acquisition de différents types d’ouvrages.
Durant l’année, les bibliothécaires se réunissent, discutent des nouveautés et passent des commandes. Ces commandes sont détaillées par type d’ouvrage : roman, policier, bande dessinée, etc.
Les données nécessaires à la réalisation de ce cas pratique se trouvent dans le fichier Budget.xlsx. Sa résolution se trouve dans le fichier Budget_résolu.xlsx.
A priori, nous pouvons définir quatre tables :
-
La table T_Budget avec le budget annuel réparti par section ou type d’ouvrage.
Il s’agit d’une table de dimension. La clé primaire est le champ Section.
-
La table T_Bibliothécaire, avec le nom et le prénom de chacun des bibliothécaires. La clé primaire est N° Bibliothécaire. Il s’agit d’une table de dimension.
-
Le table T_Commande contient la Date de commande, le numéro du bibliothécaire (N° Bibliothécaire) qui a passé la commande (il s’agit d’une clé étrangère héritée de la table T_Bibliothécaire). La clé primaire de cette table est N° Commande. Il s’agit d’une table de fait.
-
La table T_Détail commande détaille chaque Titre commandé ainsi que son prix (Montant). Elle contient deux clés étrangères :
-
Section héritée de la table T_Budget.
-
N° Commande héritée de la table T_commande.
La clé primaire est id détail. Il s’agit d’une table de fait.
Création des relations
Sélectionnez une cellule de la table T_Budget, dans l’onglet Power Pivot, cliquez sur le bouton Ajouter au modèle de données.
La fenêtre Power Pivot pour Excel s’ouvre. Elle contient les données de la table T_Budget.
Répétez cette manipulation pour les tables T_Bibliothécaire, T_Commande et T_Détail_Commande.
Les quatre tables ont été importées dans Power...