PowerPivot 2013 et versions suivantes

• PowerPivot, qu'est-ce que c'est ?
• PowerPivot est un addin d’Excel.
  Il existait dans le système SQL Server-Sharepoint utilisés dans les grandes structures et a été proposé en tant qu’addin de la version 2010.
  Il fait partie des outils de Business Intelligence (BI) et est basé sur le principe des cubes de données. Il a été intégré dans Excel depuis la version 2013, de façon différente selon la version, ce qui conduit à deux modes d’utilisation assez distincts.
  Nous allons le voir dans les exemples qui suivent.

• Quel est le principal intérêt de cet outil ?
• Il permet d’analyser de gros volumes de données via des tableaux croisés dynamiques.
  Les données peuvent être stockées dans un modèle de données indépendant des onglets du classeur, ce qui permet de dépasser le million de lignes ou enregistrements.
  Il est également possible de créer des relations entre les données insérées dans le modèle comme on le fait dans une base de données ou un cube de données.
  Les quelques exemples qui suivent vont permettre d’illustrer quelques avantages de PowerPivot en distinguant ce que l'on peut faire dans toute version et ce qu'on peut faire dans la version Pro plus.
  A noter que tout ce qui est aujourd'hui limité à la version pro plus est disponible dans l'addin pour 2010.
  Seul PowerView, que nous ne présentons pas ici, n'existe que dans la version Excel 2013 Pro plus.
  Remarque : pour simplifier, dans ce qui suit, l'abréviation TCD est souvent utiliseé pour désigner le Tableau Croisé Dynamique.

• Ajout de données au modèle de données - Toutes versions
• Pour des données intégrées à Excel, il est indispensable, pour préserver l'évolutivité, de mettre les données sous forme de tableau avant d'utiliser le modèle de données PowerPivot.
• Pour ajouter des données au modèle de données, il existe plusieurs possibilités :
  1 A partir d’un tableau lors de la demande d’insertion d'un TCD, cocher Ajouter ces données au modèle de données
    
  2 A partir d’un tableau (plage mise sous forme de tableau ou données importées d’une base de données) on ajoute 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, détaillée plus loin.
  Lors de la création du TCD, c’est le tableau qui est utilisé par défaut.
  On peut ensuite préciser que l’on souhaite utiliser le modèle en sélectionnant Plus de tables dans la liste des champs du volet de construction du TCD
  
• Utiliser des données externes :
  Si on souhaite utiliser des données externes pour une relation, on les ajoute au modèle en établissant une connexion à ces données et, dans la fenêtre d'importation, en cochant :
  - Tableau ou bien, 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 (coché par défaut pour les bases de données)
  
  Dans ce cas pour la création du tableau croisé on utilise Utiliser une source de données externes et on sélectionne la connexion qui s'est créée dans le classeur
  
  Si on souhaite créer directement un tableau croisé sur des données externes, on peut utiliser la méthode ci-dessus dans laquelle on définit la connexion.
  Dans le cas d'incorporation de données externes au modèle sans import dans un onglet, l'absence de visibilité sur ce modèle, dans toutes les versions sauf pro plus, est gênante.
  On peut au mieux visualiser où les données sont utilisées dans le classeur en passant par
  Données, Connexions :
  
  

• Ajout de données au modèle de données - Version Pro Plus
• Dans la version Pro Plus, on bénéficie de tous les avantages de l’addin qui existe dans 2010 et notamment :
  - d'un onglet PowerPivot
  
  - d'une interface spécifique qui permet de visualiser et optimiser le modèle
  
• Pour ajouter des données au modèle de données, outre les quatre possibilités citées pour toutes les versions, on dispose des possibilités suivantes :
  - utiliser 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 ou bien certains enregistrements d'une table ou vue
  
    ou de créer une requête pour définir les données à importer.
  Dans tous les cas on obtient une requête, implicite ou explicite, qui peut être modifiée à tout moment.
  
  

• Intérêt des relations - Toutes versions
• Comme dans une base de données, les relations permettent de tirer parti des liens existant entre les données en évitant la redondance. La relation est toujours une relation de 1 à N.
  Dans Excel, on doit les définir en utilisant le sens N à 1
  
• Dans les versions autres que pro plus :
  - le modèle de données n'étant pas accessible,
  - sur 2013, la possibilité d'effectuer des calculs en langage DAX étant absente, l'intérêt est limité.
  - Heureusement 2016 offre la possibilité d'ajouter des formules DAX depuis les TCD.
  
  Les relations éviteront cependant des formules de type 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 permettra une analyse du chiffre d'affaire par département ou ville ou commercial.

