Entreposage de Donn

1
Entreposage de Données et Aide à la
Décision

• Chapitre 25, 25.1 25.7

2
Objectifs

• Entreposage de données OLAP vs OLTP
• Modèle de données multidimensionnelles
• Requêtes OLAP
• Design des données multidimensionnelles
• Techniques dimplémentation
• Vues et aide à la décision
• Matérialisation des vues
• Gestion et maintien des vues matérialisées

3
Introduction

• De plus en plus, les organisations analysent les
  données courantes et historiques afin
  didentifier des patrons utiles et de supporter
  les stratégies daffaires.
• Laccent est mis sur une analyse complexe,
  interactive et exploratoire de très larges
  ensembles de données créées par intégration des
  données provenant de toutes les parties de
  lentreprise. Ces données intégrées sont
  statiques.
• Cette analyse est appelée traitement analytique
  en ligne (On-Line Analytic Processing -- OLAP)
  par opposition au traitement transactionnel en
  ligne (On-line Transaction Processing OLTP) qui
  est la manière traditionnelle de procéder. OLAP
  est souvent fait de longues requêtes, tandis que
  OLTP consiste en de courtes transactions de
  changement.

4
Trois Approches Complémentaires

• Entreposage de données (Data Warehousing)
  Consolidation de données provenant de plusieurs
  sources dans un large dépôt.
• Chargement, synchronisation périodique des
  copies.
• Intégration sémantique.
• Traitement analytique en ligne (OLAP)
• Requêtes et vues complexes en SQL.
• Requêtes basées sur des opérations du genre de
  celles exécutées sur des tableurs vues
  multidimensionnel des données.
• Requêtes interactive et en ligne..
• Exploration des données (Data Mining) Recherche
  exploratoire dans les données afin de trouver des
  tendances intéressantes et des anomalies.

5
Entreposage de Données
SOURCES DE DONNEES EXTERNES

• Entrepôt de données
• Données intégrées couvrant de longues périodes de
  temps, souvent augmentées de résumés dinfos.
• Plusieurs gigaoctets à téraoctets.
• Réponse interactive à des requêtes ad-hoc
  complexes des changements ad-hoc sont très peu
  courantes.

EXTRAIRE TRANSFORMER CHARGER
RAFRAICHIR
ENTREPOT DE DONNEES
DEPOT DE METADONNEES
SUPPORTE
EXPLORATION DES DONNEES
6
Entreposage Problématiques

• Intégration sémantique On doit éliminer les
  mauvais appariements lors du chargement des
  données provenant de plusieurs sources p.ex.
  différentes monnaies, différents schémas, etc.
• Sources hétérogènes on doit pouvoir avoir accès
  aux données provenant dune variété de formats et
  dépôts.
• Charger, Rafraichir et Purger on doit pouvoir
  charger les données dans lentrepôt, les
  rafraichir périodiquement et purger les données
  périmées.
• Gestion des métadonnées on doit maintenir les
  infos sur les sources, le temps de chargement
  ainsi que dautres paramètres sur les données
  stockées dans lentrepôt.

7
Modèle de Données Multidimensionnelles
Table de faits
timeid
locid
sales
pid

• Collection de mesures numériques qui dépendent
  dun ensemble de dimensions (représentée comme un
  tableau à n dimensions ou comme une relation.)
• P.ex. la mesure Sales dépend des dimensions
  Product (pid), Location (locid) et Time (timeid).

La tranche locid1 est montrée (orthogonal à
laxe des locids )
locid
8
MOLAP vs ROLAP

• Systèmes MOLAP Les données multidimensionnelles
  sont stockées physiquement dans un tableau à n
  dimensions sur disque.
• Systèmes ROLAP Les données multidimensionnelles
  sont stockées physiquement comme une relation.
• La relation principale qui relie les dimensions à
  une mesure est appelée table de faits (fact
  table).
• Chaque dimension peut aussi avoir des attributs
  additionnels et être ainsi associée avec une
  table de dimensions (dimension table).
• P.ex. Products(pid, pname, category, price)
• Les tables de faits sont beaucoup plus grandes
  que les tables de dimensions.

9
Hiérarchies dune Dimension

• Lensemble des valeurs (des attributs) de chaque
  dimension peut être organisé en une hiérarchie
• Products(pid, pname, category, price)
• Locations(locid,city,state,country)
• Times(timeid,date,week,month,quarter,year,holiday_
  flag)

PRODUCT
TIME
LOCATION
year
quarter country
category week month
state
pname date
city
10
Requêtes OLAP

• Influencées par SQL et par les tableurs
  (intuitives).
• Une opération courante agrégat dune mesure sur
  une ou plusieurs de ses dimensions.
• Trouver les ventes totales.
• Trouver les ventes totales pour chaque cité ou
  pour chaque état.
• Trouver les 5 produits les plus vendus par ordre
  de ventes totales.
• Zoom arrière (roll-up) Agrégation à différents
  niveaux de la hiérarchie dune dimension.
• P.ex. Étant données les ventes totales par cité,
  nous pouvons faire un roll-up pour obtenir les
  ventes totales par état.

