Agrégations définies par l’utilisateur

Les agrégations dans Power BI peuvent améliorer les performances des requêtes sur les modèles sémantiques DirectQuery volumineux. En utilisant des agrégations, vous mettez en cache les données au niveau agrégé en mémoire. Vous pouvez configurer manuellement des agrégations dans Power BI dans le modèle de données, comme décrit dans cet article. Pour les abonnements Premium, vous pouvez activer la fonctionnalité d’agrégations automatiques dans les paramètres du modèle pour les créer automatiquement.

Création de tables d’agrégation

Selon le type de source de données, vous pouvez créer une table d’agrégations à la source de données en tant que table ou vue, requête native. Pour des performances optimales, créez une table d’agrégations en tant que table d’importation créée dans Power Query. Utilisez la boîte de dialogue Gérer les agrégations dans Power BI Desktop pour définir des agrégations pour les colonnes d’agrégation avec résumé, table de détails et propriétés de colonne de détail.

Les sources de données dimensionnelles, telles que les entrepôts de données et les data marts, peuvent utiliser des agrégations basées sur des relations. Les sources de données volumineuses basées sur Hadoop basent souvent des agrégations sur des colonnes GroupBy. Cet article décrit les différences classiques de modélisation des données Power BI pour chaque type de source de données.

Gérer les agrégations

Dans le volet Données de n’importe quel affichage Power BI Desktop, cliquez avec le bouton droit sur la table d’agrégations, puis sélectionnez Gérer les agrégations.

La boîte de dialogue Gérer les agrégations affiche une ligne pour chaque colonne de la table, où vous pouvez spécifier le comportement d’agrégation. Dans l’exemple suivant, les requêtes dans la table Sales detail sont redirigées en interne vers la table d’agrégation Sales Agg .

Dans cet exemple d’agrégation basée sur une relation, les entrées GroupBy sont facultatives. À l’exception de DISTINCTCOUNT, ils n’affectent pas le comportement d’agrégation et sont principalement destinés à la lisibilité. Sans les entrées GroupBy, les agrégations sont toujours effectuées, en raison des relations. Ce comportement diffère de l’exemple Big Data plus loin dans cet article, où les entrées GroupBy sont requises.

Vérifications

La boîte de dialogue Gérer les agrégations applique les validations :

• La colonne de détails doit avoir le même type de données que la colonne d’agrégation, sauf pour les fonctions de synthétisation « Count » et les lignes de table « Count ». Les lignes de table Count et Count sont disponibles uniquement pour les colonnes d’agrégation d’entiers et ne nécessitent pas de type de données correspondant.
• Les agrégations chaînées couvrant trois tables ou plus ne sont pas autorisées. Par exemple, les agrégations sur la table A ne peuvent pas faire référence à une table B qui a des agrégations faisant référence à une table C.
• Les agrégations en double, où deux entrées utilisent la même fonction Summarization et font référence au même tableau de détails et colonne de détail, ne sont pas autorisées.
• La table de détails doit utiliser le mode de stockage DirectQuery, et non l’importation.
• Le regroupement par une colonne de clé étrangère utilisée par une relation inactive et s’appuyant sur la fonction USERELATIONSHIP pour le calcul d'agrégation n’est pas pris en charge. En guise d’alternative, vous pouvez utiliser la fonction TREATAS au lieu de USERELATIONSHIP. Lorsque vous utilisez TREATAS, vérifiez qu’il n’existe aucune relation active entre les tables. Les agrégats peuvent encore être affectés lors de l'utilisation de TREATAS avec cette configuration.
• Les agrégations basées sur des colonnes GroupBy peuvent utiliser des relations entre des tables d’agrégation, mais la création de relations entre les tables d’agrégation n’est pas prise en charge dans Power BI Desktop. Si nécessaire, vous pouvez créer des relations entre des tables d’agrégation à l’aide d’un outil tiers ou d’une solution de script via des points de terminaison XML for Analysis (XMLA).

La plupart des validations sont appliquées en désactivant les valeurs déroulantes et en affichant le texte explicatif dans l’info-bulle.

Les tables d’agrégation sont masquées

Les utilisateurs disposant d’un accès en lecture seule au modèle ne peuvent pas interroger les tables d’agrégation. L’accès en lecture seule évite les problèmes de sécurité lorsqu’il est utilisé avec la sécurité au niveau des lignes (RLS). Les consommateurs et les requêtes font référence à la table de détails, et non à la table d’agrégation et n’ont pas besoin de connaître la table d’agrégation.

