Interrogation d’un entrepôt de données
Lorsque les tables de dimension et de faits d’un entrepôt de données ont été chargées avec des données, vous pouvez utiliser SQL pour interroger les tables et analyser les données qu’elles contiennent. La syntaxe Transact-SQL permettant d’interroger des tables dans un pool SQL dédié Synapse est similaire à la syntaxe SQL utilisée dans SQL Server et Azure SQL Database.
Agrégation de mesures par attributs de dimension
La plupart des analytiques données établies avec un entrepôt de données impliquent l’agrégation des mesures numériques dans les tables de faits en fonction des attributs des tables de dimension. En raison de la façon dont un schéma en étoile ou en flocon est implémenté, les requêtes permettant d’effectuer ce type d’agrégation s’appuient sur des clauses JOIN pour connecter des tables de faits à des tables de dimension, et sur une combinaison de fonctions d’agrégation et de clauses GROUP BY pour définir les hiérarchies d’agrégation.
Par exemple, le code SQL suivant interroge les tables FactSales et DimDate dans un entrepôt de données hypothétique pour agréger les montants des ventes par année et par trimestre :
SELECT dates.CalendarYear,
dates.CalendarQuarter,
SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;
Cette requête donnerait les résultats suivants :
| CalendarYear | CalendarQuarter | TotalSales |
|---|---|---|
| 2020 | 1 | 25980.16 |
| 2020 | 2 | 27453.87 |
| 2020 | 3 | 28527.15 |
| 2020 | 4 | 31083.45 |
| 2021 | 1 | 34562.96 |
| 2021 | 2 | 36162.27 |
| ... | ... | ... |
Vous pouvez joindre autant de tables de dimension que nécessaire pour calculer les agrégations dont vous avez besoin. Par exemple, le code suivant étend l’exemple précédent pour décomposer les totaux des ventes trimestrielles par ville en fonction de l’adresse du client indiquée dans la table DimCustomer :
SELECT dates.CalendarYear,
dates.CalendarQuarter,
custs.City,
SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;
Cette fois, les résultats incluent un total de ventes trimestrielles pour chaque ville :
| CalendarYear | CalendarQuarter | City | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amsterdam | 5982.53 |
| 2020 | 1 | Berlin | 2826.98 |
| 2020 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amsterdam | 7163.93 |
| 2020 | 2 | Berlin | 8191.12 |
| 2020 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amsterdam | 7261.92 |
| 2020 | 3 | Berlin | 4202.65 |
| 2020 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amsterdam | 8262.73 |
| 2020 | 4 | Berlin | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7261.28 |
| 2021 | 1 | Berlin | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Jointure dans un schéma en flocon
Dans un schéma en flocon, les dimensions peuvent être partiellement normalisée. Plusieurs jointures sont parfois nécessaires pour lier des tables de faits aux dimensions en flocon. Par exemple, supposons que votre entrepôt de données inclut une table de dimension DimProduct à partir de laquelle les catégories de produits ont été normalisées dans une table DimCategory distincte. Voici un exemple de requête permettant d’agréger des éléments vendus par catégorie de produit :
SELECT cat.ProductCategory,
SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;
Les résultats de cette requête incluent le nombre d’articles vendus pour chaque catégorie de produit :
| ProductCategory | ItemsSold |
|---|---|
| Accessories | 28271 |
| Pièces détachées | 5368 |
| ... | ... |
Notes
Les clauses JOIN sur FactSales et DimProduct et sur DimProduct et DimCategory sont requises, même si aucun des champs de DimProduct n’est retourné par la requête.
Utilisation de fonctions de classement
Un autre type commun de requête analytique consiste à partitionner les résultats en fonction d’un attribut de dimension et à les classer dans chaque partition. Par exemple, vous pouvez établir un classement annuel des magasins en fonction de leur chiffre d’affaires. Pour atteindre cet objectif, vous avez la possibilité d’utiliser des fonctions de classement Transact-SQL : ROW_NUMBER, RANK, DENSE_RANK, NTILE, etc. Elles vous permettent de partitionner les données en catégories, chacune retournant une valeur spécifique qui indique la position relative de chaque ligne dans la partition :
- ROW_NUMBER retourne la position ordinale de la ligne dans la partition. Par exemple, la première ligne porte le numéro 1, la deuxième le numéro 2, etc.
- RANK retourne le rang de chaque ligne dans les résultats triés. Par exemple, dans une partition de magasins triés par volume de ventes, le magasin affichant le volume de ventes le plus élevé obtient le rang 1. Si plusieurs magasins présentent les mêmes volumes de ventes, ils reçoivent le même rang ; le rang attribué aux magasins suivants reflète alors le nombre de magasins dont les volumes de ventes sont plus élevés, égalités comprises.
- DENSE_RANK classe les lignes d’une partition de la même façon que RANK, à une différence près : lorsque plusieurs lignes possèdent le même rang, le rang des suivantes est établi en ignorant les égalités.
- NTILE retourne le centile spécifié dans lequel se situe la ligne. Dans une partition de magasins triés par volume de ventes,
NTILE(4)retourne le quartile dans lequel le volume de ventes d’un magasin le place.
Examinons, par exemple, la requête suivante :
SELECT ProductCategory,
ProductName,
ListPrice,
ROW_NUMBER() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
RANK() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
DENSE_RANK() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
NTILE(4) OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;
La requête partitionne les produits en groupes en fonction de leur catégorie. Dans chaque partition de catégorie, la position relative de chaque produit est déterminée en fonction de son prix catalogue. Voici les résultats possibles de cette requête :
| ProductCategory | ProductName | ListPrice | RowNumber | Rank | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Accessories | Widget | 8.99 | 1 | 1 | 1 | 1 |
| Accessories | Knicknak | 8,49 | 2 | 2 | 2 | 1 |
| Accessories | Sprocket | 5,99 | 3 | 3 | 3 | 2 |
| Accessories | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessories | Spangle | 2.99 | 5 | 5 | 4 | 3 |
| Accessories | Badabing | 0,25 | 6 | 6 | 5 | 4 |
| Pièces détachées | Flimflam | 7.49 | 1 | 1 | 1 | 1 |
| Pièces détachées | Snickity wotsit | 6,99 | 2 | 2 | 2 | 1 |
| Pièces détachées | Flange | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Notes
Les résultats de l’exemple illustrent la différence entre RANK et DENSE_RANK. Notez que, dans la catégorie Accessoires, les produits Sprocket et Doodah possèdent le même prix catalogue et sont tous deux classés comme le troisième produit le plus cher. Le produit suivant possède une valeur RANK de 5 (il existe quatre produits plus chers que lui) et une valeur DENSE_RANK de 4 (il existe trois prix plus élevés).
Pour plus d’informations sur les fonctions de classement, consultez Fonctions de classement (Transact-SQL) dans la documentation d’Azure Synapse Analytics.
Récupération d’un nombre approximatif
Bien que l’objectif d’un entrepôt de données consiste principalement à prendre en charge les modèles et rapports de données analytiques pour l’entreprise, les analystes et scientifiques des données doivent souvent effectuer une exploration initiale des données, juste pour déterminer l’échelle et la distribution de base des données.
Par exemple, la requête suivante utilise la fonction COUNT pour récupérer le nombre de ventes de chaque année dans un entrepôt de données hypothétique :
SELECT dates.CalendarYear AS CalendarYear,
COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;
Voici les résultats possibles de cette requête :
| CalendarYear | Orders (Commandes) |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
En raison du volume de données présentes dans un entrepôt de données, même les requêtes simples visant à compter le nombre d’enregistrements qui répondent à des critères spécifiés peuvent prendre beaucoup de temps. Dans de nombreux cas, un nombre précis n’est pas nécessaire : une estimation approximative suffit. Vous pouvez alors utiliser la fonction APPROX_COUNT_DISTINCT, comme dans l’exemple suivant :
SELECT dates.CalendarYear AS CalendarYear,
APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;
La fonction APPROX_COUNT_DISTINCT utilise un algorithme HyperLogLog pour récupérer un nombre approximatif. Le résultat est garanti présenter un taux d’erreur maximal de 2 % avec une probabilité de 97 %. Voici donc les résultats possibles de cette requête avec les mêmes données hypothétiques qu’auparavant :
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
Les nombres sont moins précis, mais toujours suffisants pour une comparaison approximative des ventes annuelles. Avec un grand volume de données, la requête utilisant la fonction APPROX_COUNT_DISTINCT est exécutée plus rapidement. La précision réduite peut représenter un compromis acceptable pendant l’exploration des données de base.
Notes
Pour plus d’informations, consultez la documentation de la fonction APPROX_COUNT_DISTINCT.