Support formation Microsoft Azure

Pages

DP-203

Analyser les données dans un entrepôt de données relationnelles

Gonzague Ducos

Pools SQL dédiés dans Azure Synapse Analytics

Comme toutes les bases de données relationnelles, un entrepôt de données contient des tables dans lesquelles les données que vous souhaitez analyser sont stockées.

Ces tables sont organisées dans un schéma optimisé pour la modélisation multidimensionnelle, dans lequel des mesures numériques associées aux événements, appelées faits, peuvent être agrégées par les attributs des entités associées à plusieurs dimensions.

Concevoir un schéma d’entrepôt de données

Tables d’un entrepôt de données

Modèle courant pour les entrepôts de données relationnelles : définir un schéma avec 2 types de tables :

tables de dimension

tables de faits

Tables de dimension

Décrivent les entités métier

Contiennent des colonnes pour les attributs d’une entité

Contient une colonne “clé unique” qui identifie de manière unique chaque ligne de la table

En fait, on aura 2 clés :

Une clé de substitution spécifique à l’entrepôt de données et qui identifie de manière unique chaque ligne de la table de dimension dans l’entrepôt de données (généralement, un nombre entier incrémenté).

Une clé secondaire, souvent une clé naturelle ou métier, utilisée pour identifier une instance spécifique d’une entité dans le système source transactionnel à partir duquel l’enregistrement d’entité provient (par exemple, un code de produit ou un ID client).

Pourquoi utiliser deux clés ?

Si plusieurs sources, entrainerait des doublons.

Clés numériques fonctionnent généralement mieux dans les requêtes

Les attributs d’entités peuvent changer au fil du temps, on veut conserver un enregistrement pour chaque instance d’une entité à plusieurs instants

Inclure une table de dimension qui représente le temps. La granularité la plus faible (appelée grain) d’une dimension de temps peut représenter des heures (à l’heure, à la seconde, à la milliseconde, à la nanoseconde, ou encore moins), ou des dates

Tables de faits

Stockent des détails ou des événements

Contient des colonnes pour les valeurs numériques qui peuvent être agrégées par dimensions

Contient des colonnes clés qui référencent des clés uniques dans les tables de dimension associées

Conceptions de schémas d’entrepôts de données

Dans une base de donnée transactionnelle utilisée dans les applications métier, les données sont normalisées pour réduire la duplication.

Dans un entrepôt de données, les données de dimension sont généralement dénormalisées pour réduire le nombre de jointures requises pour interroger les données.

Un entrepôt de données est organisé comme un schéma en étoile, dans lequel une table de faits est directement liée aux tables de dimension. Les attributs d’une entité peuvent être utilisés pour agréger des mesures dans des tables de faits sur plusieurs niveaux hiérarchiques.

Lorsqu’une entité comporte un grand nombre de niveaux d’attribut hiérarchique ou que certains attributs peuvent être partagés par plusieurs dimensions (par exemple, les clients et les magasins ont une adresse géographique), on applique une normalisation aux tables de dimension et on crée un schéma en flocon.

Création de tables d’entrepôt de données

⁠

Concevoir des tables - Azure Synapse Analytics⁠

⁠

Création d’un pool SQL dédié

Espace de travail Azure Synapse Analytics > Gérer

Paramètres de configuration suivants :

Le nom unique du pool SQL dédié

Le niveau de performances du pool SQL (de DW100c à DW30000c) qui détermine le coût par heure du pool en cours d’exécution

Point de départ : un pool vide ou une base de données existante restaurée à partir d’une sauvegarde

Classement du pool SQL, qui détermine les règles d’ordre de tri et de comparaison de chaînes de la base de données (non modifiable une fois créé)

Éléments à prendre en compte pour la création de tables

Pour créer des tables : CREATE TABLE (ou parfois CREATE EXTERNAL TABLE).

Types de tables créées :

Tables de faits

Tables de dimension

Tables de mise en lots : souvent utilisées dans le cadre du processus de chargement de l’entrepôt de données pour ingérer des données provenant de systèmes sources

Selon taille :

Lors de la conception d’un modèle de schéma en étoile pour les jeux de données de taille réduite ou moyenne : Azure SQL.

Pour les jeux de données plus volumineux, il peut s’avérer utile d’implémenter votre entrepôt de données dans Azure Synapse Analytics au lieu de SQL Server. Voici les différences.

Contraintes d’intégrité des données

Les pools SQL dédiés dans Synapse Analytics ne prennent pas en charge les contraintes de clé étrangère (FOREIGN KEY) ni d’unicité (UNIQUE) : les travaux utilisés pour charger des données doivent maintenir le caractère unique et l’intégrité référentielle des clés, sans compter pour cela sur les définitions de table de la base de données.

Index

Pools SQL dédiés Synapse Analytics prend en charge les index cluster comme dans SQL Server.

Pour les pools SQL dédiés Synapse Analytics, le type d’index par défaut est columnstore cluster car avantage significatif en matière de performances lors de l’interrogation de grandes quantités de données.

Distribution

Architecture :

Pools SQL dédiés Azure Synapse Analytics utilisent une

architecture de traitement massivement parallèle (MPP, Massively Parallel Processing)⁠

⁠

