OLAP IED 2006-2007. Sommaire Introduction Opérations typiques Langages Architectures.
-
Upload
colombe-guillemin -
Category
Documents
-
view
105 -
download
0
Transcript of OLAP IED 2006-2007. Sommaire Introduction Opérations typiques Langages Architectures.
OLAP
IED 2006-2007
Sommaire
Introduction
Opérations typiques
Langages
Architectures
Introduction
Contexte
un entrepôt de données offre des données - nombreuses - homogènes - exploitables - multidimensionnelles - consolidées
comment exploiter ces données à des fins d’analyse?
Entrepôts de Données et OLAP
Traditionnellement :
- les requêtes OLTP sont exécutées sur les données sources
- l’entrepôt est mis à jour chaque nuit
- les requêtes OLAP sont exécutées sur les données de l’entrepôt
Exemple
ventes(pièce, date, vendeur, montant)
produits(pièce, modèle, couleur)
vendeurs(nom, ville, département, état, pays)
temps(jour, semaine, mois, trimestre, année)
Hiérarchies
2006
Semaine 40
Trimestre 4
Octobre
03/10/2006
Vis
Cruciforme
Bleu
Etats-unis
CA
San-Jose
Bob
France
95Cerg
yKate
Total Total
Total
Modèle conceptuel
notation de Golfarelli (1998)
Requête typique simple- Dicing et Slicing
SELECT département, mois, AVG(montant) as moyenne
FROM ventes, vendeurs, produitsWHERE ventes.vendeur = vendeurs.nom AND ventes.codeProduit = produits.codeProduit AND couleur = ' noir 'GROUP BY département, moisHAVING moyenne > 5000;
Group by : Dicing
Where : Slicing
Exemple de traitement typique (1)
Exemple de traitement typique (2)
SELECT couleur, SUM(montant)FROM ventes, produitsWHERE ventes.codeProduit = produits.codeProduit AND modèle = 'vis'GROUP BY couleur ;
Les ventes de vis sont plus faibles que prévu...Quelles couleurs sont responsables?
Exemple de traitement typique (2)
Exemple de traitement typique (3)
SELECT couleur, années, SUM(montant)FROM ventes, produits, tempsWHERE ventes.codeProduit = produits.codeProduit AND ventes.date = temps.jour AND modèle = 'vis'GROUP BY couleur, années ;
Quelles années sont responsables?
Exemple de traitement typique (3)
Exemple de traitement typique (4)
SELECT couleur, trimestre, SUM(montant)FROM ventes, produits, tempsWHERE ventes.codeProduit = produits.codeProduit AND ventes.date = temps.jour AND modèle = 'vis'GROUP BY couleur, trimestre ;
Quels trimestres sont responsables?
Exemple de traitement typique (4)
Exemple de traitement typique (5)
SELECT vendeur, somme FROM(
SELECT vendeur, SUM(montant) as sommeFROM ventes, produits, tempsWHERE ventes.codeProduit = produits.codeProduitAND ventes.date = temps.jourAND ventes.vendeur = vendeurs.nomAND modèle = 'vis'GROUP BY trimestre, vendeur)
WHERE trimestre = “jui-sep”;
Quels vendeurs sont responsables?
Exemple de traitement typique (5)
Modèle de stockage de donnéestable de données
écrous est 1999 50clous est 1997 100vis ouest 1998 50
...
...écrous est total 220
...
...écrous total total 390
...
...total total total 1200
ventes pièces régions années quantités
pièce, région, année -> quantité
Modèle de stockage de donnéescube de données
Granularité
Terminologie
cube ventescellule écrous, est, 1997, 100référence écrous, est, 1997mesure 100
dimension lieuniveau régionsmembre/paramètre est
Exemple d’implémentation
Projet T3 de Microsoft, Unisys, EMC, Knosys (2001)
DW7.7 milliards de lignes8 tables de faits1.2 To
construction d’une architecture MOLAP récupération, agrégations, indexations,compression
un cube de 471 Go53 heures (40000 lignes/seconde)
Quelques solutions
Express-server/OLAP OracleDB2 OLAP Server IBMMetacube InformixSQL Server MicrosoftCrystal Business ObjectBusiness Information Warehouse SAPPowerPlay CognosMicroStrategy MicroStrategyEssbase Hyperion
nom éditeur
Opérations typiques
restructuration représentationgranularité niveau de détailensembliste extraction
3 catégories d’opérations, élémentaires
catégorie concerne
Reconstruction
RotationPivotSwitchSplitNestPush
Présentations ne sont pas gérées par SQL
Rotationannée
piècerégion
estoue
stsud
nord
1997
19991998
vis
clous
écrous
Rotation
est 10 10ouest 50 50 50sud 50 60 60nord 60 30 20
vis 60 30 20clous 40 20écrous 10
nord 1999 1998 1997
vis 1999 1998 1997
Pivot
année
pièce
région
est
ouest
sud
nord
1997
19991998
vis
clous
écrous
Pivot
nord 60 30 20sud 50 60 60ouest 50 50 50est 10 10
vis 60 30 20clous 40 20écrous 10
nord 1999 1998 1997
vis 1999 1998 1997
Switch
Switch
vis 50 60 60clous 10écrous 40 20
nord 1999 1998 1997
vis 60 30 20clous 40 20écrous 10
sud 1999 1998 1997
Split, nest, push
Split (régions)Nest (pièces, régions)Push(années)
Split (Régions)
ventes est 1999 1998 1997
écrous 50 70 100 vis 10 10 clous 70 70 100
ventes ouest 1999 1998 1997
écrous 10 30 vis 50 50 50 clous 10 40
ventes sud 1999 1998 1997
écrous 40 20 vis 50 60 60 clous 10
ventes nord 1999 1998 1997
écrous 10 vis 60 30 20 clous 40 20
Nest
ventes nest 1999 1998 1997
est 50 70 100ouest 10 30
écrous nord 10sud 40 20
est 10 10ouest 50 50 50
vis nord 60 30 20sud 50 60 60
est 70 70 100ouest 10 40
clous nord 40 20sud 10
Push
ventes push est ouest nord sud
1999 50 1999 40 écrous 1998 70 1998 10 1998 20
1997 100 1997 30 1997 10
1999 50 1999 60 1999 50 vis 1998 10 1998 50 1998 30 1998 60
1997 10 1997 50 1997 20 1997 60
1999 70 1999 40 clous 1998 70 1998 10 1998 20 1998 10
1997 100 1997 40
Visualisation (1)
Visualisation (2)
Granularité
Roll-up
Drill-down
Groupements
Agrégation
Granularité
Roll-up et Drill-down
Drill-down
Roll-up
Opérations ensemblistes
DicingSlicingCubeSélectionProjection
Manipulations classiques
Extraction des informations
Dicing et SlicingSlicing
Dicing : partition
Cube
CUBE pièce, année, région BY SUM montant
SELECT SUM (montant)FROM VentesGROUP BY grouping-list
Sélection
ventes >=50
(régions = nord ou régions = sud) et(pièces = clous ou pièces = écrous) et(années = 1998 ou années = 1999)
Projection
Tables et Cube de données
ROLAP : Tables de données/agrégations
MOLAP : Cubes de données/agrégations
HOLAP : Tables + Cubes
Extensions de SQL
MDX de Microsoft
ANSI SQL 99
MDX de Microsoft (1)
SELECT dimensions avec 1 relation par axe plusieurs membres
FROM cube de données 1 nom de cube
WHERE dimensions avec 1 tuple 1 seul membre
clause paramètres valeur
MDX de Microsoft (2)
navigation PARENT le parent d’un membreCHILDREN les enfants d’un membreMEMBERS les membres d’un
niveauou d’une dimension
structuration CROSSJOIN imbrication
ranking TOPCOUNT les premiers membres
Exemple MDX (1)
SalesCube à cinq dimensions :
- SalesPerson- Geography (Countries > Regions > States > Cities)- Quarters (Quarters > Months > Days)- Years- Products
- Measures (Sales, PercentChange, BudgetedSales)
Exemple MDX (2)
SELECT CROSSJOIN({Venkatrao, Netz},{USA North.CHILDREN, USA South, Japan})ON COLUMNS,{Qtr1.CHILDREN, Qtr2, Qtr3,
Qtr4.CHILDREN} ON ROWS
FROM SalesCube
WHERE (Sales, [1991], Products.All)
ANSI SQL-99
Ajouts OLAP par rapport à SQL-92 :
- GROUPING SETS : extension de GROUP BY- CUBE, ROLLUP : cas particulier de GROUPING SETS- ranking : extension pour ORDER BY- windowing : cumuls ou moyenne glissante
Supporté notamment par DB2
Exemple –SQL99
SELECT jour, ville, SUM(ventes)FROM c1GROUP BY jour,ville
Jour_1 ville_1 v_11Jour_1 ville_2 v_12Jour_2 ville_1 v_21.........Jour_q ville_p v_qp
C1 jour ville ventes
Cube –SQL99 (1)
calcul de l’UNION de GROUP BY de chaque sous-ensemble des attributs en paramétre
SELECT jour, ville, SUM(ventes)FROM c1GROUP BY CUBE(jour,ville)
génère l’union des groupements suivants{(jour,ville),(jour),(ville),vide }
Cube –SQL99 (2)
Jour_1 ville_1 v_11Jour_1 ville_2 v_12Jour_1 NULL v_1_AllJour_2 ville_1 v_21......Jour_q NULL v_q_ALLNULL ville_1 v_ALL_1.........NULL ville_p v_ALL_pNULL NULL v_ALL_ALL
jour ville ventes
Roll_up - SQL99 (1)
Calcul de l’UNION de GROUP BY de chaque préfixe des attributs en paramètre
SELECT jour, ville, SUM(ventes)FROM c1GROUP BY ROLLUP(jour,ville)
génère l’union des groupements suivants{(jour,ville),(jour),vide }
Roll_up - SQL99 (2)
Jour_1 ville_1 v_1_1Jour_1 ville_2 v_1_2Jour_1 NULL v_1_ALLJour_2 ville_1 v_2_1.........NULL NULL v_ALL_ALL
jour ville ventes
Roll_up - SQL99 (3)
SELECT jour, ville, SUM(ventes)FROM c1GROUP BY ROLLUP(jour), ROLLUP(ville)
génération des agrégats pour les groupements :{(jour ),vide} × {(ville),vide} = {(jour ,ville),(jour),(ville),vide}
Grouping Sets - SQL99 (1)
Jour_1 ville_1 pièce_1 v_111Jour_1 ville_2 pièce_1 v_121Jour_2 ville_1 pièce_2 v_212............Jour_q ville_p pièce_r v_qpr
soient les faits :
c1 jour ville pièce ventes
Grouping Sets - SQL99 (2)
GROUP BY multiples en précisant quelles UNION sont souhaitées
l’imbrication d’attributs permet de séparer les GROUP BY simples de l’UNION de GROUP BY
CUBE et ROLLUP sont des cas particulier de GROUPING SETS
Grouping Sets - SQL99 (3)
GROUP BY GROUP BY jour, ville, pièceGROUPING SETS((jour, ville, pièce))
GROUP BY GROUP BY jourGROUPING SETS UNION(jour, ville, pièce) GROUP BY ville
UNIONGROUP BY pièce
GROUP BY GROUP BY jourGROUPING SETS UNION(jour,(ville,pièce)) GROUP BY ville, pièce
Architectures
ROLAP
MOLAP
HOLAP
ROLAP
- technologie de stockage relationnelle
- star schéma ou snowflake schéma
- middleware de traduction dynamique
maturité de la technologie relationnelle
génération de SQL encore peu efficace
Cuboïdes
Cube = Treillis de cuboïdes
Précalcul des agrégats (1)
Cube de données = faits = tous les cuboïdes
3 possibilités- ne pas stocker d’agrégat
coûteux en temps- stocker tous les agrégats
coûteux en espace- ne stocker qu’une partie des agrégats
... mais lesquels?
Précalcul des agrégats (2)
matérialiser des cuboïdes
choisis en fonction- du grain (niveau d’agrégation)- des requêtes utilisateurs
le grain doit être suffisamment fin pour pouvoir répondre aux requêtes
Précalcul des agrégats (3)
Exempleventes(produit, vendeur, année, prix)
3 dimensions : produit, vendeur, année8 possibilités de groupement
SELECT SUM(prix)FROM ventesGROUP BY ...
Précalcul des agrégats (4)
GROUP BY nombre de tuples nom de la vueproduit, vendeur, année 6 M pvaproduit, vendeur 6 M pvproduit, année 0.8 M pavendeur, année 6 M vaproduit 0.2 M pvendeur 0.1 M vannée 0.01 M aNull 1 vide
Précalcul des agrégats (5)
matérialiser tous les agrégats coûte 19 M
matérialiser- vue pva- vue pa- vues p, v et a- vue videcoûte 7,11 M
Notations
Q1 < Q2 ssi Q1 peut être traitée via Q2
- ancêtre(x) = {y | x < y}- descendant(x) = {y | y < x}- suivant(x) = {y | x < y, pas de z tel que x < z, z < y}
p < pv , ancêtre(pva) = {pva},descendant(pv) = {pv,p,v,vide},suivant(p) = {pv,pa}
Stratégie
répondre à une requête Q1. choisir un ancêtre QA matérialisé de Q2. adapter Q à QA3. évaluer la requête adaptée sur QA
coût de la réponse à Q = nombre de tuples de QA
Techniques d’indexation (ROLAP)
listes inversées
index de vecteurs de bits (bitmap indexing)
index de jointure (join indexing)
MOLAP
technologie des bases de données multidimensionnelles- structure de stockage = tableaux- correspondance directe avec la vue multidimensionnelle
gestion de la faible densité (sparsity)- techniques de compression spécifiques- structure d’index spécifiques
problème d’extensibilité
Stockage MOLAP
73
25 95
89 62
87
implantation “row major”
A[2][2]
73 25 95 89 6287
A[0][0]
Stockage MOLAP
d dimensions, Nk membres dans la dimension kla fonction p donne la position dans le tableau en fonction dechaque indice id
Exemple : a[2][3][4] avec 3 dimensions de 10 membresp(2,3,4) = 234