Chapitre 3 - ETL - PDF

Entrepôt de données Data warehouse– Entrepôt de données Cheik OUEDRAOGO  La puissance de la connaissance 2 25/10/2024 Data wareho...

Entrepôt de données Data warehouse– Entrepôt de données Cheik OUEDRAOGO  La puissance de la connaissance 2 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) Programme I. Chapitre 1 : Introduction entrepôt de données II. Chapitre 2 : Modélisation des données III. Chapitre 3 : ETL 3 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) Programme III. Chapitre 3 : ETL I. Présentation de l’outil ETL II. La préparation des données III. Les différents ETL du marché 4 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL Rappel conception des entrepôts de données L’approche Inmon 5 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL Rappel conception des entrepôts de données Etapes de conception d’un entrepôt de données 1. Collecte d'informations 2. Définir les sources de données 3. Choix de l’architecture de l’entrepôt de données 4. Planification et développement de l’ETL 5. Conception d'un modèle de données et choix d'un schéma 6. Construire, tester et déployer 7. Maintenance et surveillance 6 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL Rappel conception des entrepôts de données Planification et développement de l'ETL Les processus ETL constituent l'épine dorsale de tout entrepôt de données, car ils permettent de transférer les données de leurs sources vers un format adapté à l'analyse. Compte tenu de son rôle central, une stratégie ETL bien planifiée est indispensable. Voici comment commencer : ❑ Identifier les outils. ❑ Cartographier vos données ❑ Décider de la logique de transformation ❑ Concevoir le flux de travail ETL ❑ Attribuer des ressources matérielles ❑ Passer au développement et aux tests 7 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL ❑ Un ETL est un outil complet permettant de récupérer, nettoyer et enfin intégrer des données provenant de différentes sources ❑ ETL : ❑ Extract ❑ Transform ❑ Load ❑ Il y a donc trois étapes majeures dans le fonctionnement d’un ETL 8 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Extract ❑ Récupération (sans modification) des données, parmi un large choix de types de sources différentes : ❑ Bases de données relationnelles (SQL Server, Oracle, MySQL, etc.) ❑ Il s’agit principalement de bases de données opérationnelles (ERP, CRM, etc.) ❑ Fichiers plats (.txt,.xls,.xlsx, etc.) ❑ Base de données Microsoft Access ❑ … ❑ Cette étape est assez peu complexe car on ne fait que récupérer les données de façon brute ❑ Le temps de traitement dépendra principalement du volume de données à extraire 9 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Transform : ❑ L’étape la plus importante et la plus complexe ❑ Elle consiste à « traiter » les données fraichement extraites ❑ Nettoyage, consolidation, vérification, formatage, etc. ❑ Le but des transformations est de rendre les données « propres » et cohérentes, afin qu’elles deviennent facilement exploitables par la suite ❑ Les données du DWH serviront de source pour les différents rapports / dashboard et cubes ❑ On doit donc s’assurer que ces données constituent une source fiable 10 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Quelques exemples de transformations : ❑ Révision des formats : ❑ Ex : Change le type et/ou la longueur de certains champs ❑ Dédoublonnage des données ❑ Ex : Un même client peut se trouver dans deux sources différentes ❑ Reformater certaines données dans un format plus adéquat pour le métier ❑ Ex : n° de téléphone « national » qui devient un n° de téléphone international 11 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Quelques exemples de transformations : ❑ Séparer des données : ❑ Ex : distinguer « Prénom » et « Nom » d’un champ « Nom complet » ❑ Fusionner ou rassembler des champs ❑ Ex1 : les informations d’un produit peuvent se trouver dans des sources séparées ❑ Source A : Code, Nom, Description ❑ Source B : Lieu de fabrication, Fabricant, Coût de production ❑ Source C : Catégorie ❑ Ex2 : l’adresse d’un client, séparée en plusieurs champs (numéro de rue, nom de rue, code postal, etc.), peut-être concaténée dans un seul champ (adresse) 12 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Quelques exemples de transformations : ❑ Traduire des noms « diminutifs » : ❑ Ex : AC = Actif, IN = Inactif, SU = Suspendu ❑ Consolider des données issues de sources multiples : ❑ Ex : ❑ « H » / « Male » / « 0 » devient « Homme » ❑ « F » / « Female » / « 1 » devient « Femme » 13 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Quelques exemples de transformations : ❑ Précalculer des données : ❑ Ex : Profit = prix - coût ❑ Conversion d’unité / de devise : ❑ Ex : Miles -> Kilomètres, Euro -> Dollars ❑ Appliquer des règles définies par le métier ❑ Ex1 : Rattacher les produits « VTT » et « BMX » à la famille « Vélos » ❑ Ex2 : Ne pas intégrer les lignes de ventes concernant des « Gants » vendus en Espagne 14 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Transform ❑ Il s’agit donc de l’étape la plus couteuse en termes de temps et de ressources ❑ En fonction des transformations nécessaires et surtout de la volumétrie à traiter, cette étape peut prendre plusieurs heures à s’exécuter ! ❑ Le nombre de transformations pouvant être généralement assez conséquent il existe des étapes « intermédiaires » au sein même du processus de transformation ❑ Nous aborderons ce point plus tard dans ce cours 15 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL Load ❑ Chargement des données modifiées dans une ou plusieurs destinations ❑ Généralement, on charge les données dans un datawarehouse… ❑ … mais l’ETL permet également de les charger dans d’autres destinations, comme un fichier Excel par exemple ! ❑ De même que pour l’extraction, le chargement se fait sans aucune transformation ❑ Les données ont déjà été traitées à l’étape précédente ❑ Le temps de traitement de cette étape dépendra donc lui aussi du volume de données à intégrer 16 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL ❑ L’ETL est un outil complet, permettant de gérer toutes les étapes d’intégration (extraction, transformation, chargement) ❑ Il dispose de nombreux avantages : ❑ Une multitude de transformations disponibles et facile à mettre en place ❑ De nombreux connecteurs proposé nativement pour se connecter à différents types de sources / destinations ❑ Possibilité de séparer les différents travaux entre eux, ou à l’inverse de paralléliser certaines tâches ❑ Capacité à exécuter d’autres types de tâches (envoi de mails, interaction avec un serveur FTP, etc.) 17 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL I. Présentation de l’outil ETL ❑ Un ETL est un outil graphique, facilitant la mise en place et la compréhension des flux d’intégration de données ❑ Les différentes étapes d’alimentation seront séparées les unes des autres, afin de permettre une meilleure maintenance du projet ❑ Le développeur doit s’efforcer de respecter certaines règles/normes afin de rendre le projet ETL le plus compréhensible possible : ❑ Convention de nommages des tâches et composants ❑ Séparation des étapes majeures de l’alimentation ❑ Documentation ou commentaire sur les parties les plus sensibles 18 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données ❑ Lors de l’intégration des données dans le datawarehouse, il est généralement recommandé de passer par plusieurs étapes ❑ Cela permet, entre autres, de réaliser toutes les vérifications et transformation dans un ordre précis et logique ❑ Les données doivent passer par trois zones : ❑ STA : Staging area ❑ ODS : Operational Data Store ❑ DWH : Datawarehouse 19 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « STA » ❑ La zone de préparation « STA » est une zone dans laquelle seront stockées les données issues des différentes sources ❑ Les données sources sont extraites de façon « brute » ❑ On n’applique aucune transformation, les données sont importées telles quelles ❑ En général, on crée dans la STA une table par source ❑ Une table source = Une table STA ❑ Un fichier plat source = Une table STA ❑ Une feuille Excel = Une table STA ❑ etc. 20 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « STA » ❑ L’existence de cette zone apporte de nombreux avantages : ❑ Rassembler les données provenant de toutes les sources différentes dans un seul et même endroit ❑ Cela rendra les transformations à venir plus faciles à réaliser ❑ Extraire les données depuis les sources le plus rapidement possible, car aucune transformation n’est appliquée aux données ❑ Cela permet notamment de ne pas ou peu impacter les bases de données « sources » par cette opération car très rapide ❑ C’est préférable car ces BDD « sources » alimentent souvent des systèmes de production, nous devons donc faire le maximum pour ne pas les ralentir avec nos extractions ! ❑ Objectif « zéro impact » sur la production 21 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « STA » ❑ Les tables de la STA contiendront donc un « copier/coller » des données issues des sources ❑ Nous ne nous occupons même pas du type des données ! ❑ On conserve le même type que celui du champ source ❑ Si pas de type spécifique défini dans la source, on choisira le type VARCHAR(255) ❑ Voire VARCHAR(500) ou VARCHAR(1000) si la donnée le nécessite ❑ On s’assure simplement de ne jamais tronquer la donnée ! 22 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « STA » ❑ Exemples : ❑ Ex1 : Si la colonne « Date_naissance » provient d’un fichier CSV (pas de type) ❑ La colonne de la table de STA sera de type VARCHAR(255) ❑ Ex2 : Si la colonne « Nom_famille » provient d’un fichier Excel et que la longueur est fixée à 50 ❑ La colonne de la table de STA sera de type « VARCHAR(50) » ❑ Ex3 : Si la colonne « Age » provient d’une table d’une base de données SQL, dont le type est défini à « INT » ❑ La colonne de la table de STA sera de type « INT » 23 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « STA » ❑ La base de données STA sert uniquement de zone d’extraction de données ❑ On se calque au maximum à la donnée source ❑ On ne s’occupe pas, pour le moment, de savoir si la donnée insérée est correcte ❑ On n’agit pas, même en cas d’incohérence sur la donnée ! ❑ Ex : la valeur « 122 » dans la colonne « Date_naissance » ❑ Ce n’est qu’à l’étape suivante qu’on s’intéressera réellement aux types de données (passage de STA vers ODS) 24 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « ODS » ❑ La zone de préparation « ODS » est une zone dans laquelle toutes les transformations vont avoir lieu avant l’intégration des données dans le datawarehouse ❑ C’est généralement au niveau de la zone ODS que la plupart des opérations d’un chargement BI vont avoir lieu ❑ Il existe une multitude de transformations possibles, que nous aborderons un peu plus tard dans le cours 25 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « ODS » ❑ La zone ODS est alimentée grâce aux données stockées dans la zone STA ❑ Lors du transfert des données depuis la zone STA vers la zone ODS, on opère une première transformation : ❑ La vérification du typage des données 26 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « ODS » ❑ Transfert de données STA vers ODS : ❑ On s’intéresse au type des données sources ❑ On vérifie, pour chaque ligne et chaque colonne, si la donnée est cohérente ❑ Ex1 : Valeurs de la colonne « Date_naissance » bien au format « DATE » ? ❑ Ex2 : Valeurs de la colonne « Salaire » bien au format numérique ? 27 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « ODS » ❑ Transfert de données STA vers ODS : ❑ En cas de problème (données de mauvais type), les lignes concernées sont rejetées et envoyées dans une table de rejet ❑ On parle alors de « table de rejets techniques » ❑ Il s’agit d’une table dans laquelle on recense toutes les erreurs tracées, ❑ On indique la source de la donnée, la date et heure de l’erreur constatée, etc. ❑ Cette table sera utilisée pour savoir ce qui n’a pas été lors du dernier chargement ❑ Ces vérifications liées au typage des données constituent une étape qu’on qualifie de « Vérifications techniques » 28 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « ODS » ❑ Une fois cette première étape de vérification terminée, on passe à la partie la plus importante du projet ETL : les transformations de données ❑ Il existe une multitude de transformations possibles que l’on peut appliquer à nos flux de données ❑ C’est cette étape qui prend le plus de temps lors du chargement du datawarehouse 29 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Parmi les nombreuses transformations possibles, on notera par exemple : ❑ La suppression de données en double ❑ Le reformatage de données ❑ Le changement de type ❑ La création de nouvelles colonnes calculées ❑ L’union de jeux de données ❑ L’agrégation de données ❑ L’application de fonctions diverses (UPPER, ROUND, etc.) ❑ etc. 30 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Une fois les données transformées, elles vont pouvoir être chargées dans la zone « DWH », qui correspond au Datawarehouse final ❑ Cependant, le passage des données depuis ODS vers DWH ne peut pas se faire sans une ultime vérification et pas des moindre : l’intégrité référentielle des données ❑ Il s’agit ici de s’assurer que toutes les données de faits peuvent être reliées à une dimension 31 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Pour mieux comprendre, prenons un exemple ❑ Imaginons un datawarehouse très simple composé d’une table de faits de ventes et de quelques tables de dimensions (Clients, Produits, Magasins, etc.) ❑ L’étape de vérification du passage ODS -> DWH va permettre de s’assurer que les données insérées dans la table de faits sont cohérentes par rapport aux données présentes dans la table de dimension ❑ En d’autres termes, on va s’assurer qu’on ne se retrouvera pas dans le DWH avec des ventes pour lesquelles, par exemple, le produit n’existe pas dans la table de dimension « Produits » 32 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Voici le déroulé « classique » de l’insertion des données dans le DWH ❑ 1) Les données référentielles (clients, produits, etc.) sont récupérées depuis l’ODS et insérées dans les tables de dimensions ❑ 2) Une fois les dimensions alimentées, les données de vente sont prêtes à être insérées dans la table de faits ❑ Chaque ligne vente est traitée individuellement ❑ Pour chaque ligne, on s’assure que la donnée de vente est « cohérente » ❑ Concrètement, on va vérifier que : ❑ Le client référencé dans la vente existe dans la table de dimension « Dim_Clients » ❑ Le produit référencé dans la vente existe dans la table de dimension « Dim_Produits » ❑ Le magasin référencé dans la vente existe dans la table de dimension « Dim_Magasins » ❑ etc. 33 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Cette vérification lors du passage ODS -> DWH est très importante car elle permet de se retrouver avec des données cohérentes dans le datawarehouse ❑ Comment affirmer qu’un datawarehouse est « propre » s’il dispose de lignes de ventes pour lesquelles certaines d’entre elles ne correspondent à aucun client par exemple ? ❑ En effet, de par le fait que les données sources proviennent de différents endroits, il se peut que certaines incohérences existent et qu’on se retrouve par endroit avec des valeurs manquantes ❑ On peut imaginer par exemple que les données de ventes soient issues d’une base de données, et que les données des clients proviennent d’un fichier Excel alimenté quotidiennement par un employé ❑ L’employé peut oublier d’entrer certains clients dans son fichier, ce qui créerait plus tard une incohérence dans le DWH 34 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Il est donc possible de rencontrer certaines valeurs « référentielles » dans les lignes de faits qu’on ne retrouve pas dans les dimensions ❑ Cette situation peut être traitée de deux façon différentes : ❑ On peut choisir d’intégrer tout de même la ligne de faits ❑ On assignera alors la valeur « N/A » pour les champs qui posent problème ❑ On peut aussi choisir de rejeter les lignes de faits concernées ❑ On parlera alors de « rejets fonctionnels » ❑ Les lignes concernées seront insérées dans une table de rejets fonctionnels ❑ Cette table sera consultable par la personne en charge de la maintenance des flux pour comprendre quelles lignes n’ont pas été intégrées et pourquoi 35 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL II. La préparation des données La zone « DWH » ❑ Attention : dans un flux d’alimentation de dimension, on ne procède jamais à la suppression de lignes (DELETE) ❑ En effet, un datawarehouse permet l’historisation des données ❑ Si un client n’existe plus dans un système source, cela ne veut pas dire qu’il ne nous servira plus dans le datawarehouse ❑ Il y a potentiellement des lignes de ventes dans la table de ventes « F_SALES » qui lui sont associées ! ❑ De ce fait, on ne supprimera aucune ligne de la dimension, afin de conserver la cohérence des données 36 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL III. Les différents ETL du marché Les outils propriétaires ❑ SQL Server Integration Services (SSIS) ❑ Oracle Data Integrator ❑ IBM Datastage ❑ Informatica 37 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL III. Les différents ETL du marché Les outils propriétaires ❑ SAP Business Objects ❑ SAS Data Integration Studio 38 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL III. Les différents ETL du marché Les outils non propriétaires ❑ Talend Data Integration ❑ Pentaho Data Integration ❑ CloverDX ❑ Singer 39 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO Licence L3 – Entrepôt de données (DW) III. Chapitre 3 : ETL Références Introduction à l’Informatique Décisionnelle et aux entrepôts de données : Bernard ESPINASSE - Professeur à Aix-Marseille Université (AMU) Ecole Polytechnique Universitaire de Marseille. Ingénierie des Systèmes d’Information : Professeur à Aix-Marseille Université (AMU) Ecole Polytechnique Universitaire de Marseille. Analyse de données : Polytech’Marseille -Patrice Bellot 40 25/10/2024 Data warehouse– Entrepôt de données Cheik OUEDRAOGO