11
Requêtes OLAP (Suite)

• Zoom avant (drill-down) Linverse de roll-up.
• P.ex. Étant données les ventes totales par état,
  nous pouvons faire un drill-down pour obtenir les
  ventes totales par cité.
• P.ex. Lon peut aussi faire un drill-down sur la
  dimension Product pour obtenir les ventes totales
  par produit pour chaque état.
• Pivotement (pivoting) Agrégation sur des
  dimensions sélectionnées.
• P.ex. Pivoter sur Location et Time
• résulte en une tabulation recoupée
• (cross-tabulation) des ventes totales
• pour chaque lieu et temps

WI CA Total
63 81 144
1995
38 107 145
1996

• Tranchage / Découpage en dés
• (Slicing / Dicing) égalité / plages
• de valeurs sur une ou plusieurs dimensions.

75 35 110
1997
176 223 339
Total
12
Comparaison avec des Requêtes SQL

• La tabulation recoupée obtenue par pivotement
  peut aussi être obtenue en utilisant une
  collection de requêtes SQL (Une 4ème requête
  calcule la somme totale finale)

SELECT SUM(S.sales) FROM Sales S, Times T,
Locations L WHERE S.timeidT.timeid AND
S.timeidL.timeid GROUP BY T.year, L.state
SELECT SUM(S.sales) FROM Sales S, Times
T WHERE S.timeidT.timeid GROUP BY T.year
SELECT SUM(S.sales) FROM Sales S, Location
L WHERE S.timeidL.timeid GROUP BY L.state
13
Opérateurs CUBE et ROLLUP

• Lexemple précédent nous montre toute la
  complexité dobtenir le même résultat avec des
  requêtes SQL!
• En général, sil y a k dimensions, un nombre 2k
  de requêtes SQL utilisant GROUP BY sera
  nécessaire afin dobtenir un résultat équivalent
  au pivotement.
• SQL1999 étend la clause GROUP BY afin de
  supporter les opérations de roll-up et de
  tabulation recoupée (pivotement)
• GROUP BY CUBE équivaut à une collection de
  commandes GROUP BY avec une commande GROUP BY
  pour chaque sous ensemble des k dimensions.
• GROUP BY ROLLUP donne le même résultat que
  GROUP BY CUBE, exceptés les tuples avec des
  valeurs null ne sont pas générés.

14
Opérateurs CUBE et ROLLUP (Suite)

• CUBE pid, locid, timeid BY SUM Sales
• Équivalent à un roll-up de Sales sur tous les 8
  sous-ensembles de lensemble pid, locid,
  timeid chacun de ces roll-ups correspond à une
  requête SQL de la forme suivante

SELECT SUM(S.sales) FROM Sales S GROUP BY
grouping-list

• grouping-list est lun des 8 sous-ensembles de
  lensemble pid, locid, timeid qui forme un
  treillis (lattice)

pid,lid,tid pid,lid
pid,tid lid,tid pid
lid tid
15
Design des Données Multidimensionnelles
TIMES
holiday_flag
week
date
timeid
month
quarter
year
(Fact table)
sales
locid
timeid
pid
SALES
PRODUCTS
LOCATIONS
price
category
pname
pid
country
state
city
locid

• La table de faits est en BCNF les tables de
  dimensions ne sont pas normalisées.
• Les tables de dimensions sont petites les
  opérations update/insert/delete sont rares. Les
  anomalies sont moins importantes que la
  performance des requêtes.
• Ce genre de schéma, appelé schéma en étoile
  (star schema), est très usuel dans les
  applications OLAP. Loperation de join pour ce
  genre de schéma est appelée join en étoile (star
  join).

16
Techniques dImplémentation

• De nouvelles techniques (tenant compte du
  statisme de lenvironnement OLAP) indexes
  binaires (bitmap index), indexes de join,
  représentations tabulaires, compression, calcul
  des agrégations à lavance, etc.
• Exemple dindexes binaires sur
• Customers(custid, name, gender, rating)
• 10 M 01 F 10000 rating 1 00100 rating
  3 etc.

gender custid name gender rating
rating
vecteur de bits 1 bit pour chaque valeur
possible. Bien de requêtes peuvent être
traitées en utilisant des operateurs sur des
vecteurs de bits
F
M
17
Indexes à Join pour Accélérer les Joins à Etoile

• Supposons que lon veuille faire le join de
  Sales, Products, Times et Locations, avec des
  conditions de sélection additionnelles selon les
  cas (p.ex. countryUSA).
• Un index à join peut être construit afin
  daccélérer ce genre de joins. Lindex contient
  le tuple s,p,t,l sil y a des tuples (avec le
  sid) s dans Sales, p dans Products, t dans Times
  et l dans Locations qui satisfont les conditions
  de joins et de selection.
• Problème Le nombre (et la taille) dindexes à
  join peut exploser rapidement.
• Une variante offre une solution à ce problème
  pour chaque colonne ayant une sélection
  additionnelle (p.ex., country), construire un
  index avec c,s sur cette colonne si un tuple
  dune table de dimension avec valeur c
  correspond à un tuple s de Sales. Ce nouvel index
  peut être un index binaire !

