Intégration des données et ETL - cours.etsmtl.ca · Source: “EII - ETL – EAI What, Why, and...

MTI820 −Entrepôtsdedonnéesetintelligenced’affaires

IntégrationdesdonnéesetETL

1DépartementdegénielogicieletdesTI MTI820Hiver2011– ©S.Chafki,C.Desrosiers

Lecycledevied’unprojetenBI• Diagrammedefluxdetravail:

2

Planificationdeprojet/programme

Définitiondes

besoinsd’affaires

Conceptiondel’architecturetechnique

Modélisationdesdonnées

Conceptiondesapplicationde

BI

Sélectionetinstallationdes

produits

Conceptionphysique

ConceptionetdéveloppementdusystèmeETL

Développementdesapplications

deBI

Déploiement

Croissance

Maintenance

Gestiondeprojet/programme

DépartementdegénielogicieletdesTI MTI820Hiver2011– ©S.Chafki,C.Desrosiers

Question• Pourquoiest-ilnécessairedefairel’intégrationdes

données?

DépartementdegénielogicieletdesTI MTI820Hiver2011– ©S.Chafki,C.Desrosiers 3

Lesproblèmesdessourcesdedonnées1. Sourcesdiversesetdisparates;

2. SourcessurdifférentesplateformesetOS;

3. Applicationslegacy utilisantdesBDetautrestechnologiesobsolètes;

4. Historiquedechangementnon-préservédanslessources;

5. Qualitédedonnéesdouteuse etchangeantedansletemps;

6. Structuredessystèmessourceschangeantedansletemps;

7. Incohérenceentrelesdifférentessources;

8. Donnéesdansunformatdifficilementinterprétableouambigu.


Question• Quellessontlesprincipalesapprochesd’intégrationet

quelssontleursprincipauxavantages/inconvénients?


Approches d’intégration


Source:“EII- ETL– EAIWhat,Why,andHow!”,TomYu,2005

IBM Software Group

13

Information Integration – Data Patterns

EII

StructuredData Source

unstructured

LegacyData Source

Data Virtualization

SQL(or Content)

� Real-time information access� Federation of data from multiple

sources� Dynamic drill down� Semi-structured & unstructured

data

EAI

Application

Application

Application

InterpretTransform

Route

� Process based integration of application data

� Message-based, transaction-oriented processing

� Workflow and data orchestration, content-based routing

DataSource

DataSource

extract

transform

load

Target /Data

Warehouse

ETL

� Bulk data integration � Set-based & hierarchical

transformations� High scale, batch-oriented data

delivery

Extract,TransformandLoad• Intégrationetlivraison desdonnées enlot

• Transformationsappliquéessur lesdonnées

EnterpriseInformationIntergration• Fédération dedonnées provenantdeplusieurs sources

• Accès temps-réel auxdonnées• Données structuréesou semi-structurées

IBM Software Group

13


EII


unstructuredLegacy

Data Source

Data Virtualization

SQL(or Content)



data

EAI

Application

Application

Application

InterpretTransform

Route




DataSource

DataSource

extract

transform

load

Target /Data

Warehouse

ETL



delivery

EnterpriseApplicationIntergration• Processus d’intégrationdesdonnées d’applications

• Basé sur l’échange demessagessur unbuscommun

IBM Software Group

13


EII


unstructuredLegacy

Data Source

Data Virtualization

SQL(or Content)



data

EAI

Application

Application

Application

InterpretTransform

Route




DataSource

DataSource

extract

transform

load

Target /Data

Warehouse

ETL



delivery

Extract,TransformandLoad(ETL)• Caractéristiques:

– Permetlaconsolidationdesdonnéesàl’aidedestroisopérationssuivantes:• Extraction:identifieretextrairelesdonnéesdesourcesayantsubiunemodificationdepuisladernièreexécution;

• Transformation:appliquerdiverses transformationsauxdonnéespourlesnettoyer,lesintégreretlesagréger;

• Chargement:insérerlesdonnéestransforméesdansl’entrepôtetgérerleschangementsauxdonnéesexistantes(ex:stratégiesSCD).

– Traitenormalement degrandequantitésdedonnéesenlotscédulés;

