Lorsque je travaillais avec des modèles de données volumineux, des sources de données sous-jacentes lentes ou des rapports complexes dans Power BI, j’étais parfois confronté à des problèmes de performance. Cela peut prendre une grande partie du temps de développement qui aurait dû être concentré à la création des rapports.
Dans cet article, je vais décrire quelques bonnes pratiques à envisager. Puis, je vais donner quelques conseils sur l’optimisation des performances des rapports Power BI. Tel que :
- Mise en place du filtrage vertical et horizontal
- Création des colonnes personnalisées dans PowerQuery
- Utilisation des variables dans les calculs des mesures DAX
D’autres optimisations peuvent être faites en dehors de l’environnement Power BI pour améliorer les performances des requêtes telles que la création d’index dans SQL Server.
Pour illustrer mes propos, j’ai utilisé la base de données WideWorldImportersDW (version 2016) dans le modèle de données Power BI.
Partie 1 : implémentation du filtrage vertical et horizontal
Quelle que soit l’efficacité du moteur de stockage Vertipaq pour aider à compresser et optimiser les données, il est toujours très important de mettre en œuvre des moyens de réduire la taille du modèle. Il est donc recommandé de ne concevoir que des modèles avec uniquement les colonnes requises. Pour réduire sa taille, je conseille alors d’appliquer un filtrage vertical, c’est-à-dire en supprimant les colonnes inutiles.
Imaginons que je conserve toutes les colonnes des tables importées dans mon modèle. Après avoir enregistré le fichier, la taille est de 88,240 Ko.
La table la plus volumineuse est la table de fait « Fact Sale ». Celle-ci contient des index d’autres dimensions qui ne sont pas présents dans mon modèle. Ainsi que des indicateurs qui ne seront pas utiles pour la conception de mes rapports. J’applique alors un filtrage vertical en sélectionnant que les colonnes dont j’ai besoin.
En enregistrant à nouveau le modèle, la taille du fichier PBIX est réduite : de 88,240 Ko à 5,018 Ko. Ce filtrage a son importance lorsqu’on travaille avec des modèles de données très volumineux qui augmentent de manière exponentielle.
En parallèle du filtrage vertical, une autre technique consiste à travailler sur un sous-ensemble de données. Cette technique est le filtrage horizontal. Cela signifie le fait de limiter le nombre de lignes dans un ensemble de données, améliorant ainsi les performances du modèle. L’objectif de cette technique est donc de travailler sur un sous-ensemble de données pour accélérer le rafraîchissement des données et le rendu visuel.
Pour cela, je crée un paramètre à partir de la fenêtre d’édition de Power BI qui sera ensuite utilisé dans un filtre personnalisé. Par exemple, un filtre sur la dernière année.
Pour appliquer le filtre personnalisé, il faut d’abord s’assurer que la colonne Date de la table de fait [Fact Sale] soit de type Date/Time. Si ce n’est pas le cas, il ne sera alors pas possible de sélectionner le paramètre.
Les données sont ainsi automatiquement filtrées sur la dernière année.
Partie 2 : création des colonnes personnalisées dans Power Query
Une autre bonne pratique consiste à créer des colonnes calculées générés par Power Query plutôt que des colonnes calculées basées sur des expressions DAX. Avant d’illustrer ceci, il est important de comprendre la différence entre ces deux types de colonnes.
Présentation des colonnes calculées DAX
Une colonne calculée DAX calcule le résultat d’une expression DAX après que le contenu du modèle ait été importé en mémoire. Le résultat de l’expression DAX est évalué pour chaque ligne d’une table. Et il est stocké en mémoire comme les autres colonnes de la table.
Exemple d’une formule DAX : 
Présentation des colonnes calculées Power Query
Une colonne calculée Power Query est une expression M (M pour Mashup) qui définit les transformations à appliquer à une source de données avant d’importer une table dans le modèle de données. Il peut également être calculé par le moteur Power Query, en modifiant les lignes provenant de la source de données qui sont utilisées pour remplir la table dans le modèle de données.
La formule se présente ainsi :
Il est important de noter que le moteur de stockage Vertipaq stocke les colonnes calculées du modèle DAX de la même manière que les colonnes calculées en langage Power Query M. Mais il existe des différences selon des scénarios spécifiques :
- Compression
- Une colonne calculée DAX ne participe pas à l’évaluation du meilleur ordre de tri pour la compression.
- Une colonne calculée Power Query est compressée comme toute autre colonne de la table.
Remarque : Une colonne avec une meilleure compression est plus petite en mémoire et offre généralement de meilleurs niveaux de performance. Ceci est important dans les opérations de filtrage, de groupe et d’agrégation impliquant la colonne.
- Actualisation de la table
- Une colonne calculée DAX ne nécessite pas une actualisation complète de la table
- Une colonne calculée PowerQuery nécessite une actualisation complète de la table
- Traitement des colonnes calculées
- Le moteur traite les colonnes calculées DAX une fois que les données de toutes les partitions ont été chargées et compressées en mémoire.
- Une colonne calculée Power Query est traitée comme toute autre colonne provenant de la source de données.
- Actualisation incrémentielle et actualisation de la partition
- Le moteur traite les colonnes calculées DAX pour toutes les lignes de la table, même si seules quelques lignes sont ajoutées ou actualisées dans la table.
- Le moteur calcule uniquement les valeurs des colonnes calculées par Power Query des lignes traitées lors d’une actualisation partielle.
Quand doit-on utiliser les colonnes calculées PowerQuery ?
Vous devez utiliser les colonnes calculées Power Query chaque fois que l’expression dépend uniquement des autres colonnes de la même ligne de la table. Si la source de données est une base de données relationnelles et que vous fusionnez des tables qui peuvent être jointes efficacement dans la source de données, il est judicieux de dénormaliser une table à l’aide des fonctions Power Query plutôt que d’importer plusieurs tables dans le modèle de données.
Quand doit-on utiliser les colonnes calculées DAX ?
Vous devez utiliser des colonnes calculées DAX lorsque vous ne pouvez pas implémenter le même calcul dans une colonne calculée Power Query. Le cas typique est celui de l’agrégation de données à partir d’autres tables du modèle.
Pour déterminer les différences de performance, je vais faire une analyse comparative sur le temps d’exécution entre les deux colonnes calculées. En créant deux « slicers » et une table, je peux voir le temps d’exécution des requêtes grâce à l’outil Performance Analyzer. Il est à noter que je suis sur une petite volumétrie de données. Donc, sur une grosse volumétrie, l’impact serait beaucoup plus important.
- Résultat du slicer « Inventaire »
La formule de Power Query a mis 30 ms (dont pour 18 ms pour préparer la requête) pour se rafraîchir au niveau du slicer et 327 ms au niveau de la table (dont 184ms pour préparer la requête).
- Résultat du slicer « InventaireDAX »
La formule de Power Query a mis 258 ms (dont pour 225 ms pour préparer la requête) pour se rafraîchir au niveau du slicer et 346 ms (dont pour 194 ms pour préparer la requête) au niveau de la table
Les résultats montrent que le slicer de la colonne calculée « Inventaire » a été beaucoup plus rapide que celui d’ « InventaireDAX ». Cet écart de performance n’est pas dû à l’exécution des requêtes DAX, mais surtout au temps requis pour préparer les requêtes.
Partie 3 : utiliser des variables dans les formules DAX
L’utilisation des variables dans les modèles de données Power BI peut être efficace à plusieurs niveaux :
- Améliorer la visibilité des requêtes
- Simplifier le débogage
- Améliorer les performances
Ici, je vais m’intéresser à l’amélioration des performances. Pour cela, je vais créer une formule de deux manières différentes : sans variable et avec variable
- Sans variable
- Avec variable
La mesure sans variable nécessite d’évaluer la même expression deux fois. Donc elle prendrait plus de temps à s’afficher. La mesure avec variable est plus efficace, car elle évalue une seule fois l’expression. En outre, le rendu visuel prend moins de temps, car l’actualisation prend généralement moins de temps. Voici les résultats via l’outil Performance Analyzer :
- Sans variable :
- Avec variable :
Le diagramme montre que la mesure avec variable a mis 167 ms. Alors que la mesure sans variable a mis 180 ms. Le temps pour préparer les requêtes était plus long (10 ms d’écart).
Partie 4 : indexation dans SQL Server et l’impact sur Power BI
Une autre façon d’améliorer énormément le temps d’exécution des requêtes est d’indexer les tables utilisées dans le modèle de données Power BI. Mais cette indexation doit être faite correctement pour qu’elle soit efficace. En effet, Power BI utilise 2 modes pour interroger les sources de données. Et chacun de ces modes fonctionne très différemment. Chacun d’eux a besoin de sa propre stratégie d’indexation dédiée.
-
Mode d’importation Power BI
Dans ce mode, Power BI met en cache toutes les données dans la mémoire. Puis il utilise le langage DAX pour exécuter les transformations du rapport.
Afin de déterminer quelle est la meilleure stratégie d’indexation, je vais créer plusieurs types d’index et observer leurs statistiques. Ces statistiques sont obtenues en rafraîchissant plusieurs fois les données du modèle. Voici la requête pour les afficher :
La colonne Logical_Reads me permet de déterminer l’efficacité de chaque index. La table qui ressort en haut de la liste est la table de fait « Fact Sale » car c’est la plus consommatrice.
Après avoir créé plusieurs index et relevé leurs statistiques, voici les résultats :
Du point de vue du mode d’importation Power BI, l’index columnstore offre les meilleures performances de lecture par rapport à l’index Rowstore le plus compressé. Bien qu’il faille garder à l’esprit que Columnstore a ses limites, en particulier lors de la mise à jour des enregistrements. En effet, il peut y avoir une dégradation significative des performances lors de la mise à jour ou de la suppression d’enregistrements.
Par conséquent, si vous avez une table qui nécessite des mises à jour fréquentes des enregistrements, je suggère d’avoir une combinaison de ces deux index :
- Index de Rowstore en cluster qui couvre la clause « where » de l’instruction de mise à jour
- Index columnstore non clusterisé avec toutes les colonnes de table utilisées incluses
-
Mode DirectQuery
Le mode DirectQuery compile la logique Power BI en requêtes T-SQL qui sont exécutées au moment où un utilisateur demande des données dans l’application. La logique qui est traduite en T-SQL comprend des filtres, des mesures, des graphiques, DAX, des tables, des agrégations et plus encore. Ce mode permet d’obtenir les données à jour en temps réel, mais pose en même temps un défi important pour les performances du moteur SQL.
Pour réaliser des tests de performance, je construis d’abord le tableau suivant :
Après avoir actualisé plusieurs fois la table, je récupère les statistiques pour le mode Direct Query Power BI uniquement à partir de la requête suivante :
J’obtiens le résultat suivant :
La première ligne correspond à la requête SQL pour exécuter le rapport. Afin de déterminer la meilleure stratégie d’indexation, je procède de la même façon :
Je constate que les résultats sont meilleurs avec un clustered Columnstore. Ce résultat n’est pas étonnant, car ce type d’index permet d’exécuter des analyses en temps réel performantes sur une charge de travail de type entrepôt de données. Il offre également un niveau très élevé de compression de données. Ce type d’index est donc bien adapté pour les modèles en mode Direct Query. Je recommande fortement pour les rapports qui utilisent des filtres sélectifs (date, entité) d’ajouter un index rowstore non clusterisé.