Datawarehouse

C. Vangenot

PlanPartie 1 : Introduction1. Objectifs 2. Qu'est ce qu'un datawarehouse? 3. Pourquoi ne pas rutiliser les BD? ROLAP MOLAP HOLAP

Partie 2 : Implmentation d'un datawarehouse

Partie 3 : Cube

Hypercube: Mesures et Dimension Oprateurs de manipulation

Partie 4 : Modlisation en toile et en flocon Partie 5 : Conception d'un datawarehouse

Partie 1: Introduction

1- ObjectifsTransformer un systme dinformation qui avait une vocation de production en un SI dcisionnel= Transformation des donnes de production en informations stratgiques

Exemple de requtes dcisionnelles :Catgorie socioprofessionnelle des meilleurs clients de chaque rgion Evolution de la part de march dun produit particulier Nombre d'employ de l'entreprise par classe d'ge, par sexe, par grade Quel est le profil des employs les plus performants?

Objectifs (2)Gestion et visualisation des donnes doit tre rapide et intuitive -> visualisation multi-dimensionnelle des donnes:Date

Ecrou Vis

Boulon Joint

Janvier Fvrier Mars

Rgion

Est

Centre Ouest

Type de Produit

Objectifs (3)Pour cela, ncessaire de retrouver et danalyser rapidement les donnes provenant de diverses sources DW offre une vision transversale des donnes de l'entrepriseintgration de diffrentes BDs

Les donnes doivent tre :Extraites Groupes ensembles et organises Corrles Transformes (rsum, agrgation)

2- Qu'est ce qu'un data Warehouse?Ensemble de donnesdestines aux "dcideurs" souvent une copie des donnes de production avec une valeur ajoute (agrgation, historique) intgres historises de regrouper les donnes de nettoyer, d'intgrer les donnes, ... de faire des requtes, rapports, analyses de faire du data mining faire l'administration du warehouse

Ensemble d'outils permettant

Fonctions d'un Data WarehouseRcuprer des donnes existants dans diffrentes BD sources Stocker les donnes (historises) Mettre disposition les donnes pour :Interrogation Visualisation Analyse

ArchitectureData Warehouse OLAP Servers Clients

Data Mining

Bases de production Data Marts Requtes Rapports OLAP

*MOLAP, ROLAP, HOLAP

INTEGRATION

Data warehouse

Cubes* Analyses statistiques Tableurs

3- Pourquoi ne pas utiliser un SGBD?SGBD et DW :ont des objectifs diffrents et font des traitements diffrents stockent des donnes diffrentes font l'objet de requtes diffrentes -> SGBD et DW ont besoin d'une organisation diffrente des donnes -> SGBD et DW doivent tre physiquement spars.

SGBD: Objectifs et traitementsLes SGBD sont des systmes dont le mode de travail est transactionnel (OLTP On-Line Transaction Processing). Permet d'insrer, modifier, interroger des informations rapidement, efficacement, en scurit. Deux objectifs principaux :Slectionner, ajouter, mettre jour et supprimer des tuples Ces oprations doivent pouvoir tre effectues trs rapidement, et par de nombreux utilisateurs simultanment.

DW: Objectifs et traitementsLes datawarehouse sont des systmes conus pour laide la prise de dcision. (Mode de travail: OLAP On-Line Analytical Processing) La plupart du temps sont utiliss en lecture (utilisateurs) Les objectifs principaux sontregrouper, organiser des informations provenant de sources diverses, les intgrer et les stocker pour donner lutilisateur une vue oriente mtier, retrouver et analyser linformation facilement et rapidement.

Donnes diffrentesDaprs BILL Inmon : Un DW est une collection de donnes orientes sujet, intgres, non volatiles, historises, organises pour la prise de dcision.Orientes sujet: thmes par activits majeures ; Intgres: divers sources de donnes ; Non volatiles: ne pas supprimer les donnes du DW ; Historises: trace des donnes, suivre lvolution des indicateurs.

Orientes sujet

Production

Donnes produit

Employs

Donnes client

Facturation Donnes BD

Donnes vendeur DW

Donnes intgresProduction

Employs

Donnes Client

Facturation Techniques d'intgration des donnes Techniques de nettoyage: Cohrence entre les diffrentes sources des noms, units de mesures, etc

Donnes non volatilesDBMS crer DW accder

maj

Ventes

supprimer

Donnes Client

insrer Pas d'historique

charger Donnes historises

Les donnesVolume trs important Donnes disperses, souvent difficiles daccs Peu ou mal intgres Complexes Non structures pour les applications dcisionnelles