– Estsurtoututiliséaveclesentrepôtsdedonnéesetlescomptoirsdedonnées.


Extract,TransformandLoad(ETL)• Avantages:

– Optimisépourlastructuredel’entrepôtdedonnées;– Peuttraiterdegrandesquantitésdedonnéesdansunemême

exécution(traitementenlot);– Permetdestransformationscomplexesetagrégationssurles

données;– Lacéduled’exécutionpeutêtrecontrôléeparl’administrateur;– Ladisponibilitéd’outilsGUIsurlemarchépermetd’améliorer la

productivité;– Permetlaréutilisationdesprocessusettransformations(ex:

packages dansSSIS).


Extract,TransformandLoad(ETL)• Inconvénients:

– Processusdedéveloppement longetcoûteux;– Gestiondeschangementsnécessaire;– Exigedel’espacedisquepoureffectuerlestransformations

(staging area);– Exécutéindépendamment dubesoinréel;– Latencedesdonnéesentrelasourceetl’entrepôt;– Unidirectionnel (dessourcesversl’entrepôtdedonnées).


EntrepriseInformationIntegration (EII)• Caractéristiques:

– Fournitunevueunifiéedesdonnéesdel'entreprise,oùlessourcesdedonnéesformentunefédération;

– Lessourcesdedonnéesdispersées sontconsolidées àl'aided'uneBDvirtuelle, demanièretransparenteauxapplicationsutilisantcesdonnées;

– TouterequêteàlaBDvirtuelle estdécomposéeensous-requêtesauxsourcesrespectives,dontlesréponsessontassembléesenunrésultatunifiéetconsolidé;

– Permetdeconsolideruniquement lesdonnéesutilisées,aumomentoùellessontutilisées (sourcedata pulling).

– Letraitementen-ligne desdonnéespeutcependantentraînerdesdélais importants.


EntrepriseInformationIntegration (EII)• Avantages:

– Accèsrelationnelàdessourcesnon-relationnelles;

– Permetd’explorerlesdonnéesaveclacréationdumodèledel’entrepôtdedonnées;

– Accélèreledéploiement delasolution;

– PeutêtreréutiliséparlesystèmeETLdansuneitérationfuture;

– Aucundéplacementdedonnées.


EntrepriseInformationIntegration (EII)• Inconvénients:

– Requiert lacorrespondancedesclésd’unesourceàl’autre;

– Consolidationdesdonnéespluscomplexequedansl’ETL;

– Surtaxelessystèmesources;

– Pluslimitéquel’ETLdanslaquantitédedonnéespouvantêtretraitée;

– Transformationslimitées surlesdonnées;

– Peutconsommerunegrandebandepassanteduréseau.


EntrepriseApplicationIntegration (EAI)• Caractéristiques:

– Approchepermettantdefourniràl'entrepôtdesdonnéesprovenantdessources(sourcedatapushing);

– Reposesurl'intégrationetlepartagedesfonctionnalitésdesapplicationssourcesàl'aided'unearchitectureSOA;

– Généralementutiliséentempsréelouensemi-temps réel(NearRealTime);

– L'EAIneremplacepasleprocessusETL,maispermetdesimplifiercedernier.


EntrepriseApplicationIntegration (EAI)• Avantages:

– Facilitel’interopérabilitédesapplications;– Permetl’accèsen(quasi)temps-réel;– Netransfèrequelesdonnéesnécessaires;– Contrôleduflotdel’information.


EntrepriseApplicationIntegration (EAI)• Inconvénients:

– Supportlimitéauxtransformationsetagrégationsdesdonnées;– Tailledestransactionslimitée(ennombredelignes);– Développement complexe;– Gestioncomplexedel’intégritésémantiquedesdonnées (e.g.,

règlesd’affaires);– Utiliselabandepassanteduréseaudurantlesheuresdepointe.


Comparaison entrelesapproches d’intégrationETL EII EAI

Flot dedonnées Unidirectionnel(sourcesà l’entrepôt)

Bidirectionnel Bidirectionnel

Mouvement dedonnées

Lotscédulés Aumoment delarequête

Déclenché parlatransaction

Latence Journalier à mensuel Temps-réel Quasitemps-réel

