Ce document est destiné aux étudiants de la Faculté d’Électronique et d’Informatique, Département d’Informatique, pour l’année académique 2015/2016, niveau M1 MIND. Il s’agit d’un examen portant sur le module des Entrepôts de Données.
Il couvre les notions suivantes:
- Choix de la méthode de conception pour un système décisionnel.
- Proposition d’un MLD ROLAP dénormalisé pour l’analyse de la répartition des délits.
- Création d’une dimension non temporelle avec hiérarchie.
- Proposition d’une vue matérialisée pour améliorer les temps d’accès.
- Création du fait du premier magasin sous forme de table et alimentation en PL/SQL.
- Proposition d’un MLD normalisé pour un cube d’analyse des coûts de la lutte contre le crime.
- Proposition d’un index de jointure bitmap.
- Exemples de décisions basées sur l’analyse des cubes.
- Requêtes SQL pour l’analyse des coûts et des ressources.
Examen Entrepots de Donnes -BI - business intelligence
Télécharger PDFExamen du module : Entrepôts de Données
Exercice N°1 (12 pts)
Un organisme chargé de l’étude de la criminalité dispose de la base de données décrite ci-dessous, et veut construire un système décisionnel dans le but d’analyser l’évolution et la répartition de la criminalité, et d’optimiser les moyens de lutte contre le crime.
Remarques : les ressources correspondent aux moyens utilisés dans la lutte contre le crime.
A) L’équipe de conception est confrontée au problème du choix de la méthode de conception, quelles sont les catégories de méthodes existantes, donnez une brève description de chaque catégorie.
B) Le premier magasin de données est dédié l’analyse de la répartition des délits, et tente de mettre en évidence les facteurs intervenants dans l’accroissement de la criminalité.
1. Proposez un MLD ROLAP dénormalisé pour ce magasin, en précisant tous les détails de conception.
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.
C) Le deuxième magasin est constitué d’un seul cube dédié à analyser les coûts de la lutte contre le crime.
1. Proposez un MLD normalisé pour ce cube, et donner les formules des mesures.
2. Proposez un index de jointure bitmap en vous basant sur un attribut au choix d’une dimension.
D) Donnez un exemple de décisions qui peut être prise suite à l’analyse de chacun des 2 cubes.
Exercice N°2 (8 pts)
Soit les tables Utilise et Ressource :
| Utilise | CodeMission | CodeRessource | DateM | NbUnités |
|---|---|---|---|---|
| M1 | R1 | 01/05/2016 | 12 | |
| M1 | R1 | 08/05/2016 | 7 | |
| M2 | R3 | 15/05/2016 | 15 | |
| M2 | R2 | 03/04/2016 | 12 | |
| M1 | R1 | 03/04/2016 | 10 | |
| M1 | R3 | 01/05/2016 | 14 | |
| M3 | R2 | 03/04/2016 | 18 | |
| M3 | R2 | 08/05/2016 | 12 | |
| M2 | R3 | 15/05/2016 | 14 | |
| M3 | R2 | 08/05/2015 | 11 | |
| M2 | R1 | 10/04/2016 | 10 | |
| M3 | R2 | 10/04/2016 | 10 |
Ressource
| CodeRessource | CoûtUnitaire |
|---|---|
| R1 | 100 |
| R2 | 200 |
| R3 | 300 |
1- Donnez le résultat de la requête SQL suivante :
Select CodeMission, dateM, grouping(CodeMission) as GM, grouping(dateM) as GD, sum(NBUnités*CoûtUnitaire) as Coût from Utilise, Ressource Where Utilise.CodeRessource=Ressource.CodeRessource Group by Rollup(CodeMission, dateM) Order by CodeMission, dateM;
2- Ecrire la requête qui donne la répartition cumulative du coût par ressource à l’intérieur de chaque mission. Donnez le résultat de la requête.
3- Ecrire la requête qui donne le rang de chaque mois, en fonction du coût. Donnez le résultat de la requête.
4- Ecrire la requête qui donne pour chaque mission la ressource qui a coûté le plus cher. Et donner le résultat de la requête.
BON COURAGE
N. Selmoune
Exercice N°1 (Remarque d’autres solutions sont possibles en fonction du choix de granularité)
A) Voir cours 1,5 pts
B) 1) 3 pts
Fait Délit( CodeDate, CodePoste, CodeLieu, CodePersonneAccusé, CodeTypeDélit , NBDélit)
Dimensions :
Date : Voir cours
PostePolice : CodePoste, CodeLieuP, NomLieuP, CodeVilleP, NomVilleP, CodeWilayaP, NomWilayaP
Lieu : CodeLieu, NomLieu, CodeVille, NomVille, CodeWilaya, NomWilaya
TypeDélit : CodeTypeDélit, TypeDélit
Accusé :CodePersonneAccusé, NomAccusé, DateNassAcc
Hierarchies :
Temps (voir cours)
PostePolice: CodePoste,-> CodeLieuP, NomLieuP, ->CodeVilleP, NomVilleP->, CodeWilayaP, NomWilayaP
Lieu : CodeLieu, NomLieu, ->CodeVille, NomVille->, CodeWilaya, NomWilaya
2) 1,5 pts
Create table Lieu (CodeLieu varchar(10) primary key, NomLieu varchar(20), CodeVille varchar(10), NomVille varchar(20), CodeWilaya varchar(10), NomWilaya varchar(20)) ; Create dimension Dlieu Level L1 is (Lieu.CodeLieu) Level L2 is (Lieu.CodeVille) Level L3 is (Lieu.CodeWilaya) Hierarchy H1 (L1 child of L2 child of L3) Attribute L1 determines (Nomlieu) Attribute L2 determines (NomVille) Attribute L3 determines (NomWilaya);
3) 1 pt
Create materialized view v1 Build immediate refresh on demand enable query rewrite as Select CodeVille, NomVille, Sum(NBDélit) From Délit d, Lieu L Where d.CodeLieu= L.CodeLieu Group by CodeVille, Nomville;
4) 1,5 pts
Create table Délit (CodeT date, CodePoste varchar(10), CodeLieu varchar(20), CodeTypeDélit varchar(10), CodePersonneAccusé varchar(20)) ; Contraintes de clés primaire + étrangère (voir cours) N. Selmoune CREATE OR REPLACE PROCEDURE alimFait BEGIN FOR i IN (SELECT Distinct DateDélit as codeDate, D.CodePoste,D.CodeLieu, D.CodePersonneAccusé,D.CodeTypeDélit , Count(CodeDélit) as NBDélit FROM Délit D, PostePolice P, Lieu L, Accusé A, TypeDélit T WHERE D.CodeCodePersonneAccusé==L.CodePersonne Accusé and D.CodePoste=P.CodePoste and D.CodeTypeDélit=T.CodeTypeDélit GROUP BY DateDélit, D.CodePoste,D.CodeLieu, D.CodePersonneAccusé, D.CodeTypeDélit LOOP INSERT INTO Délit VALUES (i.CodeDate,i.CodePoste, i.CodeLieu, i.CodePersonneAccusé, i.CodeTypeDélit , i.NBDélit); END LOOP; END alimFait; / EXECUTE alimFait;
C)
1- 2pts
Fait utilisation( CodeDate, CodePoste, CodeLieu, CodePersonnePolicier, CodeTypeRessource , Coût)
Dimensions :
Date : Voir cours
PostePolice (CodePoste, CodeLieuP*), Lieu(CodeLieuP, NomLieuP, CodeVilleP*) Ville(CodeVilleP, NomVilleP, CodeWilayaP*), Wilaya(CodeWilaya,NomWilayaP)
TypeRessource ( CodeTypeRessource, TypeRessource)
PolicierReponsable (CodePersonnePolicier, NomPersonnePolicier, DateNaiss)
Coût= coûtunitaire*nbunités
2- 0.5 pt
Create bitmap index b1 on utilisation(PolicierResponsable.NomPersonnePolicier) From utilisation , PolicierResponsable Where utilisation. CodePersonnePolicier= PolicierResponsable .CodePersonnePolicier
D) 1 pt
- Créer des postes de polices dans les lieux où il y a une forte criminalité
- Sensibiliser les responsables impliqués dans des coûts élevés.
Exercice N°2 : (8)
1- 2 pts
N. Selmoune
2- 2 pts
3- 2 pts
4- 2 pts
N. Selmoune
FAQ
1. Quelles sont les catégories de méthodes de conception pour les entrepôts de données ?
Les catégories de méthodes de conception pour les entrepôts de données incluent les méthodes top-down, bottom-up, et hybrides. Les méthodes top-down commencent par une vue d'ensemble et se concentrent sur les besoins des utilisateurs finaux. Les méthodes bottom-up commencent par les données disponibles et construisent progressivement l'entrepôt. Les méthodes hybrides combinent les deux approches.
2. Comment créer une dimension non temporelle avec une hiérarchie de trois niveaux ?
Pour créer une dimension non temporelle avec une hiérarchie de trois niveaux, vous devez d'abord créer une table contenant les attributs de la dimension. Ensuite, vous créez une dimension en spécifiant les niveaux de la hiérarchie et les attributs associés à chaque niveau.
3. Qu'est-ce qu'une vue matérialisée et comment améliorer les temps d'accès avec une vue matérialisée ?
Une vue matérialisée est une vue physique des données qui est stockée dans la base de données. Elle permet d'améliorer les temps d'accès en pré-calculant les résultats des requêtes fréquentes et en les stockant pour une utilisation ultérieure.