Guide : Exercices Corrigés sur les Entrepôts de Données

Un entrepôt de données (Data Warehouse) est une base de données spécialement conçue pour le stockage et l’analyse de données provenant de multiples sources. Les exercices ci-dessous couvrent des concepts essentiels tels que la modélisation des données, les requêtes SQL avancées, et l’optimisation des performances.

---

1. Conception d’un Modèle en Étoile

Exercice : Modélisation des données pour un magasin

Un magasin souhaite créer un entrepôt de données pour analyser ses ventes. Voici les besoins :

1. Les produits sont catégorisés par type (par exemple, Électronique, Vêtements).
2. Les clients sont identifiés par ID, nom, et localisation.
3. Les ventes contiennent des informations sur la date, le produit, le client, et le montant.

• Objectif : Concevez un modèle en étoile pour répondre à ce besoin.

---

Solution :

1. Table de faits : Ventes
   • Colonnes : ID vente, ID produit, ID client, ID date, Montant.
2. Tables de dimensions :
   • Produit : ID produit, Nom, Catégorie.
   • Client : ID client, Nom, Localisation.
   • Temps : ID date, Jour, Mois, Année.

Modèle en Étoile :

      Produit       Client
         \           /
          \         /
           Ventes -- Temps

---

2. Requête SQL : Total des Ventes par Catégorie

Exercice :

À partir du modèle ci-dessus, écrivez une requête SQL pour calculer le total des ventes par catégorie de produit.

---

Solution :

SELECT P.Categorie, SUM(F.Montant) AS Total_Ventes
FROM Faits_Ventes F
JOIN Dimension_Produit P ON F.ID_Produit = P.ID_Produit
GROUP BY P.Categorie;

---

3. Requête SQL : Nombre de Clients par Localisation

Exercice :

Écrivez une requête SQL pour compter le nombre de clients dans chaque localisation.

---

Solution :

SELECT C.Localisation, COUNT(DISTINCT C.ID_Client) AS Nombre_Clients
FROM Dimension_Client C
GROUP BY C.Localisation;

---

4. Identification des Périodes de Ventes Maximales

Exercice :

Trouvez le mois avec les ventes totales les plus élevées.

---

Solution :

SELECT T.Mois, T.Annee, SUM(F.Montant) AS Total_Ventes
FROM Faits_Ventes F
JOIN Dimension_Temps T ON F.ID_Date = T.ID_Date
GROUP BY T.Mois, T.Annee
ORDER BY Total_Ventes DESC
LIMIT 1;

---

5. Optimisation de Requêtes avec des Index

Exercice :

Expliquez pourquoi il est important de créer des index dans un entrepôt de données et proposez un index pour accélérer la requête sur les ventes par catégorie.

---

Solution :

1. Raison : Les index accélèrent la recherche et les jointures en réduisant le nombre d’opérations de lecture.
2. Index recommandé :
   • Créez un index sur la colonne ID_Produit de la table Faits_Ventes : CREATE INDEX idx_id_produit ON Faits_Ventes(ID_Produit);

---

6. Création de Table Agrégée

Exercice :

Pour optimiser les analyses, créez une table agrégée contenant les ventes totales par catégorie et par mois.

---

Solution :

1. Création de la table : CREATE TABLE Ventes_Aggregats ( Categorie VARCHAR(50), Mois INT, Annee INT, Total_Ventes DECIMAL(10, 2) );
2. Insertion des données : INSERT INTO Ventes_Aggregats (Categorie, Mois, Annee, Total_Ventes) SELECT P.Categorie, T.Mois, T.Annee, SUM(F.Montant) FROM Faits_Ventes F JOIN Dimension_Produit P ON F.ID_Produit = P.ID_Produit JOIN Dimension_Temps T ON F.ID_Date = T.ID_Date GROUP BY P.Categorie, T.Mois, T.Annee;

---

7. Analyse des KPIs

Exercice :

Calculez les indicateurs clés de performance (KPIs) suivants :

1. La vente moyenne par client.
2. Le produit le plus vendu (en quantité).

---

Solution :

1. Vente moyenne par client : SELECT AVG(Total_Client) AS Vente_Moyenne FROM ( SELECT F.ID_Client, SUM(F.Montant) AS Total_Client FROM Faits_Ventes F GROUP BY F.ID_Client ) AS Sous_Requete;
2. Produit le plus vendu : SELECT P.Nom, SUM(F.Quantite) AS Quantite_Totale FROM Faits_Ventes F JOIN Dimension_Produit P ON F.ID_Produit = P.ID_Produit GROUP BY P.Nom ORDER BY Quantite_Totale DESC LIMIT 1;

---

8. Transformation ETL

Exercice :

Décrivez un processus ETL (Extract, Transform, Load) pour charger les données des ventes dans l’entrepôt.

---

Solution :

