Modélisation et Intégration de Données

Exercice 1 - Analyse des ventes TuttoJourno

L'énoncé de ce problème présente une erreur de numérotation (les questions passent de 1 à 3, puis la question 4 fait référence à une « question 2 » inexistante). Cette correction respecte la numérotation originale du sujet pour éviter toute confusion.

Question 1 - Schéma en étoile

Pour modéliser un entrepôt de données en schéma en étoile, nous devons identifier le sujet d'analyse (la table de faits) et les axes d'analyse (les dimensions). Le texte indique qu'on souhaite analyser les « ventes des publications » (nombre d'exemplaires) selon la publication, le magasin, le vendeur et la période.

1. Table de faits :

• Nom : Fait_Ventes
• Grain : Les ventes quotidiennes par édition d'une publication, par magasin et par vendeur.
• Mesure (Indicateur) : quantite_vendue (le nombre d'exemplaires vendus). On pourrait également ajouter le montant_total si le prix était connu, mais le texte ne parle que du nombre d'exemplaires.

2. Dimensions, attributs et hiérarchies :

Dimension	Clé primaire	Attributs	Hiérarchie implicite
Dim_Temps	id_temps	date_jour, mois, trimestre, annee	Date -> Mois -> Trimestre -> Année
Dim_Publication	id_publication	titre_edition, nom_publication, categorie (ex: sport), editeur	Edition -> Publication -> Catégorie -> Editeur
Dim_Magasin	id_magasin	nom_magasin, ville, type_magasin (kiosque ou grande surface)	Magasin -> Type de magasin
Dim_Vendeur	id_vendeur	nom_vendeur, prenom_vendeur	Vendeur (pas de hiérarchie spécifique)

Note : La table de faits Fait_Ventes contiendra les clés étrangères id_temps, id_publication, id_magasin, et id_vendeur.

Question 3 - Schéma en flocon de neige

Le schéma en flocon (snowflake) consiste à normaliser les tables de dimensions du schéma en étoile pour éliminer la redondance des données. Nous allons donc éclater les hiérarchies identifiées à la question 1 en plusieurs tables liées.

1. Dimension Temps floconnée :
   • Dim_Jour (id_temps, date_jour, id_mois)
   • Dim_Mois (id_mois, nom_mois, id_trimestre)
   • Dim_Trimestre (id_trimestre, libelle, id_annee)
   • Dim_Annee (id_annee, annee)

Dimension Publication floconnée :

• Dim_Edition (id_edition, titre_edition, id_publication) (Cette table sera liée à la table de faits)
• Dim_Publication (id_publication, nom_publication, id_categorie, id_editeur)
• Dim_Categorie (id_categorie, libelle_categorie)
• Dim_Editeur (id_editeur, nom_editeur)

Dimension Magasin floconnée :

• Dim_Magasin (id_magasin, nom_magasin, id_type_magasin)
• Dim_Type_Magasin (id_type_magasin, libelle_type)

Dimension Vendeur :

• Reste inchangée : Dim_Vendeur (id_vendeur, nom, prenom)

Question 4 - Requêtes SQL

L'objectif est de trouver la quantité de ventes des publications de la catégorie "sport" en 2019.

a. Avec le schéma en étoile (de la question 1)

Dans le schéma en étoile, toutes les informations de hiérarchie sont dénormalisées dans les tables de dimensions principales. Il suffit de faire une jointure directe avec la table de faits.

SELECT SUM(F.quantite_vendue) AS total_ventes_sport
FROM Fait_Ventes F
INNER JOIN Dim_Publication P ON F.id_publication = P.id_publication
INNER JOIN Dim_Temps T ON F.id_temps = T.id_temps
WHERE P.categorie = 'sport' 
  AND T.annee = 2019;

b. Avec le schéma en flocon (de la question 3)

Dans le schéma en flocon, les attributs categorie et annee se trouvent dans des tables distinctes qu'il faut lier par de multiples jointures. En supposant que la table de faits est liée à la granularité la plus fine (Dim_Edition et Dim_Jour).

SELECT SUM(F.quantite_vendue) AS total_ventes_sport
FROM Fait_Ventes F
INNER JOIN Dim_Edition E ON F.id_edition = E.id_edition
INNER JOIN Dim_Publication P ON E.id_publication = P.id_publication
INNER JOIN Dim_Categorie C ON P.id_categorie = C.id_categorie
INNER JOIN Dim_Jour J ON F.id_temps = J.id_temps
INNER JOIN Dim_Mois M ON J.id_mois = M.id_mois
INNER JOIN Dim_Trimestre Tr ON M.id_trimestre = Tr.id_trimestre
INNER JOIN Dim_Annee A ON Tr.id_annee = A.id_annee
WHERE C.libelle_categorie = 'sport' 
  AND A.annee = 2019;

Discussion des avantages et inconvénients :

• Schéma en étoile :
  • Avantages : Requêtes très simples à écrire et très performantes car elles minimisent le nombre de jointures (idéal pour la lecture et l'analyse).
  • Inconvénients : Redondance des données dans les dimensions (espace de stockage plus important) et risque d'incohérence lors des mises à jour.
• Schéma en flocon :
  • Avantages : Données normalisées, ce qui évite la redondance, économise de l'espace disque et facilite les mises à jour des dimensions de niveau supérieur (ex: changer le nom d'une catégorie).
  • Inconvénients : Requêtes très complexes et souvent moins performantes en raison du grand nombre de jointures requises pour croiser les données.

Exercice 2 - Analyse des réussites aux examens

Cet exercice demande de modéliser un entrepôt de données à partir de questions analytiques précises. Chaque question métier fournit des indices sur les dimensions nécessaires (matière, type de matière, sexe, âge, période) et sur les mesures (nombre de réussites, nombre d'échecs).

Question 1 - Schéma en étoile

1. Table de faits :

• Nom : Fait_Resultat_Examen
• Grain : Un passage d'examen par un étudiant à une date donnée pour un cours donné.
• Mesures :
  • note : la note obtenue à l'examen.
  • est_reussite : un indicateur valant 1 si l'étudiant a réussi, 0 s'il a raté. (Cela permet de répondre facilement aux questions "Combien ont réussi..." en faisant la somme de cette colonne).

2. Dimensions :

Dimension	Clé primaire	Attributs
Dim_Etudiant	id_etudiant	age, sexe
Dim_Cours	id_cours	designation, type_cours (obligatoire ou optionnel)
Dim_Temps	id_temps	date_examen, trimestre, annee_civile, annee_universitaire

Note de conception : L'âge est souvent une dimension changeante, mais dans ce type d'exercice classique, on le place comme attribut de l'étudiant ou on considère qu'il s'agit de l'âge au moment de l'examen.

Question 2 - Schéma en flocon de neige

Pour transformer le modèle précédent en flocon, on normalise les dimensions qui contiennent des hiérarchies claires.

1. La dimension Cours se sépare en deux :
   • Dim_Cours (id_cours, designation, id_type_cours)
   • Dim_Type_Cours (id_type_cours, libelle_type) (ex: obligatoire, optionnel)
2. La dimension Temps se sépare pour éviter la redondance des trimestres et années :
   • Dim_Date (id_temps, date_examen, id_trimestre)
   • Dim_Trimestre (id_trimestre, libelle_trimestre, id_annee_civile, id_annee_univ)
   • Dim_Annee_Civile (id_annee_civile, annee)
   • Dim_Annee_Universitaire (id_annee_univ, annee_univ)
3. La dimension Etudiant ne nécessite pas forcément d'être floconnée à moins de créer des tables de référence pour le sexe ou des tranches d'âge, ce qui serait excessif ici. Elle reste : Dim_Etudiant (id_etudiant, age, sexe).

Méthode

Pour réussir les épreuves de modélisation décisionnelle (Business Intelligence) :

1. Identifier la table de faits en cherchant l'action centrale : Dans l'exercice 1, c'est "vendre". Dans l'exercice 2, c'est "passer un examen". Le fait est l'événement qui se produit et que l'on veut quantifier.
2. Repérer les mesures : Cherchez les valeurs numériques à analyser dans le texte. Des expressions comme "combien", "nombre de", "quantité" ou "bilan" indiquent vos indicateurs. Si l'énoncé demande de compter un événement (comme une réussite), une mesure binaire (1 ou 0) est une excellente technique.
3. Repérer les dimensions (axes d'analyse) : Cherchez la préposition "par" dans le texte ("analyser par publication", "par trimestre", "par sexe"). Tout ce qui suit un "par" devient une dimension ou un attribut d'une dimension.
4. Maîtriser la différence Etoile vs Flocon :
   • L'étoile est plate, dénormalisée, rapide pour la lecture, toutes les données descriptives sont dans la même table dimensionnelle.
   • Le flocon est normalisé. On sépare les hiérarchies (Catégorie -> Sous-catégorie -> Produit) dans des tables reliées entre elles, générant plus de jointures dans le SQL.