Aller au contenu principal
Dernière mise à jour le

Expressions de niveau de détail (LOD)

Les expressions de niveau de détail (LOD) vous permettent de spécifier une granularité à laquelle les agrégations sont calculées, différente du niveau de détail de vos visualisations. Cette page explique comment utiliser les expressions LOD dans les tableaux de bord AI/BI.

Que sont les expressions de niveau de détail ?​

Les expressions de niveau de détail vous permettent de spécifier exactement les dimensions à utiliser lors du calcul d'un agrégat, quelles que soient les dimensions présentes dans votre visualisation. Cela vous donne un contrôle précis sur la portée de vos calculs. Les expressions de niveau de détail peuvent être des dimensions ou des mesures.

Il existe deux types d'expressions de niveau de détail :

  • Niveau de détail fixe : agrège sur un ensemble de dimensions prédéfini spécifiées dans l'expression elle-même, en ignorant toutes les autres dimensions de la visualisation.
  • Niveau de détail plus grossier : agrégez à un niveau plus grossier que la visualisation en excluant une dimension spécifique de l’ensemble de regroupement.

Les expressions de niveau de détail sont créées à l'aide de fonctions de fenêtre dans les calculs de dataset dans des modèles d'utilisation spécifiques.

Quand utiliser des expressions de niveau de détail​

Utilisez des expressions de niveau de détail lorsque vous avez besoin de :

  • Calculer les pourcentages du total (par exemple, la part de chaque catégorie dans le total des ventes).
  • Comparez les valeurs individuelles aux agrégats de l'ensemble du dataset (par exemple, Ventes par rapport aux Ventes moyennes).
  • Créez des métriques au niveau des cohortes ou des segments qui restent constantes à travers différents regroupements.
  • Effectuer des agrégations à plusieurs niveaux dans un seul calcul.

Définir des expressions à un niveau de détail fixe​

Une expression de niveau de détail fixe calcule un agrégat à la granularité que vous définissez, en ignorant les dimensions de votre visualisation. Les expressions de niveau de détail fixe sont des dimensions et sont implémentées à l'aide de fonctions de fenêtre scalaires avec des clauses PARTITION BY.

Syntaxe​

SQL
<AGGREGATE_FUNCTION>(<column>) OVER (PARTITION BY <dimension1>, <dimension2>, ...)

Pour agréger l'ensemble du dataset, omettez la clause PARTITION BY et laissez des parenthèses vides après OVER.

Quand utiliser un niveau de détail fixe​

Utilisez des expressions de niveau de détail fixe lorsque vous avez besoin :

  • Aucune dépendance aux groupements de visualisation : métriques ayant un partitionnement statique pour toutes les utilisations.
  • **Agrégats au niveau du dataset** : agrégats globaux comparés à d'autres niveaux de lignes ou regroupements (par exemple, pourcentage des ventes totales par région).
  • Hiérarchies à plusieurs niveaux : Métriques de niveau détaillé et de niveau agrégé dans la même visualisation.

Exemple : Totaux régionaux des ventes​

Supposons que vous ayez un dataset de ventes et que vous souhaitiez afficher les ventes de chaque produit ainsi que les ventes totales pour sa région. Voici l'échantillon de données :

Région

Produit

Ventes

Ouest

Ordinateur portable

5 000

Ouest

Souris

500

Est

Ordinateur portable

6000

Est

Surveillance

3 000

Région

Produit

Ventes

Ouest

Ordinateur portable

5 000

Ouest

Souris

500

Est

Ordinateur portable

6000

Est

Surveillance

3 000

Pour calculer les ventes totales par région, utilisez :

SQL
SUM(Sales) OVER (PARTITION BY Region)

Résultat :

Région

Produit

Ventes

Total par région

Ouest

Ordinateur portable

5 000

5 500

Ouest

Souris

500

5 500

Est

Ordinateur portable

6000

9000

Est

Surveillance

3 000

9000

Région

Produit

Ventes

Total par région

Ouest

Ordinateur portable

5 000

5 500

Ouest

Souris

500

5 500

Est

Ordinateur portable

6000

9000

Est

Surveillance

3 000

9000

