Les SGBDs Supports de Décisions

1. Les SGBDs Supports de Décisions Witold Litwin

2. Besoin • Utilisation massive de fonctions agrégats • celles de SQL • autres • dans le langage hôte • Exemples • La moyenne de ventes • Le commercial du jour • La corrélation entre certaines attributs

3. Problèmes nouveaux • Parcours fréquent de tous les tuples • d'une grande table • d'une collection de tables à joindre • L'opération peut prendre beaucoup de temps • quel que soit l'optimisation de chemins d'accès • Des requêtes peuvent être impossible à formuler en SQL seul • Classement par rang selon les valeurs de T.a • Corrélation entre T.a et T.b ? • Les attributs les plus corrélés ?

4. Solutions • Nouvelles fonctions de SQL • Fonctions agrégats • Rank, Rollup, Cube, Covariance, Correlation… • Fonctions scalaires • Nouveaux concepts • Donnée multidimensionnelle • Espaces des agrégations • Espace des influences • Espace des variations

5. Solutions • Nouvelles techniques d'implémentation • Calcul parallèle et scalable • Prè-calcul partiel ou total de requêtes • Échantillonnage • Séparation de calculs en plusieurs bases et systèmes

6. Solutions • Nouveau types de SGBD • Entrepôts de Données • Data Warehousing • Fouille de Données • Data Mining (DM) • Analyse en ligne • Online Analytical Processing (OLAP) • Les SGBD "traditionnels" sont dès lors souvent appelés • Online Transaction Processing (OTPS) Systems • Les nouveaux constituent les • Systèmes de Support de Décisions • Decision Support Systems

7. Principaux produits • Informix • Basé sur l'achat de Red Brick • DB2 • SGBD • Data Warehouse Center SQL Server • SGBD • Data Analysis Services • OLAP Services renommés pour SQL Server 2000 • Oracle Express

8. OLAP traditionnel Prix de produit 123 Espace des Données Support de Decisions

9. OLAP traditionnel Prix de produit 123 Vente par produit et par mois Espace (OLAP) des Agrégations Espace des Données Support de Decisions

10. OLAP traditionnel Prix de produit 123 Vente par produit et par mois Espace (OLAP) Des Agrégations Espace des Données Espace Des Influences (Data Mining) Facteurs d'influence des ventes à Paris Support de Decisions

11. OLAP traditionnel Prix de produit 123 Vente par produit et par mois Espace (OLAP) des Agrégations Espace des Données Espace des Influences (Data Mining) Facteurs d'influence des ventes à Paris Support de Decisions Changement de ventes depuis 3 mois Espace des Variations

12. Espaces de Données Concepts Relations Ensembles Agrégations Arithmétique Influences Logique et Stats. Variations Calcul différentiel

13. Technologie • BDs parallèles, distribuées, scalables • Jointures pré-calculées • intégrité référentielle • Vues matérialisées • Algorithmes statistiques • Algorithmes décisionnels • revus pour l'application aux BDs • Systèmes multibases • Entrepôts de données • Data warehousing

14. T Vue matérialisée sum prod 30 p1 50 p2 27 p3 • create materialized view T(sum, prod) as select sum(montant) prod from Ventes groupby produit ; • Les requêtes sont reformulées contre la vue • d'une manière transparente pour l'usager select * from T where prod = 'p1' ;

15. T Vue matérialisée sum prod 30 p1 50 p2 27 p3 • Problème général • Cohérence entre la vue et ses tables de base • Algorithme de MAJ • pour toute nouvelle vente de Produit p • add le montant à T.sum where prod = p ;

16. Vue matérialisée • La cohérence peut être implantée par • des déclencheurs • des procédures stockées (stored procedures) • Exercise • Que faire pour la requête select avg (montant) prod from Ventes groupby produit Note : plusieurs solutions existent

17. Problème de scalabilité • On peut difficilement maintenir un grand nombre de vues matérialisées • il faudrait executer des dizaines de déclencheurs • et ne pas handicaper les perf. OLTP • et comment faire pour les avortements et la concurrence ?

