Entrepôt de Données : Exercices Corrigés

Les entrepôts de données sont des systèmes de gestion des données utilisés pour le stockage et l’analyse des grandes quantités d’informations provenant de diverses sources. Ils permettent aux entreprises de consolider, de gérer et d’analyser des données historiques pour faciliter la prise de décision. Dans cet article, nous explorerons des exercices pratiques et fournirons des corrigés détaillés pour aider à comprendre le fonctionnement et l’utilisation des entrepôts de données.

Exercices Pratiques

Exercice 1 : Modélisation de l’entrepôt de données

Objectif : Concevoir un schéma en étoile pour un entrepôt de données d’une entreprise de vente en ligne.

Énoncé : L’entreprise souhaite analyser les ventes de ses produits selon plusieurs dimensions : temps, produit, client et localisation. Les données suivantes sont disponibles :

Ventes (transaction_id, date, produit_id, client_id, quantité, montant)
Produits (produit_id, nom_produit, catégorie, prix)
Clients (client_id, nom, âge, sexe, ville, pays)
Temps (date, jour, mois, trimestre, année)
Localisation (ville, pays, région)

Corrigé :

Table des faits : Ventes

transaction_id	date	produit_id	client_id	quantité	montant
1	2023-01-01	101	201	2	40.00
2	2023-01-02	102	202	1	20.00

Tables de dimensions :

Dimension Produit

produit_id	nom_produit	catégorie	prix
101	T-Shirt	Vêtements	20.00
102	Casquette	Accessoires	15.00

Dimension Client

client_id	nom	âge	sexe	ville	pays
201	Alice	30	F	Paris	France
202	Bob	25	M	New York	USA

Dimension Temps

date	jour	mois	trimestre	année
2023-01-01	1	1	1	2023
2023-01-02	2	1	1	2023

Dimension Localisation

ville	pays	région
Paris	France	Europe
New York	USA	Amérique

Ce modèle permet de répondre à des questions analytiques telles que les ventes par produit, par client, par période, et par localisation.

Exercice 2 : Chargement de données dans l’entrepôt

Objectif : Utiliser un ETL (Extract, Transform, Load) pour charger des données dans un entrepôt.

Énoncé : Vous avez des fichiers CSV contenant des informations sur les ventes, les produits, les clients, le temps, et la localisation. Écrivez un script pour charger ces données dans les tables de l’entrepôt de données.

Corrigé :

import pandas as pd
from sqlalchemy import create_engine

# Connexion à la base de données
engine = create_engine('sqlite:///entrepot_de_donnees.db')

# Chargement des fichiers CSV
ventes = pd.read_csv('ventes.csv')
produits = pd.read_csv('produits.csv')
clients = pd.read_csv('clients.csv')
temps = pd.read_csv('temps.csv')
localisation = pd.read_csv('localisation.csv')

# Chargement des données dans les tables
ventes.to_sql('ventes', engine, if_exists='replace', index=False)
produits.to_sql('produits', engine, if_exists='replace', index=False)
clients.to_sql('clients', engine, if_exists='replace', index=False)
temps.to_sql('temps', engine, if_exists='replace', index=False)
localisation.to_sql('localisation', engine, if_exists='replace', index=False)

Ce script Python utilise pandas pour lire les fichiers CSV et SQLAlchemy pour charger les données dans une base de données SQLite.

Synthèse 😉

Les entrepôts de données jouent un rôle crucial dans l’analyse et la prise de décision pour les entreprises. Les exercices pratiques présentés, accompagnés de leurs corrigés détaillés, offrent une compréhension approfondie de la modélisation et du chargement des données dans un entrepôt. La maîtrise de ces concepts permet d’optimiser l’analyse des données et de renforcer les stratégies commerciales.

Entrepôt de Données : Exercices Corrigés avec Cas Particuliers

Exercice 1 : Gestion des données manquantes

Objectif : Gérer les données manquantes dans un entrepôt de données.

Énoncé : Vous avez une table des ventes où certaines informations sur les produits et les clients sont manquantes. Proposez une méthode pour traiter ces données manquantes avant de les charger dans l’entrepôt.