Pour cette raison, les tables d’agrégation sont masquées dans la vue Rapport . Si la table n’est pas déjà masquée, la boîte de dialogue Gérer les agrégations la définit comme masquée lorsque vous sélectionnez Appliquer tout.

Modes de stockage

La fonctionnalité d’agrégation fonctionne avec les modes de stockage au niveau de la table. Les tables Power BI peuvent utiliser les modes DirectQuery, Import ou Dual Storage. DirectQuery envoie des requêtes directement au serveur principal, tandis que l’importation met en cache les données en mémoire et envoie des requêtes aux données mises en cache. Toutes les sources de données d'importation Power BI et de DirectQuery non multidimensionnelles fonctionnent avec des agrégations.

Pour définir le mode de stockage d’une table agrégée à importer pour accélérer les requêtes, sélectionnez la table agrégée en mode Modèle Power BI Desktop. Dans le volet Propriétés , développez Avancé, faites défiler la sélection en mode Stockage, puis sélectionnez Importer. Une fois que vous avez défini le mode de stockage sur Importer, vous ne pouvez pas le modifier à nouveau.

Pour plus d’informations sur les modes de stockage de tables, consultez Gérer le mode de stockage dans Power BI Desktop.

RLS pour les agrégations

Pour fonctionner correctement pour les agrégations, les expressions RLS doivent filtrer à la fois la table d’agrégation et la table de détails.

Dans l’exemple suivant, l’expression RLS sur la table Geography fonctionne pour les agrégations, car Geography se trouve sur le côté filtrage des relations avec la table Sales et la table Sales Agg . Les requêtes qui utilisent la table d'agrégation et celles qui ne l'utilisent pas ont toutes deux le RLS correctement appliqué.

Une expression RLS sur la table Product filtre uniquement la table Sales de détail, et non la table Sales Agg agrégée. Étant donné que la table d’agrégation est une autre représentation des données de la table de détails, elle serait non sécurisée pour répondre aux requêtes de la table d’agrégation si le filtre RLS ne peut pas être appliqué. Filtrer uniquement la table de détails n'est pas recommandé, car les requêtes utilisateur de ce rôle ne bénéficient pas des résultats d’agrégation.

Expression RLS qui filtre uniquement la table d’agrégation Sales Agg et non la table Sales detail n’est pas autorisée.

Pour les agrégations basées sur des colonnes GroupBy, une expression RLS appliquée à la table de détails peut filtrer la table d’agrégation, car toutes les colonnes GroupBy de la table d’agrégation sont couvertes par la table de détails. En revanche, un filtre RLS sur la table d’agrégation ne peut pas filtrer la table de détails. Il est donc interdit.

Agrégation basée sur des relations

Les modèles dimensionnels utilisent généralement des agrégations basées sur des relations. Les modèles Power BI des entrepôts de données et des data marts ressemblent à des schémas en étoile et en flocon, avec des relations entre les tables de dimension et les tables de faits.

Dans l’exemple suivant, le modèle obtient les données d’une seule source de données. Les tables utilisent le mode de stockage DirectQuery. La table de faits Sales contient des milliards de lignes. La définition du mode de stockage Sales to Import pour la mise en cache consommerait une surcharge considérable en mémoire et en ressources.

Au lieu de cela, créez la table d’agrégation Sales Agg . Dans la table Sales Agg , le nombre de lignes est égal à la somme de SalesAmount groupée par CustomerKey, DateKey et ProductSubcategoryKey. La table Sales Agg est à une granularité supérieure à Sales. Par conséquent, au lieu de milliards, elle peut contenir des millions de lignes, ce qui est plus facile à gérer.

Si les tables de dimension suivantes sont utilisées le plus souvent pour les requêtes avec une valeur métier élevée, elles peuvent filtrer Sales Agg, à l’aide de relations un-à-plusieurs ou plusieurs-à-un .

• Géographie
• Client
• Date
• Sous-catégorie de produit
• Catégorie de produit

L’image suivante montre ce modèle.

Le tableau suivant montre les agrégations de la table Sales Agg .

