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

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 :

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 le menu 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 :

 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 :

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.

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.

 Cliquez sur le bouton OK.

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

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.

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

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.

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.

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