ETL ou Extract Transform and Load

Définition du processus ETL pour Extract, Transform and Load utilisé dans les projets décisionnels.

En informatique, ETL signifie Extract Transform and Load, en Français Extraire Transformer et Charger (ETC). C’est un ensemble de processus pour le chargement des bases de données décisionnelles, aussi appelées entrepôts de données. Le but est de préparer et transformer les données pour leur analyse. Les données sont souvent stockées en fin de cycle dans des bases de données multidimensionnelles.

Table of Contents

1. Le processus ETL ou Extract Transform and Load

Ces processus consistent à extraire des données provenant de sources extérieures et diverses. Les systèmes transactionnels produisent ces données dans des bases de données relationnelles. Les transformer pour répondre aux besoins en Reporting opérationnels ou décisionnels. Ensuite, les charger dans l’entrepôt de données, ou plus précisément dans un magasin de données.

2. L’extraction des données dans le processus ETL

La première partie du processus ETL consiste à extraire les données des systèmes sources. C’est l’aspect le plus technique de l’ETL, par ce qu’il faut établir des connexions vers des systèmes tier. Et exporter des volumes parfois importants de données sous forme de fichiers textes par exemple.

2.1 Des sources et des formats de données hétérogènes

Les formats de données communs sont les sources de données relationnelles et des fichiers plats. Mais peuvent inclure des structures de bases de données non relationnelles telles que le système de gestion de l’information (SGI). Ou encore d’autres structures comme la méthode Virtual Storage Access Mode (VSAM) ou Indexed Sequential Access Method (ISAM). Elles incluent aussi toute sorte de données : Web, XML, Excel, API, fichiers plats avec format propriétaire comme l’AS400, etc.

La plupart des projets Data Warehouse consistent à consolider les données provenant de systèmes sources hétérogènes, donc différents. Chaque système peut également utiliser une organisation différente des données. Comme les règles de nommages, le format des données et surtout les règles de gestion du métier.

2.2 Une copie conforme des données sources

En général, l’objectif de la phase d’extraction consiste à convertir les données dans un format unique. Format approprié pour un traitement de transformation.

Ce format de stockage est très similaire à la source, on parle de « 1 pour 1 ». C’est à dire que l’on souhaite conserver une image des données sources telles qu’elles arrivent. On y applique souvent un système intelligent d’historisation et de journalisation pour connaître les données chargées et quand. Il permet de reprendre des chargements et de gérer les erreurs de transfert de données depuis les systèmes sources.

3. La deuxième étape est la transformation des données

L’étape de transformation applique une série de règles appelées règles de gestion. Pour certaines sources de données, il faudra une manipulation très légère, voire aucune, sur les données.

3.1 Le but de l’ETL est d’appliquer des règles de gestion

Dans d’autres cas, un ou plusieurs des types de transformation suivants peuvent être nécessaires pour répondre aux besoins technico-commerciaux et de la base de données cible:

Sélectionner uniquement certaines colonnes à charger.
Traduire les valeurs codées, par exemple A2017 pour année 2017 deviendra 2017 ou vice-versa.
Joindre les données provenant de sources multiples, par exemple rattacher le nom du client au numéro de sa carte fidélité qui sont dans 2 tables différentes.
L’agrégation, avec un cumul du total des ventes pour chaque magasin par exemple.
Transposition ou pivot, c’est à dire basculer plusieurs colonnes en plusieurs lignes ou vice versa.
Fractionnement d’une colonne en plusieurs colonnes.
La suppression des données redondantes, par exemple quand l’adresse du client est stockée sur toutes les lignes des ventes. Dans la table « Ventes », alors on conservera le numéro de client uniquement. L’adresse du client sera stockée de manière unique dans la table « Clients ».
Rechercher et valider les données pertinentes à partir des tables ou des fichiers référentiels pour les dimensions à variation lente.
L’application de toute forme de validation de données simple ou complexe.

3.2 La gestion des erreurs et des reprises de chargement

Si la validation échoue, cela peut entraîner un rejet complet ou partiel des données. Et donc aucune, certaines ou toutes les données seront intégrées au prochain chargement. Plusieurs méthodes de conception de l’entrepôt de données existent, qui impactent les règles de gestion pour les exceptions.

La plupart des transformations ci-dessus peuvent entraîner des exceptions, par exemple, quand une traduction des codes analyse un code inconnu dans les données extraites.

Idéalement, les erreurs sont remontées et corrigées dans les systèmes opérationnels sources, en accord avec les équipes MDM.

SGBD ou Système de Gestion de Base de Données

4. Le chargement des données dans le processus ETL

La phase de chargement constitue la dernière étape, elle intègre les données dans l’entrepôt de données (DWH pour Data Warehouse). Selon les exigences de l’organisation, ce processus est très variable. Certains entrepôts de données peuvent remplacer les informations existantes avec des informations données cumulatives. Généralement extraites et mises à jour sur une base quotidienne, hebdomadaire ou mensuelle.

D’autres types de DW (ou même d’autres parties du même DW) ajoutent des données sous forme historisée, par exemple, toutes les heures.

4.1 Des méthodes pour gérer l’historique et les changements

Pour le comprendre, considérons un entrepôt de données qui stocke les données de ventes de l’année dernière. Ensuite, le système ETL, Extract Transform and Load écrase les données qui ont plus d’un an avec des données plus récentes. L’historisation ou non et la gestion des nouvelles lignes, montants écrasés ou additionnés, dépend des exigences métiers de l’organisation.

Les contraintes du schéma de la base de données contribuent à la performance et à la qualité globale des données et du processus ETL. Ce sont par exemple, l’unicité, l’intégrité référentielle et les champs de métadonnées, nécessaires pour une description des données. On parle alors de Framework ETL (Extract Transform and Load).

4.2 La qualité des données et la gestion du référentiel sont clefs

Par exemple, une institution financière pourrait avoir des informations sur un client dans plusieurs départements. Chaque département peut disposer d’informations que le client a énumérées d’une manière différente.

Le département relation client liste le client par son nom, alors que le service comptabilité établit une liste par numéro de client. Un ETL permet de regrouper toutes ces données et les regrouper en une présentation uniforme, dans un entrepôt de données.

C’est le domaine de la qualité des données ou Master Data Management (MDM) qui traite spécifiquement ces problématiques dans un cycle d’amélioration continue.

Liste, non exhaustive, des outils ETL connus: