Ce document est destiné aux étudiants de l'Université des Sciences et de la Technologie Houari Boumediene (USTHB) pour l'année académique 2012/2013. Il couvre les notions suivantes:
- Modélisation de bases de données pour la gestion de projets.
- Création de tableaux de bord pour le suivi des projets.
- Analyse de données avec des magasins de données.
- Utilisation de vues matérialisées et de dimensions pour l'analyse des données.
- Application de l'algorithme A priori pour déterminer les ensembles fréquents.
Examen SDED M2 IL USTHB 2012 2013 -BI - business intelligence
Télécharger PDFExercice N°1 (10 pts)
Considérons le diagramme de classes ci-dessous concernant la base de donnée d'une application de gestion de projet :
1- On veut intégrer dans l'application des tableaux de bord afin d'aider les décideurs dans le suivi des projets. Pour cela nous identifions d'abord un premier magasin de données dont le fait comporte les mesures NbProjets, et NbProjetsenRetard
a. Proposer un modèle logique en étoile conforme à la granularité du fait choisi ?
USTHB 2012 /2013
FEI
Département d’Informatique N. Selmoune Filiale
CodeFil
NomFil
CodeEnt
NomEnt
Client
NumCl NomCl TypeCl Sexe
TypeEnt CodeSect LibSect
CodeW NomWil
Temps
IdDate
Jour
IdMois
Mois
Année
Suivi de Projets
IdDate
CodeFil
NumCl
CodePart
NbProjets
NbProjetsenRetard
CodeW
NomW
Partenaire Ext
CodePart
NomPart
TypePart
b. Donner les hiérarchies des différentes dimensions ?
Temps : jour-> mois-> année
Partenaire externe : Partenaire-> typePartenaire
Filiale : Filiale-> entreprise-> TypeEntreprise
Filiale-> entreprise->SecteurActivité
Client : Client->TypeClient
Client ->Wilaya
Client->Sexe
c. Proposer une formule pour le calcul de la deuxième mesure ? Nombre de projet ayant un taux d’avancement <100% et datedebut + duréePrévue>= date courante
d. Proposer d'autres mesures pour ce magasin ?
Nombre de projets terminés, taux d’avancement moyen, Nombre de projets en avance…
e. Donner le script complet de création de la dimension Filiale sous forme de vue matérialisée.
Create materialized view VFiliale
Build immediate
Refresh complete on demand
As select CodeFil, NomFil, e.CodeEnt, NomEnt, TypeEnt, s.CodeSect, LibSect, w.CodeW, NomWil
From Filiale f, Entreprise e, Secteuractivité s, Wilaya w
Where f.CodeEnt=e.CodeEnt and e.CodeSect=s.CodeSect and f.CodeW=w.CodeW;
USTHB 2012 /2013
FEI
Département d’Informatique N. Selmoune
Alter materialized view FilialeV add constraint c1 Primary key (CodeFil);
Create dimension FilialeDim
Level Filiale is (FilialeV. CodeFil)
Level Entreprise is (FilialeV.CodeEnt)
Level TypeEntreprise is (FilialeV.TypeEnt)
Level SectAct is (FilialeV.CodeSect)
Level Wilaya is (FilialeV.CodeW)
Hierarchy h1 (Filiale child of Entreprise child of TypeEntreprise)
Hierarchy h2 (Filiale child of Entreprise child of SectAct)
Hierarchy h3 (Filiale child of Wilaya)
Attribute Filiale determine FilialeV.NomFil
Attribute Entreprise determine FilialeV.NomEnt
Attribute SectAct determine FilialeV.LibSect
Attribute Wilaya determine FilialeV.NomWil ;
2- Un deuxième magasin de données de granularité plus fine permet (entre autres) d'analyser la mesure NBTaĉhes pour chaque projet.
a. Proposer un modèle logique en flocon de neige conforme à la granularité du fait choisi ?
A titre indicatif les dimensions pourraient être :
Temps, Equipe et Projet
b. Donner les hiérarchies des différentes dimensions ?
Temps : jour-> mois-> année
Projet : Projet->Client->TypeClient
Projet->Client->Wilaya
Equipe : Equipe->Filiale-> entreprise-> TypeEntreprise
Equipe->Filiale-> entreprise->SecteurActivité
Equipe->filiale->Wilaya
c. Proposer d'autres mesures pour ce magasin ?
NbTâchesenRetard, NBTâchesTerminées…
Exercice N°2 (5 pts)
Soit la vue matérialisée (VM) ci-dessous :
Département
Faculté
Année
NbDiplômés
INF
FEI
2012
300
TELECOM
FEI
2011
50
PHYSTH
FPHY
2011
120
PHYSRAY
FPHY
2011
200
PHYSTH
FPHY
2012
180
PHYSRAY
FPHY
2012
160
INF
FEI
2011
330
TELECOM
FEI
2012
70
INSTR
FEI
2012
90
1. Ecrire la requête qui donne la répartition cumulative du nombre de diplômés par faculté pour chaque année.
Select Année, faculté, Sum(nbdiplomés) as nbd, Cume_Dist () over Partition by Année order by sum(nbdiplomés) as DC
From VM1
Group by Année, faculté;
USTHB 2012 /2013
FEI
Département d’Informatique N. Selmoune
2. Donnez le résultat de la requête
Année
Faculté
NBD
DC
2011
FEI
380
1
2011
FPHY
320
0 .5
2012
FEI
460
1
2012
FPHY
340
0.5
3. Ecrire la requête qui donne le classement dense des facultés en fonction du nombre de diplômés (toutes années confondues).
Select faculté, DENSE_RANK() OVER (ORDER BY SUM(NBdiplomés) DESC ) AS classement From VM1
Group by faculté;
Exercice N°3 ((5 pts)
Une agence de voyage veut analyser le comportement de ses clients, elle dispose d’une source de données comportant la liste de ses clients ainsi que les destinations qu’ils ont déjà visitées.
Client
Destination
C1
Tunis, Istanbul, Damas, Paris
C2
Damas, Marrakech, Tunis
C3
Paris, Istanbul
C4
Pékin, Tunis, Londres, Istanbul
C5
Paris, Tunis, Marrakech
C6
Rome, Paris, Marrakech
C7
Tunis, Damas, Marrakech
C8
Paris, Istanbul, Tunis
C9
Marrakech, Tunis
C10
Paris, Istanbul
1. Appliquer l’algorithme A priori afin de déterminer les ensembles fréquents pour Minsup = 20% de la population (l’utilisation du bitmap permet de faciliter le calcul des fréquences).
Minsup =2(10(c10)*20%), à la première itération éliminer Pékin, Rome et Londres
A la deuxième itération 10 itemsets dont 7 fréquents
A la troisième itération 3 itemsets dont 2 fréquents : (Tunis, Istanbul, Paris) et (Tunis , Damas , Marrakech).
Il y a une 4ème itération mais l’ensemble des itemsets est vide.
2. Donnez des exemples de décisions pouvant être prises en fonction de ces résultats. Proposer des formules de voyage regroupant les destinations fréquentes, négocier des tarifs préférentiels avec les chaînes hôtelières présentes dans toutes les destinations fréquentes, rendre les destinations moins fréquentes plus attractives …etc.
FAQ
1. Qu'est-ce qu'un magasin de données ?
Un magasin de données est une base de données conçue pour le reporting et l'analyse. Il stocke des données historiques et agrégées provenant de diverses sources pour faciliter l'analyse décisionnelle.
2. Qu'est-ce qu'une vue matérialisée ?
Une vue matérialisée est une requête stockée dans une base de données qui est pré-calculée et stockée physiquement. Elle permet d'accélérer les requêtes répétitives en évitant de recalculer les résultats à chaque fois.
3. Qu'est-ce que l'algorithme A priori ?
L'algorithme A priori est une méthode utilisée pour l'extraction de règles d'association dans les bases de données. Il est couramment utilisé pour l'analyse de paniers d'achat et la découverte de motifs fréquents.