Requtes (1)BD-OLTP reprsentent les donnes sous forme aplatie: relation, donnes normalisesproduit crou crou crou vis vis vis boulon boulon boulon joint joint joint rgion Est Ouest Centre Est Ouest Centre Est Ouest Centre Est Ouest Centre vente 50 60 110 70 80 90 120 10 20 50 40 70 date 01012004 12122003 01112003 01042004 10022004 29032004 05052004 24042004 11022004 01032004 01102003 01012003 vendeur X X Y Y Z Y X Z Y X Y Z fournisseur ville produit prix crou vis boulon joint 44 2 3 1 fournisseur CC DD VV BB

Requtes (2)OLTP: Requtes simples "qui, quoi"par ex. les ventes de X. jointures: les ventes de X quel prix de quel fournisseur,

OLAP: besoin de donnes agrges, synthtisesnombre de ventes par vendeur, par rgion, par mois, nombre de ventes par vendeur, par fournisseur, par mois,

SQL: Possibilit d'agrger les donnes (group by) mais trs coteux (parcourir toutes les tables) et il faut recalculer chaque utilisation Sur plusieurs tables (ex : somme des ventes par fournisseur), ncessit de faire des jointures souvent coteuses

Diffrences BD DWCaractristiques Applications Utilisateurs Donnes Requtes OLTP production un dpartement professionnel IT normalises, non agrges simples, nombreuses, rgulires, prvisibles, rptitives OLAP aide la dcision transversal (entreprise) dcideur non IT dnormalises, agrges complexes, peu nombreuses, irrgulires, non prvisibles millions 1 GB 1 TB historique

Nb tuples invoqus par dizaines requte (moyenne) Taille donnes Anciennet des donnes 100 MB 1 GB rcente, mises jour

Ncessit d'une structure muti-dimensionnelleLes BD relationnelles ne sont pas adaptes l'OLAP car :Pas les mmes objectifs Pas les mmes donnes:Les donnes ncessaires l'OLAP sont multi-dimensionnelles (i.e. ventes par vendeur, par date, par ville). Les tables en reprsentent une vue aplatie. Non seulement perte de performances mais aussi ncessit pour les utilisateurs de savoir comment trouver les liens entre les tables pour recrer la vue multi-dimensionnelle.

Pas les mmes traitements et requtes:

Il est donc ncessaire de disposer d'une structure de stockage adapte l'OLAP, i.e. permettant dereprsenter les donnes dans plusieurs dimensions, manipuler les donnes facilement et efficacement.

Sparation BD et DWLes DW vont tre physiquement spars des BD, pour des raisons de:Performance : systmes de production ne sont pas organiss pour pouvoir rpondre efficacement aux requtes des systmes daide la dcision. Mme les requtes simples peuvent dgrader srieusement les performances. Donnes diffrentes:Donnes historises : aide la dcision ncessite des donnes sur une longue dure, non conserve dans les BD Donnes agrges Qualit des donnes : sources diffrentes qui utilisent souvent des noms, formats, codes et mesures diffrents devant tre uniformiss

DW-OLAPDW-OLAP :reprsentation des donnes sous forme multidimensionnelle : Cube

produit crou crou crou vis vis vis boulon boulon boulon joint joint joint

rgion Est Ouest Centre Est Ouest Centre Est Ouest Centre Est Ouest Centre

vente 50 60 110 70 80 90 120 10 20 50 40 70

Est crous vis boulons joints 50 70 120 50

Ouest 60 80 10 40

Centre 110 90 20 70

Cube: reprsentation des donnes sous forme multidimensionnelleDate Ecrous Vis Boulons Joints Janvier Fvrier Mars Centre Type de Produit

Ouest Est Rgion

Vente de joints en janvier pour la rgion est

Partie 2 : Implmentation d'un data warehouse

Approches pour crer un DW3 possibilits: (1) Relational OLAP (ROLAP)Donnes sont stockes dans un SGBD relationnel Un moteur OLAP permet de simuler le comportement d'un SGBD multi-dimensionnel Structure de stockage en cube Accs direct aux donnes dans le cube

(2) Multidimensional OLAP (MOLAP) (3) Hybrid OLAP (HOLAP)

Donnes stockes dans SGBD relationnel (donnes de base) + structure de stockage en cube (donnes agrges)

ROLAPIde:Donnes stockes en relationnel. La conception du schma est particulire: schma en toile, schma en flocon Des vues (matrialises) sont utilises pour la reprsentation multidimensionnelle Les requtes OLAP (slice, rollup) sont traduites en SQL. Utilisation d'index spciaux: bitmap Administration (tuning) particulier de la base

