décembre 27, 2022Lire l'article
Un modèle de données qui comprend trop de tables et trop de colonnes par table, devient difficile à manipuler 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 et vérifie si les données qu'il contient peuvent occuper moins d'espace et ainsi permettre une meilleure compression.
La manière de réduire la taille d'un 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 ainsi que 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 (avec Sumx par exemple).
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 colonnes 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. À 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, on observe qu'un filtre de date est utilisé afin d'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 incluant des noms de colonne complets 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 l'environnement Power Pivot.
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é des tables de faits et des tables de dimensions devraient toujours être de type entier (integer).
Enfin, il est nécessaire de vous équiper des bons outils. En effet, vous ne pourrez pas optimiser la performance de vos modèles de données si vous n’avez pas les pré-requis suivants :
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 :
Exemples de colonnes qui méritent d’être fragmentées :
Séparer une colonne en deux comprenant des heures et des dates et les regrouper par le biais d’une colonne calculée (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-dessus, j’ai une table contenant une colonne d’identifiants uniques et des valeurs.
Si nous avons besoin des identifiants uniques dans notre analyse, je pourrai 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
Certaines lignes des tables de dates peuvent ne plus être pertinentes. C'est par exemple le cas pour les années historiques. En effet, si on laisse toutes les années dans notre modèle de données, Power Pivot effectuera des calculs pour toutes les années de notre table et ainsi, affecter significativement la performance de ce dernier.
Optimisez l’usage de segment dans le rapport :
Excel essaie de faire correspondre toutes les valeurs du deuxième segment au premier en n'affichant que les résultats correspondants. 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é via le Menu Contextuel (clic 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 elle 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.
Concernant l’actualisation, il est préférable de ne pas utiliser l'option « Actualiser Tout » si cela n’est pas nécessaire. En effet, il vaut mieux actualiser sur une table spécifique afin de gagner du temps.
Pour en savoir plus sur Power BI, consultez notre article sur le fonctionnement du Query Folding.
Pour être formé sur Power BI, cliquez ici !
Augustin de la Fouchardiere
MYPE