Power Pivot : Améliorer la performance de vos modèles de données

Vous êtes plusieurs à m’écrire pour me parler des problèmes de performance que vous éprouvez avec vos modèles de données dans Power Pivot. Cet article vise donc à clarifier les limites d’un modèle de données dans Power Pivot et à dresser la liste des meilleures pratiques à adopter afin d’optimiser leur performance.

Limites d’un modèle de données

Avant d’élaborer des modèles de données, vous devez être conscient des limites de Power Pivot. Ceci vous forcera à prendre l’habitude de maintenir vos modèles de données au plus simple de leur forme. De toutes façons, lorsqu’un modèle de données comprend trop de tables et trop de colonnes par table, il devient difficile à utiliser par les usagers, qui ont du mal à identifier les tables et les colonnes nécessaires à leurs analyses. Un tel modèle consomme également beaucoup de mémoire, ce qui affecte sa performance.

Les modèles de données dans Power Pivot utilisent un engin “In-memory” nommé xVelocity. Cet engin utilise des techniques de compression avancées qui permettent de réduire des ensembles de données à une fraction de leur taille originale. Mais pour bénéficier des avantages de cet engin de compression, il faut comprendre comment il fonctionne et élaborer des modèles de données en conséquence. En moyenne, on estime que la taille d’un modèle de données est de 7 à 10 fois plus petite que la taille des ensembles de données qui ont servi à le créer. Par exemple, si vous importez 7MB de données d’un serveur SQL dans Power Pivot, votre modèle de données pourrait facilement faire moins de 1 MB.

Ce qu’il faut surtout retenir ici, c’est que le degré de compression est intimement lié au nombre de valeurs uniques dans une colonne. Plus il y a de valeurs uniques, plus le modèle de données consomme de mémoire et l’inverse est aussi vrai. Si vous êtes conscient de cette réalité, vous élaborerez automatiquement de meilleurs modèles de données.

L’article suivant pourrait vous intéresser puisqu’il traite en détail des spécifications et des limites de Power Pivot Power Pivot: Data Model specification and limits.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Quelques concept importants

Avant d’aller plus loin, vous devez maîtriser certains concepts:

• Une table de faits inclut généralement des données transactionnelles ou des données de performance d’entreprise, comme les ventes ou le coût des ventes, qui sont ensuite associées ou non à des dimensions (divisions, régions, canaux, représentants, etc.).
• Une colonne calculée en DAX est une colonne dérivée des colonnes originales du modèle de données. Autrement, elle a les mêmes caractéristiques qu’une colonne importée.
• Une mesure DAX est définie une seule fois dans le modèle de données et est évaluée seulement lorsqu’elle est utilisée dans un tableau croisé dynamique ou dans un autre type de rapport.
• Vous ne devriez jamais charger les données à la fois dans Excel et dans votre modèle de données. Ça peut sembler aller de soi, mais j’ai vu certains de mes clients le faire et avoir de gros problèmes de performance. Évidemment, car, dans ce cas, nous sommes confrontés aux contraintes d’un fichier Excel.
• Quand une page Excel contenant des données provenant de Power Pivot est ouverte, la consommation de mémoire n’est pas affectée par le volume de données dans Power Pivot. Les données sont chargées seulement quand la fenêtre Power Pivot est ouverte ou quand un tableau/graphique croisé dynamique est mis à jour.
• Les messages suivants doivent être interprétés comme suit:
  • Retrieving Data: Traitement des données (importation depuis la source dans le modèle de données)
  • Loading Data Model : Chargement du modèle de données en mémoire
  • Reading Data: Requête sur le modèle de données et mise à jour des tableaux croisés dynamiques et autres rapports
• Power Pivot utilise 2 sortes de compressions:
  • Hash encoding
    • utilisé pour les tables de valeurs uniques (tables de dimensions)
    • consomme de la RAM (est plus lent)
  • Value encoding
    • utilisé pour les valeurs numériques et les opérations mathématiques (tables de faits)
    • consomme davantage du CPU
  • Les colonnes clés des tables de faits et des tables de dimensions devraient toujours êtres de type entier (integer)
    • Au besoin, utiliser Power Query pour remplacer du texte par des chiffres entiers
  • Pour en savoir davantage sur la compression des données dans Power Pivot:
    • How does Power Pivot store and compress data?
    • Script Update: What is Eating Up My Memory in Power Pivot?

