Azure Synapse Analytique s’appuie sur un ensemble évolutif de fonctionnalités pour prendre en charge l’entreposage de données d’entreprise. y compris l’analytique de données basée sur des fichiers dans un lac de données, ainsi que les entrepôts de données relationnelles à grande échelle et les pipelines de transfert et de transformation de données utilisés pour les charger. Dans cet atelier, vous allez découvrir comment utiliser un pool SQL dédié dans Azure Synapse Analytique pour stocker et interroger des données dans un entrepôt de données relationnelles.
Cet atelier prendra environ 45 minutes.
Avant de commencer
Vous aurez besoin d’un dans lequel vous disposez d’un accès de niveau administratif. Approvisionner un espace de travail Azure Synapse Analytique
Un espace de travail Azure Synapse Analytique fournit un point central pour la gestion des données et les environnements d’exécution de traitement des données. Vous pouvez provisionner un espace de travail à l’aide de l’interface interactive du portail Azure, ou vous pouvez déployer un espace de travail et des ressources à l’aide d’un script ou d’un modèle. Dans la plupart des scénarios de production, il est préférable d’automatiser le provisionnement à l’aide de scripts et de modèles afin d’intégrer le déploiement des ressources dans un processus DevOps (développement et opérations reproductibles).
Dans cet exercice, vous allez utiliser une combinaison d’un script PowerShell et d’un modèle ARM pour provisionner Azure Synapse Analytique.
Connectez-vous au à l’adresse https://portal.azure.com. Utilisez le bouton [>_] à droite de la barre de recherche en haut de la page pour créer un Cloud Shell dans le portail Azure, en sélectionnant un environnement PowerShell et en créant un stockage si vous y êtes invité. Cloud Shell fournit une interface de ligne de commande dans un volet situé au bas du portail Azure, comme illustré ici :
Remarque : Si vous avez déjà créé un Cloud Shell qui utilise un environnement Bash, utilisez le menu déroulant en haut à gauche du volet Cloud Shell pour le remplacer par PowerShell. Notez que vous pouvez redimensionner la coque du nuage en faisant glisser la barre de séparation en haut du volet ou en utilisant les icônes —, ◻ et X en haut à droite du volet pour réduire, agrandir et fermer le volet. Pour plus d’informations sur l’utilisation d’Azure Cloud Shell, consultez la . Dans le volet PowerShell, entrez les commandes suivantes pour cloner ce référentiel : rm -r dp203 -f
git clone https://github.com/MicrosoftLearning/Dp-203-azure-data-engineer dp203
Une fois le référentiel cloné, entrez les commandes suivantes pour accéder au dossier de cet atelier et exécuter le script setup.ps1 qu’il contient :
cd dp203/Allfiles/labs/08
./setup.ps1
Si vous y êtes invité, choisissez l’abonnement que vous souhaitez utiliser (cela ne se produira que si vous avez accès à plusieurs abonnements Azure). Lorsque vous y êtes invité, entrez un mot de passe approprié à définir pour votre pool SQL Azure Synapse.
Remarque : N’oubliez pas ce mot de passe ! Attendez que le script soit terminé - cela prend généralement environ 15 minutes, mais dans certains cas, cela peut prendre plus de temps. Pendant que vous attendez, consultez l’article dans la documentation d’Azure Synapse Analytique. Explorer le schéma de l’entrepôt de données
Dans cet atelier, l’entrepôt de données est hébergé dans un pool SQL dédié dans Azure Synapse Analytique.
Démarrer le pool SQL dédié
Une fois le script terminé, dans le portail Azure, accédez au groupe de ressources dp203-xxxxxxx qu’il a créé, puis sélectionnez votre espace de travail Synapse. Dans la page Vue d’ensemble de votre espace de travail Synapse, dans la carte Ouvrir Synapse Studio, sélectionnez Ouvrir pour ouvrir Synapse Studio dans un nouvel onglet du navigateur. Connectez-vous si vous y êtes invité. Sur le côté gauche de Synapse Studio, utilisez l’icône ›› pour développer le menu, ce qui révèle les différentes pages de Synapse Studio qui sont utilisées pour gérer les ressources et effectuer des tâches d’analytique de données. Sur la page Gérer, assurez-vous que l’onglet Pools SQL est sélectionné, puis sélectionnez le pool SQL dédié sql xxxxxxx et utilisez son icône ▷ pour le démarrer. confirmer que vous souhaitez le reprendre lorsque vous y êtes invité. Attendez que le pool SQL reprenne. Cela peut prendre quelques minutes. Utilisez le bouton ↻ Actualiser pour vérifier périodiquement son état. L’état s’affichera En ligne lorsqu’il sera prêt. Afficher les tables de la base de données
Dans Synapse Studio, sélectionnez la page Données et assurez-vous que l’onglet Espace de travail est sélectionné et qu’il contient une catégorie Base de données SQL. Développez Base de données SQL, le pool sqlxxxxxxx et son dossier Tables pour afficher les tables de la base de données.
Un entrepôt de données relationnel est généralement basé sur un schéma composé de tables de faits et de dimensions. Les tables sont optimisées pour les requêtes analytiques dans lesquelles les mesures numériques des tables de faits sont agrégées par les attributs des entités représentées par les tables de dimension, par exemple, ce qui vous permet d’agréger le chiffre d’affaires des ventes sur Internet par produit, client, date, etc. Développez le dbo. FactInternetSales et son dossier Columns pour afficher les colonnes de ce tableau. Notez que la plupart des colonnes sont des clés qui font référence à des lignes dans les tables de dimension. D’autres sont des valeurs numériques (mesures) pour l’analyse.
Les clés sont utilisées pour relier une table de faits à une ou plusieurs tables de dimensions, souvent dans un schéma en étoile ; dans lequel la table de faits est directement liée à chaque table de dimension (formant une « étoile » à plusieurs branches avec la table de faits au centre). Affichez les colonnes du dbo. DimPromotion, et notez qu’elle dispose d’une PromotionKey unique qui identifie de manière unique chaque ligne de la table. Il dispose également d’une AlternateKey.
En général, les données d’un entrepôt de données ont été importées à partir d’une ou de plusieurs sources transactionnelles. La clé secondaire reflète l’identificateur d’entreprise de l’instance de cette entité dans la source, mais une clé de substitution numérique unique est généralement générée pour identifier de manière unique chaque ligne de la table de dimension de l’entrepôt de données. L’un des avantages de cette approche est qu’elle permet à l’entrepôt de données de contenir plusieurs instances de la même entité à différents moments (par exemple, les enregistrements du même client reflétant son adresse au moment où une commande a été passée). Affichez les colonnes du dbo. DimProduct, et notez qu’il contient une colonne ProductSubcategoryKey, qui fait référence au dbo. DimProductSubcategory, qui contient à son tour une colonne ProductCategoryKey qui fait référence au dbo. table DimProductCategory.
Dans certains cas, les dimensions sont partiellement normalisées en plusieurs tables associées pour permettre différents niveaux de granularité, tels que les produits qui peuvent être regroupés en sous-catégories et catégories. Il en résulte qu’une simple étoile est étendue à un schéma en forme de flocon de neige, dans lequel la table de faits centrale est liée à une table de dimension, qui est à son tour liée à d’autres tables de dimension. Affichez les colonnes du dbo. DimDate, et notez qu’elle contient plusieurs colonnes qui reflètent différents attributs temporels d’une date, notamment le jour de la semaine, le jour du mois, le mois, l’année, le nom du jour, le nom du mois, etc.
Les dimensions temporelles dans un entrepôt de données sont généralement implémentées sous la forme d’une table de dimension contenant une ligne pour chacune des plus petites unités temporelles de granularité (souvent appelée grain de la dimension) selon lesquelles vous souhaitez agréger les mesures dans les tables de faits. Dans ce cas, le grain le plus bas auquel les mesures peuvent être agrégées est une date individuelle, et la table contient une ligne pour chaque date de la première à la dernière date référencée dans les données. Les attributs de la table DimDate permettent aux analystes d’agréger des mesures en fonction de n’importe quelle clé de date de la table de faits, à l’aide d’un ensemble cohérent d’attributs temporels (par exemple, l’affichage des commandes par mois en fonction de la date de la commande). La table FactInternetSales contient trois clés qui se rapportent à la table DimDate : OrderDateKey, DueDateKey et ShipDateKey. Interroger les tables de l’entrepôt de données
Maintenant que vous avez exploré certains des aspects les plus importants du schéma de l’entrepôt de données, vous êtes prêt à interroger les tables et à récupérer certaines données.
Interroger des tables de faits et de dimensions
Les valeurs numériques d’un entrepôt de données relationnelles sont stockées dans des tables de faits avec des tables de dimensions associées que vous pouvez utiliser pour agréger les données sur plusieurs attributs. Cette conception signifie que la plupart des requêtes dans un entrepôt de données relationnelles impliquent l’agrégation et le regroupement de données (à l’aide de fonctions d’agrégation et de clauses GROUP BY) entre des tables associées (à l’aide de clauses JOIN).
Sur la page Données, sélectionnez le pool SQL sql xxxxxxx et, dans son menu, sélectionnez Nouveau script SQL > Script vide. Lorsqu’un nouvel onglet SQL Script 1 s’ouvre, dans son volet Propriétés, remplacez le nom du script par Analyser les ventes sur Internet et modifiez les paramètres de résultat par requête pour renvoyer toutes les lignes. Utilisez ensuite le bouton Publier de la barre d’outils pour enregistrer le script, puis utilisez le bouton Propriétés (qui ressemble à 🗏 .) à l’extrémité droite de la barre d’outils pour fermer le volet Propriétés afin que vous puissiez voir le volet de script. Dans le script vide, ajoutez le code suivant : SELECT d.CalendarYear AS Year,
SUM(i.SalesAmount) AS InternetSalesAmount
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
GROUP BY d.CalendarYear
ORDER BY Year;
Utilisez le bouton ▷ Exécuter pour exécuter le script et examiner les résultats, qui doivent afficher les totaux des ventes sur Internet pour chaque année. Cette requête joint la table de faits pour les ventes sur Internet à une table de dimension de temps basée sur la date de commande et agrège la mesure du montant des ventes dans la table de faits par l’attribut de mois calendaire de la table de dimension. Modifiez la requête comme suit pour ajouter l’attribut month à partir de la dimension temporelle, puis exécutez la requête modifiée. SELECT d.CalendarYear AS Year,
d.MonthNumberOfYear AS Month,
SUM(i.SalesAmount) AS InternetSalesAmount
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
GROUP BY d.CalendarYear, d.MonthNumberOfYear
ORDER BY Year, Month;
Notez que les attributs de la dimension temporelle vous permettent d’agréger les mesures de la table de faits à plusieurs niveaux hiérarchiques, dans ce cas, l’année et le mois. Il s’agit d’un modèle courant dans les entrepôts de données.
Modifiez la requête comme suit pour supprimer le mois et ajouter une deuxième dimension à l’agrégation, puis exécutez-la pour afficher les résultats (qui affichent les totaux annuels des ventes sur Internet pour chaque région) : SELECT d.CalendarYear AS Year,
g.EnglishCountryRegionName AS Region,
SUM(i.SalesAmount) AS InternetSalesAmount
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
JOIN DimCustomer AS c ON i.CustomerKey = c.CustomerKey
JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
GROUP BY d.CalendarYear, g.EnglishCountryRegionName
ORDER BY Year, Region;
Notez que la géographie est une dimension en forme de flocon de neige qui est liée à la table de faits sur les ventes sur Internet par le biais de la dimension client. Vous avez donc besoin de deux jointures dans la requête pour agréger les ventes sur Internet par zone géographique.
Modifiez et réexécutez la requête pour ajouter une autre dimension de flocon de neige et agréger les ventes régionales annuelles par catégorie de produits : SELECT d.CalendarYear AS Year,
pc.EnglishProductCategoryName AS ProductCategory,
g.EnglishCountryRegionName AS Region,
SUM(i.SalesAmount) AS InternetSalesAmount
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
JOIN DimCustomer AS c ON i.CustomerKey = c.CustomerKey
JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
JOIN DimProduct AS p ON i.ProductKey = p.ProductKey
JOIN DimProductSubcategory AS ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
JOIN DimProductCategory AS pc ON ps.ProductCategoryKey = pc.ProductCategoryKey
GROUP BY d.CalendarYear, pc.EnglishProductCategoryName, g.EnglishCountryRegionName
ORDER BY Year, ProductCategory, Region;
Cette fois, la dimension flocon de neige pour la catégorie de produits nécessite trois jointures pour refléter la relation hiérarchique entre les produits, les sous-catégories et les catégories.
Publiez le script pour l’enregistrer. Utiliser les fonctions de classement
Une autre exigence courante lors de l’analyse de grands volumes de données est de regrouper les données par partitions et de déterminer le rang de chaque entité dans la partition en fonction d’une métrique spécifique.
Sous la requête existante, ajoutez le code SQL suivant pour récupérer les valeurs de ventes pour 2022 sur les partitions en fonction du nom du pays/de la région : SELECT g.EnglishCountryRegionName AS Region,
ROW_NUMBER() OVER(PARTITION BY g.EnglishCountryRegionName
ORDER BY i.SalesAmount ASC) AS RowNumber,
i.SalesOrderNumber AS OrderNo,
i.SalesOrderLineNumber AS LineItem,
i.SalesAmount AS SalesAmount,
SUM(i.SalesAmount) OVER(PARTITION BY g.EnglishCountryRegionName) AS RegionTotal,
AVG(i.SalesAmount) OVER(PARTITION BY g.EnglishCountryRegionName) AS RegionAverage
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
JOIN DimCustomer AS c ON i.CustomerKey = c.CustomerKey
JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
WHERE d.CalendarYear = 2022
ORDER BY Region;
Sélectionnez uniquement le nouveau code de requête et utilisez le bouton ▷ Exécuter pour l’exécuter. Examinez ensuite les résultats, qui doivent ressembler au tableau suivant :
Observez les faits suivants à propos de ces résultats :
Il existe une ligne pour chaque élément de ligne de commande client. Les rangées sont organisées en partitions en fonction de la géographie où la vente a été réalisée. Les lignes de chaque partition géographique sont numérotées par ordre du montant des ventes (du plus petit au plus élevé). Pour chaque ligne, le montant des ventes d’articles de campagne ainsi que le total régional et les montants moyens des ventes sont inclus. Sous les requêtes existantes, ajoutez le code suivant pour appliquer des fonctions de fenêtrage dans une requête GROUP BY et classer les villes de chaque région en fonction du montant total de leurs ventes :
SELECT g.EnglishCountryRegionName AS Region,
g.City,
SUM(i.SalesAmount) AS CityTotal,
SUM(SUM(i.SalesAmount)) OVER(PARTITION BY g.EnglishCountryRegionName) AS RegionTotal,
RANK() OVER(PARTITION BY g.EnglishCountryRegionName
ORDER BY SUM(i.SalesAmount) DESC) AS RegionalRank
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
JOIN DimCustomer AS c ON i.CustomerKey = c.CustomerKey
JOIN DimGeography AS g ON c.GeographyKey = g.GeographyKey
GROUP BY g.EnglishCountryRegionName, g.City
ORDER BY Region;
Sélectionnez uniquement le nouveau code de requête et utilisez le bouton ▷ Exécuter pour l’exécuter. Examinez ensuite les résultats et observez ce qui suit : Les résultats comprennent une ligne pour chaque ville, regroupée par région. Le total des ventes (somme des montants des ventes individuelles) est calculé pour chaque ville Le total des ventes régionales (la somme des montants des ventes pour chaque ville de la région) est calculé sur la base de la partition régionale. Le classement de chaque ville dans sa partition régionale est calculé en ordonnant le montant total des ventes par ville dans l’ordre décroissant. Publiez le script mis à jour pour enregistrer les modifications. Conseil : ROW_NUMBER et RANK sont des exemples de fonctions de classement disponibles dans Transact-SQL. Pour plus d’informations, consultez la référence dans la documentation du langage Transact-SQL.
Récupérer un décompte approximatif
Lors de l’exploration de très grands volumes de données, l’exécution des requêtes peut prendre beaucoup de temps et de ressources. Souvent, l’analyse des données n’exige pas de valeurs absolument précises - une comparaison de valeurs approximatives peut suffire.
Sous les requêtes existantes, ajoutez le code suivant pour récupérer le nombre de commandes client pour chaque année civile : SELECT d.CalendarYear AS CalendarYear,
COUNT(DISTINCT i.SalesOrderNumber) AS Orders
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
GROUP BY d.CalendarYear
ORDER BY CalendarYear;
Sélectionnez uniquement le nouveau code de requête et utilisez le bouton ▷ Exécuter pour l’exécuter. Examinez ensuite la sortie renvoyée :
Dans l’onglet Résultats sous la requête, affichez le nombre de commandes pour chaque année. Sous l’onglet Messages, affichez la durée totale d’exécution de la requête. Modifiez la requête comme suit, pour renvoyer un décompte approximatif pour chaque année. Réexécutez ensuite la requête.
SELECT d.CalendarYear AS CalendarYear,
APPROX_COUNT_DISTINCT(i.SalesOrderNumber) AS Orders
FROM FactInternetSales AS i
JOIN DimDate AS d ON i.OrderDateKey = d.DateKey
GROUP BY d.CalendarYear
ORDER BY CalendarYear;
Examinez la sortie renvoyée : Dans l’onglet Résultats sous la requête, affichez le nombre de commandes pour chaque année. Ceux-ci doivent être inférieurs à 2 % des nombres réels récupérés par la requête précédente. Sous l’onglet Messages, affichez la durée totale d’exécution de la requête. Celle-ci doit être plus courte que pour la requête précédente. Publiez le script pour enregistrer les modifications. Astuce : Pour plus d’informations, consultez la documentation de la fonction .
Défi - Analyser les ventes des revendeurs
Créez un script vide pour le pool SQLsql xxxxxxx et enregistrez-le sous le nom Analyser les ventes des revendeurs. Créez des requêtes SQL dans le script pour rechercher les informations suivantes en fonction de la table de faits FactResellerSales et des tables de dimension auxquelles elle est associée : Quantité totale d’articles vendus par exercice et par trimestre. Quantité totale d’articles vendus par exercice, par trimestre et par région du territoire de vente associé à l’employé qui a effectué la vente. Quantité totale d’articles vendus par exercice, trimestre et région du territoire de vente par catégorie de produits. Le classement de chaque territoire de vente par exercice financier en fonction du montant total des ventes pour l’exercice. Nombre approximatif de commandes client par an dans chaque territoire de vente.
Conseil : Comparez vos requêtes à celles du script Solution de la page Développement de Synapse Studio. Expérimentez des requêtes pour explorer le reste des tables du schéma de l’entrepôt de données à votre guise. Lorsque vous avez terminé, dans la page Gérer, mettez en pause le pool SQL dédié sql xxxxxxx. Supprimer des ressources Azure
Si vous avez terminé d’explorer Azure Synapse Analytique, vous devez supprimer les ressources que vous avez créées pour éviter des coûts Azure inutiles.
Fermez l’onglet du navigateur Synapse Studio et revenez au portail Azure. Sur le portail Azure, dans la page d’accueil, sélectionnez Groupes de ressources. Sélectionnez le groupe de ressources dp203-xxxxxxx pour votre espace de travail Synapse Analytique (et non le groupe de ressources géré) et vérifiez qu’il contient l’espace de travail Synapse, le compte de stockage et le pool SQL dédié pour votre espace de travail. En haut de la page Vue d’ensemble de votre groupe de ressources, sélectionnez Supprimer le groupe de ressources. Entrez le nom du groupe de ressources dp500-xxxxxxx pour confirmer que vous souhaitez le supprimer, puis sélectionnez Supprimer. Au bout de quelques minutes, votre groupe de ressources d’espace de travail Azure Synapse et le groupe de ressources d’espace de travail managé qui lui est associé seront supprimés. 
