ETL son las siglas de Extract, Transform, and Load. Es un concepto importante en los sistemas de almacenamiento de datos.Extraction significa extraer datos de diferentes fuentes de datos, como sistemas transaccionales o aplicaciones. Transformationsignifica aplicar las reglas de conversión a los datos para que sean adecuados para la elaboración de informes analíticos. losloading El proceso implica mover los datos al sistema de destino, normalmente un almacén de datos.
Las tres capas involucradas en un ciclo ETL son:
Staging Layer - La capa de preparación se utiliza para almacenar los datos extraídos de diferentes sistemas de datos de origen.
Data Integration Layer - La capa de integración transforma los datos de la capa de preparación y mueve los datos a una base de datos, donde los datos se organizan en grupos jerárquicos, a menudo llamados dimensionsy en hechos y hechos agregados. La combinación de tablas de hechos y dimensiones en un sistema DW se denominaschema.
Access Layer - Los usuarios finales utilizan la capa de acceso para recuperar los datos para la elaboración de informes analíticos.
Se utiliza una herramienta ETL para extraer datos de diferentes fuentes de datos, transformar los datos y cargarlos en un sistema DW. Por el contrario, una herramienta de BI se utiliza para generar informes interactivos y ad hoc para los usuarios finales, un tablero para la alta dirección, visualizaciones de datos para las reuniones de la junta mensuales, trimestrales y anuales.
Las herramientas ETL más comunes incluyen - SAP BO Data Services (BODS), Informatica, Microsoft - SSIS, Oracle Data Integrator ODI, Talend Open Studio, Clover ETL Open source, etc.
Las herramientas de BI más comunes incluyen: SAP Business Objects, SAP Lumira, IBM Cognos, JasperSoft, Microsoft BI Platform, Tableau, Oracle Business Intelligence Enterprise Edition, etc.
Las herramientas ETL populares disponibles en el mercado son:
- Informatica - Power Center
- IBM - Websphere DataStage (anteriormente conocido como Ascential DataStage)
- SAP - BODS de servicios de datos de Business Objects
- IBM - Cognos Data Manager (anteriormente conocido como Cognos Decision Stream)
- Microsoft: SSIS de servicios de integración de SQL Server
- Oracle - Integrador de datos ODI (anteriormente conocido como Sunopsis Data Conductor)
- SAS - Estudio de integración de datos
- Oracle - Generador de almacenes
- ABInitio
- ETL de Clover de código abierto
El área de preparación es un área intermedia que se encuentra entre las fuentes de datos y los sistemas de almacenamiento de datos / data marts. Las áreas de preparación pueden diseñarse para proporcionar muchos beneficios, pero las principales motivaciones para su uso son aumentar la eficiencia de los procesos ETL, garantizar la integridad de los datos y respaldar las operaciones de calidad de los datos.
El almacenamiento de datos es un concepto más amplio en comparación con la minería de datos. La minería de datos implica extraer información oculta de los datos e interpretarla para futuras predicciones. Por el contrario, el almacenamiento de datos incluye operaciones tales como informes analíticos para generar informes detallados e informes ad-hoc, procesamiento de información para generar cuadros de mando y gráficos interactivos.
OLTP significa sistema de procesamiento transaccional en línea, que comúnmente es una base de datos relacional y se utiliza para administrar las transacciones del día a día.
OLAP significa sistema de procesamiento analítico en línea, que comúnmente es un sistema multidimensional y también se denomina almacén de datos.
Suponga que una empresa vende sus productos a sus clientes. Cada venta es un hecho que tiene lugar dentro de la empresa y la tabla de hechos se utiliza para registrar estos hechos. Cada tabla de hechos almacena las claves primarias para unir la tabla de hechos a tablas de dimensiones y medidas / hechos.
Example - Fact_Units
| Cust_ID | Prod_Id | Time_Id | No. de unidades vendidas |
|---|---|---|---|
| 101 | 24 | 1 | 25 |
| 102 | 25 | 2 | 15 |
| 103 | 26 | 3 | 30 |
Una tabla de dimensiones almacena atributos o dimensiones que describen los objetos en una tabla de hechos. Es un conjunto de tablas complementarias a una tabla de hechos.
Example - Dim_Customer
| Cust_id | Cust_Name | Género |
|---|---|---|
| 101 | Jason | METRO |
| 102 | Anna | F |
Un data mart es una forma simple de almacén de datos y se centra en una única área funcional. Por lo general, solo obtiene datos de unas pocas fuentes.
Example - En una organización, pueden existir mercados de datos para Finanzas, Marketing, Recursos Humanos y otros departamentos individuales que almacenan datos relacionados con sus funciones específicas.
Las funciones agregadas se utilizan para agrupar varias filas de una sola columna para formar una medida más significativa. También se utilizan para optimizar el rendimiento cuando guardamos tablas agregadas en el almacén de datos.
Las funciones agregadas comunes son:
| MIN | devuelve el valor más pequeño en una columna determinada |
| MAX | devuelve el valor más grande en una columna determinada |
| SUMA | devuelve la suma de los valores numéricos en una columna determinada |
| AVG | devuelve el valor medio de una columna determinada |
| CONTAR | devuelve el número total de valores en una columna determinada |
| CONTAR(*) | devuelve el número de filas en una tabla |
Example
SELECT AVG(salary)
FROM employee
WHERE title = 'developer';Las declaraciones del lenguaje de definición de datos (DDL) se utilizan para definir la estructura o el esquema de la base de datos.
Examples -
CREATE - crear objetos en una base de datos
ALTER - altera la estructura de una base de datos
Las declaraciones del lenguaje de manipulación de datos (DML) se utilizan para manipular datos dentro de la base de datos.
Examples -
SELECT - recupera datos de la base de datos
INSERT - inserta datos en una tabla
UPDATE - actualiza los datos existentes dentro de una tabla
DELETE - elimina todos los registros de una tabla, el espacio para los registros permanece
Las declaraciones del lenguaje de control de datos (DCL) se utilizan para controlar el acceso a los objetos de la base de datos.
Examples -
GRANT - otorga privilegios de acceso al usuario a la base de datos
REVOKE - retira los privilegios de acceso otorgados con el comando GRANT
Los operadores se utilizan para especificar condiciones en una declaración SQL y para servir como conjunciones para múltiples condiciones en una declaración. Los tipos de operadores comunes son:
- Operadores aritméticos
- Operadores de comparación / relacionales
- Operadores logicos
- Establecer operadores
- Operadores utilizados para negar condiciones
Los operadores de conjuntos comunes en SQL son:
- UNION
- UNIÓN TODOS
- INTERSECT
- MINUS
La operación de intersección se utiliza para combinar dos sentencias SELECT, pero solo devuelve los registros que son comunes de ambas sentencias SELECT. En el caso de Intersect, el número de columnas y el tipo de datos deben ser iguales. MySQL no admite el operador INTERSECT. Una consulta Intersect tiene el siguiente aspecto:
select * from First
INTERSECT
select * from secondLa operación menos combina el resultado de dos sentencias Select y devuelve solo el resultado que pertenece al primer conjunto de resultados. Una consulta Menos tiene el siguiente aspecto:
select * from First
MINUS
select * from secondSi realiza fuente menos destino y destino menos fuente, y si la consulta negativa devuelve un valor, entonces debe considerarse como un caso de filas que no coinciden.
Si la consulta negativa devuelve un valor y el recuento de intersección es menor que el recuento de origen o la tabla de destino, las tablas de origen y destino contienen filas duplicadas.
Group-by la cláusula se usa con select declaración para recopilar un tipo similar de datos. HAVING es muy similar a WHERE excepto que las declaraciones contenidas en él son de naturaleza agregada.
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 - Mesa de empleados
| Country | Salary |
| India | 3000 |
| NOS | 2500 |
| India | 500 |
| NOS | 1500 |
Group by Country
| Country | Salary |
| India | 3000 |
| India | 500 |
| NOS | 2500 |
| NOS | 1500 |
La prueba ETL se realiza antes de que los datos se muevan a un sistema de almacenamiento de datos de producción. A veces también se denomina equilibrio de tabla o conciliación de producción.
El objetivo principal de las pruebas ETL es identificar y mitigar los defectos de los datos y los errores generales que ocurren antes del procesamiento de los datos para los informes analíticos.
La siguiente tabla captura las características clave de las pruebas de base de datos y ETL y su comparación:
| Función | Prueba de base de datos | Pruebas ETL |
|---|---|---|
| Objetivo principal | Integración y validación de datos | Extracción, transformación y carga de datos para informes de BI |
| Sistema aplicable | Sistema transaccional donde ocurre el flujo de negocios | Sistema que contiene datos históricos y no en un entorno de flujo empresarial |
| Herramientas comunes en el mercado | QTP, Selenio, etc. | QuerySurge, Informatica, etc. |
| Necesidad de Negocios | Se utiliza para integrar datos de múltiples aplicaciones, impacto severo. | Se utiliza para informes analíticos, información y previsión. |
| Modelado | Método ER | Multidimensional |
| Tipo de base de datos | Normalmente se utiliza en sistemas OLTP. | Se aplica a sistemas OLAP |
| Tipo de datos | Datos normalizados con más uniones | Datos desnormalizados con menos combinaciones, más índices y agregaciones. |
Las pruebas ETL se pueden dividir en las siguientes categorías según su función:
Source to Target Count Testing - Implica la coincidencia del recuento de registros en el sistema de origen y de destino.
Source to Target Data Testing- Implica la validación de datos entre el sistema de origen y el de destino. También implica la integración de datos y la verificación del valor umbral y la verificación de datos duplicados en el sistema de destino.
Data Mapping or Transformation Testing- Confirma el mapeo de objetos en el sistema de origen y destino. También implica verificar la funcionalidad de los datos en el sistema de destino.
End-User Testing- Implica generar informes para que los usuarios finales verifiquen si los datos en los informes cumplen con las expectativas. Implica encontrar desviaciones en los informes y verificar los datos en el sistema de destino para la validación del informe.
Retesting - Implica corregir los errores y defectos en los datos en el sistema de destino y ejecutar los informes nuevamente para la validación de los datos.
System Integration Testing - Implica probar todos los sistemas individuales y luego combinar el resultado para encontrar si existe alguna desviación.
Pérdida de datos durante el proceso ETL.
Datos incorrectos, incompletos o duplicados.
El sistema DW contiene datos históricos, por lo que el volumen de datos es demasiado grande y realmente complejo para realizar pruebas ETL en el sistema de destino.
Los probadores ETL normalmente no tienen acceso para ver horarios de trabajo en la herramienta ETL. Apenas tienen acceso a las herramientas de informes de BI para ver el diseño final de los informes y los datos dentro de los informes.
Difícil de generar y construir casos de prueba, ya que el volumen de datos es demasiado alto y complejo.
Los probadores de ETL normalmente no tienen una idea de los requisitos de informes del usuario final y el flujo comercial de la información.
Las pruebas ETL implican varios conceptos SQL complejos para la validación de datos en el sistema de destino.
A veces, a los evaluadores no se les proporciona información de mapeo de origen a destino.
Los resultados del entorno de prueba inestable retrasan el desarrollo y la prueba del proceso.
Las responsabilidades clave de un probador ETL incluyen:
Verificación de las tablas en el sistema de origen: verificación de recuento, verificación del tipo de datos, no faltan claves, datos duplicados.
Aplicar la lógica de transformación antes de cargar los datos: validación del umbral de datos, verificación ky sustituta, etc.
Carga de datos desde el área de preparación al sistema de destino: valores agregados y medidas calculadas, no faltan campos clave, verificación de recuento en la tabla de destino, validación del informe de BI, etc.
Prueba de la herramienta ETL y sus componentes, Casos de prueba: cree, diseñe y ejecute planes de prueba, casos de prueba, Pruebe la herramienta ETL y su función, Pruebe el sistema DW, etc.
Una transformación es un conjunto de reglas que genera, modifica o pasa datos. La transformación puede ser de dos tipos: activa y pasiva.
En una transformación activa, el número de filas que se crea como salida se puede cambiar una vez que se ha producido una transformación. Esto no sucede durante una transformación pasiva. La información pasa por el mismo número que se le da como entrada.
El particionamiento es cuando divide el área del almacén de datos en partes. Normalmente se hace para mejorar el rendimiento de las transacciones.
Si su sistema DW tiene un tamaño enorme, llevará tiempo localizar los datos. La partición del espacio de almacenamiento le permite buscar y analizar los datos de forma más fácil y rápida.
La partición puede ser de dos tipos: partición por turnos y partición Hash.
En el particionamiento por turnos, los datos se distribuyen uniformemente entre todas las particiones, por lo que el número de filas en cada partición es relativamente el mismo. El particionamiento hash es cuando el servidor utiliza una función hash para crear claves de partición para agrupar los datos.
Un mapplet define las reglas de transformación.
Las sesiones se definen para instruir a los datos cuando se mueven del sistema de origen al de destino.
Un flujo de trabajo es un conjunto de instrucciones que instruyen al servidor sobre la ejecución de tareas.
El mapeo es el movimiento de datos desde el origen al destino.
La transformación de búsqueda le permite acceder a datos de tablas relacionales que no están definidas en documentos de mapeo. Le permite actualizar tablas de dimensiones que cambian lentamente para determinar si los registros ya existen en el destino o no.
Una clave sustituta es algo que tiene números generados en secuencia sin significado, y solo para identificar la fila de forma única. No es visible para los usuarios o la aplicación. También se denomina clave de candidato.
Una clave sustituta tiene números generados en secuencia sin significado. Está destinado a identificar las filas de forma única.
Se utiliza una clave principal para identificar las filas de forma única. Es visible para los usuarios y se puede cambiar según los requisitos.
En tales casos, puede aplicar el método de suma de comprobación. Puede comenzar verificando el número de registros en los sistemas de origen y de destino. Seleccione las sumas y compare la información.
En esta prueba, un probador valida el rango de datos. Todos los valores de umbral en el sistema de destino deben verificarse para garantizar que coincidan con el resultado esperado.
Example - El atributo de edad no debe tener un valor mayor que 100. En la columna Fecha DD / MM / AA, el campo del mes no debe tener un valor mayor que 12.
Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;Cuando no se define una clave principal, pueden aparecer valores duplicados.
La duplicación de datos también puede surgir debido a un mapeo incorrecto y errores manuales al transferir datos desde el sistema de origen al de destino.
La prueba de regresión es cuando realizamos cambios en las reglas de transformación y agregación de datos para agregar una nueva funcionalidad y ayudar al evaluador a encontrar nuevos errores. Los errores que aparecen en los datos que vienen en las pruebas de regresión se denominan Regresión.
Los tres enfoques son: de arriba hacia abajo, de abajo hacia arriba e híbrido.
Los escenarios de prueba ETL más comunes son:
- Validación de estructura
- Validación del documento de mapeo
- Validar restricciones
- Comprobación de coherencia de datos
- Validación de integridad de datos
- Validación de la exactitud de los datos
- Validación de transformación de datos
- Validación de la calidad de los datos
- Validación nula
- Validación duplicada
- Verificación de validación de fecha
- Validación de datos completa usando menos consulta
- Otros escenarios de prueba
- Limpieza de datos
La depuración de datos es un proceso de eliminación de datos de un almacén de datos. Elimina datos basura como filas con valores nulos o espacios adicionales.
El error cosmético está relacionado con la GUI de una aplicación. Puede estar relacionado con el estilo de fuente, tamaño de fuente, colores, alineación, errores ortográficos, navegación, etc.
Se llama error relacionado con el análisis de valor límite.
Puede hacerlo creando una variable de mapeo y una transformación filtrada. Es posible que deba generar una secuencia para tener el registro ordenado específicamente que necesita.
Value comparison- Implica comparar los datos en los sistemas de origen y destino con mínima o ninguna transformación. Se puede hacer utilizando varias herramientas de prueba ETL, como Source Qualifier Transformation en Informatica.
Las columnas de datos críticos se pueden verificar comparando valores distintos en los sistemas de origen y destino.
Puede utilizar las declaraciones Minus e Intersect para realizar la validación de la integridad de los datos. Cuando realiza fuente menos destino y destino menos fuente y la consulta menos devuelve un valor, es un signo de filas no coincidentes.
Si la consulta menos devuelve un valor y el recuento de intersección es menor que el recuento de origen o la tabla de destino, entonces existen filas duplicadas.
Shortcut Transformationes una referencia a un objeto que está disponible en una carpeta compartida. Estas referencias se utilizan comúnmente para diversas fuentes y objetivos que se compartirán entre diferentes proyectos o entornos.
En el Repository Manager, se crea un acceso directo asignando el estado 'Compartido'. Posteriormente, los objetos se pueden arrastrar desde esta carpeta a otra carpeta. Este proceso permite un único punto de control para el objeto y varios proyectos no tienen todos los orígenes y destinos de importación en sus carpetas locales.
Reusable Transformation es local a una carpeta. Example- Generador de secuencia reutilizable para la asignación de ID de cliente de almacén. Es útil cargar los detalles del cliente desde múltiples sistemas fuente y asignar identificadores únicos a cada nueva clave fuente.
Cuando se une a una sola mesa a sí misma, se llama Self-Join.
La normalización de la base de datos es el proceso de organizar los atributos y las tablas de una base de datos relacional para minimizar la redundancia de datos.
La normalización implica descomponer una tabla en tablas menos redundantes (y más pequeñas) pero sin perder información.
Una tabla de hechos sin hechos es una tabla de hechos que no tiene medidas. Es esencialmente una intersección de dimensiones. Hay dos tipos de tablas sin hechos: una es para capturar un evento y la otra es para describir condiciones.
Las dimensiones que cambian lentamente se refieren al valor cambiante de un atributo a lo largo del tiempo. Los SCD son de tres tipos: tipo 1, tipo 2 y tipo 3.