Transformations/agrégations desdonnées

Grandecapacité Moyenne capacité Faible capacité

Volumedesdonnées Grand(millionsoumilliardsdelignes)

Moyen(10,000 –1,000,000delignes)

Petit(100-1000 lignes)


Quand utiliser lesapproches d’intégration• ApprocheETL:

– Consolidationd’unegrandequantitédedonnées– Transformationscomplexes

• ApprocheEII:– Relierunentrepôt(EDW)existantavecdesdonnéesdesources

spécifiques– Donnéessourcesvolatilesetaccessiblesàl’aidederequêtes

simples(ex:SQL).

• ApprocheEAI:– Intégrationdetransactions– Requêtesanalytiques simples– Sourcesnon-accessiblesdirectement


Exemplesdeproduitscommerciaux• OutilsETL:

– OracleWarehouse Builder;– IBMInfosphere InformationServer;– MicrosoftSQLServerIntegration Services(SSIS);– SASDataIntegration Studio.

• OutilsEAI:– IBMWebSphereMessageBroker;– MicrosoftBizTalkServer;– OracleSOASuite.

• OutilsEII:– SAPBusinessObjects DataFederator;– IBMWebSphereFederation Server.


Question• Quellessontlesprincipalesétapesdansle

développementdusystèmeETL?


Tâchesetétapesdel'ETL


Déterminer lesdonnéesnécessairesàlasolutiondeBI

Déterminer lessourcesinternesetexternesrenfermantcesdonnées

Définirlesrèglesd'extractiondesdonnéescibles

Définirlesrèglesdetransformationetdenettoyagedesdonnées

Planifierlesagrégationsdedonnées

Préparerlestaging area etlesoutilsd'assurancequalité

Définirlesprocédurespourlechargementdedonnées

ETLdestablesdedimension

ETLdestablesdefaits

Extractiondesdonnées• Identificationdessources:

1. Énumérer lesitemscibles(métriquesetattributsdedimension)nécessairesàl'entrepôtdedonnées;

2. Pourchaqueitemcible,trouverlasourceetl'itemcorrespondantdecettesource;

3. Siplusieurssourcessonttrouvées,choisirlapluspertinente;4. Sil'itemcibleexigedesdonnéesdeplusieurssources,former

desrèglesdeconsolidation;5. Sil'itemsourcerefermeplusieursitemscibles(ex:unseul

champspourlenometl'adresseduclient),définirdesrèglesdedécoupage;

6. Inspecterlessourcespourdesvaleursmanquantes.


Extractiondesdonnées• Extractioncomplète:

– Capturel'ensemble desdonnéesàuncertaininstant(snapshotdel'étatopérationnel);