Corrigé :

Identifier les données manquantes :

   import pandas as pd

   # Chargement du fichier CSV
   ventes = pd.read_csv('ventes.csv')

   # Identification des données manquantes
   missing_data = ventes.isnull().sum()
   print(missing_data)

Traitement des données manquantes :

Pour les produits : Remplir les valeurs manquantes avec un produit générique.
Pour les clients : Remplir les valeurs manquantes avec un client générique.

   # Remplacer les valeurs manquantes pour les produits
   ventes['produit_id'].fillna('999', inplace=True)
   ventes['nom_produit'].fillna('Produit Inconnu', inplace=True)
   ventes['catégorie'].fillna('Inconnue', inplace=True)

   # Remplacer les valeurs manquantes pour les clients
   ventes['client_id'].fillna('999', inplace=True)
   ventes['nom'].fillna('Client Inconnu', inplace=True)
   ventes['âge'].fillna(0, inplace=True)
   ventes['sexe'].fillna('Inconnu', inplace=True)

Exercice 2 : Agrégation des données

Objectif : Effectuer des agrégations sur les données pour générer des rapports.

Énoncé : Vous devez créer un rapport hebdomadaire des ventes totalisées par produit et par région.

Corrigé :

Agrégation des ventes par produit et par région :

   import pandas as pd

   # Chargement du fichier CSV
   ventes = pd.read_csv('ventes.csv')
   localisation = pd.read_csv('localisation.csv')

   # Fusionner les tables ventes et localisation
   ventes_localisation = pd.merge(ventes, localisation, on='ville')

   # Convertir la date en type datetime et extraire la semaine
   ventes_localisation['date'] = pd.to_datetime(ventes_localisation['date'])
   ventes_localisation['semaine'] = ventes_localisation['date'].dt.isocalendar().week

   # Agréger les ventes par produit et par région
   rapport_hebdomadaire = ventes_localisation.groupby(['semaine', 'produit_id', 'région'])['montant'].sum().reset_index()
   print(rapport_hebdomadaire)

Exercice 3 : Historisation des données (SCD – Slowly Changing Dimension)

Objectif : Gérer les changements dans les dimensions qui évoluent lentement.

Énoncé : Vous avez une table des clients et vous devez mettre en place un mécanisme pour historiser les changements dans les informations des clients (ex. adresse, numéro de téléphone) en utilisant les Slowly Changing Dimensions de type 2 (SCD Type 2).

Corrigé :

Création de la table des clients avec historisation :

   CREATE TABLE clients_historique (
       client_id INT,
       nom VARCHAR(100),
       adresse VARCHAR(255),
       téléphone VARCHAR(15),
       début_validité DATE,
       fin_validité DATE,
       actif BOOLEAN,
       PRIMARY KEY (client_id, début_validité)
   );

Insertion des nouvelles données avec historisation :

   -- Insertion des nouveaux clients
   INSERT INTO clients_historique (client_id, nom, adresse, téléphone, début_validité, fin_validité, actif)
   SELECT client_id, nom, adresse, téléphone, CURRENT_DATE, NULL, TRUE
   FROM clients_source
   WHERE client_id NOT IN (SELECT client_id FROM clients_historique WHERE actif = TRUE);

   -- Historisation des changements pour les clients existants
   UPDATE clients_historique
   SET fin_validité = CURRENT_DATE, actif = FALSE
   WHERE client_id IN (SELECT client_id FROM clients_source WHERE adresse != clients_historique.adresse OR téléphone != clients_historique.téléphone)
   AND actif = TRUE;

   -- Insertion des nouvelles valeurs pour les clients mis à jour
   INSERT INTO clients_historique (client_id, nom, adresse, téléphone, début_validité, fin_validité, actif)
   SELECT client_id, nom, adresse, téléphone, CURRENT_DATE, NULL, TRUE
   FROM clients_source
   WHERE client_id IN (SELECT client_id FROM clients_historique WHERE actif = FALSE AND fin_validité = CURRENT_DATE);

Entrepôt de Données : Exercices Avancés

Exercice 1 : Optimisation des Requêtes SQL

