Tutoriel PowerPivot pour Excel – Partie 1/2 : Construction d’un modèle PowerPivot à partir de plusieurs sources de données

Nous avions présenté sur ce blog PowerPivot, nous vous proposons désormais de le tester par vous même au travers d'un tutoriel complet en deux parties

Ce tutoriel a pour but de construire un model PowerPivot se fondant sur plusieurs sources de données : Base Access, Fichier Excel et fichier Texte ; de créer des relations entre ces différentes données et enfin de créer des colonnes calculées pour enrichir le model.

Scenario

Une personne d’une entreprise souhaite réaliser un tableau de bord croisant à la fois des données provenant des systèmes informatiques (Ventes, CRM) avec d’autres données non structurées (Budget Excel, fichier Texte RH). PowerPivot, add-in d’Excel 2010 est donc l’outil idéal pour réaliser cette opération. Voici une liste des données qui sont à sa disposition :

· Base Access : ERP.accdb

Cette base de données Access contient des informations sur les clients de l’entreprise (CRM) ainsi que sur les ventes 2009 (CA, COGS, Unités) et une table calendrier.

· Fichier Excel : BUDGET.xlsx

Ce fichier Excel contient le budget de l’entreprise pour 2009 par client et par commercial (CA, Quantité)

· Fichier Texte : RH.txt

Ce fichier texte a été fourni par le service RH et contient des informations plus complètes sur les commerciaux (Nom, Ville, Salaire)

Toutes ces données vont devoir être importées dans PowerPivot avant d’être utilisées dans la construction de tableaux de bord.

Intégration des données Access

· Ouvrir Excel 2010

· Aller sur l’onglet PowerPivot

· Cliquer sur « PowerPivot Window » pour accéder à l’écran de chargement des données. Une nouvelle fenêtre apparait.

· Nous souhaitons charger des données d’une base ACCESS donc cliquer sur « From DataBase » et sélectionner « From Access »

· Cliquer sur parcourir pour aller chercher le fichier ERP.accdb

· Laisser les champs « Nom Utilisateur » et « Mot de passe » à vide

· Tester la connexion en cliquant sur le bouton correspondant en bas à droite de l’écran ; le message suivant doit alors apparaître :

· Cliquer sur Ok pour fermer la fenêtre de test de connexion

· Cliquez sur Suivant

· Choisir « Sélectionner les données à importer dans une liste de tables et de vues »

· Cliquer sur « Suivant »

· Sélectionner les 3 tables sources

· Renommer la table « CRM » avec un nom convivial « Clients »

· Cliquer sur « Terminer »

· Cliquer sur « Fermer »

Les données des 3 tables de la base Access ont été importées dans PowerPivot. Ces données sont réparties dans 3 onglets de la fenêtre PowerPivot, un par table (Calendrier, Clients, Ventes).

Intégration des données Excel

Nous allons maintenant enrichir notre application PowerPivot en intégrant les données du fichier Excel BUDGET.xlsx

· Toujours dans ce même écran « PowerPivot Window », cliquer sur « From Files »

· Sélectionner « From Excel »

· Cliquer sur parcourir pour aller chercher le fichier BUDGET.xlsx

· Tester la connexion en cliquant sur le bouton correspondant en bas à droite de l’écran ; le message suivant doit alors apparaître :

· Cliquer sur Ok pour fermer la fenêtre de test de connexion

· Cliquez sur Suivant

· Cliquer sur Terminer

· Cliquer sur « Fermer »

Les données de Budget sont désormais chargées dans notre application PowerPivot. Un nouvel onglet « Budget » est apparu dans la fenêtre PowerPivot.

Intégration des données Texte

Nous allons maintenant intégrer les données du fichier texte RH.txt

· Toujours dans ce même écran « PowerPivot Window », cliquer sur « From Files »

· Sélectionner « From Text »

· Cliquer sur parcourir pour aller chercher le fichier RH.txt

· Bien sélectionner le séparateur de colonnes « Point-virgule (;) »

· Cocher « Use first row as Column Headers »

· Cliquer sur Terminer en bas à droite de l’écran.

· Cliquer sur « Fermer »

Création des relations

Toujours dans cette même fenêtre PowerPivot, sélectionner l’onglet « Table »

· Cliquer sur « Manage Relationship »

On voit que les relations existantes dans la base ACCESS ont été importées automatiquement dans PowerPivot. Reste donc à créer les relations pour relier les données des fichiers Excel et Texte au model.

· Cliquer sur Créer

· Sélectionner les mêmes valeurs que sur l’image ci-dessus

· Cliquer sur Créer

· Renouveler l’opération pour créer les relations suivante :

o Budget - Client No / Clients - Client No

o Budget- Commercial No / RH - Commercial No

o Ventes – Commercial No / RH – Commercial No

· Cliquer sur «Fermer»

Création d’une colonne calculée

Notre application PowerPivot est maintenant prête à l’emploi. Cependant, avant de commencer à exploiter ces données en créant des tableaux croisés dynamiques, nous allons d’abord l’enrichir en créant une colonne calculée, « Marge ».

· Cliquer sur l’onglet « Ventes » dans la fenêtre PowerPivot

· Dans le tableau, cliquer sur « Ajouter une nouvelle colonne »

· Saisir la formule suivante pour cette nouvelle colonne : «  ='Ventes'[CA]-'Ventes'[COGS] »

· Renommer la colonne « Marge »

La construction du model de l’application PowerPivot est donc maintenant terminée. Dans le prochain article nous aborderons comment créer des tableaux de bord à partir de ce modèle…à suivre…. Vous pouvez récupérer gratuitement les fichiers d'exemple en nous écrivant..

Pour en savoir plus sur PowerPivot :

Site officiel : https://www.powerpivot.com

Webcast en français sur PowerPivot sur ce blog

Comments

• Anonymous
  May 12, 2010
  J`adore cette version 2010,en tout point.

• Anonymous
  May 13, 2010
  Quelque chose qui est peut être intéressant pour moi. Je ne l'ai pas encore