Data Science - S4 Entrepots de Donnees
Correction Complete TP ROLAP
Correction TP
1 Mise en place de l’environnement et du schema
1.1 Creation de l’utilisateur (tped)
1 CREATE USER tped IDENTIFIED BY 123
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp
4 QUOTA 50 M ON users ;
5
6 GRANT CONNECT , RESOURCE , CREATE VIEW TO tped ;
Listing 1 – Creation de l’utilisateur tped et octroi des droits
1.2 Creation des tables
On cree les tables de dimensions (D_TEMPS, D_PRODUIT) et la table de faits (F_VENTES) conformement
au schema defini, en definissant les cles primaires et etrangeres.
1 CREATE TABLE D_TEMPS (
2 id_temps NUMBER PRIMARY KEY ,
3 jour VARCHAR2 (10) ,
4 mois VARCHAR2 (10) ,
5 annee NUMBER
6 );
7
8 CREATE TABLE D_PRODUIT (
9 id_produit NUMBER PRIMARY KEY ,
10 nom_produit VARCHAR2 (50) ,
11 categorie VARCHAR2 (30)
12 );
13
14 CREATE TABLE F_VENTES (
15 id_vente NUMBER PRIMARY KEY ,
16 id_temps NUMBER REFERENCES D_TEMPS ( id_temps ) ,
17 id_produit NUMBER REFERENCES D_PRODUIT ( id_produit ) ,
18 quantite NUMBER ,
19 montant NUMBER
20 );
Listing 2 – Creation des tables de dimensions et de faits
1.3 Insertion des donnees
Les tables sont ensuite peuplees avec les donnees fournies dans l’enonce (avec remplacement des
accents).
1 INSERT INTO D_TEMPS VALUES (1 , ’ 01 ’ , ’ Janvier ’ , 2023) ;
2 INSERT INTO D_TEMPS VALUES (2 , ’ 05 ’ , ’ Fevrier ’ , 2023) ;
3 INSERT INTO D_TEMPS VALUES (3 , ’ 12 ’ , ’ Mars ’ , 2023) ;
4 INSERT INTO D_TEMPS VALUES (4 , ’ 18 ’ , ’ Avril ’ , 2023) ;
5 INSERT INTO D_TEMPS VALUES (5 , ’ 22 ’ , ’ Mai ’ , 2023) ;
6 INSERT INTO D_TEMPS VALUES (6 , ’ 30 ’ , ’ Juin ’ , 2023) ;
Page 1
Data Science - S4 Entrepots de Donnees
7 INSERT INTO D_TEMPS VALUES (7 , ’ 04 ’ , ’ Juillet ’ , 2023) ;
8 INSERT INTO D_TEMPS VALUES (8 , ’ 10 ’ , ’ Aout ’ , 2023) ;
9 INSERT INTO D_TEMPS VALUES (9 , ’ 14 ’ , ’ Septembre ’ , 2023) ;
10 INSERT INTO D_TEMPS VALUES (10 , ’ 19 ’ , ’ Octobre ’ , 2023) ;
11 INSERT INTO D_TEMPS VALUES (11 , ’ 23 ’ , ’ Novembre ’ , 2023) ;
12 INSERT INTO D_TEMPS VALUES (12 , ’ 28 ’ , ’ Decembre ’ , 2023) ;
13 INSERT INTO D_TEMPS VALUES (13 , ’ 03 ’ , ’ Janvier ’ , 2024) ;
14 INSERT INTO D_TEMPS VALUES (14 , ’ 08 ’ , ’ Fevrier ’ , 2024) ;
15 INSERT INTO D_TEMPS VALUES (15 , ’ 15 ’ , ’ Mars ’ , 2024) ;
16 INSERT INTO D_TEMPS VALUES (16 , ’ 20 ’ , ’ Avril ’ , 2024) ;
17 INSERT INTO D_TEMPS VALUES (17 , ’ 26 ’ , ’ Mai ’ , 2024) ;
18 INSERT INTO D_TEMPS VALUES (18 , ’ 01 ’ , ’ Juin ’ , 2024) ;
19 INSERT INTO D_TEMPS VALUES (19 , ’ 06 ’ , ’ Juillet ’ , 2024) ;
20 INSERT INTO D_TEMPS VALUES (20 , ’ 11 ’ , ’ Aout ’ , 2024) ;
21
22 INSERT INTO D_PRODUIT VALUES (1 , ’ Ordinateur Portable ’ , ’ Informatique ’) ;
23 INSERT INTO D_PRODUIT VALUES (2 , ’ Cle USB ’ , ’ Accessoire ’) ;
24 INSERT INTO D_PRODUIT VALUES (3 , ’ Imprimante Laser ’ , ’ Electronique ’) ;
25 INSERT INTO D_PRODUIT VALUES (4 , ’ Ecran 24 pouces ’ , ’ Informatique ’) ;
26 INSERT INTO D_PRODUIT VALUES (5 , ’ Souris sans fil ’ , ’ Accessoire ’) ;
27 INSERT INTO D_PRODUIT VALUES (6 , ’ Clavier mecanique ’ , ’ Accessoire ’) ;
28 INSERT INTO D_PRODUIT VALUES (7 , ’ Disque dur 1 To ’ , ’ Stockage ’) ;
29 INSERT INTO D_PRODUIT VALUES (8 , ’ Tablette Android ’ , ’ Electronique ’) ;
30 INSERT INTO D_PRODUIT VALUES (9 , ’ Routeur WiFi ’ , ’ Reseau ’) ;
31 INSERT INTO D_PRODUIT VALUES (10 , ’ Scanner A4 ’ , ’ Electronique ’) ;
32 INSERT INTO D_PRODUIT VALUES (11 , ’ Webcam HD ’ , ’ Accessoire ’) ;
33 INSERT INTO D_PRODUIT VALUES (12 , ’ Microphone USB ’ , ’ Accessoire ’) ;
34 INSERT INTO D_PRODUIT VALUES (13 , ’ Switch reseau ’ , ’ Reseau ’) ;
35 INSERT INTO D_PRODUIT VALUES (14 , ’ Camera de securite ’ , ’ Electronique ’) ;
36 INSERT INTO D_PRODUIT VALUES (15 , ’ Batterie externe ’ , ’ Accessoire ’) ;
37 INSERT INTO D_PRODUIT VALUES (16 , ’ Projecteur LED ’ , ’ Multimedia ’) ;
38 INSERT INTO D_PRODUIT VALUES (17 , ’ Smartphone ’ , ’ Electronique ’) ;
39 INSERT INTO D_PRODUIT VALUES (18 , ’ Casque audio ’ , ’ Accessoire ’) ;
40 INSERT INTO D_PRODUIT VALUES (19 , ’ Lecteur DVD ’ , ’ Multimedia ’) ;
41 INSERT INTO D_PRODUIT VALUES (20 , ’ Onduleur ’ , ’ Energie ’) ;
42
43 INSERT INTO F_VENTES VALUES (1 , 1 , 1 , 5 , 5000) ;
44 INSERT INTO F_VENTES VALUES (2 , 2 , 2 , 10 , 1000) ;
45 INSERT INTO F_VENTES VALUES (3 , 3 , 3 , 2 , 800) ;
46 INSERT INTO F_VENTES VALUES (4 , 4 , 4 , 4 , 1200) ;
47 INSERT INTO F_VENTES VALUES (5 , 5 , 5 , 6 , 300) ;
48 INSERT INTO F_VENTES VALUES (6 , 6 , 6 , 3 , 450) ;
49 INSERT INTO F_VENTES VALUES (7 , 7 , 7 , 2 , 1600) ;
50 INSERT INTO F_VENTES VALUES (8 , 8 , 8 , 1 , 900) ;
51 INSERT INTO F_VENTES VALUES (9 , 9 , 9 , 3 , 750) ;
52 INSERT INTO F_VENTES VALUES (10 , 10 , 10 , 2 , 1100) ;
53 INSERT INTO F_VENTES VALUES (11 , 11 , 11 , 5 , 650) ;
54 INSERT INTO F_VENTES VALUES (12 , 12 , 12 , 4 , 560) ;
55 INSERT INTO F_VENTES VALUES (13 , 13 , 13 , 1 , 400) ;
56 INSERT INTO F_VENTES VALUES (14 , 14 , 14 , 2 , 1300) ;
57 INSERT INTO F_VENTES VALUES (15 , 15 , 15 , 3 , 900) ;
58 INSERT INTO F_VENTES VALUES (16 , 16 , 16 , 1 , 1800) ;
59 INSERT INTO F_VENTES VALUES (17 , 17 , 17 , 5 , 3500) ;
60 INSERT INTO F_VENTES VALUES (18 , 18 , 18 , 6 , 1200) ;
61 INSERT INTO F_VENTES VALUES (19 , 19 , 19 , 2 , 700) ;
62 INSERT INTO F_VENTES VALUES (20 , 20 , 20 , 3 , 1500) ;
63
64 COMMIT ;
Listing 3 – Insertion des donnees dans les tables
Page 2
Data Science - S4 Entrepots de Donnees
2 Requetes OLAP et d’Analyse
Les requetes suivantes permettent d’extraire et d’agreger les donnees pour l’analyse OLAP, ainsi que
d’effectuer des requetes d’analyse plus specifiques en utilisant les extensions SQL.
2.1 Requetes d’Agregation de Base
1. Total des ventes par annee
1 SELECT
2 dt . annee ,
3 SUM ( fv . montant ) AS total_ventes
4 FROM
5 F_VENTES fv ,
6 D_TEMPS dt
7 WHERE
8 fv . id_temps = dt . id_temps
9 GROUP BY
10 dt . annee
11 ORDER BY
12 dt . annee ;
Listing 4 – Requete - Total des ventes par annee
2. Total des ventes par produit
1 SELECT
2 dp . nom_produit ,
3 SUM ( fv . montant ) AS total_ventes
4 FROM
5 F_VENTES fv ,
6 D_PRODUIT dp
7 WHERE
8 fv . id_produit = dp . id_produit
9 GROUP BY
10 dp . nom_produit
11 ORDER BY
12 dp . nom_produit ;
Listing 5 – Requete - Total des ventes par produit
3. Total des quantites vendues par categorie de produit
1 SELECT
2 dp . categorie ,
3 SUM ( fv . quantite ) AS total_quantite
4 FROM
5 F_VENTES fv ,
6 D_PRODUIT dp
7 WHERE
8 fv . id_produit = dp . id_produit
9 GROUP BY
10 dp . categorie
11 ORDER BY
12 dp . categorie ;
Listing 6 – Requete - Total des quantites vendues par categorie
4. Total des ventes par mois et par annee
1 SELECT
2 dt . annee ,
3 dt . mois ,
4 SUM ( fv . montant ) AS total_ventes
5 FROM
Page 3
Data Science - S4 Entrepots de Donnees
6 F_VENTES fv ,
7 D_TEMPS dt
8 WHERE
9 fv . id_temps = dt . id_temps
10 GROUP BY
11 dt . annee ,
12 dt . mois
13 ORDER BY
14 dt . annee ,
15 dt . mois ;
Listing 7 – Requete - Total des ventes par mois et par annee
2.2 Requetes avec ROLLUP, CUBE, GROUPING SETS
5. Montant total des ventes par produit et par annee (avec ROLLUP)
1 SELECT
2 dp . nom_produit ,
3 dt . annee ,
4 SUM ( fv . montant ) AS total_ventes
5 FROM
6 F_VENTES fv ,
7 D_PRODUIT dp ,
8 D_TEMPS dt
9 WHERE
10 fv . id_produit = dp . id_produit AND fv . id_temps = dt . id_temps
11 GROUP BY ROLLUP ( dp . nom_produit , dt . annee )
12 ORDER BY dp . nom_produit , dt . annee ;
Listing 8 – Requete - Total des ventes par produit et annee (ROLLUP)
6. Montant total des ventes par categorie avec sous-total par annee (CUBE)
1 SELECT
2 dp . categorie ,
3 dt . annee ,
4 SUM ( fv . montant ) AS total_ventes
5 FROM
6 F_VENTES fv ,
7 D_PRODUIT dp ,
8 D_TEMPS dt
9 WHERE
10 fv . id_produit = dp . id_produit AND fv . id_temps = dt . id_temps
11 GROUP BY CUBE ( dp . categorie , dt . annee )
12 ORDER BY dp . categorie , dt . annee ;
Listing 9 – Requete - Total des ventes par categorie et annee (CUBE)
7. Montant total des ventes selon differents regroupements (GROUPING SETS)
1 SELECT
2 dt . annee ,
3 dp . categorie ,
4 dp . nom_produit ,
5 SUM ( fv . montant ) AS total_ventes
6 FROM
7 F_VENTES fv ,
8 D_PRODUIT dp ,
9 D_TEMPS dt
10 WHERE
11 fv . id_produit = dp . id_produit AND fv . id_temps = dt . id_temps
12 GROUP BY GROUPING SETS (
13 ( dt . annee , dp . categorie ) ,
Page 4
Data Science - S4 Entrepots de Donnees
14 ( dt . annee , dp . nom_produit ) ,
15 ()
16 );
Listing 10 – Requete - Total des ventes selon differents regroupements (GROUPING SETS)
8. Total des ventes par mois et produit + sous-totaux par mois
1 SELECT
2 dt . mois ,
3 dp . nom_produit ,
4 SUM ( fv . montant ) AS total_ventes
5 FROM
6 F_VENTES fv ,
7 D_PRODUIT dp ,
8 D_TEMPS dt
9 WHERE
10 fv . id_produit = dp . id_produit AND fv . id_temps = dt . id_temps
11 GROUP BY GROUPING SETS (
12 ( dt . mois , dp . nom_produit ) ,
13 ( dt . mois )
14 );
Listing 11 – Requete - Total par mois et produit + sous-totaux par mois (GROUPING SETS)
9. Donner toutes les combinaisons possibles (mois, produit)
1 SELECT
2 dt . mois ,
3 dp . nom_produit ,
4 SUM ( fv . montant ) AS total_ventes
5 FROM
6 F_VENTES fv ,
7 D_PRODUIT dp ,
8 D_TEMPS dt
9 WHERE
10 fv . id_produit = dp . id_produit AND fv . id_temps = dt . id_temps
11 GROUP BY CUBE ( dt . mois , dp . nom_produit )
12 ORDER BY dt . mois , dp . nom_produit ;
Listing 12 – Requete - Toutes les combinaisons possibles (mois, produit) (CUBE)
2.3 Requetes utilisant les Fonctions de Fenetre et les Predicats Quantifies
10. Quels sont les produits appartenant au quart des produits les plus vendus (en termes de nombre
de ventes) ?
1 SELECT
2 produits_classes . nom_produit
3 FROM (
4 SELECT
5 dp . nom_produit ,
6 NTILE (4) OVER ( ORDER BY SUM ( fv . quantite ) DESC ) AS quartile_quantite
7 FROM
8 F_VENTES fv ,
9 D_PRODUIT dp
10 WHERE
11 fv . id_produit = dp . id_produit
12 GROUP BY
13 dp . nom_produit
14 ) produits_classes
15 WHERE
16 produits_classes . quartile_quantite = 1
17 ORDER BY
Page 5
Data Science - S4 Entrepots de Donnees
18 produits_classes . nom_produit ;
Listing 13 – Requete - Produits au quart des plus vendus (NTILE)
11. Classer tous les produits par nombre total de ventes (du plus vendu au moins vendu)
1 SELECT
2 dp . nom_produit ,
3 SUM ( fv . montant ) AS total_ventes ,
4 RANK () OVER ( ORDER BY SUM ( fv . montant ) DESC ) AS classement_ventes
5 FROM
6 F_VENTES fv ,
7 D_PRODUIT dp
8 WHERE
9 fv . id_produit = dp . id_produit
10 GROUP BY
11 dp . nom_produit
12 ORDER BY
13 classement_ventes ;
Listing 14 – Requete - Classement des produits par total des ventes (RANK)
12. Quelles ventes ont un montant superieur a au moins une vente du produit « Cle USB » ?
1 SELECT
2 fv .*
3 FROM
4 F_VENTES fv
5 WHERE
6 fv . montant >= ANY (
7 SELECT
8 fv_usb . montant
9 FROM
10 F_VENTES fv_usb ,
11 D_PRODUIT dp_usb
12 WHERE
13 fv_usb . id_produit = dp_usb . id_produit
14 AND dp_usb . nom_produit = ’ Cle USB ’
15 );
Listing 15 – Requete - Ventes superieures a une vente "Cle USB" (ANY)
13. Trouver les produits dont le prix est superieur a au moins un produit de la categorie " Informatique"
1 SELECT
2 dp1 . nom_produit
3 FROM
4 D_PRODUIT dp1 ,
5 F_VENTES fv1
6 WHERE
7 fv1 . id_produit = dp1 . id_produit
8 AND fv1 . montant >= ANY (
9 SELECT
10 fv2 . montant
11 FROM
12 F_VENTES fv2 ,
13 D_PRODUIT dp2
14 WHERE
15 fv2 . id_produit = dp2 . id_produit
16 AND dp2 . categorie = ’ Informatique ’
17 );
Listing 16 – Requete - Produits dont une vente est superieure a une vente "Informatique" (ANY sans
DISTINCT)
Page 6