Cours Informatique décisionnelle
Chapitre 3: ETL: Extract-transform-load
1
Références 2
• S. Chafki, C. Desrosiers, Cours « Entrepôts de données et intelligence
d’affaires », Ecole de Technologie Supérieur, Université Québec CANADA
• Leila KJIRI, « Cours Data Warehoure », Ecole Ensias RABAT
• EL FAR Mohamed, « Cours Data Warehoure », Faculté des sciences Dhar
El Mahraz
Architecture d’un entrepôt 3
Les problèmes des sources de données 4
Les problèmes des sources de données:
1. Sources diverses et disparates;
2. Sources sur différentes plateformes et OS;
3. Applications legacy utilisant des BD et autres technologies obsolètes;
4. Historique de changement non-préservé dans les sources;
5. Qualité de données douteuse et changeante dans le temps;
6. Structure des systèmes sources changeante dans le temps;
7. Incohérence entre les différentes sources;
8. Données dans un format difficilement interprétable ou ambiguë.
Extract, Transform and Load (ETL) 5
Extract, Transform and Load (ETL) 6
Caractéristiques:
• Permet la consolidation des données à l’aide des trois opérations :
Extraction: identifier et extraire les données de sources ayant subi
une modification depuis la dernière exécution;
Transformation: appliquer diverses transformations aux données
pour les nettoyer, les intégrer et les agréger;
Chargement: insérer les données transformées dans l’entrepôt et
gérer les changements aux données existantes (ex: stratégies SCD).
• Traite normalement de grande quantités de données en lots;
• Est surtout utilisé avec les entrepôts de données et les comptoirs de
données.
Extract, Transform and Load (ETL) 7
Extract, Transform and Load (ETL) 8
Avantages:
• Optimisé pour la structure de l’entrepôt de données;
• Peut traiter de grandes quantités de données dans une même exécution
(traitement en lot);
• Permet des transformations complexes et agrégations sur les données;
• a cédule d’exécution peut être contrôlée par l’administrateur;
• La disponibilité d’outils GUI sur le marché permet d’améliorer la
productivité;
• Permet la réutilisation des processus et transformations
Extract, Transform and Load (ETL) 9
Inconvénients:
• Processus de développement long et coûteux;
• Gestion des changements nécessaire;
• Exige de l’espace disque pour effectuer les transformations (staging
area);
• Exécuté indépendamment du besoin réel;
• Latence des données entre la source et l’entrepôt;
• Unidirectionnel (des sources vers l’entrepôt de données).
Démarche et étapes de l'ETL 10
Identification des sources 11
• Énumérer les items cibles (métriques et attributs de dimension)
nécessaires à l'entrepôt de données;
• Pour chaque item cible, trouver la source et l'item correspondant de
cette source;
• Si plusieurs sources sont trouvées, choisir la plus pertinente;
• Si l'item cible exige des données de plusieurs sources, former des règles
de consolidation;
• Si l'item source referme plusieurs items cibles (ex: un seul champs pour
le nom et l'adresse du client), définir des règles de découpage;
• Inspecter les sources pour des valeurs manquantes.
Extraction des données 12
Extraction complète:
• Capture l'ensemble des données à un certain instant (snapshot de l'état
opérationnel);
• Normalement employée dans deux situations:
1. Chargement initial des données;
2. Rafraîchissement complet des données (ex: modification d'une
source).
• Peut être très coûteuse en temps (ex: plusieurs heures/jours).
Extraction des données 13
Extraction incrémentale:
• Capture uniquement les données qui ont changées ou ont été ajoutées
depuis la dernière extraction;
• Peut être faite de deux façons:
1. Extraction temps-réel;
2. Extraction différée (en lot).
Extraction des données 14
Extraction temps-réel:
• S'effectue au moment où les transactions surviennent dans les systèmes
sources.
Extraction des données 15
Option 1: Capture à l'aide du journal des transactions:
• Utilise les logs de transactions de la BD servant à la récupération en cas
de panne;
• Aucune modification requise à la BD ou aux sources;
• Doit être fait avant le rafraîchissement périodique du journal;
• Pas possible avec les systèmes legacy ou les sources à base de fichiers (il
faut une BD journalisée).
Extraction des données 16
Option 2: Capture à l'aide de triggers
• Des procédures déclenchées (triggers) sont définies dans la BD pour
recopier les données à extraire dans un fichier de sortie;
• Meilleur contrôle de la capture d'évènements;
• Exige de modifier les BD sources;
• Pas possible avec les systèmes legacy ou les sources à base de fichiers.
Extraction des données 17
Option 3: Capture à l'aide des applications sources
• Les applications sources sont modifiées pour écrire chaque ajout et
modification de données dans un fichier d'extraction;
• Exige des modifications aux applications existantes;
• Entraîne des coûts additionnels de développement et de maintenance;
• Peut être employé sur des systèmes legacy et les systèmes à base de
fichiers.
Extraction des données 18
Extraction différée:
• Extrait tous les changements survenus durant une période donnée (ex:
heure, jour, semaine, mois).
Extraction des données 19
Option 1: Capture basée sur les timestamps
• Une estampille (timestamp) d'écriture est ajoutée à chaque ligne des
systèmes sources;
• L'extraction se fait uniquement sur les données dont le timestamp est
plus récent que la dernière extraction;
• Fonctionne avec les systèmes legacy et les fichiers plats, mais peut
exiger des modifications aux systèmes sources;
• Gestion compliquée des suppressions.
Extraction des données 20
Option 2: Capture par comparaison de fichiers
• Compare deux snapshots successifs des données sources;
• Extrait seulement les différences (ajouts, modifications, suppressions)
entre les deux snapshots;
• Peut être employé sur des systèmes legacy et les systèmes à base de
fichiers, sans aucune modification;
• Exige de conserver une copie de l'état des données sources;
• Approche relativement coûteuse.
Transformation des données 21
Types de transformation:
• Révision de format:
Ex: Changer le type ou la longueur de champs individuels.
• Décodage de champs:
Consolider les données de sources multiples
Ex: ['homme', 'femme'] vs ['M', 'F'] vs [1,2].
Traduire les valeurs cryptiques
Ex: 'AC', 'IN', 'SU' pour les statuts actif, inactif et suspendu.
• Pré-calcul des valeurs dérivées:
Ex: profit calculé à partir de ventes et coûts.
Transformation des données 22
Types de transformation:
• Découpage de champs complexes:
Ex: extraire les valeurs prénom, secondPrénom et nomFamille à
partir d'une seule chaîne de caractères nomComplet.
• Pré-calcul des agrégations:
Ex: ventes par produit par semaine par région.
• Déduplication
Ex: Plusieurs enregistrements pour un même client
Transformation des données 23
Types de transformation:
• Fusion de plusieurs champs:
Ex: information d'un produit
Source 1: code et description;
Source 2: types de forfaits;
Source 3: coût.
• Conversion de jeu de caractères:
Ex: EBCDIC(IBM) vers ASCII.
• Conversion des unités de mesure:
Ex: Centimes à Dirhams
• Conversion de dates:
Ex: '24 FEB 2011' vs '24/02/2011' vs '02/24/2011'.
Transformation des données 24
Problème de résolution d'entités:
• Survient lorsqu'une même entité se retrouve sur différentes sources,
sans qu'on ait la correspondance entre ces sources;
Ex: clients de longue date ayant un identifiant différent sur les
différentes sources;
• L'intégration des données requiert de retrouver la correspondance;
• Approches basées sur des règles de résolution
Ex: les entités doivent avoir au moins N champs identiques (fuzzy
lookup / matching).
Transformation des données 25
Problème des sources multiples:
• Survient lorsqu'une entité possède une représentation différente sur
plusieurs sources;
• Approches de sélection:
Choisir la source la plus prioritaire;
Choisir la source ayant l'information la plus récente.
Transformation des données 26
Gestion des changements dimensionnels:
• Déterminer la stratégie de gestion des changements (SCD Type 1, 2 ou
3) de chaque attribut dimensionnel modifié;
• Préparer l'image de chargement (load image) en conséquence:
SCD Type 1: ancienne valeur écrasée;
SCD Type 2: nouvelle ligne ajoutée;
SCD Type 3: déplacement de l'ancienne valeur dans la colonne
d'historique et écriture de la nouvelle valeur dans la colonne
courante.
Transformation des données 27
Matrice de transformation:
Chargement des données 28
Chargement initial:
• Fait une seule fois lors de l'activation de l'entrepôt de données;
• Les indexes et contraintes d'intégrité référentielle (clé étrangères) sont
normalement désactivés temporairement;
• Peut prendre plusieurs heures.
Chargement incrémental:
• Fait une fois le chargement initial complété;
• Tient compte de la nature des changements (ex: SCD Type 1, 2 ou 3);
• Peut être fait en temps-réel ou en lot.
Chargement des données 29
Rafraîchissement complet:
Employé lorsque le nombre de changements rend le chargement
incrémental trop complexe;
Ex: lorsque plus de 20% des enregistrements ont changé depuis le
dernier chargement.
Considération additionnelles:
Faire les chargements en lot dans une période creuse (entrepôt de
données non utilisé);
Considérer la bande passante requise pour le chargement;
Avoir un plan pour évaluer la qualité des données chargées dans
l'entrepôt;
Commencer par charger les données des tables de dimension.
ETL: outils commerciaux 30
Outils ETL:
Oracle Warehouse Builder;
IBM Infosphere Information Server;
Microsoft SQL Server Integration Services (SSIS);
SAS Data Integration Studio.
ETL Open Source 31
« Pentaho Data Integration » (« Kettle » à l'origine)
« Talend Open Studio »
Ces deux ETL Open Source nous paraissent en effet à l'heure actuelle les
plus intéressants en termes :
de fonctionnalités proposées,
de maturité,
de pérennité.
Pentaho Data Integration (« PDI ») et Talend Open Studio (« TOS »)
peuvent répondre de façon équivalente à la plupart des ETL
propriétaires disponibles sur le marché.
