Ce document est destiné aux étudiants de la Faculté d’Électronique et d’Informatique, Département d’Informatique, pour l’année académique 2016/2017. Il s’agit d’un examen du module « Entrepôts de Données » pour le M1 MIND.
Il couvre les notions suivantes:
- Les principales étapes et avantages/inconvénients de la méthode de Ralph Kimball.
- La proposition d’un MLD ROLAP dénormalisé pour un magasin de données.
- La création de dimensions non temporelles et de vues matérialisées.
- La proposition d’un MLD normalisé pour un cube de données.
- La fragmentation horizontale d’une table de fait.
- Des exemples de décisions basées sur l’analyse des cubes de données.
- Des requêtes SQL pour analyser une table de ventes.
Examen Entrepots de Donnes -BI - business intelligence
Télécharger PDFFaculté d’Electronique et d’Informatique
Département d’Informatique 2016/2017
M1 MIND
Examen du module : Entrepôts de Données
Exercice N°1 (12 pts)
Une entreprise de développement de logiciels dispose de la base de données décrite ci-dessous, pour la gestion de ses projets, et veut construire un système décisionnel dans le but d’optimiser les coûts de développement.
Remarques :
- Les développeurs utilisent des outils hébergés dans un cloud et payables à l’heure
- Les développeurs sont payés à l’heure.
- Les développeurs_Seniors touchent une prime pour chaque projet.
- L’attribut ResTest prend ses valeurs dans l’ensemble (‘Concluant’, ‘non Concluant’)
A) L’équipe de conception opte pour la méthode de Ralph Kimball
1. Quelles sont les principales étapes de cette approche.
2. Quels sont les avantages et les inconvénients de cette approche.
B) Le premier magasin de données est basé sur les mesures concernant chaque module, dans le but d’optimiser les coûts de développement, et d’évaluer l’efficacité de chaque développeur.
1. Proposez un MLD ROLAP dénormalisé pour ce magasin, en précisant tous les détails de conception (y compris les formules de calcul des mesures).
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 de granularité moins fine est dédié à analyser les coûts au niveau de chaque projet.
1. Proposez un MLD normalisé pour ce cube, et donner les formules des mesures.
2. Donnez le script d’une fragmentation horizontale par intervalles de la table de fait en vous basant sur un attribut au choix (3 fragments).
3. Expliquez à travers un exemple comment cette technique optimise le temps de réponse.
D) Donnez un exemple de décisions qui peut être prise suite à l’analyse de chacun des 2 cubes.
Exercice N°2 (7 pts)
Considérons une table ‘ventes’ contenant l’extension ci-dessous
1. Donnez la requête SQL permettant d’obtenir le résultat ci-dessous :
2. Ecrire une requête qui donne pour chaque magasin, le classement non dense des clients, par rapport au chiffre d’affaires, et donner le résultat de la requête.
3. Donnez le résultat de la requête ci-dessous
Select NumCl, NumP, Sum(CA) as CA, Sum(Sum(CA) ) over(partition by (NumCl)) as TotalM, Ratio_to_report(Sum(CA)) over (partition by (NumCl)) as Ratio From Vente Group by NumCl,NumP Order by Numcl,NUMP;
4. Ecrire une requête qui donne le client le plus rentable de chaque magasin, et donner le résultat de la requête.
BON COURAGE
N. Selmoune
Exercice 1 :
A) voir cours 1 (1 pt), 2 (1 pt)
B) 1 (3,5 pts )
Fait : (CodeDate, CodeMod, CodeEmpDev, Cou_Dev, NbTestConcluants, NBTest, TauxTestConcluant)
Dim1 Module (CodeMod, NomMod, CodeApp, NomApp,CodeProjet, NomProjet,CodeManager) (hierarchie 1 Module🡪 application🡪 projet🡪 manager ; hierarchie 2 Module🡪 application🡪 typeApp)
Dim2 Developpeur (CodeEmpDev, NomEmp, CodeEq, NomEq), hiérarchie (Développeur🡪équipe)
Dim 3 Temps voir cours
Formule CouDev= (nbheures*tarifhor)+ Somme(nbheure*coûtHor)
B2 1.5 pt voir TP
B3 1 pt voir TP
B4 1 pt voir TP
C1 2 pts
Fait : (CodeDate, CodeProjet, CodeEmpDevSen, Cout_Projet)
Dim1 projet
Dim2 Developpeur_Senior
Dim 3 Temps
Formule CouDev= somme(somme (nbheure*tarifhor) pour l’ens des outils) ens desdev+ somme tarif dev chef
C2 1 pt voir TP
C3 0.5 pts voir TP
D) 0.5 pt voir TP
Exercice 2
1 ) 1.5 pt
Select decode(grouping(NUMCL),1,'TOTALC',NUMCL), decode(grouping(Magasin),1,'TOTALM',Magasin),Sum(CA) from vente group by Rollup (NUMCL,Magasin);
2) 1.5+0.5
select Magasin, NUMCL, sum(CA), Rank() over(partition by (Magasin) order by (Sum (CA)) desc) as rang from vente group by Magasin , NUMCL;
3) 1.5
Select NumCl, NumP, Sum(CA) as CA, Sum(Sum(CA) ) over(partition by (NumCl)) as TotalM, Ratio_to_report(Sum(CA)) over (partition by (NumCl)) as Ratio From Vente Group by NumCl,NumP order by Numcl,NUMP;
4) 1.5+0.5
select Magasin, NumCL from ( select Magasin, NUMCL, sum(CA), Rank() over(partition by (Magasin) order by (Sum (CA)) desc) as rang from vente group by Magasin , NUMCL ) where rang=1;
N. Selmoune
FAQ
1. Qu'est-ce qu'un entrepôt de données ?
Un entrepôt de données est une base de données centralisée qui stocke des données intégrées provenant de différentes sources. Il est conçu pour faciliter l'analyse et la prise de décision en fournissant une vue consolidée des données de l'entreprise.
2. Quels sont les avantages de la méthode de Ralph Kimball ?
La méthode de Ralph Kimball offre plusieurs avantages, notamment la rapidité de mise en œuvre, la flexibilité pour répondre aux besoins des utilisateurs finaux, et la capacité à gérer des données historiques et actuelles de manière efficace.
3. Comment optimiser les temps d'accès dans un entrepôt de données ?
Les temps d'accès peuvent être optimisés en utilisant des techniques telles que la fragmentation horizontale, les vues matérialisées, et l'indexation appropriée des tables de fait et des dimensions.