Avantages/inconvnientsSouplesse, volution facile, permet de stocker de gros volumes. Mais peu efficace pour les calculs complexes

ROLAPRelational OLAP

Outils moteur ROLAP SGBD relationnel

vues multi-dimensionnelles

ROLAP

Index spciaux donnes denormalisestable Employ

MOLAPIde:Modlisation directe du cube Ces cubes sont implments comme des matrices plusieurs dimensionsCUBE [1:m, 1:n, 1:p] (mesure)

Le cube est index sur ses dimensions

Avantages/inconvnients:rapide formats propritaires ne supporte pas de trs gros volumes de donnes

MOLAPMulti-Dimensional OLAP

outils

MOLAP

serveur multidimensionnel

HOLAPIde:MOLAP + ROLAP Donnes stockes dans des tables relationnelles Donnes agrges stockes dans des cubes. Les requtes vont chercher les donnes dans les tables et les cubes

HOLAPHybrid OLAP

outils

HOLAP

SGBD relationnel + serveur multi-dimensionnel

table Employ

Data warehouseImplmentation:ROLAP MOLAP HOLAP

Modles de reprsentation:Cubes (partie 3) toile & flocon (partie 4)

Partie 3: Cube

Hyper CubeHypercube : BD multidimensionnelleAxes: dimensions (date, type de produits, rgion), Chaque cellule de l'hypercube contient une mesure calcule (vente de produit). DateEcrou Vis Boulon Joint Janvier Fvrier Mars

Rgion

Est

Centre Ouest

Type de Produit

Vente de joints en janvier pour la rgion est

Rappelproduit crou crou crou vis vis vis boulon boulon boulon joint joint joint rgion Est Ouest Centre Est Ouest Centre Est Ouest Centre Est Ouest Centre vente 50 60 110 70 80 90 120 10 20 50 40 70

Est crous vis boulons joints 50 70 120 50

Ouest 60 80 10 40

Centre 110 90 20 70

DfinitionsPrincipe de base : ce sont les analyses des indicateurs qui intressent lutilisateur Le modle multidimensionnel contient 2 types dattributs : les dimensions et les mesures Les mesures sont les valeurs numriques que lon compare (ex : montant_ventes, qte_vendue)Ces valeurs sont le rsultat dune opration dagrgation des donnes

Les dimensions sont les points de vues depuis lesquels les mesures peuvent tre observes :Ex : date, rgion, type de produit, etc.

Dimension (1)Dimension = liste d'lments Dimension contient des membres organiss en hirarchie :Chacun des membres appartient un niveau hirarchique (ou niveau de granularit) particulier

Granularit dune dimension : nombre de niveaux hirarchiquesTemps : anne semestre trimestre - mois

Dimension (2)Les axes de dimension doivent fournir des rgles de calcul d'agrgat pour chaque mesure:130 65 30 5 10 15 20 35 5 10 15 40 10 15 10 65 25 5 10

annesomme

semestresomme

trimestresomme

mois

Hirarchies multiples de dimensionsHirarchies multiples dans une dimension :Pays

Dept

rgion de ventes

Villes

Secteur de vente

Client