1. Extraction :
   • Source : Système de gestion des ventes.
   • Outils : SQL pour extraire les données.
2. Transformation :
   • Nettoyez les données (ex. : supprimer les doublons).
   • Formatez les dates dans un format standard (ex. : AAAA-MM-JJ).
   • Ajoutez des clés primaires (ex. : ID_Client).
3. Chargement :
   • Insérez les données transformées dans les tables dimensionnelles.
   • Chargez les transactions dans la table de faits.

---

9. Création d’un Tableau Croisé Dynamique

Exercice :

À l’aide d’un outil comme Excel ou Power BI, créez un tableau croisé dynamique pour afficher les ventes totales par catégorie et par région.

---

Solution :

1. Importez les données :
   • Importez les données de l’entrepôt (par exemple via une requête SQL).
2. Configurez le tableau croisé :
   • Lignes : Catégorie.
   • Colonnes : Région.
   • Valeurs : Somme des ventes.
3. Résultat attendu : Un tableau qui résume les ventes par catégorie et région.

---

10. Optimisation des Performances de l’Entrepôt

Exercice :

Proposez des stratégies pour améliorer les performances d’un entrepôt de données.

---

Solution :

1. Indexation :
   • Créez des index sur les colonnes fréquemment utilisées dans les requêtes (ex. : clés de jointure).
2. Partionnement :
   • Divisez les tables volumineuses par périodes (par ex., par année).
3. Tables matérialisées :
   • Utilisez des tables matérialisées pour stocker des agrégats souvent demandés.
4. Compression des données :
   • Compressez les tables historiques pour économiser de l’espace.
5. Cache :
   • Configurez un cache pour accélérer les requêtes répétées.

---

Exercices avec Cas Particuliers pour la Pratique des Entrepôts de Données

Voici une liste d’exercices qui incluent des cas particuliers souvent rencontrés dans les entrepôts de données. Ces exercices visent à approfondir la compréhension des scénarios complexes impliquant la gestion, l’analyse et l’optimisation des données.

---

1. Gestion des Données Manquantes

Contexte :

Vous travaillez avec une table de faits des ventes où certaines informations de localisation client sont manquantes. Voici un extrait des données :

ID Client	Date	Montant (€)	ID Région
1	2023-01-01	100	NULL
2	2023-01-02	200	5
3	2023-01-03	150	NULL

• Objectif :
  1. Remplacez les valeurs NULL dans la colonne ID Région par une région par défaut (par exemple, -1).
  2. Calculez le total des ventes pour chaque région.

---

Solution :

1. Remplacement des NULL : SELECT ID_Client, Date, Montant, COALESCE(ID_Region, -1) AS ID_Region FROM Faits_Ventes;
2. Total des ventes par région : SELECT COALESCE(ID_Region, -1) AS ID_Region, SUM(Montant) AS Total_Ventes FROM Faits_Ventes GROUP BY COALESCE(ID_Region, -1);

---

2. Gestion des Données Duplicates

Contexte :

Dans une table de faits, certaines transactions sont dupliquées par erreur :

ID Transaction	Montant (€)
1	100
2	200
1	100
3	150

• Objectif :
  1. Identifiez les doublons dans la table.
  2. Supprimez les lignes en double en ne conservant que l’une d’entre elles.

---

Solution :

1. Identification des doublons : SELECT ID_Transaction, COUNT(*) AS Nb_Occurrences FROM Faits_Ventes GROUP BY ID_Transaction HAVING COUNT(*) > 1;
2. Suppression des doublons : DELETE FROM Faits_Ventes WHERE ID_Transaction IN ( SELECT ID_Transaction FROM ( SELECT ID_Transaction, ROW_NUMBER() OVER(PARTITION BY ID_Transaction ORDER BY ID_Transaction) AS Rn FROM Faits_Ventes ) AS Sous_Requete WHERE Rn > 1 );

---

3. Identification des Outliers

Contexte :

Vous devez analyser les montants des ventes dans une table et identifier les valeurs aberrantes (outliers). Une vente est considérée comme aberrante si elle dépasse trois fois l’écart-type par rapport à la moyenne.

ID Vente	Montant (€)
1	100
2	200
3	1500
4	50
5	300

• Objectif :
  1. Calculez la moyenne et l’écart-type des montants.
  2. Identifiez les ventes aberrantes.

---

Solution :

1. Calcul de la moyenne et de l’écart-type : SELECT AVG(Montant) AS Moyenne, STDDEV(Montant) AS Ecart_Type FROM Faits_Ventes;
2. Identification des outliers : WITH Statistiques AS ( SELECT AVG(Montant) AS Moyenne, STDDEV(Montant) AS Ecart_Type FROM Faits_Ventes ) SELECT ID_Vente, Montant FROM Faits_Ventes, Statistiques WHERE ABS(Montant - Moyenne) > 3 * Ecart_Type;