– Normalementemployée dansdeuxsituations:1. Chargementinitialdesdonnées;2. Rafraîchissementcompletdesdonnées(ex:modification

d'unesource).– Peutêtretrèscoûteuseentemps(ex:plusieursheures/jours).

• Extraction incrémentale:– Captureuniquement lesdonnéesquiontchangéesouontété

ajoutéesdepuisladernièreextraction;– Peutêtrefaitededeuxfaçons:

1. Extractiontemps-réel;2. Extractiondifférée(enlot).


Question• Commentpeut-onextrairelesdonnéesquiontchangées

danslessources:– Entemps-réel?– Endifféré(lot)?


BDsources

Extractiondesdonnées• Extractiontemps-réel:

– S'effectueaumomentoùlestransactionssurviennentdanslessystèmessources.


triggersOption1:Captureàl'aidedesjournauxdetransactions

Journaldetransactions

Fichiersgénérésparlessources

Systèmesopérationnels

sources

Fichiersgénérésparlestriggers

Zonedepréparationdedonnées(staging area)

Option3:Capturedanslesapplicationssources

Option2:Captureàl'aidedetriggers

Extractiondesdonnées• Option1:Captureàl'aidedujournaldestransactions

– UtiliseleslogsdetransactionsdelaBDservantàlarécupérationencasdepanne;

– AucunemodificationrequiseàlaBDouauxsources;– Doitêtrefaitavantlerafraîchissementpériodiquedujournal;

– Paspossibleaveclessystèmeslegacy oulessourcesàbasedefichiers(ilfautuneBDjournalisée).


Extractiondesdonnées• Option2:Captureàl'aidedetriggers

– Desprocéduresdéclenchées(triggers)sontdéfiniesdanslaBDpourrecopierlesdonnées àextrairedansunfichierdesortie;

– Meilleurcontrôledelacaptured'évènements;– ExigedemodifierlesBDsources;– Paspossibleaveclessystèmeslegacy oulessourcesàbasedefichiers.


Extractiondesdonnées• Option3:Captureàl'aidedesapplicationssources

– Lesapplicationssourcessontmodifiées pourécrirechaqueajoutetmodificationdedonnéesdansunfichierd'extraction;

– Exigedesmodificationsauxapplicationsexistantes;– Entraînedescoûtsadditionnelsdedéveloppement etde

maintenance;– Peutêtreemployé surdessystèmes legacy etlessystèmesà

basedefichiers.


BDsources

Extractiondesdonnées• Extractiondifférée:

– Extraittousleschangementssurvenusdurantunepériodedonnée(ex:heure,jour,semaine,mois).


Extractiond'aujourd'hui

Fichiersd'extractionutilisantlestimestamps

Systèmesopérationnels

sources

Fichiersd'extractionutilisantla

comparaisonZonedepréparation

dedonnées(staging area)

Option1:Capturebaséesurlestimestamps

Option2:Captureparcomparaisondefichiers

Programmed'extraction

Extractiond'hier

Programmedecomparaison

Extractiondesdonnées• Option1:Capturebaséesurlestimestamps

– Uneestampille(timestamp)d'écritureestajoutéeàchaquelignedessystèmessources;

– L'extractionsefaituniquement surlesdonnéesdontletimestamp estplusrécentqueladernièreextraction;

– Fonctionneaveclessystèmeslegacy etlesfichiersplats,maispeutexigerdesmodificationsauxsystèmessources;

– Gestioncompliquéedessuppressions.


Extractiondesdonnées• Option2:Captureparcomparaisondefichiers

– Comparedeuxsnapshots successifsdesdonnéessources;– Extraitseulement lesdifférences(ajouts,modifications,suppressions)entrelesdeuxsnapshots;

– Peutêtreemployésurdessystèmeslegacy etlessystèmesàbasedefichiers,sansaucunemodification;

– Exigedeconserverunecopiedel'étatdesdonnéessources;

– Approcherelativement coûteuse.


Extractiondesdonnées• Considérations pratiques:

– Choisir,pourchaquesource,lafenêtretemporelledurantlaquelle serafaitel'extraction;

– Déterminer laséquencedestâchesd'extraction;– Déterminer commentgérerlesexceptions.


Question• Quellessontlestransformationsàeffectuersurles

donnéessourcesavantdeleschargerdansl’entrepôt?


Transformationdesdonnées• Typesdetransformation:

1. Révisiondeformat:• Ex:Changerletypeoulalongueurdechampsindividuels.

2. Décodagedechamps:• Consoliderlesdonnéesdesourcesmultiples

– Ex:['homme','femme']vs['M','F']vs[1,2].• Traduirelesvaleurscryptiques

– Ex:'AC','IN','SU'pourlesstatutsactif,inactif etsuspendu.

3. Pré-calculdesvaleursdérivées:• Ex:profit calculéàpartirdeventes etcoûts.

4. Découpagedechampscomplexes:• Ex:extrairelesvaleursprénom,secondPrénom etnomFamille

àpartird'uneseulechaînedecaractèresnomComplet.


Transformationdesdonnées• Typesdetransformation(suite):

5. Fusiondeplusieurschamps:• Ex:informationd'unproduit

– Source1:codeetdescription;– Source2:typesdeforfaits;– Source3:coût.

6. Conversiondejeudecaractères:• Ex:EBCDIC(IBM)versASCII.

7. Conversiondesunitésdemesure:• Ex:impérialàmétrique.

8. Conversiondedates:• Ex:'24FEB2011'vs'24/02/2011'vs'02/24/2011'.

9. Pré-calculdesagrégations:• Ex:ventesparproduitparsemaineparrégion.

10. Déduplication:• Ex:Plusieursenregistrementspourunmêmeclient.


Transformationdesdonnées• Problèmederésolutiond'entités:

– Survientlorsqu'unemêmeentitéseretrouvesurdifférentes sources,sansqu'onaitlacorrespondanceentrecessources;• Ex:clientsdelonguedateayantunidentifiantdifférentsurlesdifférentessources;

– L'intégrationdesdonnées requiertderetrouverlacorrespondance;– Approchesbaséessurdesrèglesderésolution

• Ex:lesentitésdoiventavoiraumoinsNchampsidentiques(fuzzylookup /matching).

• Problèmedessourcesmultiples:– Survientlorsqu'uneentitépossèdeunereprésentationdifférentesur

plusieurssources;– Approchesdesélection:

• Choisirlasourcelaplusprioritaire;• Choisirlasourceayantl'informationlaplusrécente.


Transformationdesdonnées• Gestiondeschangementsdimensionnels:

– Déterminer lastratégiedegestiondeschangements(SCDType1,2ou3)dechaqueattributdimensionnelmodifié;

– Préparerl'imagedechargement(load image)enconséquence:• SCDType1:anciennevaleurécrasée;• SCDType2:nouvelleligneajoutée;• SCDType3:déplacementdel'anciennevaleurdanslacolonned'historiqueetécrituredelanouvellevaleurdanslacolonnecourante.


Exemplesdetransformations:(SSIS)


Transformationdesdonnées• Matricedetransformation:


Champs cible Table cible Champs source Table source Règle de transformation

Chargementdesdonnées• Typesdechargement:

– Chargementinitial:• Faituneseulefoislorsdel'activationdel'entrepôtdedonnées;• Lesindexesetcontraintesd'intégritéréférentielle (cléétrangères) sontnormalementdésactivés temporairement;

• Peutprendreplusieursheures.

– Chargementincrémental:• Faitunefoislechargementinitialcomplété;• Tientcomptedelanaturedeschangements(ex:SCDType1,2ou3);• Peutêtrefaitentemps-réelouenlot.

– Rafraîchissement complet:• Employélorsquelenombredechangementsrendlechargementincrémentaltropcomplexe;

• Ex:lorsqueplusde20%desenregistrementsontchangédepuisledernierchargement.


Chargementdesdonnées• Considération additionnelles:

– Faireleschargementsenlotdansunepériode creuse(entrepôtdedonnéesnonutilisé);

– Considérer labandepassanterequisepourlechargement;– Avoirunplanpourévaluer laqualitédesdonnéeschargéesdans

l'entrepôt;– Commencerparchargerlesdonnéesdestablesdedimension.


Extract,Load andTransform (ELT)


Source:RalfGoetz,Whatisthefundamentaldifferencebetween“ETL”and“ELT”intheworldofbigdata?,2017

Datalake

Extract,Load andTransform (ELT)• ProblèmesavecETL:

– Traite lesdonnées importantesaumomentdelaconception;– Développement longetcomplexe.

• SolutionELT:– UtiliselestechnologiesBigData (ex:Hadoop,Spark,etc.);– Chargementsrapides,potentiellement temps-réels;– Lacsdedonnées(datalakes)permettent lesdonnéesnon-structurées;

– Transformationsfaitesaumomentdelarequête;– TechnologiesmoinsmaturesqueETLetimplémentation pluscomplexe(ex:codeJavavsoutildédié).


Extract,Load andTransform (ELT)


Entrepôts dedonnées Lacsdedonnées

Données Structurées,traitées Semi-/non-structurées,brutes

Traitement Schéma-à-l’écriture Schéma-à-la-lecture

Stockage Coûteux pourgrandesquantités

Stockage à faible coût

Agilité Moins agile,configurationfixe Hautement agile,reconfiguration aubesoin

Sécurité Mature Moins mature

Utilisateurs Professionnels d’affaires Experts en datascience

Intégration des données et ETL - cours.etsmtl.ca · Source: “EII - ETL – EAI What, Why, and...

Documents

Transcript of Intégration des données et ETL - cours.etsmtl.ca · Source: “EII - ETL – EAI What, Why, and...