Chaque ligne indique les ventes de chaque produit et le total fixe pour sa région. Le total de la région reste constant, quelle que soit la façon dont vous filtrez ou regroupez par produit.

Exemple : Pourcentage du total​

Les expressions de niveau de détail fixe peuvent être composées en des expressions plus complexes, y compris des mesures calculées effectuant des calculs de « pourcentage du total ». Pour ce faire, vous pouvez référencer des expressions de niveau de détail fixe au sein d’une expression de mesure afin qu’elles soient dynamiques par rapport aux regroupements de visualisation.

Par exemple, nous pouvons d'abord définir total_sales comme une expression de niveau de détail fixe en tant que calcul autonome opérant sur l'ensemble du dataset :

SQL
SUM(sales) OVER ()

Nous pouvons ensuite faire référence à l'expression de niveau de détail fixe dans une définition de mesure calculée :

SQL
SUM(Sales) / ANY_VALUE(total_sales)

L'expression ci-dessus peut être utilisée comme mesure dans des visualisations qui regroupent par n'importe quelle dimension, telle que le produit, afin de montrer le pourcentage des ventes totales de chaque produit distinct.

remarque

Une expression de niveau de détail fixe doit être encapsulée dans une fonction d'agrégation pour être utilisée dans un calcul de mesure. Si le résultat de l'expression de niveau de détail fixe devrait être constant et répété au sein des lignes d'un groupe, une fonction comme ANY_VALUE peut être utilisée pour renvoyer une valeur unique des résultats agrégés, comme illustré dans l'exemple ci-dessus.

Filtrage sur des expressions de niveau de détail fixe​

Étant donné que les expressions de niveau de détail fixe sont calculées avant l'application des regroupements et des filtres de visualisation, si vous souhaitez qu'elles soient affectées par des filtres dynamiques sur votre tableau de bord, vous devez définir ces filtres comme des parameter qui affectent le texte sous-jacent du dataset SQL. Pour plus de détails sur les parameter de tableau de bord, consultez Travailler avec les parameter de tableau de bord.

Définition des expressions à un niveau de détail plus grossier​

Les expressions à un niveau de détail plus grossier calculent un agrégat à une granularité plus grossière que la granularité de la visualisation en excluant une ou plusieurs dimensions de l’ensemble de regroupement de la visualisation. De telles expressions sont des mesures et sont implémentées à l'aide de fonctions de fenêtre d'agrégation avec une clause PARTITION BY qui utilise * pour représenter toutes les partitions héritées de la query environnante.

Syntaxe​

SQL
<AGGREGATE_EXPRESSION> AGGREGATE OVER (
  PARTITION BY * EXCEPT (<field> [, ...])
)

Dans cette syntaxe :

  • PARTITION BY * représente toutes les partitions héritées du regroupement de la visualisation
  • EXCEPT (<field> [, ...]) spécifie les dimensions à exclure de ces partitions

Pour plus de détails, consultez Fonctions d'agrégation de fenêtre.

Exemples :

Exclure une seule dimension :

SQL
SUM(Sales) AGGREGATE OVER (PARTITION BY * EXCEPT (Region))

Exclure plusieurs dimensions :

SQL
SUM(Sales) AGGREGATE OVER (PARTITION BY * EXCEPT (Region, Product))

Combinez les exclusions avec les agrégations partielles :

SQL
SUM(Sales) AGGREGATE OVER (PARTITION BY * EXCEPT (Customer) ORDER BY Date TRAILING 7 DAY)

Quand utiliser un niveau de détail plus grossier​

Utilisez des expressions avec un niveau de détail plus grossier lorsque vous avez besoin :

  • Regroupements dynamiques : Calculer des pourcentages ou des agrégats qui s'adaptent aux regroupements de visualisation (par exemple, le pourcentage du total pour toute dimension sélectionnée)
  • Agrégations tenant compte des filtres : Calculer à une granularité plus grossière tout en respectant les filtres du tableau de bord (à l’exception de ceux sur les dimensions exclues)

Exemple : Pourcentage du total​

Le cas d'utilisation le plus courant pour les expressions de niveau de détail plus grossier est le calcul d'un pourcentage de la valeur totale. Pour calculer un pourcentage des ventes totales par Region, mais au sein d'une partition dynamique :