Limites de votre quincaillerie

Si vous êtes sérieux par rapport à l’usage que vous souhaitez faire de Power Pivot, il faut vous équiper des bons outils. Vous ne pourrez pas optimiser la performance de vos modèles de données si vous n’avez pas les pré-requis suivants:

• 64 bit Windows, 64 bit Office
• Processeur 4 coeurs
• Mémoire vive suffisante, au minimum 8 GB de RAM, mais 16 GB est largement préférable
• Un SSD (Solide State Drive) est préférable

Optimisation d’un modèle de données

1. Réduire le nombre de colonnes

Le meilleur moyen d’augmenter la performance d’un modèle de données est de réduire au maximum le nombre de colonnes, plus particulièrement, les colonnes qui comprennent un large éventail de valeurs uniques. Ce faisant, vous devez toujours vous demander si vous avez vraiment besoin d’une colonne avant de l’importer et n’importer seulement les colonnes qui seront nécessaires à la bonne conduite de vos analyses. Mieux vaut en importer moins au départ et en ajouter par la suite, au besoin.

Les colonnes comprenant des chaînes de texte devraient être analysées et transformées au besoin, sachant que:

• Power Pivot crée un dictionnaire de toutes les chaînes de texte uniques.
• Donc, avoir une longue chaîne de texte n’est pas un problème en soi si le modèle contient peu de chaînes de texte distinctes.
• Cela peut par contre devenir un problème si, en moyenne, la longueur des chaînes de texte est importante et qu’il y a beaucoup de valeurs distinctes.

Exemples de colonnes qui méritent d’être supprimées ou regroupées autrement:

• Toutes les colonnes d’un entrepôt de données qui sont le résultat d’un processus ETL comme : Create_Date, Update_Date, ETL_Run, etc.
• Toutes les clés primaires d’une table de faits, c’est-à dire les clés qui produisent des identifiants uniques pour chaque ligne.
  • Ces clés primaires ne permettent pas de créer des relations avec d’autres tables et ne sont habituellement d’aucun intérêt dans les analyses.
  • Un exemple d’une clé primaire de table de faits est le numéro de facture.
  • Évidemment, toutes les colonnes d’une table de faits qui contiennent des données d’affaires ou qui sont nécessaires pour créer des liens avec d’autres tables doivent être conservées.
• Toutes les colonnes géographiques, par exemple, pourraient être fusionnées pour n’avoir qu’une seule colonne avec un IDGeographie.
• Toutes les colonnes d’une table de faits qui pourraient être déplacées dans une table de dimensions, en créant un schéma en étoile (normalisation).
  • Cette opération n’a pas un gros impact sur la taille de la base de données, mais peut avoir un gros impact sur le temps de traitement et sur la consommation de mémoire.
  • Il ne faut pas aller trop loin non plus dans la normalisation puisque les relations entre les tables consomment aussi de la mémoire et nécessitent souvent d’ajouter des colonnes (clés communes).
  • Il faut viser un juste milieu.
• Toutes les colonnes numériques qui pourraient être regroupées en 2 colonnes seulement, une colonne de valeurs et une colonne d’étiquettes.
  • Attention! Cette approche n’apporte pas toujours les résultats escomptés!!!! Ça dépend des cas et surtout du nombre de valeurs distinctes dans la nouvelle colonne globale des valeurs.

Sur le site PowerPivot(Pro), on retrouve un article qui fournit un exemple de cette approche. En voici un extrait:

On débute avec une table de faits de 11 colonnes, dont 9 colonnes de valeurs et 700 000 lignes. Mettre à jour un important tableau croisé dynamique basé sur cette table prend environ 5,7 secondes.

On transforme ensuite la table en dépivotant les 9 colonnes de valeurs. On obtient ainsi une table avec, au total, 4 colonnes, puisque les 9 colonnes de données ont été transformées en 2 colonnes distinctes, soit une colonne de valeurs et une colonne d’étiquettes (qui précise de quel type de valeur il s’agit).

De plus, on a ajouté au modèle de données une table avec la liste de toutes les étiquettes ainsi créées (i.e. tous les types de valeurs possibles) et on a créé une relation avec la table précédente.

