Cube

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 : 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 le cube C1 représentant la somme des ventes d'articles commandés, selon 3 dimensions :

• dimension x : les produits
• dimension y : les fournisseurs
• dimension z : la date de commande

Les niveaux de la hiérarchie des produits sont : nom et type (plusieurs produits par type).
Les niveaux de la hiérarchie des fournisseurs sont : nom, pays, continent
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 fournisseur et jour).

Question 1 : Opérations algébriques

On considère le cube C2 obtenu à partir de C1 tel que :

• On s'intéresse seulement aux ventes de produits en cuivre (dont le type se termine par COPPER) effectuées avant le 1er janvier 1993.
• On considère seulement le niveau type de la dimensions produit et le niveau nom de la dimension fournisseur.

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 dont le résultat contient toutes les valeurs de C2.

Question 2 : Navigation

On suppose qu'une application graphique permet de naviguer, à partir de C2, au travers des différents niveaux hiérarchiques de la dimension fournisseurs. Pour que la navigation soit fluide, l'application pré-calcule (en SQL) toutes les données du cube, rencontrées lors de cette navigation.

a) Exprimer en SQL la requête posée par l'application. Le résultat de la requête doit contenir toutes les données du cube, à tous les niveaux de la dimension fournisseurs. Utiliser le mot clé rollup

b) Idem sans utiliser ni rollup ni cube, mais union.

c) Idem en utilisant cube. Obtenez-vous un résultat équivalent ? Décrire précisément la différence de résultat entre les requêtes a) et c)

Question 3: Présentation du résultat

a) On considère la requête 2.a). Compléter la projection (clause select) pour marquer chaque tuple avec le niveau de hiérarchie auquel il se situe. Afin de repérer le sous-total pour un type de produit, ajouter des attributs nommés STF, STP et STR, TM :

• STF : Sous Total des ventes par Fournisseur 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

Utiliser la fonction grouping()

b) 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).

Question 4: Cube en 3D avec dimension temporelle

On veut calculer les données de C1 et celles de tous les cubes obtenus par navigation sur les 3 dimensions : produit, fournisseur, 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) Expliquer comment calculer toutes les données souhaitées en utilisant Date_commande.

Question 5: Interrogation d'un cube

a) Définir en SQL une vue représentant C2 et les cubes obtenus par navigation sur les 3 niveaux de la dimension fournisseur et les 2 niveaux de la dimension produits.

• Les_cubes (nom_prod, type_prod, fournisseur, pays, continent, montant_ventes)

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 Les_cubes.

c) Les requêtes R1 er R2 sont-elle traitées de manière identique par le SGBD ?

d) Donner une expression simplifiée de R2. Expliquer les transformations algébriques que vous appliquez sur R2 pour la simplifier.

e) Donner un exemple de requête qui aggrège des données de la base TPC-H mais qu'il est impossible de poser sur la vue Les_cubes.

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') et to_char(o_orderdate, 'YY')

• 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