Support formation Microsoft Azure

Support formation Microsoft Azure

Labs 203 | Azure Date Engineer

Lab 08 | Explorer un entrepôt de données relationnelles

Gonzague Ducos

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

abonnement Azure⁠

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

portail Azure⁠

à 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

documentation Azure Cloud Shell⁠

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

Qu’est-ce que le pool SQL dédié dans Azure Synapse Analytique ?⁠

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,