Systèmes de base de données OLTP : architecture de multitraitement symétrique (SMP, Symmetric Multi-Processing).

Système MPP : les données d’une table sont distribuées pour être traitées sur un pool de nœuds.

Synapse Analytics prend en charge les types de distribution suivants :

Hachage : une valeur de hachage déterministe est calculée pour la colonne spécifiée et utilisée pour affecter la ligne à un nœud de calcul.

Tourniquet (round-robin) : les lignes sont distribuées uniformément sur tous les nœuds de calcul.

Réplication : une copie de la table est stockée sur chaque nœud de calcul.

C’est souvent le type de la table qui détermine son option de distribution.

Type de la table

Option de distribution recommandée

Dimension

Utilisez la distribution répliquée pour les petites tables afin d’éviter le brassage des données lors de la jointure à des tables de faits distribuées.

Si les tables sont trop volumineuses pour être stockées sur chaque nœud de calcul, utilisez la distribution par hachage.

Fact

Utilisez la distribution de hachage avec l’index columnstore cluster pour distribuer des tables de faits entre les nœuds de calcul.

Préproduction (staged)

Utilisez la distribution tourniquet (round-robin) pour les tables de mise en lots afin de répartir uniformément les données entre les nœuds de calcul.

There are no rows in this table

⁠

Création de tables de dimension

Inclure clés de substitution et des clés secondaires + colonnes correspondant aux attributs de la dimension.

Utiliser IDENTITY pour gérer automatiquement une clé de substitution incrémentielle.

CREATE TABLE dbo.DimCustomer

(

CustomerKey INT IDENTITY NOT NULL,

CustomerAlternateKey NVARCHAR(15) NULL,

CustomerName NVARCHAR(80) NOT NULL,

EmailAddress NVARCHAR(50) NULL,

Phone NVARCHAR(25) NULL,

StreetAddress NVARCHAR(100),

City NVARCHAR(20),

PostalCode NVARCHAR(10),

CountryRegion NVARCHAR(20)

)

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

);

Si schéma en flocon, inclure la clé de la dimension parent dans la définition de la table de dimension enfant :

CREATE TABLE dbo.DimGeography

(

GeographyKey INT IDENTITY NOT NULL,

GeographyAlternateKey NVARCHAR(10) NULL,

StreetAddress NVARCHAR(100),

City NVARCHAR(20),

PostalCode NVARCHAR(10),

CountryRegion NVARCHAR(20)

)

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

);

CREATE TABLE dbo.DimCustomer

(

CustomerKey INT IDENTITY NOT NULL,

CustomerAlternateKey NVARCHAR(15) NULL,

GeographyKey INT NULL,

CustomerName NVARCHAR(80) NOT NULL,

EmailAddress NVARCHAR(50) NULL,

Phone NVARCHAR(25) NULL

)

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

);

Tables de dimension de temps

Clés :

JJMMAAAA ou AAAAMMJJ comme clé de substitution entière

Date du type de données DATE ou DATETIME comme clé secondaire

CREATE TABLE dbo.DimDate

(

DateKey INT NOT NULL,

DateAltKey DATETIME NOT NULL,

DayOfMonth INT NOT NULL,

DayOfWeek INT NOT NULL,

DayName NVARCHAR(15) NOT NULL,

MonthOfYear INT NOT NULL,

MonthName NVARCHAR(15) NOT NULL,

CalendarQuarter INT NOT NULL,

CalendarYear INT NOT NULL,

FiscalQuarter INT NOT NULL,

FiscalYear INT NOT NULL

)

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

);

Création de tables de faits

Incluent :

les clés de chacune des dimensions auxquelles elles sont associées

les attributs et les mesures numériques des événements et observations spécifiques à analyser

CREATE TABLE dbo.FactSales

(

OrderDateKey INT NOT NULL,

CustomerKey INT NOT NULL,

ProductKey INT NOT NULL,

StoreKey INT NOT NULL,

OrderNumber NVARCHAR(10) NOT NULL,

OrderLineItem INT NOT NULL,

OrderQuantity SMALLINT NOT NULL,

UnitPrice DECIMAL NOT NULL,

Discount DECIMAL NOT NULL,

Tax DECIMAL NOT NULL,

SalesAmount DECIMAL NOT NULL

)

WITH

(

DISTRIBUTION = HASH(OrderNumber),

CLUSTERED COLUMNSTORE INDEX

);

Création de tables de mise en lots

Les tables de mise en lots sont utilisées comme stockage temporaire pour les données à mesure qu’elles sont chargées dans l’entrepôt de données.

Modèle classique :

Structurer la table de façon à rendre aussi efficace que possible l’ingestion de données de leur source externe (souvent des fichiers d’un lac de données) vers la base de données relationnelle

Utiliser des instructions SQL pour charger les données des tables de mise en lots dans les tables de dimension et de fait.

CREATE TABLE dbo.StageProduct

(

ProductID NVARCHAR(10) NOT NULL,

ProductName NVARCHAR(200) NOT NULL,

ProductCategory NVARCHAR(200) NOT NULL,

Color NVARCHAR(10),

Size NVARCHAR(10),

ListPrice DECIMAL NOT NULL,

Discontinued BIT NOT NULL

)

WITH