Business Intelligence

Chapitre 4
Introduction à l’ETL

L. Bouziani
ESI, 2019
1. Principes généraux d'un processus ETL
• Principes de l'ETL
• ETL développé ou outil d'ETL
• ETL en mode batch ou en mode flux
• ETL incrémental
Principes de l'ETL
• You get the data out of its original source location (E), you do
something to it (T), and then you load it (L) into a final set of tables
for the users to query (Kimball et al., 2008)
• L'ETL est le processus qui permet de charger un Datawarehouse à
partir de données externes généralement issues de bases
transactionnelles
• Son rôle est de récupérer ces données et de les traiter pour qu'elles
correspondent aux besoins du modèle dimensionnel
• En général les données sources doivent être "nettoyées" et aménagées pour
être exploitables par les outils décisionnels
Principes de l'ETL
• L’ETL ne se contente pas de charger les données, il doit aussi les
dé-normaliser, les nettoyer, les contextualiser, puis les charger de la
façon adéquate
• Il est important de savoir que la réalisation de l'ETL constitue 70%
d'un projet décisionnel en moyenne
• Ce système est complexe et ne doit rien laisser s'échapper, sous peine d'avoir
une mauvaise information dans l'entrepôt, donc des données fausses, donc
inutilisables
• Le secret d'un bon ETL réside dans sa complétude et dans son
exhaustivité dans la prise en charge des données depuis les sources
de données jusqu'à l'entrepôt
Phase d’extraction
• Le rapatriement des données peut se faire de trois façons différentes :
• Push : dans cette méthode, la logique de chargement est dans le système de
production, il " pousse " les données vers l’ETL quand il en a l'occasion.
Cependant, si le système est occupé, il ne fera pas de chargement
• Pull : le Pull " tire " les données de la source vers l’ETL. L'inconvénient de cette
méthode est qu'elle peut surcharger le système s'il est en cours d'utilisation
• Push-Pull : c'est le mélange des deux méthodes. La source prépare les
données à envoyer et prévient l’ETL qu'elle est prête. L’ETL va récupérer les
données. Si la source est occupée, l’ETL fera une autre demande plus tard
Phase d’extraction
• Une fois la bonne stratégie choisie, il faut se poser les questions
fondamentales qui dessineront les caractéristiques du système :
• Quelle est la disponibilité de mes sources de données ?
• Comment y accéder ?
• Comment faire des extraction incrémentiels ?
• Quel est le temps moyen d’une extraction incrémentielle ?
• Quelle est la possibilité de recharger des données dans le cas où le processus
de’extraction échoue ?
• Quelle politique vais-je utiliser dans le cas d'échec de l’extraction ?
Phase de traitement
• Voici les questions à se poser pour cette étape :
• Quels sont les champs les plus sujets à erreurs ?
• Ai-je les moyens de corriger les erreurs automatiquement ?
• Comment permettre à un utilisateur de corriger les erreurs ?
• Quelle politique vais-je utiliser pour le traitement des erreurs
• fichier journal (log),
• table de rejet dans la BD ?
• Comment montrer à l'utilisateur final que des données n'ont pas été
totalement chargées à cause d'erreurs ?
Phase de chargement
• Voici les questions qu'il faut se poser pour cette étape :
• Que faire si un chargement échoue ?
• Ai-je les moyens de revenir à l'état avant le chargement ?
• Puis-je revenir dans le temps pour un chargement donné ?
• Comment valider mon chargement, comment détecter les erreurs ?
ETL en mode batch ou en mode flux
• ETL en mode batch
• Un ETL alimente en général un data warehouse par des processus batch
périodiques
• The standard architecture for an ETL system is based on periodic batch
extracts from the source data, which then flows through the system, resulting
in a batch update of the final end user tables (Kimball, Caserta, 2004)
• ETL en mode flux
• Il existe néanmoins des applications nécessitant des data warehouses
alimentés en temps réel en mode flux
ETL incrémental
• ETL incrémental
• Un ETL instrumente normalement un processus incrémental
• Les données sont modifiées dans les systèmes transactionnels :
• mise à jour des dimensions
• ou ajouts de nouveaux faits
• L'ETL répercute les mises à jour dans le data warehouse
• Un ETL non incrémental est :
• soit un ETL qui ne sert qu'une seule fois (one shot)
• soit un ETL qui refait 100% du processus d’intégration à chaque fois que l'on souhaite
une mise à jour (le data warehouse est vidé puis rempli à nouveau avec les données
actuelles)
• On notera qu'un tel ETL ne gère pas d'historisation
2. Architecture d’un ETL
• L’architecture d’un ETL peut être organisée en trois zones :
• Zone d'extraction (E)
• Destinée à unifier les sources de données et offrir un point d'accès unique
• Zone de transformation (T)
• Destinée à traiter les sources et offrir une interface d'accès aux données
transformées (API)
• Zone d'exploitation (L)
• Destinée à implémenter le data warehouse et les data marts
Architecture d’un ETL
Architecture d’un ETL
• Il est possible de substituer l’architecture à 3 niveaux par une
architecture à 2 niveaux seulement :
Architecture d’un ETL
BD d’extraction (E)
• La BD d’extraction (E) est une BD relationnelle destinée à
implémenter la zone d'extraction d'un ETL, pour offrir un unique
point d'accès à l'ensemble des sources de données
• Elle est composée de :
• tables permettant de rapatrier les données à importer depuis des sources
externes ;
• et de vues pour se connecter à des sources dynamiques situées dans la même
BD
• Les sources de données peuvent être :
• Des fichiers CSV, des tables externes, d’autres types de fichiers (tableurs,
XML,…) ou données stockées sur d’autres BD
Sources de données
• Données sur le même serveur de BD
• Réaliser des vues pour accéder dynamiquement aux données sources
• Données dynamiques en fichier CSV
• Créer une table externe pour accéder dynamiquement aux fichiers
• Données statiques (ne demandant aucune mise à jour)
• Faire un export depuis la source en CSV et l’importer dans une table de la BDE
• Table externe
• Une table externe est une méthode d'accès sans copie des fichiers CSV
dynamiques, exactement comme s'il s'agissait d'une table de la BD
Architecture d’un ETL
BD de transformation (T)
• La BDT est une BD relationnelle ou relationnel-objet destinée à
implémenter la zone de transformation d'un ETL
• La zone T est donc composée :
• d'une BD dont le schéma correspond à un schéma transactionnel
représentant l'intégration des différentes sources
• d'une API permettant d'accéder aux données (et d'exécuter des
transformation)
Transformations
• Transformation simple
• Les transformations simples, typiquement qui ne nécessitent qu'un seul
enregistrement en entrée, seront effectuées directement et dynamiquement
par les fonctions de l'API (ou des fonctions appelées par celles-ci)
• Transformations complexes
• Les transformations plus complexes devront être réalisées par des procédures
exécutées en batch après le chargement de la BDT
• Elle produiront des données complémentaires stockées dans la BDT
Gestion des contraintes
• Les contraintes dans la zone T doivent être compatibles avec les
données sources afin de :
• Traiter 100% des données depuis la zone E (sans contrainte) vers la zone T
(avec contrainte) ;
• s'assurer le maximum d'information sur la nature de données
• Si les contraintes sont trop relâchées, il faudra faire des vérifications
inutiles pour contrôler des données, qui en fait avaient déjà les
propriétés souhaitées
• Si les contraintes sont trop fortes, les données ne passeront pas
entièrement
Gestion des contraintes
• Il est souhaitable de désactiver les contraintes sur la BDT pendant le
temps de chargement, afin d'accélérer cette procédure
• Notons que si les contraintes ne sont pas désactivées :
• à chaque ajout d'une ligne, le moteur de la base va devoir vérifier que cette
ligne respecte les contraintes ;
• de plus si les données ne sont pas chargées exactement dans le bon ordre,
des contraintes de type intégrité référentielle peuvent être temporairement
non validées
• Une fois le chargement terminé les contraintes seront réactivées afin
de vérifier que les méthodes de transformation ont fait correctement
leur travail et que les données respectent effectivement ces
contraintes
Chargement BDE->BDT
1. Désactivation des contraintes de la BDT
2. Suppression des index de la BDT
3. Copie des données de la BDE vers la BDT
4. Recréation des index
5. Réactivation des contraintes
6. Vérification que la réactivation des contraintes n'a pas rejeté de données
(100% des données sont passées de la BDE à la BDT)
7. Exécution des procédures de pré-traitement
8. Actualisation des vues matérialisées
9. Signalisation de la disponibilité de la zone T
Architecture d’un ETL
BD de chargement (L)
• La zone de chargement est en fait la BD en étoile ou en flocon qui
implémente le data warehouse et les data marts
• Elle reçoit une copie des données sous leur forme transformée (depuis la
zone T)
• Cette BD dimensionnelle peut être :
• directement exploitée pour effectuer des requêtes ;
• ou utilisée pour effectuer des exports vers des logiciels d'analyse spécialisés
Chargement BDT->DW
1. Désactivation des contraintes du DW
2. Suppression des index du DW
3. Chargement de chaque dimension du datawarehouse via l'API de la zone
T
4. Chargement des tables des faits du datawarehouse via l'API de la zone T
5. Recréation des index du DW
6. Réactivation des contraintes du DW
7. Vérification que la réactivation des contraintes n'a pas rejeté de données
(100% des données sont passées de la BDT au DW)
8. Signalisation de la disponibilité du DW
3. Éléments avancés pour l'ETL
• Gestion des erreurs
• Clés artificielles
• Éléments pour l'ETL incrémental
• Intégration des dimensions multi-sources
• Performance et maintenance
Gestion des erreurs
• En cas d'erreur, il y a trois approches possibles :
1. Arrêt du chargement, traitement de l'erreur (amélioration des traitements)
et reprise du chargement. Dans un processus incrémental, c'est en général
une mauvaise solution
2. Rejeter la donnée dans une table d'erreur
3. Laisser passer la donnée en prenant une décision par défaut et
journalisation pour vérification
Gestion des erreurs
• Les approches 2 et 3 ne pose pas de problème si :
• les problèmes sont très minoritaires
• répartis sur la population des faits
• traités au fur et à mesure
• Méthode « Rejeter »
• Créer une copie de la structure du DW pour accueillir les données rejetées
• Ajouter un espace de stockage des commentaires (raison du rejet...)
• Méthode « Laisser passer »
• Adopter une approche permettant de laisser systématiquement passer les
données (par exemple en ajoutant des valeurs d'erreur dans les dimensions)
• Journaliser dans une table temporaire les cas traités par défaut
Clés artificielles
• Introduire des clés artificielles pour identifier les dimensions :
• Dans la zone de transformation, ajouter à chaque dimension un attribut PK
• À chaque ajout d'un nouvel enregistrement dans une dimension, générer une
nouvelle valeur pour la clé artificielle dans PK
• Lors de l'ajout des dimensions dans le data warehouse utiliser une fonction
de conversion pour identifier les enregistrements
• Lors de l'ajout des faits, il faut substituer les clés étrangères
Clés artificielles
• Il faut mémoriser la correspondance entre la clé identifiant la
dimension dans le système transactionnel et la clé artificielle dans le
data warehouse
• Pour cela :
• soit les tables sont persistantes dans la zone de transformation
• soit les clés du système transactionnel doivent être conservées dans le DW
Remarques sur les ETL incrémentiels
• Dans un DW incrémental le système est mis à jour régulièrement par :
• l'ajout de nouveaux faits
• l’éventuel ajout ou mise à jour des dimensions
• Pour l’ajout de faits, il y a deux cas de figures :
• La source des faits est remplacée à chaque incrément
• Les nouveaux faits remplacent les anciens
• Les nouveaux faits sont ajoutés de la même source à chaque incrément
• Il faut une méthode qui permette de discriminer les faits déjà intégrés des nouveaux
Remarques sur les ETL incrémentiels
• L'ajout de dimensions résulte de :
• l'ajout de nouveaux faits,
• l'ajout des dimensions dans les sources transactionnelles
• Solution :
• soit l'on supprime et récrée la dimension
• soit l'on gère l'ajout des nouveaux enregistrements après les avoir identifiés
• Il faut adopter une stratégie pour la gestion des SCD
Remarques sur les ETL incrémentiels
• La mise à jour de dimension résulte de :
• l'ajout de nouveaux faits,
• la mise à jour des sources transactionnelles des dimensions
• Lorsque les dimensions ont plusieurs sources, il faut :
• Contrôler les doublons (dé-duplication)
• Conformer et/ou confronter les différentes sources
4. Choix d’un ETL
• Beaucoup de solutions d'ETL existent, et elles répondent à toutes les
demandes de performance et de portefeuille. Cependant, devant un
choix si diversifié, on se retrouve un peu perdu :
• Open Source ou payant,
• Solution intégrée ou indépendante,
• Sous-traitance ou développement
Choix d’un ETL
• Les éléments à prendre en compte dans le choix de votre ETL sont les
suivants :
• Taille de l'entreprise : S'il s'agit d'une multinationale on ira plus pour une solution
intégrée et complète. S’il s’agit d’une PME, on optera plutôt pour des solutions
payantes assurant un certain niveau de confort sans impliquer des mois de
développement
• Taille de la structure informatique : une entreprise avec une grande DSI pourra se
permettre d'opter pour une solution Open Source et la personnaliser selon les
besoins
• Culture d'entreprise : Si une entreprise à une culture Open Source, l'application
d'une solution payante risquera d’être rejetée
• Maturité des solutions : il existe des solutions qui fonctionnent bien et qui
bénéficient d’un bon retour d'expérience, c'est en général les plus chères (BO,
Oracle, SAP). Il existe d'autres solutions, moins matures, mais qui semblent offrir de
très bonnes performances (Microsoft). Enfin, il existe des solutions Open Source
Choix d’un ETL
• Donc un ETL peut être :
• développé en interne spécialement pour un projet directement dans un
langage bas niveau (Java, SQL, PL/SQL...)
• ou s'appuyer sur un ETL Open Source
• Talend Open Studio, Pentaho Data Integration, …
• ou s'appuyer sur un ETL propriétaire
• Oracle Warehouse Builder, Business Object Data Integrator, Informatica PowerCenter,
Cognos DecisionStream, Microsoft Integration Services, ...
Choix d’un ETL
• Les avantages offerts par un développement sont :
• L'homogénéité technologique et la disponibilité interne des compétences : les
équipes utilisent les langages qu'elles maîtrisent sans apprentissage et
médiation d'un outil tiers
• La flexibilité : tout est possible
• Le traitement des fichiers plats (hors BD) peut être plus simples et plus
performant avec des langages proches des systèmes
Choix d’un ETL
• Les avantages offerts par les outils ETL sont :
• D'offrir une représentation graphique des flux et opérations
• De faciliter la maintenance et l'évolution de l'ETL
• D'intégrer la gestion des métadonnées
• D'intégrer la gestion des erreurs
• De disposer d'API dédiées (connexion, import/export...) d'accès aux données
(CSV, BD, XML...)