Tableur EXCEL/PowerPivot

Début de la boite de navigation du chapitre

Ce chapitre a pour but de présenter les fonctions de PowerPivot.

fin de la boite de navigation du chapitre

Une leçon existe désormais sur Powerpivot indépendamment de la leçon sur le Tableur Excel.

Présentation

Le nom de cet outil est bien évocateur: "Power" pour "Puissance" et "Pivot" pour "Tableaux croisés dynamiques".

PowerPivot est un add-in d’EXCEL c'est-à-dire un module d’extension, il se trouve dans les outils de Business Intelligence (BI) simple, aussi nommée « Self-BI » et est fondé sur le principe des cubes de données.

PowerPivot existait dans le système SQL Server-Sharepoint utilisé par de nombreuses entreprises, il a été dans un premier temps proposé en tant que module d'extension gratuit pour la version EXCEL 2010. La version 2013 d'EXCEL comporte maintenant PowerPivot en standard.

PowerPivot est utilisé pour insérer et intégrer de grandes quantités de données dans des classeurs EXCEL. Les données ajoutées au classeur sont stockées en interne, sous la forme d'une base de données PowerPivot incorporée dans le fichier.xlsx qui peut être utilisée dans les rapports EXCEL ou Power View.

Dans EXCEL, le classeur offre toutes les fonctionnalités de visualisation des données et d'interaction avec celles-ci. Vous pouvez générer des classeurs sophistiqués basés sur des données analytiques performantes que vous générez facilement grâce à la création de tableau croisé dynamique.

PowerPivot est un logiciel intuitif et évolutif qui donne la possibilité aux utilisateurs de créer des rapports ou solution de REPORTING. Il permet également d'étendre les possibilités d’analyse de données d’EXCEL mais aussi d’obtenir rapidement des informations agrégées à partir d'un grand nombre de données.

Intérêt de l'outil

Les avantages de ce module d'extension sont nombreux, PowerPivot permet de:

• Multiplier la puissance des TCD dans Excel et permet des calculs impressionnants, qui peuvent traiter des millions de lignes comme s’il s’agissait de quelques milliers
• Travailler directement avec les données dans l'espace de travail EXCEL, l'onglet menu PowerPivot est intégré au ruban EXCEL.
• Ajouter et consolider des données émanant de plusieurs sources tels qu' EXCEL, ACCESS, SQL, HTML, ... Il suffit simplement d'associer les différentes bases de données entre elles avec un champ commun, par exemple l'identifiant d'un client
• Intégrer des données qui évoluent sans cesse aussi facilement qu’un flux RSS, la mise à jour se fait instantanément sans alourdir les fichiers Excel
• Utiliser Sharepoint et permettre ainsi de publier, partager et collaborer facilement
• Traiter les données avec un moteur d'analyse local (en mémoire xVelocity Analysis Services VertiPaq); ce dernier compacte, charge les données et les met à disposition d'objets de visualisation, comme des tableaux croisés dynamiques, dans une feuille de travail.
• Eviter des tâches d'administration ou de configuration, le moteur s'exécute dans le processus EXCEL(en tant que composant interne du complément PowerPivot à EXCEL).

En Conclusion, PowerPivot permet à EXCEL de devenir un outil de décisionnel (Business Intelligence) très puissant et en même temps simple d’utilisation.

Ajout de données

À partir de cet add-in, il est possible d'ajouter des données de différentes manières :

• à partir d’une liste de données (sous forme de plage ou de tableau) lors de la demande d’insertion d'un TCD => cocher "ajouter ces données au modèle de données". Dans ce cas, le TCD utilisera automatiquement le modèle de données. Il sera possible dans le volet de construction de ce TCD, de sélectionner "Actif" ou "Tous" pour afficher les champs du tableau ou les champs de l’ensemble des tableaux en relation.

Powerpivot