18. Axes de solutions • Systèmes multiordinateurs • Cohérence souple (ang. loose) entre les tables de bases et les vues matérialisées • la somme de ventes peut être 0.05 % inexacte pendant 10 sec., de temps en temps, sans que le DBA se fasse gronder • nécessité dans les systèmes mobiles • Les MAJ sont propagées en asynchrone après le Commit

19. Les statistiques • Correlations • DFs notamment (correlation = 1) • Regressions • Nuées Dynamiques • Diday • Analyse factorielle & multi-dimensionnelle en général • Benzecri • Aide au diagnostic • Bayes

20. OLAP / Data Mining • OLAP • Les agrégations demandées sont connues • On peut formuler les requêtes SQL • ou SQL imbriqué • Data Mining • on ne sais pas a priori quoi demander • quelles sont les corrélations parmi les attributs de Ventes ? • Cette dichotomie est floue

21. Data Mining dans un espace des influences • Le résultat peut être attendu sous forme • d'un graphique (business graphics)

22. Data Mining dans un espace des influences • Le résultat peut être attendu sous forme • d'un graphique (business graphics) 2-D

23. Data Mining dans un espace des influences • Le résultat peut être attendu sous forme • d'un graphique (business graphics) 3-D

24. Data Mining dans un espace des influences • Le résultat peut être attendu sous forme • d'une règle logique if customer.age < 25 and power > '9 CH' and status = 'bachelor' then prob (accid.# / year > 1) = 0.99 ;

25. Data Mining dans un espace des influences • Ceci conduit au problèmes d'inférence de règles • la logique • BDs actives • BD de connaissances • BDs déductives

26. Mesures dans un espace des variations • On observe les variations selon des dimensions • variation de cours de bourse • depuis une semaine, une année, 1 Janv. 1996... • variation de ventes (ang. lift) • selon la campagne de promotion • selon la saison • etc • Précalcul de variations est encore plus couteux que celui de facteurs statiques • surtout de variations temporelles

27. Mesures dans un espace des variations

28. Data Mining dans un espace des influences • On attend deux types de résultats distincts • l'état de l'existant • découverte, explication, confirmation • l'analyse de la tendance • prédictions • Lex deux aspects sont encore en état de recherche ou, au mieux, en développent avancé

29. Schéma en Étoile • Table de faits • En général très grande • Tables de dimensions • En général très petites • Liens clé primaire / clé étrangère

30. Architecture d'entrepôt de données IBM

31. Fonctions Agrégat pour OLAPDB2 • CountBig • Pour le nombre de tuples > 2**31 • Covariance • entre des attributs ou des expressions de valeur • Correlation • entre des attributs ou des expressions de valeur • Regression functions • 10 fonctions • Les paramètres de la droite de régression entre des attributs ou des expressions de valeur • Rank, Dense_Rank • Rank en présence de duplicata : 1,2,2,4,4,6… • Dense_Rank en présence de duplicata : 1,2,2,3,3,4…

