Transformez le chaos en profit : Comprendre le processus ETL

L'une des erreurs les plus critiques qui empêchent les entreprises de fournir des informations vitales à la prise de décision aux utilisateurs professionnels est l'absence de données fiables provenant d'une ou plusieurs sources de données, rassemblées en un seul endroit, organisées et prêtes à être utilisées.

Imaginez ce scénario : vous êtes le responsable informatique d'une entreprise d'étuis pour téléphones portables avec des points de vente partout aux États-Unis. Votre conseil d'administration a du mal à prendre des décisions basées sur les ventes car l'information est disponible mais elle se trouve dans des lieux et des formats différents. Un des directeurs vous demande de générer un tableau de bord avec les informations de vente de tous les points de vente à présenter au prochain conseil d'administration.

Vous savez déjà qu'il est presque impossible de consolider les informations en raison des différents formats et structures. Certains des kiosques de vente au détail utilisent encore un système propriétaire dans une base de données Oracle. Les grands magasins utilisent un nouveau système Salesforce. Les kiosques les plus récents qui ont commencé à fonctionner pendant la transition du système ont des feuilles de calcul partagées utilisées pour calculer les ventes.

Comment rassemblerez-vous toutes les données provenant de différents emplacements, formats et structures dans une base de données unique accessible pour la génération de tableaux de bord ?

Fondamentaux de l'ETL

ETL signifie Extraire, Transformer et Charger . ETL est un groupe de processus conçus pour transformer ce stockage complexe de données en un processus organisé, fiable et reproductible pour aider votre entreprise à générer plus de ventes avec les données dont vous disposez déjà.

Dans notre cas, nous allons recevoir des données d'une base de données Oracle (la plupart des kiosques), de Salesforce (magasins) et de feuilles de calcul (kiosques plus récents), extraire les données, les transformer si nécessaire et les charger dans une seule base de données d'entrepôt de données pour être accessibles par des outils de reporting et utilisés pour générer des tableaux de bord et des tableaux de bord.

Plongeons-nous dans les trois étapes d'ETL pour élaborer sur le processus.

Extraction

L'extraction est le processus de réception de données provenant de sources uniques ou multiples. Les sources peuvent avoir différents formats et structures, tels que des documents, des feuilles de calcul, des fichiers CSV, des fichiers plats, des bases de données relationnelles telles qu'Oracle, MySQL, SQL Server, des bases de données non relationnelles, etc.

Il existe deux principaux types d'extraction : complète et partielle .

• L'extraction complète est utilisée pour les extractions initiales ou lorsque la quantité de données et par conséquent le temps d'extraction sont acceptables.
• L'extraction partielle est recommandée lorsqu'il n'est pas nécessaire d'extraire à nouveau toutes les données ou que la quantité de données est suffisamment importante pour rendre impossible une extraction complète. Dans une extraction partielle, seules les données mises à jour ou nouvelles seront extraites.

En plus de ces aspects, d'autres considérations sont nécessaires lors du choix entre une extraction complète ou partielle, et je souhaite en décrire une : la disponibilité et l'intégrité des données .

Cela signifie que seules les transactions terminées seront prises en compte pour l'extraction, éliminant ainsi les données susceptibles de provoquer un défaut d'intégrité. Par exemple, un test en ligne pour identifier les connaissances d'un ingénieur avec 10 questions. Si un ingénieur est au milieu du test et a répondu à quelques questions mais n'a pas encore terminé, le processus d'extraction ne peut pas lire les questions des tests inachevés. Cela pourrait provoquer un défaut d'intégrité.

Transformation

Après avoir extrait les données, nous pouvons commencer le processus de transformation : nettoyer, manipuler et transformer les données selon des règles métier et des critères techniques pour maintenir un niveau acceptable de qualité des données.

Selon un certain nombre de facteurs, l'utilisation d'une zone de rassemblement peut être nécessaire. Une zone de staging est un espace de stockage intermédiaire utilisé pour stocker temporairement des données extraites de sources de données à transformer.

Dans certains projets, normalement ceux avec une petite quantité de données, il n'est pas nécessaire d'utiliser des zones de transit, mais la plupart des projets l'utilisent.

Plusieurs tâches sont effectuées pendant la phase de transformation :