Évidemment, pour obtenir la somme des ventes, il faudra ajuster notre mesure DAX en conséquence (il faudra utiliser une fonction CALCULATE avec un filtre sur l’étiquette Ttl_Dollars) mais en bout de ligne, le même tableau croisé dynamique prend maintenant 2,4 secondes à se mettre à jour. Il y a donc un gain important.

Comment supprimer une colonne d’un modèle de données?

Voici la procédure pour supprimer une colonne dans un modèle de données.

D’abord, une fois vos sources de données sélectionnées, cliquez sur le bouton Preview and Filter, tel qu’illustré ci-dessous.

Vous aurez alors la possibilité de décocher les colonnes que vous ne souhaitez pas importer.

Comment dépivoter des colonnes?

• Utiliser Power Query
• Utiliser une vue avec les colonnes déjà dépivotées
  • Si on ne veut pas jouer dans le code, il faut demander aux TI de préparer des vues de ce que l’on a vraiment besoin, mais à ce moment-là, on passe un peu à côté du côté “libre” de l’expression “libre-service”.
• Utiliser un script SQL
  • Le site PowerPivot(Pro) donne un exemple de script pour dépivoter des colonnes.
  • Bien sûr, ce ne sont pas tous les analystes qui sont à l’aise avec les requêtes SQL. Voici donc une façon plus simple, pour les débutants, d’accéder et de modifier un script SQL, à partir de Power Pivot.

D’abord, cliquez sur l’option Table Properties:

Choisissez ensuite l’option Query Editor dans le menu déroulant en haut, à droite:

Décochez au moins une colonne de votre table ou apposez un filtre à l’une ou l’autre de ses colonnes. Ceci modifiera la structure de la requête, qui passera d’un format agrégé:

à un SELECT statement incluant le nom des colonnes:

À partir de là, il vous sera plus facile de modifier le script. Vous trouverez d’ailleurs de nombreux articles de blogue à ce sujet.

2. Augmenter le nombre de colonnes

Quoi? Ne vient-on pas de dire exactement le contraire au point précédent? Oui, mais il faut être vigilant et y aller cas par cas. En général, il est préférable d’avoir le moins de colonnes possible. Mais parfois, il est mieux d’avoir plusieurs colonnes avec un nombre respectable de valeurs uniques, qu’une seule colonne avec un large éventail de valeurs uniques. Donc, quand on a besoin d’une colonne qui comporte une quantité incroyable de valeurs uniques, il vaut mieux la diviser en plusieurs colonnes, de façon à ce que la somme de toutes les valeurs uniques de chacune des divisions soit inférieure à la quantité de valeurs uniques dans la colonne initiale.

D’ailleurs, sur le site PowerPivot(Pro), duquel j’ai tiré l’exemple au point no.1, on fait la démonstration que dépivoter une table pour réduire le nombre de colonnes ne donne pas toujours les résultats escomptés. Dans l’article PowerPivot Compression: Mysterious Ways, le fait de convertir 5 colonnes de valeurs en une colonne de valeurs et une colonne d’étiquettes augmente la taille du modèle résultant. On y explique que cela est lié, d’une part, au nombre élevé de lignes dans chaque table, et au fait que la colonne de valeurs globales ne soit pas triée. En triant cette colonne, la taille diminue effectivement, pour autant que l’on effectue le tri avant l’importation dans Power Pivot.

Rappelez-vous également qu’il est plus facile de compresser des nombres entiers que d’autres types de données. Ce faisant, si vous pouvez fragmenter une colonne problématique en plusieurs colonnes avec des nombres entiers, ce serait l’idéal. Par exemple, ci-bas, j’ai une table avec une colonne d’identifiants uniques et des valeurs.

En supposant que j’aie besoin des identifiants uniques dans mon analyse, je pourrais séparer chaque partie numérique de l’identifiant en 3 colonnes distinctes (ceci réduirait le nombre de valeurs distinctes totales et utiliserait le plus performant des types de compression, soit celui effectué sur des nombres entiers) et je pourrais ensuite les réunir en une seule valeur numérique (là, on ne pourrait pas réduire le nombre de valeurs distinctes, mais on utiliserait le plus performant des types de compression, soit celui effectué sur des nombres entiers).

Vous constatez qu’il n’y a pas de solution unique. Il faut tester chaque cas de figure, en utilisant la logique.

Exemples de colonnes qui méritent d’être fragmentées

