Support formation Architecture Power BI

Optimisations

Agrégations manuelles et automatiques

⁠

Intro to Power BI Aggregations⁠

(DAX TIPS)

⁠

The How and Why of Power BI Aggregations⁠

(Guy in a Cube)

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 :

un grand volume de données,

une accélération de l’actualisation

une réduction de la taille du modèle

anticiper (l’augmentation de la taille du modèle)

On ajustera donc la granularité :

⁠

⁠

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.

Accueil > Obtenir les données > SQL Server

Compléter la fenêtre avec le code ci-dessous, en prenant soin de cocher Importer :

⁠

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_DateProduct AS 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

b) Avec Power Query

Dans Power Query, clic droit > Référence, pour faire référence à la requête.

La renommer par exemple FactInternetSales_Agg_DateProduct (NomDeLaTable suivi de Agg suivi des champs de dimension le lequel porte l’agrégation)

Supprimer les colonnes que l’on ne souhaite pas agréger, avec Accueil > Choisir les colonnes.

Transformer > Regrouper par, par exemple

⁠

2- Gérer les agréations

Dans Power BI Desktop :

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.

A des fins de test de performance, créer une mesure, par exemple la somme des ventes : Somme de SalesAmount = SUM(FactInternetSales[SalesAmount])

Créer un graphique simple avec la mesure et une dimension (par exemple Color ou CalendarYear).

Afficher > Analyseur de performance. Cliquer sur Démarrer l’enregistrement puis sur Actualiser les visuels. Constater la vitesse (lente) :

⁠

Définir le mode Double pour les tables de dimensions, à partir du volet Propriétés de la Vue de modèle.

⁠

Cf. le support

Modèle composite⁠

pour des explications sur ce mode de stockage.

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.

⁠

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.

⁠

L’actualisation prend effectivement beaucoup moins de temps…

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

⁠

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.