PowerPivot est un module complémentaire d’Excel, disponible pour la version Microsoft Office Excel 2010 dont l’objectif est de permettre à l’utilisateur, autonome, de réaliser une analyse rapide, mais complète, d’un ensemble de données. Ces données peuvent, et c’est l’intérêt de l’outil, provenir de sources diverses tant d’un point de vue format que structure.
Un projet décisionnel
L’analyse de données à titre informatif ou prospectif est actuellement couverte par des projets décisionnels qui, malgré une mise en œuvre non négligeable, permettent de fournir une analyse précise d’une situation ou des outils adaptés à l’analyse avancée. Un projet décisionnel se décompose en trois grandes parties : l’import des données dans un entrepôt, l’analyse et la restitution.
Import
L’ETL (Extract Transform Load) est le projet logiciel qui permet de collecter dans l’ensemble du système d’information (SI) de l’entreprise (bases de données de production, logiciel de comptabilité, fichiers Excel, documents texte, web services, etc.) les données nécessaires à l’analyse.
Cette première brique pose les bases du projet et permet d’alimenter un entrepôt de données unique. Cet entrepôt a pour seul objectif de permettre d’analyser les données collectées selon les besoins définis par les consommateurs du projet décisionnel.
Concrètement, l’ETL manipule les données du SI ; il les :
- - Collecte (connexion aux sources, requêtage, filtre, etc.)
- - Transforme si besoin (changement de format, ajout de valeurs calculées, etc.)
- - Corrige ou isole les enregistrements erronés (application de règles de validation métier)
- - Lie en établissant des relations entre elles.
Souvent négligé, ce sous-projet est fondamental pour le projet complet. Sans un entrepôt correctement alimenté et fiable, la suite du projet n’est pas exploitable.
D’un point de vue technique, Microsoft SQL Server est livré avec un service dédié à ce type de projet : Microsoft SQL Server Integration services.
Flux de données dans un ETL
Analyse
L’analyse passe souvent par la création d’un projet d’analyse multidimensionnelle, composé d’un cube et de ses dimensions.
Un cube permet de définir les mesures, c'est-à-dire les valeurs que l’on souhaite analyser, ventiler (chiffre d’affaire, montant des ventes, nombre de connexions, durée de sessions, etc.) et les dimensions, c'est-à-dire les axes selon lesquels on souhaite analyser ces mesures (client, temps, géographies, utilisateurs, etc.).
L’objectif du cube est de fournir un outil d’analyse performant et rapide sur l’ensemble des données. L’utilisateur exploitant le cube se connecte sur une seule source, dont la mise à jour des données est gérée et fiabilisée par l’ETL, cette source étant optimisée pour le requêtage sous plusieurs axes.
D’un point de vue technique, Microsoft SQL Server est livré avec un service dédié à ce type de projet : Microsoft SQL Server Analysis services.
Création d’un cube pour Analysis Services
Publication
La publication est l’action de mettre à disposition le résultat d’une analyse dans un format acceptable par l’utilisateur et, selon les besoins, d’envoyer automatiquement ce rapport directement vers l’utilisateur, par email notamment.
Dernière brique d’un projet décisionnel, c’est celle qui permettra de concrétiser le travail réalisé en amont. Ce sous projet a pour contrainte principale l’ergonomie, c'est-à-dire la capacité de représenter simplement des informations parfois complexes. Selon les besoins, il sera demandé de fournir des rapports statiques sans interactions, des rapports légèrement interactifs (permettant de développer des vues résumées en vues de détail) ou de fournir un outil d’exploitation plus fin.
D’un point de vue technique, Microsoft SQL Server est livré avec un service dédié à ce type de projet : Microsoft SQL Server Reporting Services. De plus, Microsoft Office Excel est communément utilisé pour réaliser l’exploitation avancée.
Restitution d’une analyse dans un rapport Reporting Services
Exploitation d’un cube dans Excel
PowerPivot : un outil transverse
Les différentes étapes d’un tel projet d’analyse ayant été détaillées, nous constatons que la mise en œuvre n’est pas triviale. Notamment, la création et l’alimentation de l’entrepôt de données sont une étape délicate dans la réalisation.
A ce jour, la mise en place de telles solutions passent :
- Soit par l’utilisation, parfois abusive, d’outil tel Excel ; l’obtention de données consolidées et exploitables amène les différents utilisateurs à dupliquer les documents Excel, remanier, fusionner ces documents avec des risques d’erreurs et de décalage par rapport aux données réelles,
- soit par la mise en place d’une solution de décisionnel avec les contraintes classiques d’un tel projet (coût, temps, maintenance) mais une fiabilisation et une richesse du résultat final.
PowerPivot propose une étape intermédiaire, destinée a priori à l’utilisateur désireux de réaliser à moindre coût une maquette d’un tel projet en incluant dans une application ‘utilisateur’ plus que ‘développeur’ (Microsoft Office Excel) les outils nécessaires pour réaliser les trois étapes d’un tel projet (ETL, Analyse, Restitution) sous réserve :
- de rester dans une utilisation traditionnelle et simple de ces outils
- d’accepter les contraintes d’une solution ‘document’ plus que serveur, c'est-à-dire dont l’ensemble des données et des définitions sont enregistrées dans un document Excel sur le poste client.
Import
L’import des données est réalisée via le module complémentaire ‘PowerPivot’, accessible depuis la barre d’outils.
Une fois le module complémentaire lancé, c'est-à-dire après appel de l’application PowerPivot via le plugin Excel, l’import est accessible dans le menu ‘Get External Data’ qui permet de se connecter à n’importe quelles sources de données exploitables (SQL Server, Access, Analysis Services, d’autres sources comme Oracle, SAP, Excel, fichier plat, rss, etc.).
Cet article se basera, à titre d’exemple uniquement, sur les données issues du recensement de la population française de 2006, disponibles sur le site de l’INSEE au format Excel.
Connexion et import des données
L’importation commence donc par la sélection d’une source de données, ici un fichier Excel.
Une fois la source choisie, un assistant permet de :
- - sélectionner les champs utiles
- - renommer les champs
- - filtrer les données par table (ou préciser directement une requête SQL pour les bases relationnelles)
Via cet assistant, nous retrouvons le premier rôle de l’ETL qui ne doit collecter que les informations utiles.
L’opération d’import peut alors commencer : les données sont concrètement collectées et insérées dans le document.
Une fois terminée, l’assistant informe du nombre de lignes et de tables transférées.
Cette étape est effectuée autant de fois que nécessaire …
Attention, PowerPivot reste un module complémentaire de Microsoft Excel, une application cliente : en fonction du poste client, l’outil peut ne pas être adapté aux traitements volumineux (ici une consommation mémoire supérieure à 1Go) !
Consolidation des données
Une fois les données importées, la fenêtre principale du module affiche autant d’onglets que de tables transférées. Notons que l’origine des tables disparait de l’environnement mais qu’il est toujours possible, via le bouton ‘Refresh’, d’actualiser les données en se connectant à nouveau sur l’ensemble des sources utilisées pour les mettre à jour.
Cet environnement correspond finalement à notre entrepôt de données. La suite consiste donc à consolider ce modèle en :
- liant les données entre elles via des ‘relations’ entre les tables
- renommant les champs et tables pour pouvoir exposer lors de l’analyse des noms métier cohérents
- calculant de nouveaux champs
- créant de nouvelles tables alimentées par copier/coller et/ou construites avec des champs calculés.
Voilà, l’étape la plus importante du projet est réalisée : l’entrepôt a été conçu par le choix des données à importer depuis différentes sources et consolidé par différentes opérations faites sur les données ou le modèle.
Analyse
L’analyse rejoint vite les outils classiques d’analyse d’un cube. En effet, PowerPivot inclut un moteur de manipulation de données multidimensionnelles (OLAP) qui en rend l’utilisation similaire.
Sans aller jusqu’à la définition explicite des mesures et des dimensions, PowerPivot propose la ventilation de toutes les données (tables et champs de l’entrepôt) selon elles mêmes : l’utilisation raisonnée de l’outil passera par la définition implicite des mesures et dimensions (« j’analyse un nombre d’habitants actifs et un nombre d’enfants scolarisés par département » décrit le nombre d’habitants ou d’enfants comme des mesures, le département comme une dimension).
L’accès se fait via le module PowerPivot en ajoutant au document Excel hôte un tableau croisé dynamique et/ou des graphiques.
L’objet inséré dans la feuille Excel est semblable à celui utilisé pour exploiter un cube :
Un rapide coup d’œil aux connexions montrent bien la présence d’une source de données locale (‘Embedded’) de type OLAP :
Il ne reste plus qu’à faire glisser mesures et dimensions sur la surface de travail pour débuter l’analyse des populations (actifs, chômeurs, retraités, scolarisés) de la région Nord-Pas de Calais, filtrées lors de l’import des données.
On note également l’apparition dans Excel 2010 d’un nouvel outil de filtrage des données, le ‘slicer’, indépendant de PowerPivot, qui permet ici de sélectionner dynamiquement les arrondissements sur lesquels l’analyse est faite.
En bref …
PowerPivot permet de réaliser toutes les étapes d’un projet décisionnel simple, de l’import des données à l’analyse dans un tableau croisé dynamique en passant par le retraitement de ces données dans l’entrepôt.
Sans remplacer un projet décisionnel construit sur des services complets (la suite décisionnel de SQL Server notamment), sans proposer une solution pérenne et complète d’analyse des données d’un SI, PowerPivot a l’avantage de proposer un premier outil accessible facilement, dont l’usage tendra a priori vers de l’analyse simple et ponctuelle ou le maquettage de solutions complètes.