La modification du mode de stockage de la table agrégée Sales Agg en Importation ouvre une boîte de dialogue indiquant que les tables de dimension associées peuvent être définies sur le mode de stockage Dual.

La définition des tables de dimension associées sur Double leur permet d’agir comme Import ou DirectQuery, en fonction de la sous-requête. Dans l’exemple :

• Requêtes qui agrègent les métriques de la table Sales Agg en mode Importation et qui regroupent par attributs des tables Dual associées retournent les résultats du cache en mémoire.
• Les requêtes qui agrègent les métriques de la table DirectQuery Sales et regroupent par attributs des tables Double associées, retournent des résultats en mode DirectQuery. La logique de requête, y compris l’opération GroupBy, est transmise à la base de données source.

Pour plus d’informations sur le mode de stockage double, consultez Gérer le mode de stockage dans Power BI Desktop.

Relations régulières et limitées

Les résultats d’agrégation basés sur les relations nécessitent des liens réguliers.

Les relations régulières incluent les combinaisons de mode de stockage suivantes, où les deux tables proviennent d’une seule source :

Le seul cas où une relation inter-sources est régulière est si les deux tables sont configurées pour l'importation. Les relations multilatérales sont toujours limitées.

Pour les tirs d’agrégation entre sources qui ne dépendent pas des relations, consultez Agrégations basées sur des colonnes de type GroupBy.

Exemples de requêtes d’agrégation basées sur des relations

La requête suivante utilise l’agrégation, car les colonnes de la table Date sont à un niveau de granularité permettant l'utilisation de l'agrégation. La colonne SalesAmount utilise l’agrégation Sum .

La requête suivante n’utilise pas l’agrégation. Malgré la demande de la somme de SalesAmount, la requête effectue une opération GroupBy sur une colonne de la table Product, dont la granularité ne permet pas l'utilisation de l’agrégation. Si vous observez les relations dans le modèle, une sous-catégorie de produit peut avoir plusieurs lignes Product . La requête ne peut pas déterminer le produit auquel agréger. Dans ce cas, la requête revient à DirectQuery et envoie une requête SQL à la source de données.

Les agrégations ne sont pas uniquement destinées aux calculs simples qui effectuent une somme simple. Les calculs complexes peuvent également bénéficier. Conceptuellement, un calcul complexe est divisé en sous-requêtes pour chaque SOMME, MIN, MAX et COUNT. Chaque sous-requête est évaluée pour déterminer si elle peut utiliser l’agrégation. Cette logique n’est pas vraie dans tous les cas en raison de l’optimisation du plan de requête, mais en général, elle doit s’appliquer. L’exemple suivant utilise l’agrégation :

La fonction COUNTROWS peut tirer parti des agrégations. La requête suivante utilise l’agrégation, car il existe une agrégation de lignes de table Count définie pour la table Sales .

La fonction AVERAGE peut tirer parti des agrégations. La requête suivante utilise l’agrégation, car AVERAGE est convertie en interne en une fonction SUM divisée par une fonction COUNT. Étant donné que la colonne UnitPrice a des agrégations définies pour SOMME et COUNT, l’agrégation est utilisée.

Dans certains cas, la fonction DISTINCTCOUNT peut tirer parti des agrégations. La requête suivante utilise l’agrégation, car il existe une entrée GroupBy pour CustomerKey, qui gère la distinctité de CustomerKey dans la table d’agrégation. Cette technique peut toujours atteindre le seuil de performances où plus de 2 à 5 millions de valeurs distinctes peuvent affecter les performances des requêtes. Toutefois, il peut être utile dans les scénarios où il existe des milliards de lignes dans la table de détails, mais 2 à 5 millions de valeurs distinctes dans la colonne. Dans ce cas, DISTINCTCOUNT peut fonctionner plus rapidement que l’analyse de la table avec des milliards de lignes, même si elle est mise en cache dans la mémoire.

Les fonctions DAX (Data Analysis Expressions) d’intelligence temporelle sont conscientes de l’agrégation. La requête suivante utilise l'agrégation, car la fonction DATESYTD génère une table de valeurs CalendarDay, et la table d'agrégation est à un niveau de granularité qui correspond aux colonnes de regroupement de la table Date. Il s’agit d’un exemple de filtre table sur la fonction CALCULATE, qui peut fonctionner avec des agrégations.

Agrégation basée sur les colonnes de GroupBy

