ETL signifie Extraire, Transformer et Charger. C'est un concept important dans les systèmes d'entreposage de données.Extraction signifie l'extraction de données à partir de différentes sources de données telles que des systèmes transactionnels ou des applications. Transformationsignifie appliquer les règles de conversion sur les données afin qu'elles deviennent adaptées au reporting analytique. leloading Le processus implique le déplacement des données dans le système cible, normalement un entrepôt de données.

Les trois couches impliquées dans un cycle ETL sont -

  • Staging Layer - La couche intermédiaire est utilisée pour stocker les données extraites de différents systèmes de données source.

  • Data Integration Layer - La couche d'intégration transforme les données de la couche intermédiaire et déplace les données vers une base de données, où les données sont organisées en groupes hiérarchiques, souvent appelés dimensions, et en faits et faits agrégés. La combinaison de tableaux de faits et de dimensions dans un système DW est appeléeschema.

  • Access Layer - La couche d'accès est utilisée par les utilisateurs finaux pour récupérer les données pour le reporting analytique.

Un outil ETL est utilisé pour extraire des données de différentes sources de données, transformer les données et les charger dans un système DW. En revanche, un outil BI est utilisé pour générer des rapports interactifs et ad hoc pour les utilisateurs finaux, un tableau de bord pour la haute direction, des visualisations de données pour les réunions mensuelles, trimestrielles et annuelles du conseil d'administration.

Les outils ETL les plus courants incluent - SAP BO Data Services (BODS), Informatica, Microsoft - SSIS, Oracle Data Integrator ODI, Talend Open Studio, Clover ETL Open source, etc.

Les outils de BI les plus courants incluent: SAP Business Objects, SAP Lumira, IBM Cognos, JasperSoft, Microsoft BI Platform, Tableau, Oracle Business Intelligence Enterprise Edition, etc.

Les outils ETL populaires disponibles sur le marché sont -

  • Informatica - Centre d'alimentation
  • IBM - Websphere DataStage (anciennement appelé Ascential DataStage)
  • SAP - Business Objects Data Services BODS
  • IBM - Cognos Data Manager (anciennement connu sous le nom de Cognos Decision Stream)
  • Microsoft - SSIS des services d'intégration SQL Server
  • Oracle - Data Integrator ODI (anciennement Sunopsis Data Conductor)
  • SAS - Studio d'intégration de données
  • Oracle - Créateur d'entrepôt
  • ABInitio
  • Open source Clover ETL

La zone de transit est une zone intermédiaire qui se situe entre les sources de données et les systèmes d'entrepôt de données / data marts. Les zones de transit peuvent être conçues pour offrir de nombreux avantages, mais les principales motivations de leur utilisation sont d'augmenter l'efficacité des processus ETL, d'assurer l'intégrité des données et de prendre en charge les opérations de qualité des données.

L'entreposage de données est un concept plus large par rapport à l'exploration de données. L'exploration de données consiste à extraire des informations cachées des données et à les interpréter pour des prévisions futures. En revanche, l'entreposage de données comprend des opérations telles que des rapports analytiques pour générer des rapports détaillés et des rapports ad hoc, le traitement de l'information pour générer des tableaux de bord et des graphiques interactifs.

OLTP signifie système de traitement transactionnel en ligne qui est généralement une base de données relationnelle et est utilisé pour gérer les transactions quotidiennes.

OLAP signifie système de traitement analytique en ligne qui est généralement un système multidimensionnel et est également appelé entrepôt de données.

Supposons qu'une entreprise vend ses produits à des clients. Chaque vente est un fait qui a lieu au sein de l'entreprise et la table de faits est utilisée pour enregistrer ces faits. Chaque table de faits stocke les clés primaires pour joindre la table de faits aux tables de dimension et aux mesures / faits.

Example - Fact_Units

Cust_ID Prod_Id Time_Id Nombre d'unités vendues
101 24 1 25
102 25 2 15
103 26 3 30

Une table de dimension stocke des attributs ou des dimensions qui décrivent les objets dans une table de faits. Il s'agit d'un ensemble de tables associées à une table de faits.

Example - Dim_Customer

Cust_id Cust_Name Le genre
101 Jason M
102 Anna F

Un data mart est une forme simple d'entrepôt de données et se concentre sur un seul domaine fonctionnel. Il obtient généralement des données uniquement à partir de quelques sources.

Example - Dans une organisation, des data marts peuvent exister pour les services financiers, marketing, ressources humaines et autres départements individuels qui stockent des données liées à leurs fonctions spécifiques.

Les fonctions d'agrégation sont utilisées pour regrouper plusieurs lignes d'une seule colonne pour former une mesure plus significative. Ils sont également utilisés pour l'optimisation des performances lorsque nous sauvegardons des tables agrégées dans l'entrepôt de données.

