Introduction
Power BI est la solution reporting de Microsoft. Elle est composée entre autres de Power BI Desktop, de Power Query, etc.
A l’heure où savoir traiter des données volumineuses est plus nécessaire que jamais, Power BI nous fournit de nombreux outils dont les pré-agrégations pour répondre au mieux à cette demande.
Un des intérêts de Power BI Desktop est qu’à travers le mode de connexion « en Import » (qui est le mode de connexion recommandé par défaut par Microsoft) il donne accès à Vertipaq : un moteur de base de données qui autorise de meilleures performances.
Néanmoins, même avec Vertipaq, il peut arriver que les performances ne soient pas suffisantes malgré tout, nous allons donc voir ensemble comment mettre en place des (pré) agrégations avec Power BI.
Cette méthode permet de définir à l’avance des agrégations qui sont susceptibles d’être visualisées par les utilisateurs finaux afin de calculer leurs résultats une seule et unique fois car ils seront stockés en mémoire.
Ces agrégations ne sont pas triviales à mettre en place, nous allons voir ensemble comment les définir.
Le moteur de base de données Vertipaq
Vertipaq est le moteur de base de données qui permet, à l’aide d’une série d’algorithmes, de compresser les données et ainsi rendre les traitements en DAX plus rapides. Les traitements réalisés par Vertipaq sur vos données sont des traitements à forte valeur ajoutée en termes de performances.
Concrètement, ces traitements consistent à réaliser des compressions (faisant intervenir des dictionnaires et des hashs permettant de requêter plus rapidement les données), des encodages (pour que les données prennent moins de place de stockage) et des segmentations. Mais Vertipaq met aussi à profit les bases de données en colonnes pour traiter plus vite vos requêtes.
Ce sont tous ces points qui expliquent les différences de performances entre le mode de connexion « En Import » et le mode de connexion « Direct Query ». Pour en savoir plus sur les différences entre ces deux modes de connexions : https://www.decivision.com/blog/microsoft-bi/power-bi-desktop-directquery-vs-import
Vertipaq a donné naissance à un module Excel nommé « Vertipaq Analyser » qui permet de mesurer les performances de vos tableaux de bord.
Les (pré) agrégations
Si dans un de vos rapports vous visualisez souvent vos données avec la même dimension d’agrégation, ou si vous utilisez souvent la même agrégation dans vos formules DAX, alors les pré-agrégations peuvent améliorer les performances de votre rapport.
Une pré-agrégation simplement appelée agrégation dans Power BI consiste à :
- Agréger des données/des résultats selon une ou plusieurs dimensions
- Stocker ces résultats dans une table dans le modèle de données
Tout ceci afin de calculer ces résultats une seule et unique fois car ils seront ensuite stockés en mémoire.
C’est une fonctionnalité de Power BI qui n’est pas triviale puisque si vous définissez une ou plusieurs agrégations qui ne sont pas utilisées, alors les performances de votre rapport empireront.
Nous allons voir ensemble dans quel cas et comment mettre en place ces agrégations. Puis comment vérifier qu’elles sont bien utilisées par vos requêtes.
Comment les mettre en place ?
Premièrement, cette fonctionnalité n’est pas adaptée au temps réel, car quand on veut afficher des données en temps réel avec Power BI, on utilise habituellement le type de connexion « Direct Query ». Bien sûr, le temps réel impliquerait de recalculer très régulièrement les résultats de l’agrégation et donc on ne gagnera pas en performances.
Les conditions à réunir pour obtenir une agrégation fonctionnelle sont :
- Créer une nouvelle table qui servira à stocker les résultats de l’agrégation
- Utiliser la boite de dialogue « Gérer les agrégations » pour choisir une donnée à agréger et au moins un champ dimension sur lequel grouper les résultats
- Grouper sur la clé de la table dimension
- Créer une relation entre la clé de la table dimension et la clé de la table d’agrégation (si c’est possible)
Vous trouverez ci-dessous le modèle vide permettant de définir les agrégations.
Comment les mettre en place ?
Afin d’utiliser la table de détails lors de l’agrégation, il faut que la table d’agrégation (ici Table_agg) soit masquée.
Pour ce faire, il faut cocher l’option dans les propriétés de cette table :
Dans votre table, le logo des champs va changer, il va passer de 
à 
.
Vous trouverez ci-dessous deux requêtes. La première qui ne permet pas d’utiliser l’agrégation définie précédemment, et la seconde qui utilise l’agrégation.
L’agrégation n’est pas atteinte ci-dessous du fait de l’utilisation de la table « Product », qui n’a pas le niveau de détail suffisant. La requête ne comprend pas quel « produit » utiliser.
Cette requête atteint bien l’agrégation par granularité des colonnes de la table « Date ».
« SalesAmount » est agrégée par la fonction « Somme ».
Quelles sont les subtilités ?
Vous savez maintenant comment définir des agrégations et vérifier qu’elles sont bien utilisées. Nous allons parcourir quelques subtilités supplémentaires quant à leur mise en place.
Il est possible qu’une requête atteigne/utilise plusieurs agrégations, c’est pourquoi le champ « Précédence » existe.
Ce champ permet d’indiquer dans quel ordre utiliser les agrégations en commençant par l’agrégation de plus grande précédence, cela correspond en tout point à un ordre de priorité. Une agrégation de précédence 10 sera donc utilisée avant une agrégation de précédence 3.
Les agrégations fonctionnent aussi bien avec des tables en mode de stockage « Importer » qu’avec des tables en mode de stockage « DirectQuery » ou « Double » (mode hybride entre l’Import et le DirectQuery). Néanmoins, si la table d’agrégation est en mode « Importer » et qu’une autre table du modèle est en mode DirectQuery, alors vous pourrez constater un décalage entre les données présentes dans ces deux tables, car l’agrégation nécessitera une actualisation des données du rapport pour être à jour.
Les modes de stockage peuvent être paramétrés dans le volet « Propriétés ».
Si vous souhaitez améliorer les performances d’un calcul souvent réalisé par vos utilisateurs et qui est du type « Quantité * Prix Net », la bonne pratique consiste à créer une mesure qui correspond à votre calcul, puis à créer une agrégation sur cette mesure.
Dernière information utile, si vous voyez apparaitre le message d’erreur suivant, c’est que la table dont vous essayez de vous servir comme agrégation, ici la table « DimMagasin1 », est une table à l’origine d’un filtrage à sens unique.
CONCLUSION DE L’EXPERT
Il est important de bien prendre conscience que seul le mode de connexion « En Import » donne accès à Vertipaq et donc à des rapports plus performants.
Les pré-agrégations peuvent être utilisées pour avoir accès à de meilleure performances en complément de Vertipaq ; néanmoins il convient de les utiliser avec prudence car mal utilisées elles peuvent rendre vos rapports plus lents qu’à l’origine. De plus elles nécessitent, à chaque fois que le modèle de données change, d’être vérifiées pour savoir si elles sont bien toujours utilisées ou si elles nécessitent une mise à jour pour s’adapter au nouveau modèle.