Les modèles Big Data basés sur Hadoop ont des caractéristiques différentes des modèles dimensionnels. Pour éviter les jointures entre des tables volumineuses, les modèles Big Data n’utilisent souvent pas de relations, mais dénormalisent les attributs de dimension pour les tables de faits. Vous pouvez déverrouiller ces modèles Big Data pour une analyse interactive à l’aide d’agrégations basées sur des colonnes GroupBy.

Le tableau suivant contient la colonne numérique Mouvement à agréger. Toutes les autres colonnes sont des attributs par lesquels regrouper. La table contient des données IoT et un grand nombre de lignes. Le mode de stockage est DirectQuery. Les requêtes sur la source de données qui couvrent l'ensemble du modèle sont lentes en raison du volume pur.

Pour activer l’analyse interactive sur ce modèle, ajoutez une table d’agrégation qui regroupe la plupart des attributs, mais exclut les attributs de cardinalité élevée comme la longitude et la latitude. Cette approche réduit considérablement le nombre de lignes et est suffisamment petite pour s’adapter confortablement à un cache en mémoire.

Définissez les mappages d’agrégation pour la table Agg d’activité du pilote dans la boîte de dialogue Gérer les agrégations .

Dans les agrégations basées sur des colonnes GroupBy, les entrées GroupBy ne sont pas facultatives. Sans eux, les agrégations ne sont pas touchées. Ce comportement diffère de l’utilisation d’agrégations basées sur des relations, où les entrées GroupBy sont facultatives.

Le tableau ci-dessous montre les agrégations de la table Activité du Pilote Agg.

Définissez le mode de stockage de la table Agg d’activité du pilote agrégé sur Importer.

Exemple de requête d’agrégation GroupBy

La requête suivante utilise l’agrégation, car la colonne Date d’activité est couverte par la table d’agrégation. La fonction COUNTROWS utilise l’agrégation de lignes de table comptées .

En particulier pour les modèles qui contiennent des attributs de filtre dans les tables de faits, il est judicieux d’utiliser des agrégations de comptage des lignes de table. Power BI peut envoyer des requêtes au modèle à l’aide de COUNTROWS dans les cas où il n’est pas explicitement demandé par l’utilisateur. Par exemple, la boîte de dialogue de filtre affiche le nombre de lignes pour chaque valeur.

Techniques d’agrégation combinées

Vous pouvez combiner les relations et les techniques de colonnes GroupBy pour les agrégations. Les agrégations basées sur des relations peuvent exiger que les tables de dimension dénormalisées soient divisées en plusieurs tables. Si cette exigence est coûteuse ou peu pratique pour certaines tables de dimension, vous pouvez répliquer les attributs nécessaires dans la table d’agrégation pour ces dimensions et utiliser des relations pour d’autres.

Par exemple, le modèle suivant réplique mois, trimestre, semestre et année dans la table Sales Agg . Il n’existe aucune relation entre Sales Agg et la table Date , mais il existe des relations avec customer and Product Subcategory. Le mode de stockage sales Agg est Import.

Le tableau suivant montre les entrées définies dans la boîte de dialogue Gérer les agrégations pour la table Sales Agg . Les entrées GroupBy où Date est la table de détails sont obligatoires pour utiliser des agrégations pour les requêtes qui regroupent les attributs Date . Comme dans l’exemple précédent, les entrées GroupBy pour CustomerKey et ProductSubcategoryKey n’affectent pas l’utilisation de l’agrégation, à l’exception de DISTINCTCOUNT, en raison de la présence de relations.

Exemples de requêtes d’agrégation combinées

La requête suivante utilise l’agrégation, car la table d’agrégation couvre CalendarMonth et vous pouvez accéder à CategoryName via des relations un-à-plusieurs. La requête utilise l’agrégation SUM pour SalesAmount.

La requête suivante n’utilise pas l’agrégation, car la table d’agrégation ne couvre pas CalendarDay.

La requête time-intelligence suivante n’utilise pas l’agrégation, car la fonction DATESYTD génère une table des valeurs CalendarDay et la table d’agrégation ne couvre pas CalendarDay.

Priorité d’agrégation

La priorité d’agrégation permet à une sous-requête unique d’envisager plusieurs tables d’agrégation.

L’exemple suivant est un modèle composite contenant plusieurs sources :