• Séparer une colonne qui comprend des montants composés de dollars et de cents en deux colonnes et les regrouper par le biais d’une mesure DAX par la suite TotalAmount= SUM([Dollars])+DIVIDE(SUM([Cents]),100). Cela réduit le nombre de valeurs uniques totales.
• Séparer une colonne qui comprend des heures composées de dates et d’heures en deux colonnes et les regrouper par le biais d’une colonnes calculées par la suite.

3. Réduire le nombre de lignes

Il vaut mieux toujours penser à réduire le nombre de lignes également, lorsque c’est possible.

Exemples de lignes qui méritent d’être supprimées

• Des lignes de transactions d’une table de faits pour des années historiques qui ne sont pas pertinentes pour l’analyse ou pour un volet de l’entreprise qui n’a pas besoin d’être analysé.
• Des lignes d’une table de dates pour des dates qui ne sont pas pertinentes pour l’analyse, par exemple, les années historiques.
  • Si vous laissez toutes les années dans le modèle de données, Power Pivot fait ses calculs pour toutes les années de la table de dates. Ça peut sérieusement affecter la performance.
  • Vous empirez la situation lorsque vous ajoutez des mesures de temps comme PriorYear, YeartoDate, PriorYeartoDate, etc. et/ou que vous ajoutez ensuite des segments (slicers) sur ces calculs.

Supprimer des lignes d’un modèle de données

Voici la procédure pour supprimer des lignes dans un modèle de données.

Vous devez vous rendre encore dans la section Preview et Filter mais cette fois-ci, vous devez utiliser les menus déroulants pour apposer vos filtres. Dans l’exemple ci-dessous, nous pouvons filtrer notre table de données selon les dates que nous souhaitons conserver. Nous pourrions choisir d’importer toute la dernière année, les deux dernières années, etc.

4. Optimiser le poids d’une colonne

Pour les colonnes dont vous avez absolument besoin, sachez qu’il existe quelques techniques qui permettent d’optimiser la compression d’une colonne. Il s’agit toujours de réduire au maximum le nombre de valeurs uniques dans cette colonne.

Exemples de colonnes à optimiser en réduisant la granularité

• Modifier les colonnes avec des valeurs qui comprennent une portion date et une portion temps
  • Vérifier si vous avez besoin de la portion temps
  • Vérifier si vous avez besoin de la portion temps en heures? en minutes? en secondes? en milisecondes?
  • Vérifier si vous devez calculer des différences entre différentes colonnes de dates et de temps ou si vous avez simplement besoin de regrouper vos données par années, mois, trimestre, etc.
  • Vous avez un exemple complet ici qui enseigne comment modifier le script SQL en ce sens: Create Efficient Data Model using Excel 2013 and the Power Pivot Add-in
• Réduire le nombre de décimales (peut avoir un impact significatif)

5. Remplacer des colonnes ou des colonnes calculées par des mesures DAX

Il peut s’avérer efficace de remplacer des colonnes régulières ou des colonnes calculées par des mesures. Par exemple, une table qui possède une colonne UnitPrice et Quantity n’a pas besoin d’une colonne TotalSales. TotalSales peut être définie par une mesure DAX.

TotalSales:=SUMX(SalesTable,SalesTable[UnitPrice]*SalesTable[Quantity])

Les colonnes calculées sont comme des colonnes régulières, dans le sens où les deux occupent de l’espace dans le modèle. À l’inverse, les mesures sont calculées au moment d’une requête. Si le même calcul peut être défini par une colonne calculée ou par une mesure, la dernière option est meilleure d’un point de vue de consommation de ressources. Par contre, les mesures peuvent entraîner des délais au moment des requêtes par les usagers. Il faut donc écrire les mesures les plus efficaces possibles. Il faut aussi prendre conscience qu’un filtre (un segment par exemple) peut affecter plusieurs tables et donc plusieurs mesures à la fois, augmentant d’autant le temps de traitement.

En somme, les colonnes calculées sont recalculées au rafraîchissement de la source. Ceci signifie que ça prend plus de temps à l’ouverture. Les mesures sont recalculées lors d’une requête (en utilisant des filtres), ce qui retarde la mise à jour du tableau de bord, par exemple. Ceci peut déranger les usagers. Mais en général, on recommande néanmoins de remplacer les colonnes calculées par des mesures.

