Le guide ultime des agrégations Power BI
Si vous avez utilisé une fonctionnalité de modèle composite dans Power BI, vous avez peut-être déjà entendu parler d’un autre concept extrêmement important et puissant : les agrégations ! En effet, dans de nombreux scénarios, notamment avec les modèles à l’échelle de l’entreprise, les agrégations sont un « ingrédient » naturel du modèle composite.
Cependant, comme les fonctionnalités des modèles composites peuvent également être exploitées sans agrégation, j’ai pensé qu’il serait logique d’expliquer le concept d’agrégation dans un article séparé.
Avant d’expliquer le fonctionnement des agrégations dans Power BI et d’examiner quelques cas d’utilisation spécifiques, répondons d’abord aux questions suivantes :
Pourquoi avons-nous besoin d’agrégations en premier lieu ? Quel est l’avantage d’avoir deux tables avec des données identiques dans le modèle ?
Avant de clarifier ces deux points, il est important de garder à l’esprit qu’il existe deux types d’agrégations différents dans Power BI.
- Agrégations définies par l’utilisateur étaient, jusqu’il y a quelques années, le seul type d’agrégation dans Power BI. Ici, vous êtes en charge de définir et de gérer les agrégations, même si Power BI identifie ultérieurement automatiquement les agrégations lors de l’exécution de la requête.
- Agrégations automatiques sont l’une des fonctionnalités les plus récentes de Power BI. Avec la fonctionnalité d’agrégations automatiques activée, vous pouvez prendre un café, vous asseoir et vous détendre, car les algorithmes d’apprentissage automatique collecteront les données sur les requêtes les plus fréquemment exécutées dans vos rapports et créeront automatiquement des agrégations pour prendre en charge ces requêtes.
La distinction importante entre ces deux types, bien sûr, outre le fait qu’avec les agrégations automatiques, vous n’avez rien d’autre à faire que d’activer cette fonctionnalité dans votre locataire, réside dans les limitations de licence. Bien que les agrégations définies par l’utilisateur fonctionnent à la fois avec Premium et Pro, les agrégations automatiques nécessitent actuellement une licence Premium.
À partir de maintenant, nous parlerons uniquement des agrégations définies par l’utilisateur, gardez cela à l’esprit.
Ok, voici une brève explication des agrégations et de leur fonctionnement dans Power BI. Voici le scénario : vous disposez d’une grande, très grande table de faits, qui peut contenir des centaines de millions, voire des milliards de lignes. Alors, comment gérer les demandes d’analyse sur une telle quantité de données ?
Vous créez simplement des tableaux agrégés ! En réalité, il est très rare, ou disons que c’est plus une exception qu’une règle, que l’exigence analytique soit de considérer la transaction individuelle, ou l’enregistrement individuel, comme le niveau de détail le plus bas. Dans la plupart des scénarios, vous souhaitez effectuer une analyse sur des données résumées : par exemple, combien de revenus avons-nous eu un jour spécifique ? Ou quel a été le montant total des ventes du produit X ? De plus, combien le client X a-t-il dépensé au total ?
De plus, vous pouvez regrouper les données sur plusieurs attributs, ce qui est généralement le cas, et résumer les chiffres pour une date, un client et un produit spécifiques.
Si vous vous demandez à quoi ça sert d’agréger les données… Eh bien, l’objectif final est de réduire le nombre de lignes et par conséquent, de réduire la taille globale du modèle de données, en préparant les données à l’avance.
Ainsi, si j’ai besoin de voir le montant total des ventes dépensées par le client X pour le produit Y au cours du premier trimestre de l’année, je peux profiter du fait que ces données sont déjà résumées à l’avance.
« Ingrédient » clé – Rendre Power BI « conscient » des agrégations !
Ok, c’est un côté de l’histoire. Vient maintenant la partie la plus intéressante. La création d’agrégations en soi ne suffit pas pour accélérer vos rapports Power BI : vous devez informer Power BI des agrégations !
Juste une remarque avant d’aller plus loin : la prise en compte de l’agrégation est quelque chose qui ne fonctionnera que si la table de faits d’origine utilise le mode de stockage DirectQuery. Nous viendrons bientôt vous expliquer comment concevoir et gérer des agrégations et comment définir le mode de stockage approprié de vos tables. Pour le moment, gardez simplement à l’esprit que la table de faits d’origine doit être en mode DirectQuery.
Commençons à construire nos agrégations !
Comme vous pouvez le voir dans l’illustration ci-dessus, notre modèle est assez simple : composé d’une table de faits (FactOnlineSales) et de trois dimensions (DimDate, DimStore et DimProduct). Toutes les tables utilisent actuellement le mode de stockage DirectQuery.
Allons créer deux tables supplémentaires que nous utiliserons comme tables agrégées : la première regroupera les données par date et par produit, tandis que l’autre utilisera la date et stockera pour le regroupement :
/*Table 1: Agg Data per Date & Product */
SELECT DateKey
,ProductKey
,SUM(SalesAmount) AS SalesAmount
,SUM(SalesQuantity) AS SalesQuantity
FROM FactOnlineSales
GROUP BY DateKey
,ProductKey/*Table 2: Agg Data per Date & Store */
SELECT DateKey
,StoreKey
,SUM(SalesAmount) AS SalesAmount
,SUM(SalesQuantity) AS SalesQuantity
FROM FactOnlineSales
GROUP BY DateKey
,StoreKey
J’ai renommé ces requêtes respectivement Sales Product Agg et Sales Store Agg et fermé l’éditeur Power Query.
Comme nous souhaitons obtenir les meilleures performances possibles pour la majorité de nos requêtes (ces requêtes qui récupèrent les données résumées par date et/ou produit/magasin), je vais changer le mode de stockage des tables agrégées nouvellement créées de DirectQuery à Import :
Désormais, ces tables sont chargées dans la mémoire cache, mais elles ne sont toujours pas connectées à nos tables de dimensions existantes. Créons des relations entre les dimensions et les tableaux agrégés :
Avant de continuer, permettez-moi de m’arrêter un instant et d’expliquer ce qui s’est passé lorsque nous avons créé des relations. Si vous vous souvenez de notre article précédent, j’ai mentionné qu’il existe deux types de relations dans Power BI : régulières et limitées. C’est important : chaque fois qu’il existe une relation entre les tables de différents groupes sources (le mode Import est un groupe source, DirectQuery en est un autre), vous aurez une relation limitée ! Avec toutes ses limites et contraintes.
Mais j’ai une bonne nouvelle pour vous ! Si je change le mode de stockage de mes tables de dimensions sur Dual, cela signifie qu’elles seront également chargées dans la mémoire cache et, selon la table de faits qui fournit les données au moment de la requête, la table de dimensions se comportera soit en mode Import (si la requête cible les tables de faits en mode Import), soit en DirectQuery (si la requête récupère les données de la table de faits d’origine dans DirectQuery) :
Comme vous pouvez le constater, il n’y a plus de relations limitées, ce qui est fantastique !
Donc, pour conclure, notre modèle est configuré comme suit :
- La table FactOnlineSales originale (avec toutes les données détaillées) – DirectQuery
- Tableaux de dimensions (DimDate, DimProduct, DimStore) – Double
- Tableaux agrégés (Sales Product Agg et Sales Store Agg) – Importer
Génial! Nous avons maintenant nos tables agrégées et les requêtes devraient s’exécuter plus rapidement, n’est-ce pas ? Bip! Faux!
Le visuel du tableau contient exactement ces colonnes que nous avons pré-agrégées dans notre tableau Sales Product Agg. Alors, pourquoi diable Power BI exécute-t-il une requête DirectQuery au lieu d’obtenir les données de la table importée ? C’est une bonne question !
Rappelez-vous quand je vous ai dit au début que nous devions créer Power BI conscient de la table agrégée, pour qu’elle puisse être utilisée dans les requêtes ?
Revenons à Power BI Desktop et faisons ceci :
Cliquez avec le bouton droit sur le tableau Sales Product Agg et choisissez l’option Gérer les agrégations :
Quelques remarques importantes ici : pour que les agrégations fonctionnent, les types de données entre les colonnes de la table de faits d’origine et de la table agrégée doivent correspondre! Dans mon cas, j’ai dû changer le type de données de la colonne SalesAmount dans ma table agrégée de « Nombre décimal » à « Nombre décimal fixe ».
De plus, vous voyez le message écrit en rouge : cela signifie qu’une fois que vous aurez créé un tableau agrégé, il sera caché à l’utilisateur final ! J’ai appliqué exactement les mêmes étapes pour ma deuxième table agrégée (Store), et maintenant ces tables sont masquées :
Revenons maintenant en arrière et actualisons notre page de rapport pour voir si quelque chose a changé :
Bon! Pas de DirectQuery cette fois, et au lieu de presque 2 secondes nécessaires au rendu de ce visuel, cette fois cela n’a pris que 58 millisecondes ! De plus, si je récupère la requête et que je vais dans DAX Studio pour vérifier ce qui se passe…
Comme vous le voyez, la requête d’origine a été mappée pour cibler la table agrégée à partir du mode d’importation, et le message « Match trouvé » indique clairement que les données du visuel proviennent de la table Sales Product Agg ! Même si notre utilisateur n’a même pas la moindre idée que ce tableau existe dans le modèle !
La différence de performances, même sur cet ensemble de données relativement petit, est énorme !
Plusieurs tableaux agrégés
Maintenant, vous vous demandez probablement pourquoi j’ai créé deux tableaux agrégés différents. Eh bien, disons que j’ai une requête qui affiche les données de différents magasins, également regroupées par dimension de date. Au lieu d’avoir à analyser 12,6 millions de lignes en mode DirectQuery, le moteur peut facilement servir les numéros du cache, à partir de la table qui ne contient que quelques milliers de lignes !
Essentiellement, vous pouvez créer plusieurs tables agrégées dans le modèle de données – non seulement en combinant deux attributs de regroupement (comme nous l’avons eu ici avec Date+Produit ou Date+Store), mais en incluant des attributs supplémentaires (par exemple, en incluant Date et Product et Store dans une seule table agrégée). De cette façon, vous augmenterez la granularité du tableau, mais dans le cas où votre visuel aurait besoin d’afficher les numéros à la fois du produit et du magasin, vous pourrez récupérer les résultats du cache uniquement !
Dans notre exemple, comme je n’ai pas de données pré-agrégées au niveau qui inclut à la fois le produit et le magasin, si j’inclus un magasin dans le tableau, je perds l’avantage d’avoir des tableaux agrégés :
Ainsi, afin de tirer parti des agrégations, vous devez les définir exactement au même niveau de grain que celui requis par le visuel !
Priorité d’agrégation
Il y a une autre propriété importante à comprendre lorsque vous travaillez avec des agrégations : la priorité ! Lorsque la boîte de dialogue Gérer les agrégations s’ouvre, il existe une option permettant de définir la priorité de l’agrégation :
Cette valeur « indique » à Power BI sur quelle table agrégée utiliser au cas où la requête pourrait être satisfaite à partir de plusieurs agrégations différentes ! Par défaut, il est défini sur 0, mais vous pouvez modifier la valeur. Plus le nombre est élevé, plus la priorité de cette agrégation est élevée.
Pourquoi est-ce important ? Eh bien, pensez à un scénario dans lequel vous avez la table de faits principale avec des milliards de lignes. Et vous créez plusieurs tableaux agrégés sur différents grains :
- Tableau agrégé 1 : regroupe les données au niveau de la date – comporte environ 2 000 lignes (5 années de dates)
- Tableau agrégé 2 : regroupe les données au niveau de la date et du produit – contient environ 100 000 lignes (5 années de dates x 50 produits)
- Tableau agrégé 3 : regroupe les données au niveau de la date, du produit et du magasin – comporte environ 5 000 000 lignes (100 000 du grain précédent x 50 magasins)
Supposons maintenant que le visuel du rapport affiche des données agrégées au niveau de la date uniquement. Qu’en pensez-vous : vaut-il mieux scanner le tableau 1 (2 000 lignes) ou le tableau 3 (5 millions de lignes) ? Je pense que vous connaissez la réponse 🙂 En théorie, la requête peut être satisfaite à partir des deux tables, alors pourquoi s’appuyer sur le choix arbitraire de Power BI ?!
Au lieu de cela, lorsque vous créez plusieurs tables agrégées, avec différents niveaux de granularité, assurez-vous de définir la valeur de priorité de manière à ce que les tables avec une granularité inférieure soient prioritaires !
Conclusion
Les agrégations sont l’une des fonctionnalités les plus puissantes de Power BI, en particulier dans les scénarios avec de grands ensembles de données ! Bien que les caractéristiques et les agrégations des modèles composites puissent être utilisées indépendamment l’une de l’autre, ces deux modèles sont généralement utilisés en synergie, pour fournir l’équilibre le plus optimal entre performances et disponibilité de tous les détails des données !
Merci d’avoir lu!
