Entreposage de Donn - PowerPoint PPT Presentation

About This Presentation
Title:

Entreposage de Donn

Description:

Title: Data Warehousing and Decision Support Author: Raghu Ramakrishnan and Johannes Gehrke Description: Chapter 25 Last modified by: Samer Sader Created Date – PowerPoint PPT presentation

Number of Views:81
Avg rating:3.0/5.0
Slides: 25
Provided by: RaghuRa80
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com