---

4. Analyse des Périodes Sans Ventes

Contexte :

Vous devez identifier les périodes où aucune vente n’a été réalisée en comparant avec une table de dates.

Date	Montant (€)
2023-01-01	100
2023-01-03	200
2023-01-05	300

• Table des dates :

Date
2023-01-01
2023-01-02
2023-01-03
2023-01-04
2023-01-05

• Objectif : Trouvez les dates où aucune vente n’a été enregistrée.

---

Solution :

1. Requête : SELECT D.Date FROM Dimension_Dates D LEFT JOIN Faits_Ventes F ON D.Date = F.Date WHERE F.Date IS NULL;

---

5. Analyse de la Rétention Client

Contexte :

Un e-commerce souhaite mesurer la rétention client. Un client est considéré comme actif s’il a passé au moins une commande sur une période donnée. Voici les données des commandes :

ID Client	Date Commande
1	2023-01-01
2	2023-01-02
1	2023-02-15
3	2023-02-20

• Objectif :
  1. Identifiez les clients ayant passé au moins une commande chaque mois.
  2. Calculez le taux de rétention client.

---

Solution :

1. Clients actifs chaque mois : SELECT ID_Client, T.Mois, T.Annee FROM Faits_Commandes C JOIN Dimension_Temps T ON C.Date_Commande = T.Date GROUP BY ID_Client, T.Mois, T.Annee HAVING COUNT(*) > 0;
2. Taux de rétention : WITH Clients_Mois AS ( SELECT DISTINCT ID_Client, T.Mois, T.Annee FROM Faits_Commandes C JOIN Dimension_Temps T ON C.Date_Commande = T.Date ), Total_Clients AS ( SELECT COUNT(DISTINCT ID_Client) AS Total FROM Faits_Commandes ) SELECT COUNT(DISTINCT CM.ID_Client) * 100.0 / TC.Total AS Taux_Retention FROM Clients_Mois CM, Total_Clients TC WHERE CM.Mois = 2;

---

6. Gestion des Changements Historiques

Contexte :

Un entrepôt de données doit gérer l’historique des changements des informations client (par exemple, changement d’adresse). Voici une table actuelle :

ID Client	Nom	Adresse	Date Début	Date Fin
1	Alice	Paris	2023-01-01	2023-06-30
1	Alice	Lyon	2023-07-01	NULL

• Objectif :
  1. Mettez à jour la table pour clôturer une période existante.
  2. Insérez une nouvelle ligne avec la nouvelle adresse.

---

Solution :

1. Clôturer la période existante : UPDATE Dimension_Client SET Date_Fin = '2023-06-30' WHERE ID_Client = 1 AND Date_Fin IS NULL;
2. Insérer une nouvelle ligne : INSERT INTO Dimension_Client (ID_Client, Nom, Adresse, Date_Debut, Date_Fin) VALUES (1, 'Alice', 'Lyon', '2023-07-01', NULL);

---

Ces exercices offrent une vision pratique des défis réels rencontrés dans les entrepôts de données et couvrent des domaines comme la gestion des données manquantes, la détection des anomalies, et l’analyse de la rétention client. Ils permettent de consolider vos compétences tout en explorant des cas concrets.

Exercices avec Cas Particuliers pour la Pratique des Entrepôts de Données

Voici une liste d’exercices qui incluent des cas particuliers souvent rencontrés dans les entrepôts de données. Ces exercices visent à approfondir la compréhension des scénarios complexes impliquant la gestion, l’analyse et l’optimisation des données.

---

1. Gestion des Données Manquantes

Contexte :

Vous travaillez avec une table de faits des ventes où certaines informations de localisation client sont manquantes. Voici un extrait des données :

ID Client	Date	Montant (€)	ID Région
1	2023-01-01	100	NULL
2	2023-01-02	200	5
3	2023-01-03	150	NULL

• Objectif :
  1. Remplacez les valeurs NULL dans la colonne ID Région par une région par défaut (par exemple, -1).
  2. Calculez le total des ventes pour chaque région.

---

Solution :

1. Remplacement des NULL : SELECT ID_Client, Date, Montant, COALESCE(ID_Region, -1) AS ID_Region FROM Faits_Ventes;
2. Total des ventes par région : SELECT COALESCE(ID_Region, -1) AS ID_Region, SUM(Montant) AS Total_Ventes FROM Faits_Ventes GROUP BY COALESCE(ID_Region, -1);

---

2. Gestion des Données Duplicates

Contexte :

Dans une table de faits, certaines transactions sont dupliquées par erreur :

ID Transaction	Montant (€)
1	100
2	200
1	100
3	150

• Objectif :
  1. Identifiez les doublons dans la table.
  2. Supprimez les lignes en double en ne conservant que l’une d’entre elles.