• à partir d’un tableau, il faudra pour cela ajouter automatiquement le tableau au modèle de données en établissant une relation avec un autre tableau : nouvelle fonctionnalité accessible depuis l’onglet "Données". Dans ce cas il faut préciser, lors de la création d’un tableau croisé, ce que l’on souhaite faire. Si on ne précise rien, c’est le tableau qui est utilisé pour la création du TCD. De ce fait, on peut ensuite préciser si l’on souhaite utiliser le modèle en sélectionnant "Plus de tables" dans la liste des champs du volet de construction du TCD. Sinon, on peut, comme dans le cas « d’ajout à partir d’une liste de données », cocher "Ajouter ces données au modèles de données". Ceci parait étonnant du fait que les données soient déjà dans le modèle.

Powerpivot

• à partir de données externes, pour la création du TCD, on devra cocher "Utiliser une source de données externes", puis on sélectionnera la connexion qui s'est créée dans le classeur, si on souhaite utiliser des données externes pour une relation, on peut :
  • Établir une connexion à ces données en cochant dans la fenêtre d'importation
  • "Tableau", ou si on ne souhaite pas les importer dans un onglet, "Ne créer que la connexion"
  • "Ajouter ces données au modèle de données"

Powerpivot

Powerpivot

Intérêt des relations PowerPivot

Sur le même principe qu'une base de données, les relations permettent de tirer parti des liens existants entre les données en évitant la répétition. La relation est toujours une relation de 1 à N, contrairement à EXCEL qui n'impose pas de relation naturelle.

Les relations PowerPivot évitent cependant des formules comme RECHERCHEV pour croiser les données de plusieurs tables si l'objectif de la formule est l'analyse par TCD. Par exemple avec :

• un tableau de factures où seul l'identifiant client apparait.
• un tableau de clients, où figurent l'adresse du client et l'identifiant du commercial chargé du client

La relation entre les deux tableaux permet une analyse du chiffre d'affaire par département ou par ville ou par commercial.

PowerPivot version ProPlus

Dans la version pro plus, on bénéficie de tous les avantages de l’add-in tels que :

• un onglet PowerPivot
• une interface spécifique permettant de visualiser et optimiser le modèle de données
• l'autorisation des calculs en langage DAX permettent de tirer toute la puissance possible des relations

Pour ajouter des données au modèle, en plus des trois possibilités citées pour toutes les versions, PowerPivot ProPlus permet :

• L’utilisation de la commande Ajouter au modèle de données de l'onglet PowerPivot
• Obtenir des données externes dans l'interface PowerPivot qui permet de :
  • n’importer que certains champs d'une table ou vue.
  • n’importer que certains enregistrements d'une table ou vue.
• créer une requête pour définir les données à importer.

Dans les 3 cas on obtient une requête, implicite ou explicite pouvant être modifiée à tout moment.

Langage DAX

Le langage DAX permet de réaliser des calculs pouvant être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot. Cela permet d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.

Les formules DAX sont utilisées dans des tables PowerPivot ou dans des tableaux croisés dynamiques Excel. DAX représente un ensemble de fonctions, d'opérateurs et de constantes qui peuvent être utilisés dans une formule ou une expression, afin de calculer une ou plusieurs valeurs. DAX vous aide à créer de nouvelles informations à partir de données figurant déjà dans votre modèle.

Les formules DAX sont très semblables aux formules Excel. Pour en créer une formule DAX, tapez sur le signe égal, suivi d'un nom de fonction ou d'une expression, et de toutes les valeurs requises.

Pour mieux comprendre prenons un exemple simple :

DAX langage

• A ==> l'opérateur de signe égal (=) indique le début de la formule
• B ==> la colonne [MontantVentes], les crochets indiquent une référence de colonne dans une formule, contrairement aux formules Excel qui font référence à une cellule, une formule DAX fait toujours référence à une colonne
• C ==> Opérateur mathématique de soustraction (-).
• D ==> la colonne référencée [CoutTotl]