Les fonctions d'agrégation communes sont -

MIN renvoie la plus petite valeur d'une colonne donnée
MAX renvoie la plus grande valeur d'une colonne donnée
SOMME renvoie la somme des valeurs numériques dans une colonne donnée
AVG renvoie la valeur moyenne d'une colonne donnée
COMPTER renvoie le nombre total de valeurs dans une colonne donnée
COMPTER(*) renvoie le nombre de lignes dans une table

Example

SELECT AVG(salary) 
FROM employee 
WHERE title = 'developer';

Les instructions DDL (Data Definition Language) sont utilisées pour définir la structure ou le schéma de la base de données.

Examples -

  • CREATE - pour créer des objets dans une base de données

  • ALTER - modifie la structure d'une base de données

Les instructions DML (Data Manipulation Language) sont utilisées pour manipuler les données dans la base de données.

Examples -

  • SELECT - récupère les données d'une base de données

  • INSERT - insère des données dans une table

  • UPDATE - met à jour les données existantes dans une table

  • DELETE - supprime tous les enregistrements d'une table, l'espace pour les enregistrements reste

Les instructions DCL (Data Control Language) sont utilisées pour contrôler l'accès aux objets de base de données.

Examples -

  • GRANT - donne les privilèges d'accès de l'utilisateur à la base de données

  • REVOKE - retire les privilèges d'accès donnés avec la commande GRANT

Les opérateurs sont utilisés pour spécifier des conditions dans une instruction SQL et pour servir de conjonctions pour plusieurs conditions dans une instruction. Les types d'opérateurs courants sont -

  • Opérateurs arithmétiques
  • Comparaison / Opérateurs relationnels
  • Opérateurs logiques
  • Définir les opérateurs
  • Opérateurs utilisés pour annuler les conditions

Les opérateurs d'ensemble courants dans SQL sont -

  • UNION
  • UNION TOUT
  • INTERSECT
  • MINUS

L'opération d'intersection est utilisée pour combiner deux instructions SELECT, mais elle ne renvoie que les enregistrements qui sont communs aux deux instructions SELECT. En cas d'intersection, le nombre de colonnes et le type de données doivent être identiques. MySQL ne prend pas en charge l'opérateur INTERSECT. Une requête Intersection se présente comme suit -

select * from First 
INTERSECT 
select * from second

L'opération Moins combine le résultat de deux instructions Select et renvoie uniquement les résultats qui appartiennent au premier ensemble de résultats. Une requête Moins ressemble à ceci -

select * from First 
MINUS 
select * from second

Si vous exécutez source moins cible et cible moins source, et si la requête moins renvoie une valeur, cela doit être considéré comme un cas de lignes incompatibles.

Si la requête moins renvoie une valeur et que le nombre d'intersection est inférieur au nombre source ou à la table cible, les tables source et cible contiennent des lignes en double.

Group-by la clause est utilisée avec select déclaration pour collecter un type similaire de données. HAVING est très similaire à WHERE sauf que les déclarations qu'il contient sont de nature agrégée.

Syntax -

SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no;  
SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no HAVING COUNT( 1 ) > 1;

Example - Table des employés

Country Salary
Inde 3000
NOUS 2500
Inde 500
NOUS 1500

Group by Country

Country Salary
Inde 3000
Inde 500
NOUS 2500
NOUS 1500

Les tests ETL sont effectués avant que les données ne soient transférées dans un système d'entrepôt de données de production. Il est parfois également appelé équilibrage de table ou réconciliation de production.

Le principal objectif des tests ETL est d'identifier et d'atténuer les défauts de données et les erreurs générales qui se produisent avant le traitement des données pour les rapports analytiques.

Le tableau suivant présente les principales fonctionnalités des tests de base de données et ETL et leur comparaison -

Fonction Test de base de données Test ETL
Objectif principal Validation et intégration des données Extraction, transformation et chargement de données pour le reporting BI
Système applicable Système transactionnel où se déroule le flux commercial Système contenant des données historiques et non dans un environnement de flux commercial
Outils communs sur le marché QTP, sélénium, etc. QuerySurge, Informatica, etc.
Besoin commercial Il est utilisé pour intégrer les données de plusieurs applications, impact sévère. Il est utilisé pour les rapports analytiques, les informations et les prévisions.
La modélisation Méthode ER Multidimensionnel
Type de base de données Il est normalement utilisé dans les systèmes OLTP Il est appliqué aux systèmes OLAP
Type de données Données normalisées avec plus de jointures Données dé-normalisées avec moins de jointures, plus d'index et d'agrégations.