---

Solution :

1. Identification des doublons : SELECT ID_Transaction, COUNT(*) AS Nb_Occurrences FROM Faits_Ventes GROUP BY ID_Transaction HAVING COUNT(*) > 1;
2. Suppression des doublons : DELETE FROM Faits_Ventes WHERE ID_Transaction IN ( SELECT ID_Transaction FROM ( SELECT ID_Transaction, ROW_NUMBER() OVER(PARTITION BY ID_Transaction ORDER BY ID_Transaction) AS Rn FROM Faits_Ventes ) AS Sous_Requete WHERE Rn > 1 );

---

3. Identification des Outliers

Contexte :

Vous devez analyser les montants des ventes dans une table et identifier les valeurs aberrantes (outliers). Une vente est considérée comme aberrante si elle dépasse trois fois l’écart-type par rapport à la moyenne.

ID Vente	Montant (€)
1	100
2	200
3	1500
4	50
5	300

• Objectif :
  1. Calculez la moyenne et l’écart-type des montants.
  2. Identifiez les ventes aberrantes.

---

Solution :

1. Calcul de la moyenne et de l’écart-type : SELECT AVG(Montant) AS Moyenne, STDDEV(Montant) AS Ecart_Type FROM Faits_Ventes;
2. Identification des outliers : WITH Statistiques AS ( SELECT AVG(Montant) AS Moyenne, STDDEV(Montant) AS Ecart_Type FROM Faits_Ventes ) SELECT ID_Vente, Montant FROM Faits_Ventes, Statistiques WHERE ABS(Montant - Moyenne) > 3 * Ecart_Type;

---

4. Analyse des Périodes Sans Ventes

Contexte :

Vous devez identifier les périodes où aucune vente n’a été réalisée en comparant avec une table de dates.

Date	Montant (€)
2023-01-01	100
2023-01-03	200
2023-01-05	300

• Table des dates :

Date
2023-01-01
2023-01-02
2023-01-03
2023-01-04
2023-01-05

• Objectif : Trouvez les dates où aucune vente n’a été enregistrée.

---

Solution :

1. Requête : SELECT D.Date FROM Dimension_Dates D LEFT JOIN Faits_Ventes F ON D.Date = F.Date WHERE F.Date IS NULL;

---

5. Analyse de la Rétention Client

Contexte :

Un e-commerce souhaite mesurer la rétention client. Un client est considéré comme actif s’il a passé au moins une commande sur une période donnée. Voici les données des commandes :

ID Client	Date Commande
1	2023-01-01
2	2023-01-02
1	2023-02-15
3	2023-02-20

• Objectif :
  1. Identifiez les clients ayant passé au moins une commande chaque mois.
  2. Calculez le taux de rétention client.

---

Solution :

1. Clients actifs chaque mois : SELECT ID_Client, T.Mois, T.Annee FROM Faits_Commandes C JOIN Dimension_Temps T ON C.Date_Commande = T.Date GROUP BY ID_Client, T.Mois, T.Annee HAVING COUNT(*) > 0;
2. Taux de rétention : WITH Clients_Mois AS ( SELECT DISTINCT ID_Client, T.Mois, T.Annee FROM Faits_Commandes C JOIN Dimension_Temps T ON C.Date_Commande = T.Date ), Total_Clients AS ( SELECT COUNT(DISTINCT ID_Client) AS Total FROM Faits_Commandes ) SELECT COUNT(DISTINCT CM.ID_Client) * 100.0 / TC.Total AS Taux_Retention FROM Clients_Mois CM, Total_Clients TC WHERE CM.Mois = 2;

---

6. Gestion des Changements Historiques

Contexte :

Un entrepôt de données doit gérer l’historique des changements des informations client (par exemple, changement d’adresse). Voici une table actuelle :

ID Client	Nom	Adresse	Date Début	Date Fin
1	Alice	Paris	2023-01-01	2023-06-30
1	Alice	Lyon	2023-07-01	NULL

• Objectif :
  1. Mettez à jour la table pour clôturer une période existante.
  2. Insérez une nouvelle ligne avec la nouvelle adresse.

---

Solution :

1. Clôturer la période existante : UPDATE Dimension_Client SET Date_Fin = '2023-06-30' WHERE ID_Client = 1 AND Date_Fin IS NULL;
2. Insérer une nouvelle ligne : INSERT INTO Dimension_Client (ID_Client, Nom, Adresse, Date_Debut, Date_Fin) VALUES (1, 'Alice', 'Lyon', '2023-07-01', NULL);

---

Ces exercices offrent une vision pratique des défis réels rencontrés dans les entrepôts de données et couvrent des domaines comme la gestion des données manquantes, la détection des anomalies, et l’analyse de la rétention client. Ils permettent de consolider vos compétences tout en explorant des cas concrets.