PowerPivot

Depuis quelques années sont apparues trois tendances dans la Business Intelligence. Il s’agit de la BI d’entreprise, celle d’équipe et la BI personnelle. PowerPivot figure dans les deux dernières tendances. En effet, associé à SharePoint, il permet la BI d’équipe. Vous pourrez publier les rapports en mode web et bénéficier d’une interface d’administration centralisée pour toutes les applications créées avec PowerPivot. Lorsqu’il est ajouté uniquement à Excel, la Business Intelligence personnelle, encore appelée « Self-BI » est possible. Nous nous pencherons dans un premier temps sur la Self-BI, c’est-à-dire, PowerPivot pour Excel et ensuite nous passerons en revue PowerPivot pour SharePoint.

1.1 Présentation

PowerPivot est un outil simple de la BI. Il est utilisé par les personnes ayant des profils moins techniques. C’est une extension ajoutée à Microsoft Excel qui est un logiciel familier, intuitif et évolutif. Il ne déroge pas aux qualités d’Excel. Il permet aux utilisateurs fonctionnels de créer eux-mêmes des rapports, solutions de reporting. PowerPivot élargit les possibilités d’analyse de données d’Excel, il permet d’obtenir rapidement des informations utiles à partir d’importantes quantités de données.

PowerPivot est gratuit pour Excel 2010. Il existe à ce jour deux versions, la première avec SQL Server R2 et la seconde avec SQL Server 2012 (Denali).

1.2 PowerPivot pour Excel

1.2.1 Prérequis

Afin d’installer PowerPivot, vous devez avoir déjà installé sur votre poste Excel 2010 et les composants partagés d’Office. Vous devez également disposer du Framework 4.0 de Microsoft .NET et de Microsoft Visual Studio 2010 Tools pour Office Runtime.

1.2.2 Installation et import d’une source de données

Après avoir pris soin de vérifier que tous les prérequis étaient installés, nous téléchargeons de PowerPivot V2 à l’adresse suivante :

https://www.microsoft.com/downloads/fr-fr/details.aspx?familyid=e081c894-e4ab-42df-8c87-4b99c1f3c49b. Nous procédons ensuite à son installation.

Une fois installé, allez dans MS Excel 2010, vous verrez apparaître un nouvel onglet « PowerPivot » rajouté à la barre de menus.

Nous allons passer au chargement d’une source de données. Pour ce faire, nous allons utiliser la source « contoso V2/store.xls » disponible à cette adresse :https://powerpivotsdr.codeplex.com/

Cliquez sur « PowerPivot Windows », vous obtiendrez ceci

Nous devons définir la source de la connexion ; elle peut être soit de la base de données SQL Server, ODBC, Access ou des fichiers plats (txt ou excel). Cliquer sur « obtenir de données externes à partir d’autres sources ».Dans notre cas nous choisirons Excel comme source.

Description: E:\BI\Microsoft BI\Article-Supinfo\img\choix_vues.PNG

Description: E:\BI\Microsoft BI\Article-Supinfo\img\affichage_vue.PNG

Nous venons de voir comment il était facile d’installer PowerPivot pour Excel et de charger des sources de données. Cependant, il n’est pas aisé de gérer ces rapports produits, les sources de données dans un cadre professionnel c’est-à-dire dans un environnement collaboratif. Pour y arriver, nous avons PowerPivot pour SharePoint.

1.3 PowerPivot pour SharePoint

PowerPivot pour SharePoint permet une prise en charge de la gestion des documents, de la collaboration et du traitement côté serveur pour les classeurs PowerPivot que vous publiez dans SharePoint grâce au service Excel services. En effet, les classeurs publiés sont mis à la disposition des utilisateurs de votre entreprise selon des règles de sécurité défini au sein de celle-ci, en un mot la gestion de documents est assurée. Notons également que les composants serveur PowerPivot fournissent un traitement des requêtes côté serveur concernant les données PowerPivot dans les classeurs Excel accessibles à partir des sites SharePoint permettant ainsi aux utilisateurs n’ayant pas PowerPivot sur leur poste de pouvoir consulter ces classeurs.

1.3.1 Prérequis