Les tests ETL peuvent être divisés dans les catégories suivantes en fonction de leur fonction -

  • Source to Target Count Testing - Cela implique la mise en correspondance du nombre d'enregistrements dans le système source et cible.

  • Source to Target Data Testing- Cela implique la validation des données entre le système source et cible. Cela implique également l'intégration des données et la vérification de la valeur de seuil et la vérification des données en double dans le système cible.

  • Data Mapping or Transformation Testing- Il confirme le mappage des objets dans le système source et cible. Cela implique également de vérifier la fonctionnalité des données dans le système cible.

  • End-User Testing- Il s'agit de générer des rapports pour les utilisateurs finaux afin de vérifier si les données des rapports sont conformes aux attentes. Cela implique de trouver des écarts dans les rapports et de vérifier les données dans le système cible pour la validation des rapports.

  • Retesting - Il s'agit de corriger les bogues et les défauts dans les données du système cible et d'exécuter à nouveau les rapports pour la validation des données.

  • System Integration Testing - Cela implique de tester tous les systèmes individuels, puis de combiner le résultat pour trouver s'il y a un écart.

  • Perte de données pendant le processus ETL.

  • Données incorrectes, incomplètes ou dupliquées.

  • Le système DW contient des données historiques, de sorte que le volume de données est trop important et vraiment complexe pour effectuer des tests ETL dans le système cible.

  • Les testeurs ETL ne sont normalement pas fournis avec l'accès pour voir les horaires de travail dans l'outil ETL. Ils ont à peine accès aux outils de reporting BI pour voir la présentation finale des rapports et des données à l'intérieur des rapports.

  • Difficile à générer et à créer des cas de test car le volume de données est trop élevé et complexe.

  • Les testeurs ETL n'ont généralement aucune idée des exigences de rapport de l'utilisateur final et du flux commercial de l'information.

  • Les tests ETL impliquent divers concepts SQL complexes pour la validation des données dans le système cible.

  • Parfois, les testeurs ne sont pas fournis avec les informations de mappage source-cible.

  • Un environnement de test instable entraîne un retard dans le développement et le test du processus.

Les principales responsabilités d'un testeur ETL comprennent:

  • Vérification des tables dans le système source - Contrôle du nombre, contrôle du type de données, les clés ne manquent pas, données en double.

  • Application de la logique de transformation avant le chargement des données: validation du seuil de données, contrôle ky de substitution, etc.

  • Chargement des données de la zone de transfert vers le système cible: agréger les valeurs et les mesures calculées, les champs clés ne sont pas manquants, le comptage dans la table cible, la validation du rapport BI, etc.

  • Test de l'outil ETL et de ses composants, cas de test - Créer, concevoir et exécuter des plans de test, des cas de test, l'outil de test ETL et sa fonction, le système de test DW, etc.

Une transformation est un ensemble de règles qui génère, modifie ou transmet des données. La transformation peut être de deux types: active et passive.

Dans une transformation active, le nombre de lignes créées en tant que sortie peut être modifié une fois qu'une transformation s'est produite. Cela ne se produit pas lors d'une transformation passive. Les informations passent par le même numéro qui lui est donné en entrée.

Le partitionnement consiste à diviser la zone du magasin de données en plusieurs parties. Cela est normalement fait pour améliorer les performances des transactions.

Si votre système DW est de grande taille, il faudra du temps pour localiser les données. Le partitionnement de l'espace de stockage vous permet de trouver et d'analyser les données plus facilement et plus rapidement.

La séparation peut être de deux types: le partitionnement circulaire et le partitionnement par hachage.

Dans le partitionnement circulaire, les données sont uniformément réparties entre toutes les partitions, de sorte que le nombre de lignes dans chaque partition est relativement le même. Le partitionnement de hachage se produit lorsque le serveur utilise une fonction de hachage afin de créer des clés de partition pour regrouper les données.

  • Un mapplet définit les règles de transformation.

  • Les sessions sont définies pour instruire les données lorsqu'elles sont déplacées du système source au système cible.

  • Un workflow est un ensemble d'instructions qui indique au serveur l'exécution de la tâche.

  • Le mappage est le déplacement des données de la source vers la destination.

La transformation de recherche vous permet d'accéder aux données des tables relationnelles qui ne sont pas définies dans les documents de mappage. Il vous permet de mettre à jour les tables de dimension à évolution lente pour déterminer si les enregistrements existent déjà dans la cible ou non.

Une clé de substitution est quelque chose ayant des nombres générés par séquence sans signification, et juste pour identifier la ligne de manière unique. Il n'est pas visible pour les utilisateurs ou l'application. Elle est également appelée clé candidate.

Une clé de substitution a des nombres générés par séquence sans signification. Il est destiné à identifier les lignes de manière unique.

