PowerPivot : Vers une BI en self-service

I – Avant-propos

Excel n’est plus à présenter, le tableur de Microsoft est là référence dans son domaine, très développé dans le monde professionnel.

Excel est un outil, qui bien utilisé, peut être très puissant, mais qui présente tout de même quelques limites, et restait accessible à des techniciens ou des utilisateurs chevronnés, avec notamment une connaissance du SQL ou VBA.

Depuis longtemps, tout le monde souhaitait des fonctionnalités avancés, plus de lignes, plus de fonctions pour les formules, plus, plus, plus…

La problématique : Par quoi commencer ? Et comment apporter ces évolutions ?

Microsoft nous apporte des éléments de réponse avec PowerPivot, que nous allons aborder dans ses fonctions de base, accompagnées de quelques explications techniques pour les plus pointilleux.

Objectifs et fonctionnalités de PowerPivot

Power Pivot est un module qui s’intègre à Excel et se présentant sous forme d’add-in, qui nous permet d’importer des données de différentes sources (Base de données SQL, ficher texte/Excel, base Access…), afin de toutes les regrouper en un seul jeu de données grâce aux relations.

En effet, PowerPivot a pour dessein de rapprocher l’utilisation des données de celle d’une base donnée SQL structurée, en permettant de créer des « jointures » entre nos « tables » représentées par les feuilles PowerPivot. Fini donc les RECHERCHEV à foison dans les fichiers Excel…

Certains se demanderont à quoi bon pouvoir importer des quantités de données de toute part, vu qu’Excel nous limite à un peu plus d’un million de lignes ?

Je répondrais simplement en précisant que PowerPivot pour Excel s’appuie sur la puissance du moteur SQL Serveur Analysis Services 2008, dans une version allégée (VertiPaq) afin de pouvoir être utilisé facilement sur des postes clients (sans avoir à installer un client SQL Server 2008).

Avec ce moteur Vertipaq, le nombre de lignes maximum autorisé dans les feuilles n’est plus de 1, mais plusieurs millions, simplement limité par la mémoire de la machine utilisée. En sachant qu’un poste standard peut facilement traiter des fichiers contenant des dizaines de millions de lignes, le tout en gardant des fichiers qui répondent instantanément lors de l’application de filtre à nos tableaux.

Pour aller plus loin, il est possible d’installer PowerPivot sur une instance SharePoint 2010 Enterprise, qui nous permettra de bénéficier des fonctionnalités de l’outil, et Excel Services prendra en charge l’affichage de vos fichiers PowerPivot.

Comme l’indique le schéma suivant, Power Pivot n’est pas réellement intégré à Excel, c’est plutôt une couche supplémentaire qui utilise les fonctionnalités Excel.

A ce titre, les macros VBA ne pourront pas être utilisées pour modifier des éléments dans les feuilles Power Pivot.

II - PowerPivot en détail

PowerPivot pour Excel :

• Fenêtre Power Pivot
  

L’add-in PowerPivot permet d’ouvrir une fenêtre supplémentaire qui comporte un ensemble de fonctionnalités supplémentaires.

Environnement de travail Excel et PowerPivot sont donc distincts, mais les tableaux croisés dynamiques ou graphiques créés à partir de vos données contenues dans vos feuilles PowerPivot se trouveront dans la fenêtre Excel.

• Import de tables
  

Fonction principale de PowerPivot, importer des tables de différentes sources de données via l’assistant qui est très intuitif. Les imports possibles vont de la base de données classique, que ce soit SQL Server, Oracle, Teradata ou autre, à un simple fichier texte, en passant par les fichiers Excel ou une base Access.

En bref, tout ce dont nous avions besoin.

Les étapes sont simples. Voyons un exemple d’import de données à partir d’une base SQL en sélectionnant SQL Server dans notre outils d’import « à partir de la base de données » :

L’assistant s’ouvre et demande les informations relatives à la connexion au serveur SQL :

Nous pourrons ensuite sélectionner les tables dont nous aurons besoin :

Nous remarquons que deux options nous sont proposées :