18
Index à Join Binaire
TIMES
quarter
holiday_flag
week
date
timeid
month
year
(table de faits)
sales
locid
timeid
pid
SALES
PRODUCTS
LOCATIONS
price
category
pname
pid
country
state
city
locid

• Supposez une requête avec les conditions price10
  et countryUSA. Supposez quun tuple (avec
  sid) s dans Sales corresponde (via le join) à un
  tuple p tel que price10 et un tuple l tel que
  country USA. On aura deux indexes à join un
  contenant 10,s et lautre USA,s.
• Lintersection de ces deux indexes nous donnera
  les tuples de Sales qui sont dans le résultat du
  join et satisfont la condition de sélection.

19
Recherche des Séquences en SQL1999

• Lanalyse des tendances est difficile à faire en
  SQL-92
• Trouver le de changement dans les ventes
  mensuels
• Trouver les 5 produits les plus vendus p.r.
  ventes totales
• Pour chaque jour, calculer la moyenne des ventes
  journalières des n jours le précédant.
• Les deux premières requêtes peuvent être
  exprimées avec difficulté mais la 3ème ne peut
  pas être exprimée en SQL-92 si n est un
  paramètre de la requête.
• La clause WINDOW en SQL1999 permet décrire les
  requêtes ci-dessus en considérant implicitement
  une relation comme une séquence.
• Intuitivement, WINDOWS identifie une fenêtre de
  lignes autour de chaque tuple dune table.

20
La Claude WINDOW
SELECT L.state, T.month, AVG(S.sales) OVER W AS
movavg FROM Sales S, Times T, Locations L WHERE
S.timeidT.timeid AND S.locidL.locid WINDOW W AS
(PARTITION BY L.state ORDER BY T.month RANGE
BETWEEN INTERVAL 1 MONTH PRECEDING AND
INTERVAL 1 MONTH FOLLOWING)

• Supposez que le résultat des clauses FROM et
  WHERE soit Temp.
• Conceptuellement, Temp est partitionné selon la
  clause PARTITION BY. (Similaire à GROUP BY, mais
  la réponse a une ligne pour ligne dans la
  partition, pas seulement une seule ligne par
  partition!)
• Chaque partition est triée selon la clause ORDER
  BY.
• Pour chaque ligne dans la partition, la clause
  WINDOW crée une fenêtre de tuples se trouvant
  aux alentours (juste avant et juste après).
• Utiliser RANGE pour des valeurs
• Utiliser ROWS pour le nombre de tuples à inclure
  dans la fenêtre
• Lopération dagrégat est appliquée la fenêtre
  correspondant à chaque ligne de la partition.

21
Requêtes pour Les N Meilleurs Items

• Si lon cherche les 10 véhicules les moins
  couteux, il serait bon que le SGBD évite de
  calculer les coûts de tous les véhicules avant
  de trier le résultats et enfin déterminer les 10
  véhicules les moins couteux.
• Idée Deviner un coût c tel que les 10 véhicules
  les moins couteux coutent tous en dessous de c et
  le nombre de véhicules coutant moins de c ne soit
  pas trop grand ajouter la condition de sélection
  costltc à la requête et enfin lévaluer.
• Si le choix de c est correct, nous évitons des
  calculs pour des véhicules qui coutent plus que
  c.
• Sil est incorrect, lon recommence.

22
Top N Queries
SELECT P.pid, P.pname, S.sales FROM Sales S,
Products P WHERE S.pidP.pid AND S.locid1 AND
S.timeid3 ORDER BY S.sales DESC OPTIMIZE FOR 10
ROWS
SELECT P.pid, P.pname, S.sales FROM Sales S,
Products P WHERE S.pidP.pid AND S.locid1 AND
S.timeid3 AND S.sales gt c ORDER BY S.sales DESC

• La clause OPTIMIZE FOR nest pas encore dans le
  standard SQL1999!
• La valeur c est choisie par loptimisateur.

23
Agrégation en Ligne

• Supposez une requête dagrégat Trouver la
  moyenne des ventes par état.
• Peut-on déjà donner de linfo préliminaire à
  lutilisateur avant que la moyenne exacte ne soit
  calculée pour tous les états?
• Montrer les moyennes courantes pour chaque état
  pendant que le calcul se poursuit.
• Mieux encore, des techniques statistiques et de
  léchantillonnage des tuples à agréger (en lieu
  et place dun scan de toute la table) permettent
  de fournir des bornes du genre la moyenne pour
  Wisconsin est entre 2,800.20 et 2,963.60 avec
  une probabilité de 95.
• De tels algorithmes doivent être non bloquants.

24
Résumé

• Laide à la décision suppose la création de
  larges dépôts de données consolidées appelés
  entrepôts de données (data warehouses).
• Les entrepôts de données sont utilisés au moyen
  de techniques danalyse sophistiquées requêtes
  SQL complexes et requêtes OLAP pour données
  multidimensionnelles.
• De nouvelles techniques sont utilisées pour le
  design des bases de données, lindexage et les
  requêtes interactives.