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 utilisée pour interroger des tables dans un pool SQL dédié Synapse est similaire à SQL utilisée dans SQL Server ou Azure SQL Database.

Agrégation des mesures par attributs de dimension

La plupart des analyses de données avec un entrepôt de données impliquent l'agrégation des mesures numériques dans les tables de faits par les 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 JOIN des clauses pour connecter des tables de faits à des tables de dimension et une combinaison de fonctions d’agrégation et GROUP BY de clauses 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 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;

Les résultats de cette requête ressemblent au tableau suivant :

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 des détails de l’adresse du client 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 :

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 :

Utilisation de fonctions de classement

Un autre type courant de requête analytique consiste à partitionner les résultats en fonction d’un attribut de dimension et à classer les résultats 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 pouvez utiliser Transact-SQL fonctions de classement telles que ROW_NUMBER, , RANKDENSE_RANK, et NTILE. 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 la position classée de chaque ligne dans les résultats ordonné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 la ligne tombe. 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.

Par exemple, considérez 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 :

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 :

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 APPROX_COUNT_DISTINCT fonction 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 :

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.