32. Covariance • Soit avgexp1 le résultat de AVG(expression1) et soit avgexp2 le résultat de AVG(expression2), • Alors COVARIANCE(expression1, expression2) est: • AVG( (expression1 - avgexp1) * (expression2 - avgexp2 ) SELECT COVARIANCE(SALARY, BONUS) FROM EMPLOYEE WHERE WORKDEPT = 'A00'

33. Correlation Définition : COVARIANCE(expression1,expression2)/ (STDDEV(expression1)*STDDEV(expression2)) SELECT CORRELATION(SALARY, BONUS) FROM EMPLOYEE WHERE WORKDEPT = 'A00'

34. Fonctions Agrégat pour OLAPDB2 • Fonctions spécifiques à DB2 Data Warehouse Center: • Analyse de la variance (ANOVA) • Distributions de Fisher-F • Valeur P • Sous-totaux par période de temps • Test de Chi-carré • Statistique T pour n paires de valeurs et à partir du coeff. de corrélation r T = r * ((n – 2 ) / (1 - r2 ))0.5 • T  0 indique une corrélation entre les attributs

35. Fonctions Agrégat pour OLAPTeradata • RANK • CSUM (Cumulative (Running) Sums) • MAVG and MSUM • MDIFF (Moving Difference) • QUANTILE • QUALIFY • SAMPLE

36. SELECT EmployeeName, (HireDate - DATE) AS ServiceDays, RANK( ServiceDays ) as Seniority FROM Employee ORDER BY EmployeeName; EmployeeName Service Days Seniority Robyn Baker 9931 2 Nick Garrison 9931 2 Kyle McVicker 9408 5 Eva O’Malley 10248 1 Norma Powers 9409 4

37. SELECTItem, SalesDate,CSUM(Revenue,Item,SalesDate)asCumulativeSalesFROM (SELECT Item, SalesDate, Sum( Sales ) as Revenue FROM DailySales WHERE StoreId=5 AND SalesDate Between ‘1/1/1999’ and ‘1/31/1999’ GROUP BY Item, SalesDate) AS ItemSales ORDER BY SalesDate; Item SalesDateCumulativeSales InstantCOW 01/01/1999 972.99 InstantCOW 01/02/1999 2361.99 InstantCOW 01/03/1999 5110.97 InstantCOW 01/04/1999 7793.91

38. SELECTMarketDay,ClosingPrice,MAVG(ClosingPrice,50,MarketDay)asXYZAverageSELECTMarketDay,ClosingPrice,MAVG(ClosingPrice,50,MarketDay)asXYZAverage FROM MarketDailyClosing WHERE Ticker = ‘XYZ’ ORDERBY MarketDay; MarketDay ClosingPrice XYZAverage 12/27/1999 89 1/16 85 1/2 12/28/1999 91 1/8 86 1/16 12/29/1999 92 3/4 86 1/2 12/30/1999 94 1/2 87

39. SELECTMarketWeek,WeekVolume,MDIFF(WeekVolume,1,MarketWeek)asXYZVolumeDiffSELECTMarketWeek,WeekVolume,MDIFF(WeekVolume,1,MarketWeek)asXYZVolumeDiff FROM (SELECT MarketWeek, SUM(Volume) as WeekVolume FROM MarketDailyClosing WHERE Ticker = ‘XYZ’ GROUP BY MarketWeek) ORDERBY MarketWeek; MarketWeekWeekVolume XYZVolumeDiff 11/29/1999 9817671 ? 12/06/1999 9945671 128000 12/13/1999 10099459 153788 12/20/1999 10490732 391273 12/27/1999 11045331 554599

40. SELECT Item, Profit, QUANTILE( 10, Profit ) as Decile FROM (SELECTItem,Sum(Sales)—(Count(Sales)*ItemCost) asProfit FROM DailySales, Items WHERE DailySales.Item = Items.Item GROUP BY Item) AS ItemProfit; ItemProfit Decile High Tops 97112 9 Low Tops 74699 7 Running 69712 6 Casual 28912 3 Xtrain 100129 9

41. SELECT Item, Profit, QUANTILE( 100, Profit ) AS Percentile FROM (SELECTItem,Sum(Sales)—(Count(Sales)* Items.ItemCost) as ProfitFROM DailySales, Items WHERE DailySales.Item = Items.Item GROUP BY Item) AS ItemProfit QUALIFY Percentile = 99; ItemProfit Percentile Fat Free Ice Cream 10.79 99 Sugar Free Ice Cream -100.55 99 Fake Chocolate Ice Cream -1110.67 99 Fat/Sugar Free Ice Cream -2913.88 99 Fake Ice Cream -4492.12 99

42. SELECT customer_id, age, income, marital_status, …, SAMPLEIDFROM customer_tableSAMPLE 0.6, 0.25, 0.15 customer_id age income marital_status ... SAMPLEID 1362549 17 0 1 1 1362650 21 17804 2 1 1362605 34 16957 2 1 1362672 50 16319 2 3 1362486 76 10701 3 1 1362500 40 56708 1 3 1362489 35 55888 3 2 1362498 60 9849 2 1 1362551 27 23085 1 1 1362503 18 5787 1.. 2

43. ROLLUP(DB2 & SQL-Server) • Groupements multiples (super-groupes) selon une dimension • l ’ordre des attributs dans la clause a l ’importance • les attributs sont progressivement « oublies » de droite à gauche • Remplace plusieurs requêtes GROUP BY SELECT p#, sum (qty) as tot-qty from S,SP, P WHERE SP. P# = P.P# AND SP. S# = S.S# GROUP BY ROLLUP (P#, S.CITY, COLOR) HAVING tot-qty > 100 ; • Problèmes avec des nulls que l’on verra plus tard

44. SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ) ORDER BY WEEK, DAY_WEEK, SALES_PERSON WEEK DAY_WEEK SALES_PERSON UNITS_SOLD ----------- ----------- --------------- ----------- 13 6 GOUNOT 11 13 6 LEE 12 13 6 LUCCHESSI 4 13 6 - 27 13 7 GOUNOT 21 13 7 LEE 21 13 7 LUCCHESSI 4 13 7 - 46 13 - - 73 - - - 73