Comme toute installation, vous devez avoir les droits d’utilisateurs pour la démarrer l’installation. Le poste devra appartenir à un domaine. SharePoint Server Enterprise Edition doit être utilisé et mis à jour avec le SP1 afin de bénéficier des dernières fonctionnalités apportées par Denali (SQL Server 2012).Veuillez à ce qu’il n’y ait pas d’instance nommé « PowerPivot » sur le poste.

1.3.2 Installation de PowerPivot pour SharePoint

Insérez ou ouvrez le dossier contenant les fichiers d’installation de SQL Server puis lancez l’installation. Choisissez les options d’installation selon votre cas, s’agit-il d’une nouvelle installation ou d’un ajout de fonctionnalités à une installation existante. L’installation fera en standalone, tous les composants seront installés sur un seul poste ou en cluster.

Validez les règles de support, choisissez l’édition entreprise ou celle d’évaluation du produit et le cas échéant entrez la clé du produit. Mettez à jour les fichiers d’installation si vous êtes invité à la faire.

Une fois passée cette étape, dans les rôles d’installation, sélectionner SQL SERVER pour SharePoint, vous pouvez ajouter une instance du moteur de base de données.

Continuez l’installation en acceptant les paramètres par défaut et configurez le compte de SQL Server Analysis Service.

Terminez cette installation à l’aide des boutons « Next ».

Vous devez finaliser l’installation en configurant PowerPivot. Vous avez trois méthodes pour le faire :

· Configurer ou réparer PowerPivot pour SharePoint (outil de configuration de PowerPivot)

· Configuration de PowerPivot dans l’Administration centrale

· Configuration de PowerPivot à l’aide de PowerShell

Suivez les taches à réaliser qui sont dans la colonne de gauche et renseignez les paramètres lorsque cela vous est demandé jusqu’à cliquer sur le bouton « run »

.

Si vous souhaitez avoir la possibilité d’exécuter des versions antérieures et récentes des classeurs PowerPivot, vous devez donc installer le fournisseur OLE DB Analysis Services livré avec SQL Server 2008 R2 sur un serveur SQL Server 2012 PowerPivot pour SharePoint.

Après avoir passé en revue les cas d’installation, allons à la découverte de PowerPivot v2.

PowerPivot v2

La première version dont les fonctionnalités majeures étaient l’import de table de différentes sources, la définition des relations, le processeur VertiPaq (qui offre un traitement rapide et orienté colonne des datasets Powerpivot publiés dans une batterie de serveurs SharePoint et un accès en mode hors connecté pour la génération ou la modification de données PowerPivot à grande échelle dans un classeur Excel), certaines fonctions de calculs, filtrage, de date et d’agrégation mais également le langage DAX (Data Analysis Expression), a permis d’améliorer les possibilités d’analyse d’Excel. Cependant, la version 2 fournit de nouvelles fonctionnalités. Les principales seront présentées.

2.1 Diagram View

Singulièrement agréable et pratique, il permet de visualiser en un clin d’œil les relations entre les tables. Pour ce faire, vous trouverez un nouvel onglet « PowerPivot » dans le ruban de votre fenêtre Excel 2010, cliquez dessus.Ensuite, il vous faudra cliquer sur le bouton « Fenêtre PowerPivot » comme représenté ci-dessous :

Une nouvelle fenêtre spécifique aux outils PowerPivot s’ouvrira. Vous retrouvez alors le bouton « Vue de diagramme » vous permettant d’afficher la vue de votre diagramme des différentes tables.Bien entendu, il vous faudra avoir ajouté au préalable une connexion vers votre source de données (fichiers plats, SQL Server, Access, ect.)

2.2 Création de KPI

Il permet de définir et d’enregistrer des KPI, des objectifs et des tendances aux mesures. Grace à cet assistant l’affichage des icônes et limites des KPI pourront être paramétrés.

2.3 Tri par colonne

Vous avez la possibilité de trier une colonne par une autre colonne ; Par exemple, on peut trier les libellés de mois par colonne « numéro du mois » qui attribue à chaque mois son numéro.

2.4 Les slicers

Le Slicer, vous permettra de découper notre analyse très facilement et visuellement (de manière dynamique)

2.5 Les autres fonctionnalités