De plus, chaque cellule d’un tableau croisé dynamique est calculée indépendamment, donc plus vous ajoutez de détails dans le TCD (des sous-catégories, par exemple), plus vous compliquez les choses.

• L’article suivant vous présente comment utiliser le DAX lorsque vous devez présenter un tableau croisé dynamique détaillé et passer de 192 000 calculs à 1 calcul: Using DAX to retrieve tabular data

6. Optimiser l’usage des segments (slicers)

Certains d’entre vous rencontrez des problèmes de performance lorsque vous effectuez des requêtes sur votre modèle de données, notamment par le biais de segments (slicers) dans Excel. Voici donc ce que vous devez savoir sur les segments:

• Chaque segment envoie 2 requêtes
  • 1 requête envers lui-même pour savoir s’il doit ajouter/supprimer/modifier ses boutons.
  • 1 requête en fonction des autres segments (cross-slicers filtering).
• Excel essaie de faire correspondre toutes les valeurs du deuxième segment au premier et n’affiche que les résultats correspondants (ça demande beaucoup de travail).
• Excel fait ça pour tous les segments, à chaque modification. Quelques filtres croisés (cross-slicers filtering) peuvent brimer considérablement la performance (de 5 à 10 fois, parfois même plus).

Fort de cette connaissance, vous pouvez en conclure que:

• Chaque fois que vous ajoutez un segment dans un rapport, vous faites aussi en sorte que les requêtes générées par les autres segments deviennent plus complexes, donc, retirer un segment peut permettre aux autres segments d’être plus performants.
  • Vous pouvez remplacer les segments par des filtres de rapports traditionnels au besoin (vous pouvez relire l’article Contrôler plusieurs tableaux croisés dynamiques avec un seul menu déroulant pour vous inspirer à ce sujet).

Vous pouvez également réduire les segments croisés ou fermer cette option:

• Il est judicieux d’utiliser des mesures DAX plus performantes. Par exemple, la fonction FILTER va tirer plus de jus qu’un simple SUM. En effet, la mesure FILTER devra se mettre à jour par rapport au tableau croisé dynamique, mais également par rapport à chaque segment.
• Utiliser le segment sur la table de faits plutôt que la table de dimension peut ralentir énormément le traitement des données. Par exemple, si vous utilisez un segment de dates à partir de la colonne dates d’une table de faits (plutôt que la colonne date de la table de dates) vous demandez au segment de se mettre à jour en étudiant chaque ligne de la table de faits plutôt que la table de dates (vous augmentez donc le temps de traitement).
  • Mais attention! Là aussi, il y a des exceptions. Voici un exemple où le fait d’utiliser le segment sur la table de dimensions plutôt que sur la table de faits n’a pas l’effet escompté, à cause du schéma snowflake (table intermédiaire dans le chemin): Stars and Snowflakes and Bears Oh My
  • Les schémas en étoile (star schema) sont donc plus efficaces que les schémas en flocons de neige (snowflake schema)

Outils

Il existe différents outils pour vous aider à améliorer la performance de vos modèles de données.

• Le Workbook Size Optimizer inspecte vos modèles de données élaborés dans Power Pivot, évalue si ceux-ci peuvent occuper moins d’espace et, le cas échéant, améliore la compression.
• Vous pouvez également utiliser un fichier Shema.ini pour préciser le format de vos fichiers texte dès l’importation dans Power Pivot.
• Une macro rédigée par Kasper de Jonge permet d’analyser votre modèle de données et de fournir un onglet comme le suivant, précisant comment chaque élément de votre modèle de données consomme de mémoire.

Références:

Power Pivot Performance Gotchas

Slicers and Pivot Update Performance

Less Columns, More Rows, More Speed

Power Pivot Compression Mysterious Ways

Speed: Another Reason to Trim Calendar Tables

The High Cost of Poor Equipment

Performance Tips for your Power Pivot Sheet

Checklist for Memory Optimizations in Power Pivot and Tabular Models

Power Pivot Tips and Tricks for IT Pros

Create Efficient Data Model using Excel 2013 and the Power Pivot Add-in

Fichier d’accompagnement

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour une introduction à la modélisation de données avec Power Pivot, un complément BI d’Excel, qui permet d’importer, de croiser et de bonifier diverses sources de données externes pour finalement les analyser à l’aide de tableaux croisés dynamiques, suivez la formation Excel – Excel – Introduction à Power Pivot et aux modèles de données.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.