Epreuve de « Modélisation et Intégration de Données » - Université Virtuelle de Tunis

Cette épreuve de « Modélisation et Intégration de Données » teste les compétences en conception de schémas d'entrepôts de données (modèles en étoile et en flocon), en formulation de requêtes SQL adaptées à ces modèles, ainsi qu'en analyse critique des avantages et inconvénients des différentes modélisations. Elle évalue également la capacité à traduire un besoin métier en modèle de données décisionnel.

Exercice 1

Il s'agit de concevoir un entrepôt de données pour la chaîne de magasins « TuttoJourno », spécialisée dans la vente de journaux et magazines, afin d'analyser les ventes par publication, type, magasin, vendeur et période.

1. Proposition d’un schéma en étoile

On demande de proposer un schéma en étoile adapté au scénario, avec des indicateurs pertinents, ainsi que les hiérarchies et attributs des dimensions.

Analyse du besoin :

• Faits à analyser : ventes quotidiennes (nombre d’exemplaires vendus) par magasin, publication, vendeur, période.
• Dimensions à considérer : Publication (avec type et éditeur), Magasin (type de magasin), Vendeur, Temps (date).

Schéma en étoile proposé :

Table de faits	Tables de dimensions
Faits_Ventes	Dim_Publication : publication_id, titre, type_publication, éditeur, édition Dim_Magasin : magasin_id, nom, type_magasin (kiosque, grande surface), adresse Dim_Vendeur : vendeur_id, nom, prénom, magasin_id Dim_Temps : date_id, date, jour, mois, trimestre, année

Indicateurs (mesures) :

• Quantité vendue : nombre d’exemplaires vendus

Hiérarchies et attributs :

• Dimension Temps : date > jour > mois > trimestre > année
• Dimension Publication : édition < publication (titre) < type_publication < éditeur
• Dimension Magasin : magasin_id < type_magasin (kiosque, grande surface)
• Dimension Vendeur : vendeur_id < magasin_id (relation)

Conclusion : Ce schéma en étoile permet d’analyser les ventes selon plusieurs axes (publication, magasin, vendeur, temps) avec un indicateur principal : la quantité vendue.

3. Transformation en schéma en flocon de neige

On demande de transformer le schéma en étoile précédent en schéma en flocon, c’est-à-dire en normalisant certaines dimensions.

Transformation :

• Dimension Publication : on décompose en plusieurs tables liées
  • Dim_Éditeur : éditeur_id, nom_éditeur
  • Dim_Type_Publication : type_id, nom_type (mode, sport, voiture, enfant, ...)
  • Dim_Publication : publication_id, titre, édition, éditeur_id, type_id
• Dimension Magasin : on peut créer une table Dim_Type_Magasin
  • Dim_Type_Magasin : type_magasin_id, nom_type (kiosque, grande surface)
  • Dim_Magasin : magasin_id, nom, adresse, type_magasin_id
• Dimension Vendeur reste inchangée, mais on peut envisager une table Magasin liée
• Dimension Temps reste inchangée (souvent déjà normalisée)

Schéma en flocon résumé :

Faits_Ventes	Dim_Publication	Dim_Éditeur	Dim_Type_Publication	Dim_Magasin	Dim_Type_Magasin	Dim_Vendeur	Dim_Temps
publication_id, magasin_id, vendeur_id, date_id, quantité_vendue	publication_id, titre, édition, éditeur_id, type_id	éditeur_id, nom_éditeur	type_id, nom_type	magasin_id, nom, adresse, type_magasin_id	type_magasin_id, nom_type	vendeur_id, nom, prénom, magasin_id	date_id, date, jour, mois, trimestre, année

Conclusion : Le schéma en flocon normalise les dimensions, ce qui réduit la redondance mais complexifie les jointures.

4. Requête SQL pour la quantité de vente des publications de la catégorie sport en 2019

On doit écrire deux requêtes SQL, une pour le schéma en étoile, une autre pour le schéma en flocon, puis discuter les avantages et inconvénients.

a. Requête SQL pour le schéma en étoile

SELECT SUM(F.quantité_vendue) AS total_ventes_sport_2019
FROM Faits_Ventes F
JOIN Dim_Publication P ON F.publication_id = P.publication_id
JOIN Dim_Temps T ON F.date_id = T.date_id
WHERE P.type_publication = 'sport'
  AND T.année = 2019;