• Sélectionner les tables associées : si la base de données contient des clefs étrangères, toutes les tables en relation avec celle sélectionnée le seront à leur tour.
• Afficher un aperçu et filtrer : un outil qui ressemble à celui présent dans l’outils d’import de données dans SQL Server Management Studio en un peu plus évolué, car celui-ci permet, au sein même de l’aperçu, de filtrer nos données comme nous le ferions dans Excel, en décochant simplement les lignes ou les colonnes non souhaitées dans la combo box.

L’utilisation des données est simplifiée et permet à un utilisateur lambda, connaissant un minimum la structure de la base, mais pas forcément le langage SQL, d’importer des données sois même et de remplacer le technicien pour des requêtes simples.

Une fois nos tables sélectionnées et filtrées, l’outil importe les données souhaitées en créant une feuille PowerPivot par table :

Il faudra créer la relation entre nos tables, à savoir que si les clefs étrangères sont définies, l’action est automatique.

• Définition de relations
  

La définition des relations nous permet de lier nos tables, représentées maintenant par nos feuilles PowerPivot, et ainsi pouvoir utiliser toutes nos informations dans un seul et unique jeu de données, afin de créer des tableaux croisés utilisant l’ensemble de nos feuilles sans avoir à utiliser une multitude de RECHERCHEV.

Nous pouvons créer des relations entre nos feuilles Power Pivot quelle que soit leur provenance, à ce titre nous pourrons créer une relation entre des données d’un fichier Excel fourni par un utilisateur et celles contenues dans notre base de données SQL.

La fonction est disponible dans l’onglet « Conception » de Power Pivot, il faut sélectionner une des deux colonnes sur lesquelles nous voulons créer la relation :

Il faudra ensuite sélectionner dans les combos box de la seconde ligne le second champ permettant la relation entre nos tables :

• Langage DAX  (Data Analysis Expression)
  

Quand nos données sont importées dans le fichier Power Pivot, nous pourront les utiliser afin de créer nos rapports contenant, tableaux croisés dynamiques et toutes sorte de graphiques.

Pour répondre à des besoins supplémentaires, il est possible d’utiliser le langage DAX, pour créer des colonnes calculées, dérivées de nos données contenues dans notre fichier.

Le DAX contient des fonctions avancées en comparaison des possibilités des formules Excel :

• Fonctions de date et heure :

  On retrouve bien sûr toutes celles disponibles sous Excel, mais également des fonctions comme NEXTYEAR, PREVIOUSYEAR, NEXTMONTH, SAMEPERIODLASTYEAR, PARALLELPERIOD…

Fonction se calquant en majeure partie sur celles du MDX, pour les connaisseurs.

• Fonctions de filtrage :
  

  A mon avis, les fonctions qui manquaient le plus dans Excel.

  Elles nous permettrons de créer des colonnes calculées retournant des ratios, et combinées aux fonctions de date tel que NEXTYEAR ou PREVIOUS YEAR des croissances, chose beaucoup plus complexe sous Excel.

  Un exemple de la fonction CALCULATE sera plus parlant qu’un grand discours :