45. CUBE(DB2 & SQL-Server) • Groupements multiples selon toutes les dimensions • l ’ordre des attributs dans la clause n ’a pas d ’importance • les attributs sont progressivement « oublies » de droite à gauche • Remplace plusieurs requêtes GROUP BY SELECT p#, sum (qty) as tot-qty from S,SP, P WHERE SP. P# = P.P# AND SP. S# = S.S# GROUP BY CUBE (P#, S.CITY, COLOR) HAVING tot-qty > 100 ; • Problèmes avec des nuls que l’on verra plus tard • Opération bien plus chère que Rollup • 2N requêtes générées au lieu de N, pour N attributs

46. CUBE(DB2 & SQL-Server) SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ) ORDER BY WEEK, DAY_WEEK, SALES_PERSON

47. WEEK DAY_WEEK SALES_PERSON UNITS_SOLD ----------- ----------- --------------- ----------- 13 6 GOUNOT 11 13 6 LEE 12 13 6 LUCCHESSI 4 13 6 - 27 13 7 GOUNOT 21 13 7 LEE 21 13 7 LUCCHESSI 4 13 7 - 46 13 - GOUNOT 32 13 - LEE 33 13 - LUCCHESSI 8 13 - - 73 - 6 GOUNOT 11 - 6 LEE 12 - 6 LUCCHESSI 4 - 6 - 27 - 7 GOUNOT 21 - 7 LEE 21 - 7 LUCCHESSI 4 - 7 - 46 - - GOUNOT 32 - - LEE 33 - - LUCCHESSI 8 - - - 73

48. GROUPING SETS • On indique explicitement les groupes • entre (..) • le groupe () est constitué de toute la table SELECT p#, sum (qty) as tot-qty from S,SP, P WHERE SP. P# = P.P# AND SP. S# = S.S# GROUP BY GROUPING SETS ((P#, S.CITY, COLOR), (P#, COLOR), ()) HAVING tot-qty > 100 ; • Problèmes avec des nuls que l’on verra plus tard

49. GROUPING SETS SELECT SALES_PERSON, MONTH(SALES_DATE) AS MONTH, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)), () ) ORDER BY SALES_PERSON, MONTH

50. GROUPING SETS SALES_PERSON MONTH UNITS_SOLD --------------- ----------- ----------- GOUNOT 3 35 GOUNOT 4 14 GOUNOT 12 1 LEE 3 60 LEE 4 25 LEE 12 6 LUCCHESSI 3 9 LUCCHESSI 4 4 LUCCHESSI 12 1 - - 155