SQL
SUM(Sales) / (SUM(Sales) AGGREGATE OVER (PARTITION BY * EXCEPT (Region)))

Cette expression :

  1. Calcule les Ventes totales dans le partitionnement de la visualisation, tels que Region et Product.
  2. Calcule les ventes totales dans le partitionnement de la visualisation, à l'exclusion de Region (agrégation sur toutes les valeurs Product).
  3. Divise les deux pour obtenir un pourcentage représentant la part du total des ventes que chaque Region contribue pour chaque Product.

Pour exclure plusieurs dimensions :

SQL
SUM(Sales) / (SUM(Sales) AGGREGATE OVER (PARTITION BY * EXCEPT (Region, Product)))

Cela calcule le pourcentage des Ventes totales en plus des sous-totaux des appariements Region et Product.

Exemples​

Fréquence des ventes de produits​

Le calcul du nombre de ventes pour chaque produit est simple, mais le calcul du nombre total de produits au sein de plages de ventes spécifiques nécessite une approche différente. Par exemple, réfléchissez à la manière dont vous créeriez un histogramme qui affiche le nombre de produits regroupés par tranches de volume de ventes.

Calculs personnalisés :

  • Définir sale_count comme une expression de niveau de détail fixe : COUNT(*) OVER (PARTITION BY Product)

Utilisation : créez un histogramme groupé par sale_count sur l'axe des X, et sélectionnez COUNT(*) sur l'axe des Y.

Comparez à la moyenne de la cohorte​

Comment la taille des transactions se compare-t-elle à la taille moyenne des transactions dans la même région ?

Calculs personnalisés :

  • Définir average_deal_amount_by_region comme une expression de niveau de détail fixe : AVG(deal_amount) OVER (PARTITION BY Region)
  • Définir percentage_of_region_average comme deal_amount - average_deal_amount_by_region

Les deux expressions ci-dessus peuvent également être combinées en un seul calcul.

Utilisation : définissez une table et sélectionnez percentage_of_region_average ainsi que toute autre dimension nécessaire à la visualisation

Limitations​

DISTINCT dans les fonctions de fenêtre scalaires​

Vous ne pouvez pas utiliser DISTINCT directement dans les fonctions de fenêtre scalaires. Par exemple, cela ne fonctionne pas :

SQL
COUNT(DISTINCT customer_id) OVER (PARTITION BY region)

Utilisez plutôt ARRAY_SIZE avec COLLECT_SET:

SQL
ARRAY_SIZE(COLLECT_SET(customer_id) OVER (PARTITION BY region))

Fonctions de fenêtre scalaires d'agrégation​

Vous ne pouvez pas agréger directement une fonction de fenêtre scalaire (utilisée pour un niveau de détail fixe) lors de la création d'une expression de mesure.

Modèle 1 : définissez la fonction de fenêtre comme un calcul de dimension distinct et référencez-le dans l'expression de mesure, comme dans l'exemple de pourcentage du total ci-dessus.

Schéma 2 : Alternativement, pour les agrégats décomposables comme SUM, vous pouvez utiliser l'agrégation imbriquée, qui calcule d'abord l'agrégat interne (non fenêtré) dans chaque groupe, puis agrège les résultats de groupe dans un second passage :

SQL
SUM(quantity) / SUM(SUM(quantity)) OVER ()

Ce modèle fonctionne pour des fonctions comme SUM, MIN, MAX et COUNT, mais pas pour des fonctions comme MEDIAN ou PERCENTILE qui ne sont pas décomposables. Notez attentivement l'ordre des Opérations tel que défini par les parenthèses ; une expression comme SUM(SUM(quantity) OVER ()), bien que similaire, ne sera pas reconnue comme valide et devra être exprimée en utilisant le premier modèle de cette section.

remarque

N'utilisez pas le modèle 2 dans un tableau croisé dynamique. Cela pourrait entraîner des résultats inattendus, car la fonction de fenêtre s'applique après le regroupement, ce qui fait que les valeurs sont comptées plus d'une fois. Utilisez le modèle 1 à la place.

Ressources supplémentaires​

Sur cette page