=( SUM(‘ResellerSales_USD'[SalesAmount_USD]))

/CALCULATE( SUM(‘ResellerSales_USD'[SalesAmount_USD])

,ALL(‘ResellerSales_USD’))

La formule ci-dessus retourne la somme des ventes d’un revendeur, divisé par la somme des ventes de tous les revendeurs grâce au filtre ALL(), pour au final nous retourner la part de marché de chaque revendeur dans notre portefeuille.

• Fonctions d’agrégation :
  

  Les fonctions de base (MIN, MAX, SUM, COUNT, AVERAGE…) déclinées dans des versions plus avancées comme COUNTBLANK, COUNTROW, AVERAGEX et bien d’autres.

Le langage DAX se rapproche de celui du MDX, et a pour vocation de remplacer des projets Analysis Services simple, par des rapports PowerPivot qui ne nécessiteront pas des techniciens qualifiés.

Pour une description plus approfondie du langage DAX et de ses fonctions vous pouvez consulter la documentation MSDN qui est, comme à son habitude, très complète.

Et en bonus, une vidéo de conférence Microsoft faisant la démonstration du langage DAX : http://powerpivot-info.com/post/744-video-enriching-your-bi-semantic-models-using-da

• Processeur VertiPaq local
  

Comme précisé en introduction, le moteur Vertipaq est une version dérivée de celui utilisé pour Analysis Services, qui lui permet de bénéficier de la puissance de l’outil.

Il compresse les données pour optimiser les performances lors d’application de filtres, en clair pour chaque colonne les valeurs distinctes et les lignes associées sont stockées en mémoire, pour un temps de réponse optimisé.

Les performances seront fonction de la redondance des informations dans chaque colonne, le temps de réponse sera beaucoup plus court sur une colonne contenant l’information de civilité d’un contact que sur la colonne d’adresse.

PowerPivot pour SharePoint :

• Intégration d’Excel Services
  

Les fichiers PowerPivot sont pris en charge par Excel Services, ce qui permettra aux utilisateurs n’ayant pas installé Power Pivot sur leur poste (et n’ayant pas un besoin absolu de le faire), de consulter vos créations.

Pour les plus curieux, le parcours d’une requête Excel est détaillé dans le schéma suivant, nous remarquerons à nouveau que PowerPivot est bien une surcouche au-dessus d’Excel et s’appuie sur le moteur Analysis Services.

• Galerie PowerPivot
  

PowerPivot possède une galerie de présentation des fichiers dédiée dans SharePoint, son visuel est plutôt agréable et la prévisualisation des feuilles de chaque fichier est réellement très pratique.

Le tout est accessible via Excel Services.

On se demandera juste pourquoi Silverlight est utilisé ici, ce qui rallonge un peu le chargement de la page.

• Actualisation des données PowerPivot
  

Le plus gros inconvénient des rapports sous Excel restait l’actualisation des données, où les utilisateurs devaient avoir des droits en lecture seule sur la base de données ou demander à mettre à jour les fichiers par des technicien, dans les 2 cas la manipulation peut prendre du temps, en particulier si la requête est longue à s’exécuter.

PowerPivot pour SharePoint permet, comme dans Reporting Services, de planifier l’actualisation des données de vos fichiers, afin que ceux-ci soient prêt à votre arrivée le matin (ou celle des utilisateurs), le tout paramétrable dans le service d’administration SharePoint.

En précisant que l’actualisation des sources Office n’est pas possible, il ne faudra pas oublier de supprimer les connexions Excel ou Access de vos fichiers Power Pivot afin de pouvoir utiliser cette fonctionnalité.

Les étapes pour la mise en place de la planification sont détaillées sur le site MSDN de Microsoft.

III – Utilisations et mise en œuvre de PowerPivot dans l’entreprise

Grandes entreprises

En environnement de grandes entreprises, où un système BI est en général déjà existant, PowerPivot possède les qualités suffisantes pour se faire une petite place.

La rapidité de mise place de rapports, peut servir pour des projets ponctuels, qui permettront de réaliser quelques économies de développement, et parfois d’éviter de toucher à la structure du système en place.

Toujours en raison du délai de création de rapports, PowerPivot peut être utilisé pour des fichiers temporaires ou des maquettes de projet.

Par exemple si des données doivent être intégrées à un système pour ajouter un axe d’analyse, le temps de développement est parfois conséquent alors que l’utilisateur souhaite voir ses informations au plus vite.

En attendant la fin des développements, des rapports PowerPivot temporaires peuvent être créés, avec 2 sources : les données du système actuel (en SQL) et un fichier plat contenant les informations supplémentaires, et ensuite créer des graphes ou laisser créer les graphes par les utilisateurs.

Pour répondre aux contraintes de volume d’utilisateurs et de sécurité d’accès aux données, il sera conseillé d’utiliser PowerPivot pour SharePoint, qui permettra également une actualisation planifiée des rapports.

Si un SharePoint est déjà utilisé dans la société, l’installation de la fonctionnalité est rapide, en revanche un projet de mise en place de SharePoint dans son intégralité pour de la publication automatisée est un peu plus complexe.

PME/PMI

Toutes les solutions mises en œuvre dans les grandes entreprises peuvent s’appliquer à des environnements plus restreints si des cas d’utilisation et les moyens le permettent.

Dans PME/PMI, que les données proviennent de fichiers plats ou d’une base de données SQL, PowerPivot peut être utilisé pour développer la totalité des rapports d’analyse de l’entreprise, pour potentiellement remplacer des rapports Excel complexes, comme des Dashboard ou Scorecard.

IV – Conclusion

Avantages et Inconvénients de PowerPivot :

• PowerPivot pour Excel
  • Avantages :
    • Gratuit.
    • Débridage du nombre de lignes utilisables dans Excel.
    • Réactif même avec un nombre conséquent de lignes grâce à l’utilisation du processeur VertiPaq.
    • Multiples sources de données possibles.
    • Création de relation entre nos données.
    • Prise en main rapide car toujours sous interface Excel.
    • Accès à des fonctions supplémentaires pour les champs calculés avec le langage DAX.
  • Inconvénients :
    • Utilisable sous Excel 2010 uniquement.
    • Nécessite un apprentissage du langage DAX, pour des utilisateurs ciblés qui ne sont pas forcément informaticiens.
    • Les utilisateurs devront tous avoir installé PowerPivot.
    • Pas de possibilité d’utiliser les macros VBA pour modifier la structure d’un fichier Power Pivot.

• Power Pivot pour SharePoint
  • Avantages :
    • Bénéficie de tous les avantages de SharePoint (sécurité, partage collaboratif …)
    • Galerie avec prévisualisation des feuilles du fichier pratique et au visuel agréable.
    • Planification de l’actualisation des fichiers
  • Inconvénients :
    • Les prérequis… notamment par la nécessité de disposer de licences SharePoint Enterprise.
    • L’utilisation de Silverlight pour la galerie qui la rend un peu lourde.

Peu d’inconvénients en général, ce qui est proposé est en général bien fait, intuitif dans un environnement familier. Nous pourrons juste nous poser la question des implémentations futures de l’outil.

Fonctionnalité à venir :

La vidéo de présentation du langage DAX utilise une mouture plus avancée de PowerPivot, qui tourne sous « Denali » (nom de projet du prochain SQL Server), nous pouvons apercevoir la fonctionnalité « Diagram View » qui semble particulièrement agréable et pratique, permettant de visualiser en un clin d’œil les relations entre nos données.

Les évolutions souhaitées :

Les évolutions présentées ici le sont purement à titre personnel, tirées des utilisations d’Excel durant mes expériences professionnelles, toutes autres propositions d’évolution m’intéressent.

• Concaténation de feuilles de même format :
  

Je m’explique : imaginons un fichier à compléter avec le détail des ventes de la semaine est envoyé à une dizaine de personnes.

Une fois la part du travail effectué par les intéressés, ceux-ci renvoient le fichier au responsable qui centralise le tout. Ce responsable sera chargé de concaténer tous ces fichiers en seul, à la main avec des copier/coller ou avec une macro VBA développée à cet effet si les compétences existent dans l’entreprise.

Une option dans power pivot lors de l’import de données, qui permettrait de sélectionner plusieurs fichiers en précisant que ceux-ci sont du même format et qu’une concaténation est possible, pourrait faciliter la vie de notre responsable.

• Sécurité :
  

Pour le moment il est impossible de protéger l’accès à une cellule, à une feuille ou à une connexion, ce qui limite les possibilités de diffusion de fichier lors d’une utilisation de PowerPivot dans Excel uniquement.

Sera-t-il possible de le faire dans une version future ?

• Une alternative aux macros VBA :
  

Power Pivot étant une surcouche à Excel, les macros VBA ne peuvent modifier ou utiliser les données d’un fichier Power Pivot, nous ne pourrons donc plus automatiser facilement des tâches.

Microsoft proposera-t-il une fonctionnalité similaire à l’avenir ?

Conclusion

PowerPivot est une évolution majeure d’Excel qui démocratise le domaine de la BI en la rendant « Self-Service », car, pour des requêtes simples, l’utilisateur possédant les connaissances suffisantes du schéma des bases de données, pourra construire via l’assistant sa propre requête et importer les données filtrées dont il a besoin, sans passer par un technicien du pôle de développement BI ou technique, et ainsi construire en totale autonomie ses propres rapports et les mettre à disposition des autres utilisateurs.

L’outil est bien développé, les manipulations sont intuitives, et des actions à la base compliquées, deviennent accessibles à un plus grand panel d’utilisateur.

De plus l’outil étant gratuit, faire quelques essais et les présenter à votre direction ne vous prendra que peu de temps.