Support formation Architecture Power BI

Agrégations manuelles et automatiques

Les agrégations dans Power BI consistent à créer une version agrégée d’une table, puis de paramétrer le modèle pour qu’il choisisse ensuite la version agrégée des données - ou pas - selon la requête à afficher.

Agrégations manuelles

0- Pourquoi agréger ?

On utilisera cette fonctionnalité quand on a :

  1. un grand volume de données,
  2. une accélération de l’actualisation
  3. une réduction de la taille du modèle
  4. anticiper (l’augmentation de la taille du modèle)

On ajustera donc la granularité :

Sans titre.png
Sans titre.png

Pour créer un exemple, installer SQL Server et la base de données Adventure Works DW YYYY.

1- Créer une table agrégée

Choisissez l’une ou l’autre des méthodes.

a) A la source

Evidement la meilleur option si vous avez accès à la source.

  1. Accueil > Obtenir les données > SQL Server
  2. Compléter la fenêtre avec le code ci-dessous, en prenant soin de cocher Importer :
image.png
SELECT
 FactInternetSales.OrderDateKey,
 FactInternetSales.ProductKey,
 SUM([OrderQuantity]) AS OrderQuantity,
 SUM([SalesAmount]) AS [SalesAmount],
 COUNT(*) AS CoutOfRows
FROM 
 dbo.FactInternetSales
GROUP BY 
 FactInternetSales.OrderDateKey,
 FactInternetSales.ProductKey

Vous pouvez aussi créer une vue :CREATE OR ALTER VIEW dbo.FactInternetSales_Agg_DateProductASSELECT FactInternetSales.OrderDateKey, FactInternetSales.ProductKey, SUM([OrderQuantity]) AS OrderQuantity, SUM([SalesAmount]) AS [SalesAmount], COUNT(*) AS CoutOfRowsFROM dbo.FactInternetSalesGROUP BY FactInternetSales.OrderDateKey, FactInternetSales.ProductKey

b) Avec Power Query

  1. Dans Power Query, clic droit > Référence, pour faire référence à la requête.
  2. La renommer par exemple FactInternetSales_Agg_DateProduct (NomDeLaTable suivi de Agg suivi des champs de dimension le lequel porte l’agrégation)
  3. Supprimer les colonnes que l’on ne souhaite pas agréger, avec Accueil > Choisir les colonnes.
  4. Transformer > Regrouper par, par exemple
    image.png

2- Gérer les agréations

Dans Power BI Desktop :

  1. S’assurer que les champs numériques (SalesAmount dans notre cas) sont de même type dans les deux tables (la version de base et la version agrégée). Par exemple, Nombre décimale fixe dans les 2 tables.
  2. A des fins de test de performance, créer une mesure, par exemple la somme des ventes :Somme de SalesAmount = SUM(FactInternetSales[SalesAmount])
  3. Créer un graphique simple avec la mesure et une dimension (par exemple Color ou CalendarYear).
  4. Afficher > Analyseur de performance. Cliquer sur Démarrer l’enregistrement puis sur Actualiser les visuels. Constater la vitesse (lente) :
    image.png
  5. Définir le mode Double pour les tables de dimensions, à partir du volet Propriétés de la Vue de modèle.
    image.png

    Cf. le support Modèle composite pour des explications sur ce mode de stockage.

  6. Clic droit clic droit sur la table agrégée > Gérer les agrégations. Compléter la boîte en faisant correspondre les colonnes agrégées. Par exemple, la colonne SalesAmount sera la Somme (Résumé) de la table FactInternetSales (Table de détails) de la colonne SalesAmount (Colonne de détails). Cliquer sur Appliquer tout.
    image.png

Les GroupBy sur les dimensions sont nécessaires s’il n’a pas de relation dans le modèle vers ces dimensions. Nous avons des relations dans le modèle, donc pas de GroupBy ici.

8. Relancer l’analyseur de performance : Actualiser les visuels.

image.png

L’actualisation prend effectivement beaucoup moins de temps…

Résumé des modes de stockage avec une agrégation manuelle

Sans titre.png
Sans titre.png

3- Vérifier l’utilisation de la table agrégée [WiP]

Agrégations automatiques

  • Beaucoup plus simples à mettre en œuvre que les agréations manuelles
  • Utilisent le Machine Learning (ML)
  • Conditions :
    • Licence Power BI Premium par capacité, Premium par utilisateur ou Power BI Embedded
    • Être propriétaire du modèle
  • Concernent les sources suivantes :
    • Azure SQL Database
    • Pool SQL dédié Azure Synapse
    • SQL Server 2019 ou version ultérieure
    • Google BigQuery
    • Snowflake
    • Databricks
    • Amazon Redshift
  • Mise en pratique optimum :
    • Peuvent être activées en même temps que les agréations manuelles.
      • S’appliquent aux requêtes avec connexions DirectQuery qui ne trouvent pas leur réponse dans les tables d’agrégation définies par l’utilisateur
    • Compatible avec la Mise en cache des requêtes · Support Formation Architecture Power BI

      “Avec la mise en cache des requêtes, le service met en cache seulement les requêtes pour le chargement initial de la page de rapport : les performances des requêtes ne sont donc pas améliorées quand les utilisateurs interagissent avec un rapport. En revanche, les agrégations automatiques optimisent la plupart des requêtes de rapport en mettant préalablement en cache les résultats agrégés des requêtes, notamment les requêtes générées quand des utilisateurs interagissent avec des rapports”.

    • Compatible avec la Mise en cache des requêtes.
  • A la première actualisation de la journée, le modèle est entrainé (apprentissage), les autres actualisations de la journée n’entraine pas le modèle.
  • En pratique : dans les Paramètres du modèle, section Actualisation planifiée et optimisation des performances.
    • Cocher Activé dans la zone Formation aux agréation automatiques.
    • Cocher également Activé dans la zone Configurer une planification d’actualisation.
      image.png