Manipulation d'un cubeOprateurs appliqus sur le cube sont algbriques (le rsultat est un autre cube) et peuvent tre combins Oprateurs sont:Oprateur sur le cube Slicing & Dicing (extraction) Changement de la granularit d'une dimension Roll up (agrgation d'une dimension => rsum) Drill down (plus dtailles) Oprateurs sur les dimensions

Slicing et dicingSlicing: Slection de tranches du cube par des prdicats selon une dimensionfiltrer une dimension selon une valeur Exemple: Slice (2004) : on ne retient que la partie du cube qui correspond cette date

Dicing: extraction d'un sous-cube

Exemple: slicing

SliceCentre Ouest Est 2001 2002 2003 50 50 70 100 60 10 30 40 20 10 10 60 40 40 40 20 70 50 50 60 60 30 20 crous vis boulon joint

Slice (2004)Ventes 2004 est ouest centre crou 220 160 20 vis 100 50 150 boulon 60 10 170 joint 10 60 110

2004

220

100 60

110 60

10

10

Exemple: DicingCentre Ouest Est 2001 2002 2003 50 50 70 100 60 10 30 40 20 10 10 60 40 40 40 20 70 50 50 60 60 30 20 crous vis boulon joint

ouest est 40 1996 1995 40 20

boulon joint 40 40 20

2004

220

100 60

110 60

10

10

Dice

Oprations lies la granularitLes oprations agissant sur la granularit dobservation des donnes caractrisent la hirarchie de navigation entre les diffrents niveaux. Roll-up ou forage vers le haut: consiste reprsenter les donnes du cube un niveau de granularit suprieur conformment la hirarchie dfinie sur la dimension.Utilisation de la fonction dagrgation (somme, moyenne, etc) spcifie pour la mesure et la dimension

Drill-down ou forage vers le bas : consiste reprsenter les donnes du cube un niveau de granularit de niveau infrieur, donc sous une forme plus dtaille.

Rollup ExempleCentre Ouest Est 2001 2002 2003 50 50 70 100 60 10 30 40 20 10 10 60 40 40 40 20 70 50 50 60 60 30 20 crous vis boulon joint

Roll up sur anne

2004

220

100 60

110 60

10

10

crous vis Centre Ouest Est440 440 200 160 200 20 50 120 120 20 150 10 20

boulon joint170 100 100 20 220 220

Drill-Down exempleDrill-down ~ opration inverse de Roll-upDrill-down du niveau des rgions au niveau villes

joint boulon vis crous 2001 2002 2003 2004 30 30 10 10 20 10 20 20 10 30 20

bordeaux dijon grenoble lille 30 30 10 20 30 30 30 20 60 10 70 30 20 30 40 40 30 40

lyon marseille montpellier nantes paris 30 30 30 20 20 10 50 30 40 20 20 20 50 30 10 10 10 10 10 20 10 10 10

poitiers 10 70 10 10 10

10

Partie 4: Modles en toile et en flocon

Modlisation en toile ou en floconsModlisation conceptuelle BD : entit et relation Modlisation de DW : dimension et mesure Les mesures sont les valeurs numriques que lon compare (ex : montant_ventes, qte_vendue)Ces valeurs sont le rsultat dune opration dagrgation des donnes

Les dimensions sont les points de vues depuis lesquels les mesures peuvent tre observes :Ex : date, localisation, produit, etc. Elles sont stockes dans les tables de dimensions

Les dimensionsUne dimension peut tre dfinie comme :un thme, ou un axe (attributs), selon lequel les donnes seront analyses Ex : Temps, Dcoupage administratif, Produits

Une dimension contient des membres organiss en hirarchie :Chacun des membres appartient un niveau hirarchique (ou niveau de granularit) particulier Ex : pour la dimension Temps: anne semestre mois jour

Les mesuresUne mesure est un lment de donne sur lequel portent les analyses, en fonction des diffrentes dimensionsEx : cot des travaux, nombre daccidents, ventes

Les faitsUn fait reprsente la valeur dune mesure, mesure ou calcule, selon un membre de chacune des dimensionsExemple : 250 000 euros est un fait qui exprime la valeur de la mesure cot des travaux pour le membre 2002 du niveau anne de la dimension temps et le membre Versailles du niveau ville de la dimension dcoupage administratif

La table de faitsLes mesures sont stockes dans les tables de faitsTable de fait contient les valeurs des mesures et les cls vers les tables de dimensions

Le modle en toileUne (ou plusieurs) table(s) de faits comprenant une ou plusieurs mesures. Plusieurs tables de dimension dnormalises: descripteurs des dimensions. Les tables de dimension n'ont pas de lien entre elles Avantages :Facilit de navigation Performances : nombre de jointures limit ; gestion des donnes creuses. Gestion des agrgats Toutes les dimensions ne concernent pas les mesures Redondances dans les dimensions Alimentation complexe.

Inconvnients :

Modle en toile

"Data Warehouse Models and OLAP Operations", Franconi

Exemple

"Data Warehouse Models and OLAP Operations", Franconi

Exemple de modle en toilePRODUIT COMMANDE N Cde Date Cde TABLE DE FAITS CLIENT N Client Nom Client Ville Pays VENDEUR Code vendeur Nom Vendeur Code chef Nom chef Code produit Nom Produit Description Produit Catgorie Description catgorie Prix unitaire DATE Clef date Date Mois Anne

N Cde N Client Code vendeur Code produit Clef date quantit prix total

Le modle en floconsLe schma en flocon est driv du schma en toile o les tables de dimensions sont normalises (la table des faits reste inchange). Avec ce schma, chacune des dimensions est dcompose selon sa (ou ses) hirarchie(s). Exemple : Commune, Dpartement, Rgion, Pays, ContinentClient Pepone Testut Soinin Vepont Martin Elvert Continent Europe Europe Europe Europe Europe Europe Pays France France France France France France Region RhneAlpes RhneAlpes RhneAlpes Ile de France Ile de France Ile de France Dpartement Rhne Rhne Rhne Paris Paris Yvelines Commune Lyon1 Lyon2 Lyon3 Paris1 Paris2 Versailles

Modle en floconPRODUIT COMMANDE N Cde Date Cde TABLE DE FAITS CLIENT N Client Nom Client Ville Pays VENDEUR Code vendeur Nom Vendeur Code chef Nom chef Code produit Nom Produit Description Produit Catgorie Description catgorie Prix unitaire DATE Clef date Date Mois Anne

N Cde N Client Code vendeur Code produit Clef date quantit prix total

Le modle en floconsPRODUIT COMMANDE Pays N Cde Date Cde TABLE DE FAITS CLIENT N Client Nom Client Ville Code produit Nom Produit Description Produit Prix unitaire catgorie catgorie Description catgorie

Ville pays

N Cde N Client Code vendeur Code produit Clef dateDATE

VENDEUR Code vendeur Nom Vendeur Code chef code chef nom Chef

quantit prix total

Clef date Date Mois Mois Anne Anne

Modle en flocons de neigeModle en toile + normalisation des dimensions Lorsque les tables sont trop volumineusesAvantages :rduction du volume,

Inconvnients :navigation difficile, nombreuses jointures.

Le modle en constellationLa modlisation en constellation consiste fusionner plusieurs modles en toile qui utilisent des dimensions communes. Un modle en constellation comprend donc plusieurs tables de faits et des tables de dimensions communes ou non ces tables de faits.

Modle en constellation

Partie 5: Conception d'un DW

Conception d'un data warehouseDiffrentes phases:1. Conception1. Dfinir la finalit du DW : Piloter quelle activit de lentreprise ? 2. Dfinition du modle de donnes (modle en toile/flocon ou cubes)

2. Acquisition des donnes1. Dterminer et recenser les donnes entreposer: recherche des donnes dans les sources de l'entreprise 2. Nettoyage des donnes 2. Dmarches dalimentation

3. Dfinir les aspects techniques de la ralisation 4. Dfinir les modes de restitution, 5. Stratgies dadministration, volution, maintenance

Acquisition des donnesTrois tapes:Dterminer et recenser les donnes entreposer: recherche des donnes dans les sources de donnes de l'entreprise Nettoyage des donnesconversions de donnes filtrages intgration

dmarche d'alimentation

Incrmental ou total Off-line ou. on-line Frquence de chargement: chaque nuit, 1/mois Dtermination de la taille de l'historique (5 ans, 10 ans) Ralise gnralement par des outils ddis (e.g. Powermart, Info Suite...)

Exemple de nettoyage des donnescm inches yards Jan.22,1999 22/01/99 01/22/99 BD Facturation BD Personnelcm

Jan.22,1999

Personne 1(Paul) Personne 2(Paul)

intgrer (Paul)

StockageChoix d'implmentationMOLAP ROLAP HOLAP

Implmentation du modle en toile et/ou des cubes et/ou des vues matrialises Dfinition des index Stockage les donnes

RestitutionCest le but du processus dentreposage des donnes. Elle conditionne souvent le choix de larchitecture du DW et de sa construction. Elle doit permettre toutes les analyses ncessaires pour la construction des indicateurs recherchs.

Diffrents types d'outils de restitutionDiffrents types:Requteurs et Outils d'analyse Outils de data mining

2 formes de data warehouseDatawarehouseContient les donnes concernant lensemble des composantes de lorganisation Plusieurs BD oprationnelles et sources extrieures

Data martUn sous-ensemble de lentrept global concernant un groupe spcifique dutilisateursEx : datamart concernant les ressources humaines

Peut tre dpendant ou non de lentrept de donns

ArchitectureData Warehouse OLAP Servers Clients

Data Mining

Bases de production Data Marts Requteurs Rapports OLAP

*MOLAP, ROLAP, HOLAP

INTEGRATION

Data warehouse

Cubes* Analyses statistiques Tableurs

Datawarehouse - outilsOLAP:Monde BD : Oracle (Express server), Informix, IBM DB2 (Arbor sofware) Monde dcisionnel: Essbase, Business-object, Powerplay, MMDB (SAS institute )

Restitution:Impromptu (rapports), Dbminer (data mining),

RfrencesTransparents:Sophie Montis: "Data Warehousing" M-A Aufaure: "Les entrepts de donnes" Enrico Franconi: "Data Warehouse Models and OLAP Operations" Hector Garcia-Molina : "Data Warehousing and OLAP"

Livre ou chapitre de livres:J-F. Goglin: "La construction du data warehouse", dition Herms G. Gardarin: "Internet/intranet et bases de donnes", chapitre 4