Objectif : Optimiser une requête SQL pour améliorer les performances de l’entrepôt de données.

Énoncé : Vous avez une requête SQL qui extrait les ventes totales par produit et par mois. Cette requête prend beaucoup de temps à s’exécuter. Optimisez-la en utilisant des index et des techniques de partitionnement.

Corrigé :

Requête originale :

   SELECT produit_id, DATE_TRUNC('month', date) AS mois, SUM(montant) AS ventes_totales
   FROM ventes
   GROUP BY produit_id, DATE_TRUNC('month', date);

Création d’index pour optimiser la requête :

   CREATE INDEX idx_ventes_produit_date ON ventes (produit_id, date);

Partitionnement de la table des ventes par mois :

   CREATE TABLE ventes_partitioned (
       produit_id INT,
       date DATE,
       montant DECIMAL(10, 2)
   ) PARTITION BY RANGE (date);

   CREATE TABLE ventes_2023_01 PARTITION OF ventes_partitioned
   FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

   CREATE TABLE ventes_2023_02 PARTITION OF ventes_partitioned
   FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

   -- Insertion des données dans les partitions
   INSERT INTO ventes_partitioned SELECT * FROM ventes;

Requête optimisée :

   SELECT produit_id, DATE_TRUNC('month', date) AS mois, SUM(montant) AS ventes_totales
   FROM ventes_partitioned
   GROUP BY produit_id, DATE_TRUNC('month', date);

Exercice 2 : Gestion des Slowly Changing Dimensions (SCD) Type 2

Objectif : Implémenter la gestion des Slowly Changing Dimensions (SCD) de type 2 pour suivre les changements historiques dans les données de dimension.

Énoncé : Vous avez une table de dimensions pour les employés avec des informations telles que le département et le salaire. Vous devez mettre en place une stratégie SCD Type 2 pour historiser les changements dans ces informations.

Corrigé :

Création de la table des employés avec gestion SCD Type 2 :

   CREATE TABLE employes_scd (
       employe_id INT,
       nom VARCHAR(100),
       departement VARCHAR(100),
       salaire DECIMAL(10, 2),
       debut_validite DATE,
       fin_validite DATE,
       actif BOOLEAN,
       PRIMARY KEY (employe_id, debut_validite)
   );

Script pour gérer les mises à jour :

   -- Mettre à jour la table des employés avec les nouvelles données
   INSERT INTO employes_scd (employe_id, nom, departement, salaire, debut_validite, fin_validite, actif)
   SELECT employe_id, nom, departement, salaire, CURRENT_DATE, NULL, TRUE
   FROM employes_source
   WHERE employe_id NOT IN (SELECT employe_id FROM employes_scd WHERE actif = TRUE)
   OR (employe_id IN (SELECT employe_id FROM employes_scd WHERE actif = TRUE)
       AND (departement != (SELECT departement FROM employes_scd WHERE employe_id = employes_source.employe_id AND actif = TRUE)
           OR salaire != (SELECT salaire FROM employes_scd WHERE employe_id = employes_source.employe_id AND actif = TRUE)));

   -- Définir la fin de validité des anciens enregistrements
   UPDATE employes_scd
   SET fin_validite = CURRENT_DATE, actif = FALSE
   WHERE employe_id IN (SELECT employe_id FROM employes_source)
   AND actif = TRUE
   AND (departement != (SELECT departement FROM employes_source WHERE employe_id = employes_scd.employe_id)
       OR salaire != (SELECT salaire FROM employes_source WHERE employe_id = employes_scd.employe_id));

Exercice 3 : Extraction, Transformation et Chargement (ETL)

Objectif : Concevoir et implémenter un processus ETL pour intégrer des données provenant de différentes sources dans un entrepôt de données.

Énoncé : Vous devez intégrer des données de ventes provenant de trois sources différentes : une base de données SQL, un fichier CSV et une API web. Implémentez un processus ETL pour consolider ces données dans une table unique dans l’entrepôt de données.

Corrigé :

import pandas as pd
import requests
from sqlalchemy import create_engine

# Connexion à la base de données SQL
engine = create_engine('mysql://user:password@host