Explication : On joint la table des faits avec la dimension Publication pour filtrer sur le type 'sport', et avec la dimension Temps pour limiter à l'année 2019. On somme la quantité vendue.

b. Requête SQL pour le schéma en flocon

SELECT SUM(F.quantité_vendue) AS total_ventes_sport_2019
FROM Faits_Ventes F
JOIN Dim_Publication P ON F.publication_id = P.publication_id
JOIN Dim_Type_Publication TP ON P.type_id = TP.type_id
JOIN Dim_Temps T ON F.date_id = T.date_id
WHERE TP.nom_type = 'sport'
  AND T.année = 2019;

Explication : Ici, la dimension Publication est décomposée, donc on doit joindre aussi la table Dim_Type_Publication pour filtrer sur 'sport'.

Discussion des avantages et inconvénients

• Schéma en étoile :
  • Avantages : requêtes plus simples, moins de jointures, meilleure performance en lecture.
  • Inconvénients : redondance possible dans les dimensions, plus de stockage.
• Schéma en flocon :
  • Avantages : normalisation réduit la redondance, facilite la maintenance des données dimensionnelles.
  • Inconvénients : requêtes plus complexes avec plus de jointures, potentiellement moins performant en lecture.

Exercice 2

Il s'agit de concevoir un entrepôt de données pour une école supérieure qui souhaite analyser les facteurs influant sur la réussite aux examens, avec plusieurs questions à traiter.

1. Schéma en étoile de l’entrepôt de données

On doit proposer un schéma en étoile permettant de répondre aux questions sur les réussites aux examens selon matière, sexe, âge, période, etc.

Analyse du besoin :

• Faits : résultats d’examens (réussite ou échec, note)
• Dimensions : Étudiant (âge, sexe), Cours (désignation, obligatoire/optionnel), Date (date examen)
• Indicateur principal : nombre de réussites (compte des examens réussis)

Schéma en étoile proposé :

Table de faits	Tables de dimensions
Faits_Examens	Dim_Étudiant : étudiant_id, âge, sexe Dim_Cours : cours_id, désignation, type_cours (obligatoire, optionnel) Dim_Temps : date_id, date, jour, mois, trimestre, année, année_universitaire

Indicateurs :

• Réussite : booléen ou 0/1 indiquant réussite ou échec
• Note obtenue (optionnel pour analyses plus fines)

Hiérarchies :

• Temps : date > jour > mois > trimestre > année > année universitaire
• Cours : type_cours (obligatoire/optionnel) < désignation
• Étudiant : âge, sexe (attributs simples)

2. Transformation en schéma en flocon de neige

On doit normaliser certaines dimensions pour obtenir un schéma en flocon.

Transformation :

• Dimension Cours décomposée en :
  • Dim_Type_Cours : type_cours_id, nom_type (obligatoire, optionnel)
  • Dim_Cours : cours_id, désignation, type_cours_id
• Dimension Étudiant reste simple, pas de décomposition nécessaire
• Dimension Temps reste inchangée (déjà normalisée)

Schéma en flocon résumé :

Faits_Examens	Dim_Étudiant	Dim_Cours	Dim_Type_Cours	Dim_Temps
étudiant_id, cours_id, date_id, réussite, note	étudiant_id, âge, sexe	cours_id, désignation, type_cours_id	type_cours_id, nom_type	date_id, date, jour, mois, trimestre, année, année_universitaire

Conclusion : La normalisation réduit la redondance dans la dimension Cours, mais complexifie les requêtes.

Méthode

Cette épreuve valorise la capacité à :

• Analyser un besoin métier précis et identifier les faits, dimensions et indicateurs pertinents.
• Concevoir un schéma en étoile clair, avec des hiérarchies bien définies dans les dimensions.
• Transformer un schéma en étoile en schéma en flocon en normalisant les dimensions, tout en conservant la cohérence des données.
• Écrire des requêtes SQL adaptées à chaque type de schéma, en maîtrisant les jointures nécessaires.
• Comparer les avantages et inconvénients des modèles en étoile et en flocon, notamment en termes de simplicité, performance et maintenance.

Les erreurs pénalisées seront notamment :

• Confusion entre faits et dimensions.
• Omission des hiérarchies dans les dimensions.
• Requêtes SQL incorrectes ou incomplètes, sans jointures adéquates.
• Absence de discussion critique sur les modèles proposés.