• ADMIN

Tutoriel > Optimisation du temps d'affichage dans PowerPivot


Un modèle de données qui comprend trop de tables et trop de colonnes par table, devient difficile à utiliser par les utilisateurs, 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 classeurs contenant un modèle de données et des visualisations de données sont souvent très volumineux, dépassant parfois les limites de taille de fichier imposées par SharePoint Online ou Office Online dans Office 365

Pour les modèles de données de classeurs contenant des millions de lignes, vous atteindrez assez rapidement la limite de 10 Mo.


L'optimiseur de taille de classeur inspecte la composition du modèle de données dans votre classeur compatible PowerPivot vérifie si les données qu'il contient peuvent occuper moins d'espace et, si possible, permet une meilleure compression.

La manière de réduire la taille de fichier et les besoins en mémoire d'un modèle de données consiste à réduire le nombre total de colonnes et de lignes et le nombre de valeurs uniques apparaissant dans chaque colonne.

Les différences de stockage requises pour les colonnes individuelles peuvent être énormes. Dans certains cas, il est préférable d'avoir plusieurs colonnes avec un faible nombre de valeurs uniques plutôt qu'une seule colonne avec un grand nombre de valeurs uniques.

Supprimer des colonnes est bien sûr le meilleur moyen de gagner de la place. Décidez quelles colonnes vous avez vraiment besoin. Parfois, vous pouvez supprimer une colonne et la remplacer par une mesure calculée dans la table avec Sumx que nous avons vu).

Les colonnes calculées sont utilisées pour dériver de nouvelles colonnes basées sur une autre colonne du modèle, alors que les mesures calculées sont définies une fois dans le modèle, mais évaluées uniquement lorsqu'elles sont utilisées dans un tableau croisé dynamique ou un autre rapport. Une technique d'économie de mémoire consiste à remplacer les colonnes régulières ou calculées par des mesures calculées. L'exemple classique est le prix unitaire, la quantité et le total. Si vous avez les trois, vous pouvez économiser de l'espace en n'en conservant que deux et en calculant le troisième à l'aide de DAX.

Vous n'avez peut-être pas besoin de toutes les lignes d'une table. Vous pouvez filtrer les lignes dans l'Assistant Importation de table. En général, séparer une seule colonne en plusieurs parties distinctes est un bon moyen de réduire le nombre de valeurs uniques dans une colonne. Chacune des parties aura un petit nombre de valeurs uniques et le total combiné sera plus petit que la colonne unifiée d'origine. Dans de nombreux cas, vous avez également besoin de parties distinctes à utiliser comme segments dans vos rapports. Le cas échéant, vous pouvez créer des hiérarchies à partir d'éléments tels que Heures, Minutes et Secondes. Souvent, les colonnes contiennent plus d'informations que vous n'en avez besoin. Par exemple, supposons qu'une colonne stocke des décimales, mais vous avez appliqué un formatage pour masquer toutes les décimales. L'arrondi peut être très efficace pour réduire la taille d'une colonne numérique.

Importation depuis SQL Server

Lors de l’importation pour chaque tableau, vous pouvez cliquer sur le bouton "Aperçu et filtre" et sélectionner les parties de la table dont vous avez réellement besoin.


Qu'en est-il du filtrage uniquement des lignes nécessaires ?

De nombreuses tables des bases de données d'entreprise et des entrepôts de données contiennent des données historiques accumulées sur de longues périodes. De plus, vous constaterez peut-être que les tables qui vous intéressent contiennent des informations sur les domaines d'activité non requis pour votre analyse spécifique. A l'aide de l'assistant d'importation de tableau, vous pouvez filtrer les données historiques ou non associées et économiser ainsi beaucoup d'espace dans le modèle. Dans l'image suivante, un filtre de date est utilisé pour extraire uniquement les lignes contenant des données pour l'année en cours, à l'exclusion des données historiques qui ne sont pas nécessaires.


Afin de compresser l’importation d’une table il est nécessaire de procéder à la modification d'une requête SQL. Pour faciliter la modification des requêtes, vous devez filtrer au moins une colonne dans chaque table. En filtrant une colonne, vous modifiez la construction de requête d'un format abrégé (SELECT *) en une instruction SELECT qui inclut des noms de colonne complets, qui sont beaucoup plus faciles à modifier. Dans la boîte de dialogue Propriétés du tableau, vous pouvez basculer vers l'éditeur de requête et afficher la requête SQL actuelle pour chaque table depuis environnement Powerpivot.



L'Éditeur de requête affiche la requête SQL utilisée pour remplir la table. Si vous avez filtré une colonne lors de l'importation, votre requête inclut des noms de colonne complets:


Si vous importez une table dans son intégralité sans décocher une colonne ou appliquer un filtre, vous verrez la requête "Select * from", qui sera plus difficile à modifier :

De plus

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 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

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

On devrait toujours se demander si nous avons 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 fragmentées :


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. (TabDate)

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.


Si nous avons besoin des identifiants uniques dans notre analyse, je pourrais séparer chaque partie numérique de l’identifiant en 3 colonnes distinctes et ceci réduirait le nombre de valeurs distinctes totales.

Nous pouvons 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).

Réduire le nombre de lignes

Des lignes d’une table de dates pour des dates qui ne sont pas pertinentes pour l’analyse, par exemple, les années historiques car si on laisse 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 et cela peut sérieusement affecter la performance.


Optimisez l’usage de segment dans le rapport :

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). Vous pouvez remplacer les segments par des filtres de rapports traditionnels au besoin dans le TCD.

On peut réduire l’usage de segment croisé par l’option à décocher depuis Menu Contextuel (clik droit sur le segment) et choisir propriétés pour accéder à cette fenêtre.

Optimiser votre modèle de donnée par le schéma en étoile.

Il est judicieux d’utiliser des mesures DAX plus performantes. Voilà pourquoi utiliser la fonction FILTER car 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).

Pour l’actualisation, on peut éviter de choisir « Actualiser Tout » si cela n’est pas nécessaire et juste choisir la table dans PowerPivot et cliquer sur « Actualiser ». L’opération ne se fera que sur la table en question.


Augustin de la Fouchardiere

MYPE

#PowerPivot

299 vues

MYPE - prononcer "Maïp" -

signifie : Make Your Process Easier

  • Facebook - Gris Cercle
  • Twitter - Gris Cercle
  • LinkedIn - Gris Cercle
  • YouTube - Gris Cercle

Retrouvez notre brochure :