Conception optimisée de Vues Matérialisées dans un Environnement réel d’Entreposage de données

1. Conception optimisée de Vues Matérialisées dans un Environnement réel d’Entreposage de données G. K. Y. Chan Q.Li L. Feng Bienvenu Marie - Chapelle Cédric

2. Introduction • Présentation d’un algorithme de réduction des coûts d’exécution des requêtes sur un entrepôt de données • Définition d’un modèle de coût • Conception de l’algorithme qui déterminera les vues à matérialiser

3. Introduction (2) • Étude d’un cas concret : la société R, extraction d’une partie de la base pour former l’entrepôt de données - Table de fait : Facture INV (Co_no, Inv_no, Inv_date, P_no, Qty, Amt) - Table de dimensions :Société et Produit CO (Co_no, Co_name, R_no) PD (P_no, P_name, Mfr_no, Type_no, Cat_no)

4. Conception du datawarehouse (1) • Différents schémas • Application à la société ‘R’: éléments nécessaires • Stockage des informations fréquemment utilisées dans des vues récapitulatives • Architecture du système et caractéristiques

5. Différents schémas pour les systèmes de Datawarehousing • Schéma en étoile • Schéma en constellation • Schéma en flocon

6. Schéma en étoile

7. Schéma en constellation

8. Schéma en flocon

9. Conception du datawarehouse (2) • Différents schémas • Application à la société ‘R’: éléments nécessaires • Stockage des informations fréquemment utilisées dans des vues récapitulatives • Conception du système et caractéristiques

10. Application à la société ‘R’: éléments nécessaires (1) • Conception du Diagramme Entité-Association du système de facturation

11. Application à la société ‘R’: éléments nécessaires (2) • Nécessité d’estimer la fréquence de chaque requête et donc la fréquence d’accès à la vue associée

12. Conception du datawarehouse (3) • Différents schémas • Application à la société ‘R’: éléments nécessaires • Stockage des informations fréquemment utilisées dans des vues récapitulatives • Architecture du système et caractéristiques

13. Stockage dans des vues récapitulatives • Stocker dans des vues les clés et certains attributs des dimensions fréquemment utilisés par les requêtes + : réduit le nombre de jointures entre table de fait et tables de dimensions • : occupe de l’espace disque supplémentaire Pb : Trouver l’ensemble optimal de clés et attributs de dimension à stocker dans les vues (notions de bénéfice et d’efficacité )

14. Coûts d’ajout de différentes informations à la table de fait

15. Conception du datawarehouse (4) • Différents schémas • Application à la société ‘R’: éléments nécessaires • Stockage des informations fréquemment utilisées dans des vues récapitulatives • Conception du système et caractéristiques

16. Conception du système et caractéristiques (1) • Schéma hybride de la société ‘R’ • Nécessité de calculer les tailles des tables de dimension normalisées

18. Conception du système et caractéristiques (2) • Schéma hybride de la société ‘R’ • Nécessité de calculer les tailles des tables de dimension normalisées

19. Taille des tables normalisées

20. Sélection des vues à matérialiser • Modèle de coût • Adaptation d’un algorithme optimal de sélection des vues à matérialiser • Analyse du coût

21. Modèle de coût coût total = coût de requête + coût de maintenance des vues matérialisées + coût de stockage

22. Coût de requête • Hypothèse : ni index , ni clé de hashage sur les vues => balayage séquentiel

23. Coût de maintenance (1) • Hiérarchisation des vues : notion de descendance • Calcul du coût

25. Coût de maintenance (2) • Hiérarchisation des vues : notion de descendance • Calcul du coût

26. Calcul de coût Hypothèse : fui = 1 ; Toutes les vues sur les ventes sont actualisées régulièrement

27. Coût de stockage (1) • Dépendance de la capacité du disque dur • Calcul du coût

28. Coût de stockage (2) • Dépendance de la capacité du disque dur • Calcul du coût Hypothèse : U=1, d’où

29. Indice d’efficacité de matérialisation • Bénéfice net • Efficacité de matérialisation

30. Bénéfice net • Bénéfice net : net(Bi) = bénéfice Bi - coût maintenance - coût stockage Où ai : ancêtre de i, ni : descendante de i m : nombre de descendants de i

31. Indice d’efficacité de matérialisation • Bénéfice net • Efficacité de matérialisation

32. Efficacité de matérialisation • bénéfice net par unité de stockage de la vue matérialisée

33. Coût total • Calcul du coût total des requêtes apres matérialisation d’une vue Coût total = Coût total – Bénéfice + Coût de stockage + coût de maintenace

34. Algorithme • Déterminer les chemins optimaux pour les requêtes et la maintenance des vues • Calculer le bénéfice net et le coefficient d’efficacité de chaque vue • Classer les vues par ordre décroissant d’efficacité • Déterminer le coût total minimum • Sélectionner l’ensemble optimal de vues à matérialiser

35. Chemins optimaux (1)

36. INV Co-P-Day R-P-Day R-P-Month P-Sale-Day P-Sale-Month Chemins optimaux (2)

37. Algorithme • Déterminer les chemins optimaux pour les requêtes et la maintenance des vues • Calculer le bénéfice net et le coefficient d’efficacité de chaque vue • Classer les vues par ordre décroissant d’efficacité • Déterminer le coût total minimum • Sélectionner l’ensemble optimal de vues à matérialiser

38. Algorithme • Déterminer les chemins optimaux pour les requêtes et la maintenance des vues • Calculer le bénéfice net et le coefficient d’efficacité de chaque vue • Classer les vues par ordre décroissant d’efficacité • Déterminer le coût total minimum • Sélectionner l’ensemble optimal de vues à matérialiser

39. Classer les vues par ordre décroissant d’efficacité

40. Algorithme • Déterminer les chemins optimaux pour les requêtes et la maintenance des vues • Calculer le bénéfice net et le coefficient d’efficacité de chaque vue • Classer les vues par ordre décroissant d’efficacité • Déterminer le coût total minimum • Sélectionner l’ensemble optimal de vues à matérialiser

41. Déterminer le coût total minimum

42. Algorithme • Déterminer les chemins optimaux pour les requêtes et la maintenance des vues • Calculer le bénéfice net et le coefficient d’efficacité de chaque vue • Classer les vues par ordre décroissant d’efficacité • Déterminer le coût total minimum • Sélectionner l’ensemble optimal de vues à matérialiser

43. Sélectionner l’ensemble optimal de vues à matérialiser

44. Exemple CO_P_Day (1) • Select INV.Co_No,Co_Name,INV.P_No,Type_No,Cat_No,MFR_No,R_No,INV_DT,SUM(AMT)AMT,SUM(QTY)QTY From INV, CO, PD Where Inv.Co_No=Co.Co_No and INV.P_No=PD.P_No

45. Exemple Co_P_Day (2) • Coût de stockage D’après le coût d’ajout de différentes informations : {CO_P}UX = 240B

46. Coûts d’ajout de différentes informations à la table de fait

47. Exemple Co_P_Day (3) • Coût de la requête : Coût de selection + Coût des jointures 240+(12+12*240)+(6+6*240)=4578 B

48. Taille des tables normalisées

49. Exemple Co_P_Day (4) • Coût de maintenance Coût de requête de l’ancêtre + Coût de jointure de l’ancêtre Ancêtre de Co_P_Day : INV 114 + (12+12*114) + (6+6*114) = 2184 B

50. Exemple Co_P_Day (5) • Bénéfice Net Bénéfice - Coût de stockage - Coût de maintenance 97694.88 - 240 - 2184 = 95270.88 B