• Sélection : critères de sélection des données extraites. La sélection peut être faite pendant la phase d'extraction, pendant la phase de transformation, ou dans les deux phases.
• Intégration : la combinaison des données de la phase d'extraction à la zone de staging. Cette combinaison signifie ajouter, supprimer et mettre à jour des données dans la zone de transfert en fonction des données extraites.
• Jointures : utilisées pour joindre des données extraites, similaires aux jointures SQL (inner join, left join, right join, full join, etc.)
• Nettoyage ou nettoyage : supprime les données incohérentes ou non valides, ou les données contenant des erreurs pour améliorer la qualité des données. Travailler avec plusieurs sources de données augmente les risques d'avoir des problèmes de données qui doivent être nettoyés, tels que :
  • Intégrité référentielle (client avec catégorie inexistante)
  • Valeurs manquantes (client sans ID)
  • Unicité (plus d'une personne avec le même SSN)
  • Faute d'orthographe (Sun Diego, Cannada, L.Angeles)
  • Valeurs contradictoires (Alex ddn 04.27.1974, Alex ddn 04.14.2000)
  • et plein d'autres
• Résumés : résumer des ensembles de données pour une utilisation ultérieure
• Agrégations : données compilées et résumées en groupes
• Consolidations : données provenant de plusieurs sources ou structures consolidées dans un seul ensemble de données

Voici quelques types de transformation courants :

• Supprimer les données en double
• Fractionner et fusionner
• Conversions (date, heure, masques de nombres, mesures)
• Encodage (Homme à M)
• Calculs (item_value = unit_Price * quantité)
• Génération de clé

Chargement

Enfin et surtout, le processus final dans ETL consiste à charger les données dans la destination. Le chargement est l'acte d'insérer des données transformées (à partir d'une zone de préparation ou non) dans le référentiel, normalement une base de données d'entrepôt de données.

Il existe trois principaux types de données de chargement : complet ou initial, incrémentiel et actualisé.

• Complet ou initial signifie un chargement complet de données extraites et transformées. Toutes les données de la zone de préparation seront chargées dans la destination finale pour être préparées pour les utilisateurs professionnels.
• Le chargement incrémentiel est le processus de comparaison des données transformées avec les données de la destination finale et de chargement uniquement des nouvelles données. La charge incrémentielle peut être utilisée conjointement avec la charge d'actualisation, expliquée ci-dessous.
• Le chargement d'actualisation est le processus de mise à jour des données dans la destination finale pour refléter les modifications apportées à la source d'origine. Une actualisation peut être complète ou incrémentielle.

En résumé, chaque entreprise, quelle que soit sa taille, peut utiliser les processus ETL pour intégrer des informations déjà existantes et générer une richesse encore plus grande d'informations pour la prise de décision, transformant des données qui auparavant ne pouvaient pas être utilisées en une nouvelle source de revenus.

Essai

Les tests sont l'une des phases les plus importantes de l'ETL, et pourtant l'une des plus négligées aussi.

Transformer des données provenant de différentes sources et structures et les charger dans un entrepôt de données est très complexe et peut générer des erreurs. Les erreurs les plus courantes ont été décrites dans la phase de transformation ci-dessus.

L'exactitude des données est la clé du succès, tandis que l'inexactitude est une recette pour le désastre. Par conséquent, les professionnels ETL ont pour mission de garantir l'intégrité des données tout au long du processus. Après chaque phase, un test doit être effectué. Qu'il s'agisse d'extraire des données d'une source unique ou de plusieurs sources, les données doivent être vérifiées pour s'assurer qu'il n'y a pas d'erreurs.

La même chose doit être faite après toute transformation. Par exemple, lors de la synthèse des données pendant la phase de transformation, les données doivent être vérifiées pour garantir qu'aucune donnée n'a été perdue et que les sommes sont correctes.

Après avoir chargé les données transformées dans l'entrepôt de données, le processus de test doit être appliqué à nouveau. Les données chargées doivent être comparées aux données transformées, puis aux données extraites.

Dans notre exemple d'entreprise de cas de téléphone portable, nous travaillons avec trois sources différentes (base de données propriétaire Oracle, Salesforce et feuilles de calcul) et différents formats. La phase de test peut utiliser des exemples de données provenant des sources d'origine et les comparer avec les données qui se trouvent dans la zone de préparation pour garantir que l'extraction s'est déroulée sans erreur.

Les données de l'échantillon, qui dans ce cas peuvent être des informations sur les ventes de trois emplacements différents (magasins, anciens kiosques, nouveaux kiosques), doivent être comparées à la source d'origine. Les différences, le cas échéant, doivent être analysées pour voir si elles sont acceptables ou s'il s'agit d'erreurs.

Si des erreurs sont trouvées, elles doivent être corrigées, et il y a quelques décisions à prendre si vous devez les corriger : Les données d'origine doivent-elles être modifiées ? Est-il possible de le faire ? Si les erreurs ne peuvent pas être corrigées dans la source d'origine, peuvent-elles être corrigées par une transformation ?

Dans certains cas, les données erronées doivent être éliminées et une alerte déclenchée pour informer les responsables.

Quelques exemples de test :

• Les données doivent être validées
• Qualité des données
• Performance
• Règles de données
• La modélisation des données

Enregistrement

La journalisation des processus ETL est la garantie clé que vous disposez de systèmes maintenables et faciles à réparer.

Un ETL avec le processus de journalisation correct est important pour maintenir l'ensemble de l'opération ETL dans un état d'amélioration constante, aidant l'équipe à gérer les bogues et les problèmes avec les sources de données, les formats de données, les transformations, les destinations, etc.

Un processus de journalisation robuste aide les équipes à gagner du temps en leur permettant d'identifier les problèmes plus rapidement et plus facilement, et les ingénieurs principaux ont besoin de moins de temps pour identifier directement le problème. Parfois, des erreurs se produisent au milieu de l'extraction de tonnes de données, et sans journal, identifier le problème est difficile, parfois presque impossible. Sans journaux, l'ensemble du processus doit être réexécuté. À l'aide des journaux, l'équipe peut rapidement identifier le fichier et la ligne à l'origine du problème et ne peut corriger que ces données.

Le seul cas où je peux imaginer que les journaux ne sont pas si importants est celui des très petits systèmes non automatisés, où le processus s'exécute manuellement et où une petite quantité de données peut être surveillée manuellement.

Les journaux améliorent l'automatisation. Les processus ETL avec une grande quantité de données qui s'exécutent automatiquement ont besoin de systèmes de journalisation. S'ils sont bien planifiés et exécutés, tous les efforts déployés pour créer un système de journalisation rapporteront des dividendes sous la forme d'une identification plus rapide des erreurs, de données plus fiables et des points d'amélioration trouvés dans les fichiers journaux.

La création d'un système de journalisation comporte trois étapes principales : générer, archiver et analyser les fichiers .

• Générer est le processus de documentation de ce qui se passe pendant l'exécution des pipelines ETL : le démarrage du processus, le fichier ou la table en cours d'extraction, les données enregistrées dans la zone de staging, les messages d'erreur, etc. Toutes les informations importantes qui peuvent aider les ingénieurs doivent être enregistrées. Alerte : Faites attention à ne pas générer trop d'informations qui ne feront que consommer du temps et de l'espace et ne seront d'aucune utilité.
• Archiver les données du journal signifie conserver un historique des exécutions passées pour rechercher des scénarios passés afin d'identifier les erreurs ou de comparer avec le scénario actuel à la recherche d'améliorations. Il est important de vérifier la pertinence d'un point précis de l'historique à sauvegarder - les données d'il y a longtemps, où la structure a changé plusieurs fois, ne valent pas la peine d'être conservées.
• Analysez . L'analyse des journaux est d'une importance cruciale. Stocker des tonnes de données qui ne sont pas analysées n'a aucun sens. Cela coûte juste du temps et de l'argent pour générer et stocker les données. L'analyse des journaux est importante non seulement pour aider à rechercher les erreurs, mais aussi pour identifier les points d'amélioration et améliorer la qualité globale des données.

Performance

Les processus ETL peuvent fonctionner avec des tonnes de données et peuvent coûter cher, à la fois en termes de temps consacré à leur configuration et de ressources de calcul nécessaires pour traiter les données. Lors de la planification d'une intégration, les ingénieurs doivent garder à l'esprit la nécessité d'utiliser toutes les données. La meilleure chose à faire est de travailler avec le minimum de données pour atteindre les objectifs, plutôt que de perdre du temps et de l'argent à simplement migrer des données inutiles. Gardez également à l'esprit que les quantités de données ont tendance à augmenter avec le temps, alors essayez de tenir compte de vos besoins futurs.

Quel effort devrait être consacré à la conception d'un système de journalisation ?

Cela dépend d'un certain nombre de facteurs différents tels que la quantité et la fréquence des données à traiter. Les petits systèmes avec peu de données peuvent être gérés manuellement, sans qu'il soit nécessaire d'investir dans des systèmes de journalisation avancés.

Les entreprises disposant d'une grande quantité de données, de nombreux processus différents, de sources et de formats de données différents et de pipelines complexes doivent investir dans la création de systèmes de journalisation. Comme nous l'avons déjà mentionné, un bon système de journalisation peut économiser beaucoup de temps et d'argent.

Pour une approche plus évidente, quelle que soit la taille de l'entreprise, la quantité de données et la fréquence d'intégration, un processus ETL doit être rentable . Un investissement en temps, en argent et en ressources techniques doit générer des rendements - comme l'a dit l'économiste Milton Friedman : « Il n'y a pas de repas gratuit ».

En résumé, un processus ETL peut aider les entreprises à augmenter leurs bénéfices avec des données qu'elles possèdent déjà mais qui ne sont pas utilisées de la bonne manière. Les pipelines ETL peuvent intégrer différents systèmes, ces feuilles de calcul contenant des informations importantes et d'autres parties de données réparties dans différents départements et succursales, permettant aux organisations de tirer le meilleur parti de leurs données.