• La table Driver Activity DirectQuery contient plus de mille milliards de lignes de données IoT provenant d’un système de données volumineux. Il utilise des requêtes de forage pour afficher des relevés IoT individuels dans des contextes de filtrage contrôlés.
• La table Activité du conducteur Agg est une table d’agrégation intermédiaire en mode DirectQuery. Il contient plus d’un milliard de lignes dans Azure Synapse Analytics (anciennement SQL Data Warehouse) et est optimisé à la source à l’aide d’index columnstore.
• La table d'importation de l'activité du conducteur Agg2 est à une granularité élevée, car les attributs de regroupement par sont peu nombreux et de faible cardinalité. Le nombre de lignes peut être aussi faible que des milliers, de sorte qu’il peut facilement s’adapter à un cache en mémoire. Ces attributs sont utilisés par un tableau de bord exécutif de haut profil. Les requêtes qui les font référence doivent donc être aussi rapides que possible.

L’empreinte mémoire de ce modèle est relativement petite, mais elle permet d'activer un modèle énorme. Il représente une architecture équilibrée, car elle répartit la charge de requête entre les composants de l’architecture, en les utilisant en fonction de leurs forces.

La boîte de dialogue Agrégations managées pour l’activité du pilote Agg2 définit le champ Prioritésur 10, ce qui est supérieur à celui de l’activité du pilote Agg. Le paramètre de priorité plus élevé signifie que les requêtes qui utilisent des agrégations prennent d’abord en compte l’activité du pilote Agg2 . Les sous-requêtes qui ne sont pas au niveau de la granularité que l’activité du pilote Agg2 peut répondre peuvent prendre en compte l’activité du pilote Agg à la place. Les requêtes détaillées qui ne peuvent pas être répondues par une table d’agrégation peuvent diriger vers l’activité du pilote.

La table spécifiée dans la colonne Table de détails est l’activité du pilote, et non l’activité du pilote Agg, car les agrégations chaînées ne sont pas autorisées.

Le tableau suivant présente les agrégations pour la table Agg2 de l’activité du pilote .

Détecter si les requêtes atteignent ou échouent à atteindre les agrégations

SQL Profiler peut détecter si les requêtes proviennent du moteur de stockage du cache en mémoire ou si DirectQuery les envoie (push) à la source de données. Vous pouvez utiliser le même processus pour détecter si les agrégations sont utilisées. Pour plus d’informations, consultez Requêtes qui atteignent ou manquent le cache.

SQL Profiler fournit également l’événement Query Processing\Aggregate Table Rewrite Query étendu.

L’extrait de code JSON suivant montre un exemple de sortie de l’événement lorsqu’une agrégation est utilisée.

• matchingResult indique que la sous-requête utilise une agrégation.
• dataRequest affiche les colonnes GroupBy et les colonnes agrégées que la sous-requête utilise.
• Le mappage affiche les colonnes de la table d’agrégation auxquelles elles sont associées.

Conserver les caches synchronisés

Les agrégations qui combinent les modes DirectQuery, Import et Dual Storage peuvent retourner différentes données, sauf si le cache en mémoire reste synchronisé avec les données sources. Par exemple, l’exécution de requête ne tente pas de masquer les problèmes de données en filtrant les résultats DirectQuery pour qu’ils correspondent aux valeurs mises en cache. Vous devrez peut-être gérer ces problèmes à la source. Les optimisations des performances ne doivent jamais compromettre votre capacité à répondre aux besoins de l’entreprise. Vous devez comprendre vos flux de données et concevoir en conséquence.

Considérations et limitations

• Les agrégations ne prennent pas en charge les paramètres de requête M dynamique.

• À compter d’août 2022, en raison des modifications apportées aux fonctionnalités, Power BI ignore les tables d’agrégation du mode d’importation avec des sources de données activées pour l’authentification unique en raison de risques de sécurité potentiels. Pour garantir des performances de requête optimales avec des agrégations, désactivez l’authentification unique pour ces sources de données.

Communauté

Power BI a une communauté dynamique où les MVP, les professionnels du décisionnel et les pairs partagent une expertise dans les groupes de discussion, les vidéos, les blogs et bien plus encore. Lorsque vous découvrez les agrégations, veillez à consulter ces ressources :

• Communauté Power BI
• Rechercher « Agrégations Power BI » sur Bing

Contenu connexe

• Agrégations automatiques
• Modèles composites