RecentChanges
|
FindPage
|
LikePages
|
BackLinks
View Source:
Cube
Note:
This page has been locked and cannot be edited.
!!! Cube : manipulation de données multi-dimensionnellles L'objectif de ce TD/TME est de savoir calculer, transformer et interroger un cube en SQL !!Documentation Voir le cours et le livre Data Warehousing, chapitre 20 [SQL for aggregation | https://docs.oracle.com/cd/B19306_01/server.102/b14223/part5.htm]. Consulter les opérateurs rollup, cube et la fonction grouping. Lire également les questions fréquentes ci-dessous. !!Présentation On considère la base de données de TPC-H (voir [TpcH]) Soit les cubes représentant la somme des ventes d'articles commandés, selon 3 dimensions : * dimension ''x'' : les **produits** * dimension ''y'' : les **clients** * dimension ''z'' : la **date de commande** Les niveaux de la hiérarchie des **produits** sont : **nom, type** (plusieurs produits par type). %%% Les niveaux de la hiérarchie des **clients** sont : **nom, pays, continent** (le nom d'un continent est l'atribut r_name dans la table Region)%%% Les niveaux de la hiérarchie de la **date** sont : **jour, mois, année** Le cube C1 contient des données sur les ventes, au niveau **le plus détaillé** de chaque dimension (ie. nom de produit, nom de client et jour). Voir un extrait de [C1 | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/c1-res.txt]. Le cube C1 a 60098 cellules. !!Question 1 : Opérations algébriques On considère le cube C2 à deux dimensions obtenu à partir de C1 tel que : * On s'intéresse seulement aux ventes de produits en cuivre (dont le type se termine par ''COPPER'' : like '%COPPER') effectuées après le 1er juin 1998. * On considère seulement le niveau ''type'' de la dimensions produit et le niveau ''nom'' de la dimension client. Les valeurs des cellules de C2 représentent la somme des ventes quelle que soit la date. a) Quelles sont, d'après le cours, les opérations à appliquer sur C1 pour obtenir C2 ? Donner l'ordre dans lequel les opérations sont appliquées. b) Donner la requête SQL calculant toute les cellules de C2 (264 cellules).Voir un extrait de [C2 | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/c2-res.txt]. !!Question 2 : Agrégation sur les niveaux d'une dimension On veut calculer, en une seule requête T2, tous les cubes obtenus à partir de C2 par agrégation sur la dimension ''client'' (ayant 3 niveaux). Le résultat de la requête contient toutes les données des cubes à tous les niveaux d'agrégation sur la dimension ''client''. a) T2R: Ecrire T1 en utilisant le mot-clé ''ROLLUP''. Voir le résultat de [T2R | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/t2r-res.txt]. b) T2U: Ecrire T1 sans utiliser ''rollup'' mais ''UNION''. !!Question 3: Projection agrégative sur une ou plusieurs dimensions a) En utilisant le mot clé ''CUBE'' écrire la requête T3 calculant tous les cubes obtenus à partir de C1 par projection agrégative sur toutes les combinaisons des dimensions ''client'' (au niveau region r_name), ''produits'' (au niveau du type p_type) et ''date'' au niveau de l'année qu'on obtient avec ''extract(year from o_orderdate)''. Le schéma de T3 est: * __T3__ (p_type, r_name, annee, total_ventes). Expliquer ce que représente le 1er tuple du résultat qui a 3 valeurs NULL. Voir le résultat de [T3 | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/t3-res.txt]. b) Soit la requête R1 donnant le total des ventes effectuées en afrique. * R1: Exprimer cette requête sur le schéma TPC-H directement. * R2: Exprimer une requête équivalente, posée sur la vue ''T3''. c) Les requêtes R1 er R2 sont-elle traitées de manière identique par le SGBD ? d) Donner un exemple de requête calculant un cube qui ne peut pas être obtenu seulement à partir de T3. !!Question 4: Agrégation sur trois dimensions On veut calculer, en une seule requête T4, tous les cubes obtenus à partir de C1 par navigation sur les 3 dimensions : produit, client, date. Pour cela on définit la vue : * __Date_commande__ (orderdate, numMois, numAnnée). L'attribut orderdate correspond au jour de la commande. a) Définir en SQL la vue Date_commande b) Ecrire T4 en utilisant Date_commande. !!Question 5: Sélectionner un cube parmi un ensemble de cubes On a vu qu'une requête utilisant les mots-clés ROLLUP ou CUBE calcule en ensemble de cubes. On veut sélectionner un certain cube contenu dans le résultat d'une requête. a) On considère la requête T2R définie précédemment. En utilisant la fonction grouping(), après avoir lu la [documentation sur grouping()| https://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#i1007434], compléter la projection (clause select) pour marquer chaque tuple avec le niveau de hiérarchie auquel il se situe. b) Afin de repérer le sous-total pour un type de produit, ajouter des attributs nommés STF, STP et STR, TM : * STC : __S__ous __T__otal des ventes par __C__client pour un type de produit * STP : ventes nationales pour un type de produit * STR : ventes continentales pour un type de produit * TM : __T__otal des ventes __M__ondiales : pour un type de produit c) Compléter la requête avec les sous-totaux tous types de produits confondus (sous total par pays et région, et le total mondial). !! Questions fréquentes * Comment obtenir une valeur de type __date__ à partir d'une chaîne de caractères ? ** préciser le format par défaut: alter session set nls_date_format = 'DD/MM/YY'; ** ou appeler la fonction de conversion to_date('01/01/93', 'DD/MM/YY'). Exemple de formats :'YYYY-MM-DD' ou 'YYYY-MM-DD HH24:MI:SS' (en précisant l'heure) * Comment obtenir le mois et l'année d'une __date__ ? ** to_char(o_orderdate, 'MM') to_char(o_orderdate, 'YY') ** extract( month from d) extract( year from d) * L'ordre des attributs du __rollup__ importe--t-il ? ** oui : group by rollup(a, b, c) est différent de group by rollup(c, b, a) * Comment compléter le résultat d'une requête R avec un nouvel attribut A ayant une valeur constante ? ** réponse générale: Effectuer un produit cartésien entre R et une relation T(A) contenant un seul n-uplet. ** réponse spécifique: ajouter directement le nouvel attribut dans la clause select de R: ''select v as A, ... from ...''. La valeur ''v'' peut être ''null''. * Peut-on faire l'__union__ de 2 relations R1 et R2 qui n'ont pas le même schéma ? ** Non, les schémas de R1 et R2 doivent être de type identiques (i.e., type des attributs identiques 2 à 2). * Peut-on faire l'union de sous requêtes qui sont chacunes __triées__ avec un order by ? ** Non, il faut effectuer le tri après l'union, donc pas d' ''ORDER BY'' dans des sous-requêtes combinées par une union. * Peut-on ajouter un nouvel attribut avec une valeur constante dans le résultat d'une requête ? ** Oui, il est possible d'ajouter un attribut avec une valeur constante (par exemple la valeur null) dans la clause ''SELECT''. ---- LesTme, LesCours, [Accueil]