• Intérêt des relations - Version pro plus
• L'optimisation possible du modèle de données, d'une part, et le langage DAX, d'autre part, vont permettre de tirer tout le parti possible des relations.
  Le langage DAX permet de réaliser des calculs.
  Ces calculs peuvent être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot.
  Ils permettent notamment d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.
  
  

• Utilisation - Toutes versions hors pro plus
• Dans toutes les versions hors pro plus, on pourra soit n'utiliser que les possibilités classiques des tableaux croisés soit utiliser les données du modèle de données, mais, à partir du moment où le TCD utilise les données du modèle de données, on perd les possibilités :
  - d'ajouter des champs calculés,
  - de regroupement.
  Il convient donc d’analyser les avantages/désavantages d’utiliser soit le modèle de données, soit le tableau source pour un TCD. Selon le TCD à réaliser on pourra utiliser l’un ou l’autre dans un même classeur.
  
  
• Nous allons illustrer 2 cas concrets :
  - utilisation de relations
  - valeurs distinctes
  Soit un tableau de factures et un tableau des clients
  
  Nous créons une relation entre les deux tableaux sur le nom du client (onglet Données, Relations, Nouveau)
  
  Cette relation permet d'établir des statistiques sur le Chiffres d'Affaire et le nombre de Clients par Commercial et par Ville en construisant le TCD sur des champs issus des deux tableaux
  
  Le nombre de clients par Commercial et Ville est obtenu avec le nouveau calcul Total Distinct qui permet de dénombrer les clients distincts et non les lignes de factures.
  
  
• Utilisation - Version pro plus
• Nous allons profiter des possibilités du langage DAX pour illustrer 3 cas concrets correspondant à des demandes récurrentes sur les forums.
  Pour ces 3 cas, nous allons utiliser quatre tables d'une base de données Access :
  Factures, Lignes de Factures, Sociétés, Produits
  
  Les relations existant entre les 4 tables sont importées
  
  
  
• Premier cas : multiplication prix par quantité
  Une demande récurrente concerne la nécessité d'effectuer un calcul entre deux champs. Dans les TCD classiques, les champs calculés sont basés sur la somme des valeurs du champ, ce qui limite les possibilités.
  Le langage DAX permet d'ajouter un champ calculé prenant en compte les valeurs unitaires et agrégeant le résultat selon le contexte de filtre du TCD.
  Dans la table des lignes de factures figurent les champs Quantité et Prix.
  Nous allons créer un TCD depuis l'interface PowerPivot et ajouter à celui-ci, depuis l'onglet PowerPivot, un champ calculé en langage DAX :  = SUMX(FACT_LIGNES[PRIX]*[QUANTITE])
  
  Si l'on souhaite calculer également le nombre de clients uniques par produit vendu, il est nécessaire de modifier le modèle de données afin d'associer à chaque ligne de facture le client concerné avec une formule DAX =RELATED(SOCIETES[Code]).
  Ce nouveau champ sera ensuite utilisé dans le TCD avec le calcul Total Distinct.
  
  
• Deuxième cas : calculer les ratios sur le total général ou le total colonne
  Une autre demande récurrente est de conserver pour le calcul des ratios, effectués sur le total général ou bien sur le total de la colonne, le ratio sur l'ensemble des valeurs même si un filtre est actif.
  Dans un TCD classique quel que soit le filtre actif le total des ratios est égal à 100%.
  Avec une formule DAX, il est possible d'obtenir le ratio de ce qui est affiché par rapport à l'ensemble des données : =(SUM(FACTURES[Montant]))/CALCULATE(SUM(FACTURES[Montant]);ALL(FACTURES[RS])
  
  
  A noter que pour nombre de cas simples, on peut le faire directement depuis le paramétrage du TCD comme décrit ici
  Garder le total de l'ensemble des éléments même en cas de filtre
  
  
• Troisième cas : test sur valeur non numérique
  Une 3ème demande récurrente est le test sur le champ de valeur utilisé dans le TCD ou sur un autre champ des données sources.
  Par exemple le montant et le pourcentage de factures réglées : le règlement étant matérialisé par la présence d'une date de règlement.
  Avec une formule DAX, il est possible d'obtenir le total des règlements : la formule est très proche de ce qui serait fait dans une colonne de la source avec une formule Excel classique
  =SUMX(FACTURES;IF(ISBLANK(FACTURES[Date reglt]);0;FACTURES[Montant]))
  
  Une seconde formule nous donnera le pourcentage :
    =SUMX(FACTURES;[Encaissé])/SUMX(FACTURES;[Montant])
  
  Résultat :
  
  Une petite mise en forme conditionnelle pour repérer les risques termine le tout
  
  
  
  

Trucs et astuces Tableaux croisés dynamiques

Copyright Archétypes - Informations légales