·Mode avancé: Les fonctionnalités avancées sont maintenant disponibles dans un onglet séparé. Elles permettent de créer ou de modifier des perspectives, de synthétiser une colonne numérique par une fonction d’agrégation.

·Table de temps: permet de définir une dimension comme « temporelle » et de bénéficier ainsi de fonctions dédiées.

·Perspectives: permet de définir des « vues » différentes sur le modèle. c’est équivalent aux perspectives d’Analysis Services

·Hiérarchies: permet d’organiser les membres d’une dimension de manière hiérarchique. Les hiérarchies parents / enfants sont notamment gérées.

Pour plus de détails vous avez ce lien : https://technet.microsoft.com/fr-fr/library/hh272053.aspx

2.6 Les nouvelles fonctions DAX

2.6.1 Des fonctions statistiques

·DistinctCount : Fonction permettant de compter le nombre de valeurs différentes d’une colonne.

·Ecart type : permet de mesurer la dispersion d’un ensemble de données.

·TopN : permet d’afficher les N premières lignes de la table spécifiée.

·Variance (VAR.S, VAR.P,…) : Permet de calculer la variance d’un ensemble ou d’un échantillon d’une colonne.

2.6.2 Des fonctions de relations

  • LOOKUPVALUE: Fonction de recherche informationnelles pour rechercher une certaine valeur dans une table (LOOKUPVALUE()).
  • Relation multiples : permet de créer plusieurs relations entre tables, contrairement à la première version.
  • Relations parents-enfants: Fonction parent-enfant informationnelle (PATH(), PATHCONTAINS()).
  • SWITCH: Fonction logique pour fournir une sélection à choix multiple.

2.6.3 Des fonctions de présentation de données

  • Changement des types de données: modifier le type de données pour une colonne qu’elle soit calculée ou non.
  • Formats nombre de mesures: définir le type de format de nombre (comme les devises).
  • Description: Ajouter des descriptions (tableaux, mesures, indicateurs de performance clés).

PowerView

3.1 Présentation

Expérience d’exploration interactive et visuelle des données sémantiquement modélisées.

Power View est un outil de création de rapport qui s’utilise directement dans votre navigateur via le portail SharePoint.

