Ce document est destiné aux étudiants de la Faculté d’Électronique et d’Informatique, Département d’Informatique, pour l’année académique 2014/2015, niveau M1 MIND. Il s’agit d’un examen portant sur le module des Entrepôts de Données.
Il couvre les notions suivantes:
- Proposition d’un MLD ROLAP dénormalisé pour trois cubes d’analyse des performances scientifiques des laboratoires de recherche.
- Création d’une dimension non temporelle avec une hiérarchie d’au moins trois niveaux.
- Proposition d’une vue matérialisée pour améliorer les temps d’accès sur cette dimension.
- Création du fait du premier magasin sous forme de table et script d’alimentation en Pl/SQL sous Oracle.
- Proposition d’un MLD normalisé pour un cube dédié à l’analyse des frais de participation dans des conférences.
- Exemples de décisions pouvant être prises suite à l’analyse des cubes.
- Application de la méthode de Kortink avec des exemples d’entités transactionnelles, de composition et hiérarchiques.
- Résolution de requêtes SQL sur une table d’étudiants et de modules.
Examen Entrepots de Donnes -BI - business intelligence
Télécharger PDFFaculté d’Electronique et d’Informatique
Département d’Informatique 2014/2015 M1 MIND
Examen du module : Entrepôts de Données
Exercice N°1
Le ministère de l'enseignement supérieur et de la recherche scientifique veut construire un système décisionnel dans le but d’améliorer le rendement des laboratoires de recherches en terme de publications dans des revues, de participation à des conférences, et d’encadrement. Nous disposons du modèle de données source suivant :
Remarques : TypeRevue et TypeConf ont pour occurrences possibles 'Nationale' et 'Internationale'.
A) Le premier magasin de données est constitué de trois cubes dont le but est l’analyse des performances scientifiques de chaque laboratoire de recherche.
- Cube 1 : Publication_Revue
- Cube 2 : Participation_Conférence
- Cube 3 : Encadrement
1. Proposez un MLD ROLAP dénormalisé pour chacun de ces 3 cubes, en précisant tous les détails de conception (Sans reprendre les hiérarchies des dimensions qui se répètent).
2. Donnez le script de création d’une dimension non temporelle (au choix) contenant une hiérarchie d’au moins trois niveaux (create table + create dimension).
3. Proposez une vue matérialisée pour améliorer les temps d’accès sur cette dimension (script de la vue).
4. Donnez le script de création du fait du premier magasin, sous forme de table, ainsi qu’un script d’alimentation en Pl/SQL sous Oracle.
B) Le deuxième magasin est constitué d’un seul cube dédié à analyser les frais de participation dans des conférences pour les laboratoires de recherche. Proposez un MLD normalisé pour ce cube.
C) Donnez des exemples de décisions qui peuvent être prises suite à l’analyse des 4 cubes.
D) Si on devait appliquer la méthode de Kortink, donnez un exemple d’une entité transactionnelle, une entité de composition et une entité hiérarchique.
Exercice N°2
Soit la table Etud_Mod :
| Matricule | CodeMod | CodeEMD | Note |
|---|---|---|---|
| m1 | BD | 1 | 12 |
| m1 | SYS | 1 | 7 |
| m2 | BD | 1 | 15 |
| m2 | BD | 2 | 12 |
| m1 | SYS | 2 | 10 |
| m1 | BD | 2 | 14 |
| m3 | BD | 1 | 18 |
| m3 | SYS | 2 | 12 |
| m2 | SYS | 2 | 14 |
| m3 | BD | 2 | 11 |
| m2 | SYS | 1 | 10 |
| m3 | SYS | 1 | 10 |
1- Donnez le résultat de la requête SQL suivante :
Select Matricule, CodeMod, Avg(note) from Etud_Mod group by Cube(Matricule, CodeMod) Order by Matricule, CodeMod;
2- Modifiez la requête précédente afin de déterminer les niveaux en utilisant la fonction grouping_id, et donnez le résultat affiché.
3- Écrire une requête qui donne la moyenne de chaque étudiant, et son rang dense, et donnez le résultat affiché. (Pour simplifier on ne tiendra pas compte des coefficients des modules).
BON COURAGE
N. Selmoune
Exercice N°1 (Remarque d’autres solutions sont possibles en fonction du choix de granularité)
A)
1)
Fait publication : CodeDate, CodeLabo, CodeDomaine, TypeRevue, Editeur, NBPublication
Dimensions :
- Date : Voir cours
- Laboratoire : CodeLabo, Dénomination Année création, CodeDept, NomDept, CodeFa, NomFac, CodeUniv, NomUniv
- Domaine : CodeDomaine, LibelléDomaine
- TypeRevue : TypeRevue
- Editeur : Editeur
Hierarchies :
- Temps (voir cours)
- Laboratoire : CodeLabo, Dénomination Année création-> CodeDept, NomDept -> CodeFa, NomFac -> CodeUniv, NomUniv
Fait Participation : CodeDate, CodeLabo, CodeDomaine, TypeConférence, CodeVille, NBParticipation
Dimensions :
- Date : Voir cours
- Laboratoire : CodeLabo, Dénomination Année création, CodeDept, NomDept, CodeFa, NomFac, CodeUniv, NomUniv
- Domaine : CodeDomaine, LibelléDomaine
- TypeConférence : TypeConférence
- Lieu : CodeVille, NomVille, CodePays, NomPays
Hierarchies :
- Lieu : CodeVille, NomVille,-> CodePays, NomPays
Fait Encadrement : CodeDate, CodeLabo, CodeProjet, TypePFE, NBEncadrement, NBEtudiant
Dimensions :
- Date : Voir cours
- Laboratoire : CodeLabo, Dénomination Année création, CodeDept, NomDept, CodeFa, NomFac, CodeUniv, NomUniv
- Projet : CodeProjet, TitreProjet, DateDebProjet
- TypePFE : TypePFE
Hierarchies :
- Projet : CodeProjet, TitreProjet->DateDebProjet
2)
Create table Laboratoire (CodeLabo varchar(10) primary key, Dénomination varchar(20), Annéecréation number (4) , CodeDept varchar(10), NomDept varchar(20), CodeFa varchar(10), NomFac varchar(20), CodeUniv varchar(10), NomUniv varchar(10)) ; Create dimension Dlaboratoire Level L1 is (Laboraoire.CodeLabo) Level L2 is (Laboraoire.CodeDept) Level L3 is (Laboraoire.CodeFac) Level L4 is (Laboraoire.CodeUniv) Hierarchy H1 (L1 child of L2 child of L3 child of L4) Attribute L1 determines (Dénomination, Annéecréation) Attribute L2 determines (NomDept) Attribute L3 determines (NomFac) Attribute L4 determines (NomUniv) ;
3)
Create materialized view v1 Build immediate refresh on demand enable query rewrite as Select CodeDept, NomDept, Sum(NBPublication) From Publication p, Laboratoire L Where p.CodeLabo= L.CodeLabo Group by CodeDept, NomDept;
4)
Create table Publication (CodeT date, CodeLabo varchar(10), CodeDomaine varchar(20), TypeRevue varchar(10), Editeur varchar(20)) ; Contraintes de clés primaire + étrangère (voir cours) CREATE OR REPLACE PROCEDURE alimFait IS BEGIN FOR i IN (SELECT Distinct DatePublication as CodeT, L.CodeLab, D.CodeDomaine, TypeRevue, Editeur ,Count(Titre_Article) AS NBPlublication FROM Chercheur , Domaine D, Publie P, Revue R, Laboratoire_de_Recherche L WHERE C.CodeLab=L.CodeLab and C.CodeLab=P.CodeLab and P.CodeDomaine=D.CodeDomaine and P.CodeRevue= R.CodeRevue GROUP BY DatePublication as CodeT, L.CodeLab, D.CodeDomaine, TypeRevue, Editeur) LOOP INSERT INTO Publication VALUES (i. CodeT, i.CodeLab, i.CodeDomaine, i.TypeRevue, i.Editeur, i.NBPublication); END LOOP; END alimFait; / EXECUTE alimFait;
B)
Fait Participation2 : CodeDate, CodeLabo, CodeDomaine, CodeConférence, FraisParticipation
Dimensions : (représentation graphique en flocon de neige (voir cours)
- Date : Voir cours
- Laboratoire : CodeLabo, Dénomination Année création, CodeDept, NomDept, CodeFa, NomFac, CodeUniv, NomUniv
- Domaine : CodeDomaine, LibelléDomaine
- Conférence : CodeConférence, TypeConférence, CodeVille, NomVille, CodePays, NomPays
Hierarchies :
- Conférence : CodeConférence, TypeConférence, -> CodeVille, NomVille,-> CodePays, NomPays
C)
- Récompenser les laboratoires ayant un grand nombre de publications internationales
- Reclasser les universités en fonction de leur participation à des conférences
- Sanctionner les projets de recherche dont le nombre d’encadrement est insuffisant.
- Interdire la participation aux conférences trop couteuses
D)
Entité transaction : table publie, entité de composition table Chercheur, entité hiérarchique : table Laboratoire_de_recherche
Exercice N°2 : (5,5)
1- 1,5 pt
N. Selmoune
2- 2 pt
select matricule , codemod, avg(note) , grouping_id(matricule, codemod) as gr from notes group by cube (matricule , codemod) order by matricule,codemod;
3- 2 pt
select matricule ,avg(note), dense_rank() over (order by avg(note) desc) from notes group by matricule order by matricule;
N. Selmoune
FAQ
1. Qu'est-ce qu'un entrepôt de données ?
Un entrepôt de données est un système de gestion de bases de données conçu pour l'analyse et le reporting. Il stocke des données provenant de différentes sources et les organise de manière à faciliter l'analyse et la prise de décision.
2. Quelles sont les dimensions et les faits dans un cube de données ?
Les dimensions sont des catégories d'analyse, comme le temps, le lieu, ou le produit. Les faits sont les mesures quantitatives, comme les ventes ou les profits, qui sont analysées en fonction des dimensions.
3. Qu'est-ce qu'une vue matérialisée ?
Une vue matérialisée est une requête pré-calculée et stockée dans la base de données. Elle permet d'accélérer les temps d'accès aux données en évitant de recalculer les résultats à chaque fois qu'une requête est exécutée.