Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Business Intelligence avec Excel
  3. La création d'un modèle de données avec Power Pivot
Extrait - Business Intelligence avec Excel Des données brutes à l'analyse stratégique (2e édition)
Extraits du livre
Business Intelligence avec Excel Des données brutes à l'analyse stratégique (2e édition)
2 avis
Revenir à la page d'achat du livre

La création d’un modèle de données avec Power Pivot

Objectif

La deuxiè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 de milliers de lignes dans un moteur analytique aussi évolué et accessible qu’Excel était révolutionnaire.

Avec la sortie d’Excel 2016, Microsoft a choisi d’incorporer Power Pivot directement dans le ruban d’Excel.

Cependant, contrairement aux concepts traditionnels d’Excel, où l’approche de développement de solutions est relativement intuitive, vous devez avoir une compréhension de base de la terminologie...

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...

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). 

images/2SOB27_1.png

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 le menu Accueil du ruban de Power Pivot, cliquez sur le bouton Tableau croisé dynamique.

images/2022_SOB02_13.png

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.

images/2022_SOB02_14.png

 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 :

images/2022_SOB02_15.png

 Cliquez sur la table T_livres et sélectionnez le champ Livre.

Excel a automatiquement placé le champ Livre dans la zone des Lignes.

 Cliquez sur la table...

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 et charger - Fermer et charger dans.

images/2SOB39_1.png

La boîte de dialogue Importation de données apparaît à l’écran.

 Sélectionnez Ne créer que la connexion.

 Cochez l’option Ajouter ces données au modèle de données.

images/2SOB39_2.png

 Cliquez sur le bouton OK.

Les données ajoutées au modèle de données se retrouvent directement dans Power Pivot.

images/2022_SOB02_29.png

Connexion à une base de données relationnelle...

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.

images/2SOB39_14.png

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.

 Dans le ruban d’Excel, sélectionnez l’onglet Données puis cliquez sur le bouton Requêtes et connexions.

images/2SOB39_15.png

Le volet Requêtes et connexions apparaît sur la droite de la fenêtre Excel.

 Au niveau du volet Requêtes et connexions, réalisez un clic droit sur la connexion à actualiser Access modèle_données_access.

 Dans le menu contextuel, cliquez sur Propriétés.

images/2022_SOB02_32a.png

La boîte de dialogue...

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.

images/2022_SOB02_34.png
  • 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.

images/2022_SOB02_35.png
  • 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.

images/2022_SOB02_36.png
  • 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...