Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Les SGBDs Décisionnels Didier DONSEZ Université Joseph Fourier - Grenoble 1 Pol

Les SGBDs Décisionnels Didier DONSEZ Université Joseph Fourier - Grenoble 1 PolyTech’Grenoble LIG/ADELE Didier.Donsez@imag.fr, Didier.Donsez@ieee.fr Thierry CRUANES Oracle Corp., US thierry.cruanes@oracle.com http://www-adele.imag.fr/users/Didier.Donsez/cours 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 2 Plan Limites du SQL en décisionnel Nouvelles fonctions et types de données Aspect Systèmes Index Bitmap RAID Benchmarks 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 3 Limites du SQL et des SGBDs « Transactionnels » orienté vers le OLTP Interface Manque d ’expressivité des requêtes SQL pour l’OLAP Système Structures et Algorithmes inadaptées à la charge de travail de l ’OLAP 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 4 Winter 2003 VLDB Survey Largest Database Size, Decision Support 1. Sears Teradata 4.63 2. HCIA Informix 4.50 3. Wal-Mart Teradata 4.42 4. Tele Danmark DB2 2.84 5. CitiCorp DB2 2.47 6. MCI Informix 1.88 7. NDC Health Oracle 1.85 8. Sprint Teradata 1.30 9. Ford Oracle 1.20 10. Acxiom Oracle 1.13 SBC Teradata 10.50 First Union Informix 4.50 Dialog Proprietary 4.25 Telecom Italia DB2 3.71 FedEx Teradata 3.70 Office Depot Teradata 3.08 AT & T Teradata 2.83 SK C&C Oracle 2.54 NetZero Oracle 2.47 Telecom Italia Informix 2.32 2001 Survey 1998 Survey France Telecom Oracle 29.23 AT&T Daytona 26.27 SBC Teradata 24.81 Anonymous DB2 16.19 Amazon.com Oracle 13.00 Kmart Teradata 12.59 Claria Oracle 12.10 HIRA Sybase IQ 11.94 FedEx Teradata 9.98 Vodafone Teradata 9.91 2003 Survey 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 5 Exemple France Telecom ajoute 1To de données brutes par mois DW de 65 To actuellement. Comment Gérer ce volume Maintenir les index, les partitions, les vues materialisées, les stats (plusieurs jours de calculs from scratch) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 15 Amazon.com TODO 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 16 Systèmes MOLAP (obsolète) SGBD Spécialisé ROLAP (inefficace) SGBD Relationnel H-OLAP (H comme Hybrid) (l’actualité) SGBD Relationnel avec des extensions OLAP Nouveaux opérateurs GROUP BY CUBE, … Algorithmes adéquats StarJoin, Index Bitmap, ... 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 17 Nouvelles Fonctions pour SQL BREAK BY (SAS) RANK Rang d’une ligne par rapport à un agrégat TOP / BOTTOM Requête de type « Top Ten » Les 10 meilleurs / Les 10 moins bons Echantillonnage (Informix Online XPS) Requête effectuée sur un échantillon de données tiré aléatoirement (preview) Limite de temps de calcul pour une approximation du résultat Extension du Group By (SQL99) Grouping Sets, Rollup, Cube Fenêtre mouvante pour les agrégat : Windows Exemple : moyenne et déviance sur le jour courant et les 3 jours précédents SQL:2003 OLAP 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 18 Ranking select rank('01-Jan-1990','12-Dec-1989') within group (order by #client, #produit) montant from Ventes; 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 19 GROUP BY GROUPING SETS Partitionnement selon plusieurs dimensions Exemple SELECT #Client, #Produit, SUM(Montant) FROM Ventes GROUP BY GROUPING SETS ((#Client), (#Produit)) est équivalent à (SELECT #Client, NULL, SUM(Montant) FROM Ventes GROUP BY #Client) UNION (SELECT NULL, #Produit, SUM(Montant) FROM Ventes GROUP BY #Produit) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 20 GROUP BY ROLLUP Réduire progressivement Exemple SELECT #Client, #Produit, SUM(Montant) FROM Ventes GROUP BY ROLLUP (#Client, #Produit) est équivalent à (SELECT #Client, #Produit , SUM(Montant) FROM Ventes GROUP BY #Client, #Produit) UNION (SELECT #Client, NULL, SUM(Montant) FROM Ventes GROUP BY #Client) UNION (SELECT NULL, NULL, SUM(Montant) FROM Ventes) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 21 GROUP BY ROLLUP SELECT MONTH(SALES_DATE), REGION, SALES_MGR, SUM(SALES) FROM SALES WHERE YEAR(SALES_DATE) = 1996 GROUP BY ROLLUP (MONTH(SALES_DATE), REGION, SALES_MGR) MONTH REGION SALES_MANAGER SUM(SALES) April Central Chow 25000 April Central Smith 15000 April Central - 40000 April North - 15000 April North - 15000 April - - 55000 May Central Chow 25000 May Central - 25000 May North Smith 15000 May North - 15000 May - - 40000 - - - 95000 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 22 GROUP BY CUBE Partitionnement selon tous les sous-ensembles possibles de Grouping Sets SELECT #Client, #Produit, #Fournisseur, SUM(Montant) FROM Ventes GROUP BY CUBE (#Client, #Produit, #Fournisseur) est équivalent à SELECT #Client, #Produit, SUM(Montant) FROM Ventes GROUP BY GROUPING SETS ( (), -- total des ventes (#Client), -- total des ventes par Client (#Fournisseur), -- total des ventes par Forunisseur (#Produit), -- total des ventes par Produit (#Client, #Fournisseur) -- total des ventes par Client et par Fournisseur (#Client, #Produit), -- total des ventes par Client et par Produit (#Produit, #Fournisseur), -- total des ventes par Produit et par Fournisseur (#Client, #Produit, #Fournisseur) -- total des ventes par Client, ¨Produit et Fournisseur ) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 23 Fenêtre glissante But: cumul, moyenne, dérivation sur une fenêtre glissante du temps SELECT Sf.Region, Sf.Month, Sf.Sales, AVG (Sf.Sales) OVER ( PARTITION BY Sf.Region ORDER BY Sf.Month ASC ROWS 1 PRECEDING ) AS Moving_avg FROM SalesFact AS Sf ORDER BY Sf.Month ASC; Region Month Sales Moving_Avg Eastern Oct, 1994 27497 27497 Eastern Nov, 1994 24168 25832 Eastern Dec, 1994 27801 25984 Eastern Jan, 1995 25991 26896 Eastern Feb, 1995 25968 25979 Eastern Mar, 1995 23610 24789 Mid Atlantic Oct, 1994 7150 7150 Mid Atlantic Nov, 1994 7586 7368 Mid Atlantic Dec, 1994 6164 6875 Mid Atlantic Jan, 1995 6051 6108 Mid Atlantic Feb, 1995 4299 5175 Mid Atlantic Mar, 1995 6283 5291 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 24 Exercice : Best-profit Query price 15 19 16 17 15 13 5 8 7 13 11 14 10 5 2 5 15 19 16 17 15 13 5 8 7 13 11 14 10 5 2 5 0 2 4 6 8 10 12 14 16 18 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 time q u o te Find the best profit one could make by buying a stock and selling it later in the same day D’après Alberto Lerner and Dennis Shasha 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 25 Exercice : Best-profit Query price 15 19 16 17 15 13 5 8 7 13 11 14 10 5 2 5 mins(price)15 15 15 15 15 13 5 5 5 5 5 5 5 5 2 2 0 4 1 2 0 0 0 3 2 8 6 9 0 0 0 3 0 2 4 6 8 10 12 14 16 18 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 time q u o te D’après Alberto Lerner and Dennis Shasha Find the best profit one could make by buying a stock and selling it later in the same day 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 26 Solution : Best-profit Query [SQL:1999] SELECT max(rdif) FROM (SELECT ID,tradeDate, price - min(price) OVER (PARTITION BY ID,tradeDate ORDER BY timestamp ROWS UNBOUNDED PRECEDING) AS rdif FROM Ticks ) AS t1 WHERE ID=“S” AND tradeDate=‘1/10/03' D’après Alberto Lerner and Dennis Shasha 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 28 MS MDX (Multidimensional Expression) Langage d’expression OLAP pour MS SQL Server Exemples SELECT NON EMPTY {[Time].[1997], [Time].[1998]} ON COLUMNS, [Promotion Media].[Media Type].Members ON ROWS FROM Sales SELECT {[Measures].[Unit Sales]} ON COLUMNS, ORDER( EXCEPT( [Promotion Media].[Media Type].members, { [Promotion Media].[Media Type].[No Media]}),[Measures].[Unit Sales], DESC) ON ROWS FROM Sales 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 31 ADT Séries Temporelles (Time Series) Définition Suite de couple (Valeur, estampille de temps) Applications Finance (stock value), Santé (épidémiologie), ... Type calendar, ... Opérations Index par rapport au temps 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 32 ADT Séries Temporelles 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 33 ADT Séries Temporelles 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 34 Architecture des SGBD décisionnels et des infocentres Charge de travail Requêtes complexes (nombreuses jointures + agrégats) Très gros volumes de données Réponses SGBD Parallèles sur machines parallèles (SMP,Cluster, …) RAID et SAN (Storage Area Networks) Index Bitmaps, algorithme Placement sur disque (Partitionnement, …) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 35 Aspects Systèmes Stockage Tables de fait Append only (RID ≈≈Timekey) Ligne de fait clés et mesures de type de taille fixe enregistrement de taille fixe (accès aléatoire) Jointure en Etoiles Star Query Index Binaire (Bitmap) 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs décisionnels 36 FaitVente Produit Magasin Agrégation PK=PK SK= SK FaitVente Magasin Produit Agrégation SK=SK PK=PK time_key day_of_week month quarter year holiday_flag Temps time_key product_key store_key dollars_sold units_sold dollars_cost Fait de Vente product_key description brand category Produit store_key store_name address floor_plan_type Magasin Jointure en Etoile (Star Join) Exemple d’entrepôt Optimiseur d’un SGBD classique 2 stratégies 03/01/2008 Didier Donsez, 1997-2006, Les SGBDs uploads/Ingenierie_Lourd/ bdsgdw.pdf