Une clé primaire est utilisée pour identifier les lignes de manière unique. Il est visible pour les utilisateurs et peut être modifié selon les besoins.

Dans de tels cas, vous pouvez appliquer la méthode de la somme de contrôle. Vous pouvez commencer par vérifier le nombre d'enregistrements dans les systèmes source et cible. Sélectionnez les sommes et comparez les informations.

Dans ce test, un testeur valide la plage de données. Toutes les valeurs de seuil du système cible doivent être vérifiées pour s'assurer qu'elles correspondent au résultat attendu.

Example - L'attribut Âge ne doit pas avoir une valeur supérieure à 100. Dans la colonne Date JJ / MM / AA, le champ mois ne doit pas avoir une valeur supérieure à 12.

Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;

Lorsqu'aucune clé primaire n'est définie, des valeurs en double peuvent apparaître.

La duplication des données peut également survenir en raison d'un mappage incorrect et d'erreurs manuelles lors du transfert des données du système source au système cible.

Le test de régression consiste à apporter des modifications aux règles de transformation et d'agrégation des données pour ajouter une nouvelle fonctionnalité et aider le testeur à trouver de nouvelles erreurs. Les bogues qui apparaissent dans les données fournies dans les tests de régression sont appelés régression.

Les trois approches sont: descendante, ascendante et hybride.

Les scénarios de test ETL les plus courants sont:

  • Validation de la structure
  • Validation du document de mappage
  • Valider les contraintes
  • Contrôle de cohérence des données
  • Validation de l'exhaustivité des données
  • Validation de l'exactitude des données
  • Validation de la transformation des données
  • Validation de la qualité des données
  • Validation nulle
  • Validation en double
  • Contrôle de validation de la date
  • Validation complète des données à l'aide de la requête moins
  • Autres scénarios de test
  • Nettoyage des données

La purge des données est un processus de suppression de données d'un entrepôt de données. Il supprime les données indésirables telles que les lignes avec des valeurs nulles ou des espaces supplémentaires.

Le bogue cosmétique est lié à l'interface graphique d'une application. Cela peut être lié au style de police, à la taille de la police, aux couleurs, à l'alignement, aux fautes d'orthographe, à la navigation, etc.

Il s'agit d'un bogue lié à l'analyse de la valeur des limites.

Vous pouvez le faire en créant une variable de mappage et une transformation filtrée. Vous devrez peut-être générer une séquence afin d'avoir l'enregistrement spécifiquement trié dont vous avez besoin.

Value comparison- Il s'agit de comparer les données des systèmes source et cible avec une transformation minimale ou nulle. Cela peut être fait à l'aide de divers outils de test ETL tels que Source Qualifier Transformation dans Informatica.

Les colonnes de données critiques peuvent être vérifiées en comparant des valeurs distinctes dans les systèmes source et cible.

Vous pouvez utiliser les instructions Minus et Intersect pour effectuer la validation de l'exhaustivité des données. Lorsque vous exécutez source moins cible et cible moins source et que la requête moins renvoie une valeur, cela signifie que les lignes ne correspondent pas.

Si la requête moins renvoie une valeur et que le nombre d'intersection est inférieur au nombre source ou à la table cible, des lignes en double existent.

Shortcut Transformationest une référence à un objet disponible dans un dossier partagé. Ces références sont couramment utilisées pour diverses sources et cibles qui doivent être partagées entre différents projets ou environnements.

Dans le gestionnaire de référentiel, un raccourci est créé en attribuant le statut «Partagé». Plus tard, les objets peuvent être déplacés de ce dossier vers un autre dossier. Ce processus permet un point de contrôle unique pour l'objet et plusieurs projets n'ont pas toutes les sources et cibles d'importation dans leurs dossiers locaux.

Reusable Transformation est local dans un dossier. Example- Générateur de séquence réutilisable pour l'attribution des identifiants clients de l'entrepôt. Il est utile de charger les détails du client à partir de plusieurs systèmes sources et d'allouer des identifiants uniques à chaque nouvelle clé source.

Lorsque vous joignez une seule table à elle-même, cela s'appelle Auto-jointure.

La normalisation de la base de données est le processus d'organisation des attributs et des tables d'une base de données relationnelle pour minimiser la redondance des données.

La normalisation consiste à décomposer une table en tables moins redondantes (et plus petites) mais sans perdre d'informations.

Une table de faits sans faits est une table de faits qui ne contient aucune mesure. C'est essentiellement une intersection de dimensions. Il existe deux types de tableaux sans faits: l'un sert à capturer un événement et l'autre à décrire les conditions.

Les dimensions qui changent lentement font référence à la valeur changeante d'un attribut au fil du temps. Les SCD sont de trois types - Type 1, Type 2 et Type 3.