On retrouve dans Power View un environnement proche d’Excel, avec la possibilité de se connecter à SSAS (SQL Server Analysis Services (cube tabular) pour pouvoir jouer avec ces données.

L’avantage de ce nouvel outil, c’est qu’il rend la BI (partie reporting) accessible à tous que ce soit en interne (pour les employés) ou en externe (pour les clients)

Ils pourront travailler sur leurs différents jeux données très facilement, grâce à la visualisation dynamique en temps réelle des données et ainsi construire rapidement des rapports à des fins d’analyses et de présentations, puisqu’il est possible d’enregistrer les rapports sous PowerPoint.

* Self-Service BI

* Rapport Ad Hoc

* Utilisation sous IE, SharePoint avec Silverlight

* Environnement similaire à Excel

* Visualisation des données dynamiquement

* Analyse et Datamining

* Basé sur SSRS (SQL Server Reporting Services qui fournit une gamme complète d’outils et de services prêts à l’emploi pour aider à créer, déployer et gérer des rapports)

* PowerView crée un fichier .rdl spécial et l’encapsule dans un .rdlx

3.2 Prérequis

* SQL Server 2012 Edition BI minimum

* SSAS tabular

* SharePoint Server 2010 Entreprise

* SharePoint 2010 SP1

* Installation Batterie de Serveur

* Compte de Domaine

* Silverlight pour l’affichage de PowerPivot et PowerView dans SharePoint 2010.

3.3 Interface

3.4 Fonctionnalités & Exemples d’utilisations

PowerView propose de nombreuses fonctionnalités afin de pouvoir créer au mieux des digrammes les plus pertinents possible. Dans cette partie, nous allons vous présenter l’essentiel :

3.4.1 La vue dynamique

La création de plusieurs diagrammes ou tableaux dans un même rapport permet de constater qu’ils sont tous liés entre eux. Ainsi, le fait de vouloir visualiser des données précises sur un tableau modifiera dynamiquement l’affichage des données dans les autres tableaux. Voici un exemple :

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4e92c425.PNGIci, on peut observer les interactions entre les deux diagrammes à barres (la répartition des types d’incident du diagramme du bas par rapport à l’ensemble des types d’incidents du diagramme du haut)

3.4.2 Les filtres

La notion de filtre est toujours présente dans la partie droite du rapport. Les filtres permettront de définir plus précisément les données à utiliser (ex : afficher uniquement l’année 2011 et non 2010…)

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4e7e0baf.PNGBien que les filtres aient le même fonctionnement que dans Excel, les développeurs ont pris soin de les rendre plus ergonomique et simple d’utilisation avec à un simple double clic sur le petit icone carré (voir les deux partie entourée ci-dessus). Il y a bien sûr toujours la possibilité de définir une valeur (= « XXXX ») et de mettre plusieurs conditions.

3.4.3 Les slicers et mosaïques

On a également un Slicer ou la mosaïque, qui va permettre de découper notre analyse très facilement et visuellement (de manière dynamique)

3.4.4 Les tuiles

Tiles (filtre sous forme de tuiles) : système de filtrage avec une visualisation comme pour les filtres sauf qu’il permet comme vous pouvez le voir ci-dessous d’afficher des images directement dans le rapport lorsque celles-ci ont été rattaché dans la base de données tabulaire.

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4e800920.PNG

3.4.4.1 L’Axe de lecture

Dans PowerView vous avez la possibilité de créer un digramme à bulles dans lequel vous devrez définir les différents axes d’analyse à utiliser : en abscisse, en ordonné, la série, la lecture. La nouveauté ici c’est ce dernier, l’axe de lecture ou « Play axis » pour la version anglaise. Il permettra le plus souvent de lier des données temporelles afin de faire évoluer le contenu de votre diagramme à bulles en fonction de la position du curseur. Ceci toujours dans l’optique de créer de l’interactivité entre l’utilisateur et le rapport.

Dans le cas ci-dessous, ce nouvel axe permettra de jouer dynamiquement avec la courbe d’évolution des incidents:

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4e8f15ed.PNGRapport du nombre d’incidents, nombre d’entreprises et la somme des montants par rapport aux types d’incidents dans le temps

3.4.5 Exporter un rapport PowerView en PowerPoint (PPT)

Il est possible d’enregistrer l’état de notre rapport au format PPT et ainsi utiliser rapidement les diagrammes que nous aurions construits avec PowerView mais cela ne s’arrête pas là ! En effet, lorsque l’on ouvre notre fichier PPT et alors que l’on retrouve très fidèlement les derniers états par slide, nous sommes forcé de constater qu’à partir du moment où nous passons en affichage de présentation (plein écran) un bouton apparait en bas à droite du slide « click to interact » :

En cliquant sur le bouton, vous pourrez alors interagir avec le mode « Reading » de Power View. Cela ne vous permettra pas de modifier la structure des slides mais de pouvoir jouer avec les données (filtres, visualisation dynamique) précédemment créées pour votre rapport PowerView. Il faut savoir également, que chaque vue est séparée par un contrôle Silverlight pour chaque slide.

Création d’un rapport PowerView à partir d’un projet SSAS Tabular

4.1 À partir d’un fichier Excel-Power Pivot

o Import du fichier Power Pivot dans un nouveau projet SSAS Tabular

§ Ouvrir VS2010 > Fichier > Nouveau > Projet…

o Dans le Menu latéral > Business Intelligence > Analysis Services

o Sélectionner « Import à partir de PowerPivot »

o Renommer votre projet et le nom de la solution…

o Valider en cliquant sur OK

o Sélectionner l’emplacement de votre fichier PV

o Puis faire Ouvrir

o Vous devriez avoir accès aux différentes tables de votre projet :

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4ea11cd0.PNG

Si les données ne sont pas importées il faut modifier les connexions à la base en passant par l’icône dans le ruban comme ci-dessous :

Description: C:\Users\gsabat\AppData\Local\Temp\SNAGHTML4e9dca2e.PNG

Déploiement du projet dans votre instance :

§ Clic droit sur la solution dans l’explorateur de solutions > Déployer :

o Une fois le projet déployé, il devient accessible depuis SharePoint et donc dans PowerView :

– Ouvrir votre site SP, dans les dossiers partagés (par exemple), cliquez sur l’onglet « Documents » afin de pouvoir afficher les options sur les dossiers

– Cliquer sur le bouton « Nouveau document » puis sur « Source de données du rapport » afin de créer notre connexion vers notre solution Tabular :

– Ici, remplir les différents champs proposés :

o Type de source de données :

§ Microsoft BI Semantic Model for PowerView

o Chaîne de connexion :

§

o Informations d’identifications:

§ Authentification Windows ou Utilisateur Sharepoint

o Tester la connexion et cocher la case d’activation de la source de données

o Finir la création en cliquant sur OK

– Votre Source de données est à présent créée et devrait être accessible dans le dossier.

– Passer la souris sur la Source de données précédemment créée afin que vous puissiez faire apparaitre la flèche comme ci-dessous. Vous pouvez alors lancer la création d’un rapport sous PowerView en cliquant sur « Create Power View report

– Voilà, vous pouvez maintenant manipuler vos données avec Power View :

4.2 Rajouter un document dans la « Galerie Power Pivot »

o Cliquer sur « Galerie PowerPivot » dans le menu latéral :

o Une fois la page chargée, cliquez sur Bibliothèque, puis sur la flèche à coté de « Gallery » afin de le faire passer en affichage « Tous les documents » comme su l’image suivante :

o Ensuite ajouter un nouvel élément :

o À présent vous pouvez ouvrir le fichier Excel précédemment uploadé soit en double cliquant dessus directement soit en repassant par la Galerie PowerPivot. En passant par ce dernier, vous aurez accès visuellement aux différentes feuilles du fichier Excel-PowerPivot :

Remarque : Il n’y a pas besoin d’avoir Excel pour pouvoir naviguer dans Power Pivot Remarque 2 : Il est possible d’uploader vos fichiers PowerView dans la galerie Power Pivot en suivant les étapes précédentes :

Le fichier Power Pivot dans l’encadré vert permet

1/ de l’ouvrir directement dans le navigateur, pour y consulter les tableaux dynamiques que vous auriez pu créer (pas possible de faire de modification ici). Ça ouvre tout simplement la feuille Excel (sans avoir forcement Excel d’installé sur votre poste) et vous pouvez jouer avec vos tableaux.

2/Depuis les 3 icones en haut à droite :

1. Permet d’ouvrir le fichier PowerPivot dans Excel et ainsi y apporter des modifications (renommer les tables, les champs, les feuilles…)

2. Permet de créer un rapport PowerView depuis le fichier Power Pivot dans le navigateur

3. Permet de gérer l’actualisation des données (planification tous les jours, toutes les heures…)

Le fichier PowerView dans l’encadré rouge permet :

D’ouvrir le fichier PowerView soit pour le modifier (rajouter des diagrammes, des filtres…) soit l’utiliser comme un fichier de présentation en plein écran.

CONCLUSION

Avec la sortie récente de SQL Server 2012, Microsoft a réussi à remettre au goût du jour son outil PowerPivot Il ne se limite plus qu’à une utilisation avec Excel 2010 mais il permet de pouvoir étendre son utilisation dans un environnement d’entreprise. Pour cela Microsoft a également pensé à tous et c’est surement grâce à PowerView que vous déciderez ou non de vous lancer dans l’aventure. Certes, pour pouvoir en profiter pleinement ou en tout cas dans l’idée que Microsoft veut en faire, il vous faudra investir dans une architecture comprenant SQL Server 2012 et SharePoint 2010 mais cela en vaudra la peine.

Vous aurez alors une solution BI (d’entreprise, d’équipe et personnelle) avec Power Pivot qui vous permettra de jouer avec vos données très rapidement et simplement en montant votre propre cube en mémoire dans Excel puis de pouvoir l’exploiter depuis Power View pour obtenir un rendu design et dynamique de vos rapports. Grâce au « nouveau » service SSAS Tabular, vous aurez la possibilité de l’intégrer à votre environnement d’entreprise afin que tous les monde puisse en profiter et ce dans des conditions bien plus optimale.

C’est dans cette idée que nous vous avons présenté ces deux outils PowerPivot et PowerView et j’espère que nous aurons su le faire convenablement.

Merci.