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.
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. 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.
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.
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.
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(),
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 : Sous Total des ventes par Cclient pour un type de produit
- STP : ventes nationales pour un type de produit
- STR : ventes continentales pour un type de produit
- TM : Total des ventes Mondiales : 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.