Transformación ETL con Pentaho paso a paso

Curso de Data Warehousing y Modelado OLAP

Contenido del curso

No sé dónde empezar

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

26
De datos crudos a modelo dimensional listo
01:18 min

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:0015:19

Transformación ETL con Pentaho paso a paso

Curso de Data Warehousing y Modelado OLAP

Clase 20 de 26

Clase 20 / 26

Recursos

Transformar datos en Pentaho es el paso donde conviertes la información cruda del sistema transaccional en registros listos para alimentar tu data warehouse. Aquí defines reglas de negocio, identificas registros nuevos frente a los existentes y generas los IDs que tu dimensión necesita para mantener integridad histórica.

Esta guía te muestra cómo construir ese flujo de transformación paso a paso, desde el cruce de fuentes hasta la fórmula condicional que decide qué ID lleva cada registro.

Cómo cruzar la fuente transaccional con el data warehouse

Todo arranca con dos lecturas paralelas. Por un lado, consultas la base transaccional con un query que trae los registros actuales. Por otro, lees la dimensión del data warehouse, que en la primera ejecución estará vacía porque acabas de construir la tabla [00:24].

El cruce se hace por el código de cliente, que es el campo común entre ambas fuentes. Cuando un registro existe en el transaccional pero no en la dimensión, el ID llega en nulo y lo reemplazas por -1. Ese -1 se convierte en tu marca para identificar registros nuevos.

¿Para qué sirve marcar un ID como -1 en una ETL? Es una convención para señalar que el registro no existe aún en la dimensión. Después usas esa marca para decidir si insertas un ID nuevo o conservas el que ya tiene asignado.

Por qué necesitas una fecha de carga y un indicador de insert o update

Después del cruce viene el paso Get system info, que captura la fecha del sistema y la asigna a un campo llamado load date [01:25]. Esa fecha te indica cuándo se ejecutó la ETL y cuándo entró cada registro a la bodega.

El siguiente bloque es una constante que actúa como indicador de insert o update. Es un campo entero que copia el valor del ID de cliente original antes de que lo modifiques. Si el indicador queda en -1, sabrás que el registro va para insertar; si tiene otro valor, va para actualizar.

Este campo es clave porque más adelante en el flujo el ID original se sobreescribe, y sin esta copia perderías la trazabilidad de qué hacer con cada fila.

Cómo generar IDs nuevos con un step de secuencia en Pentaho

Con el indicador asegurado, aplicas un filter rows que separa el flujo en dos caminos según la condición ID cliente igual a -1.

Rama false: el registro ya existe, así que creas una constante llamada max cliente ID y le asignas el mismo valor del ID actual.
Rama true: el registro es nuevo, así que entra al step Add sequence para generar un consecutivo.
Unión final: un step Dummy recombina ambos flujos en uno solo.

En el step de secuencia configuras el campo max cliente ID para que arranque desde un valor inicial e incremente de uno en uno. Pero ese valor inicial no puede estar fijo en el código, porque cambia con cada ejecución conforme la dimensión crece.

La solución es declarar una variable con la sintaxis ${max_registro} que se inicializa al momento de correr la ETL [05:50]. Así, si hoy tu dimensión tiene 30 registros, mañana arrancará desde 31 sin tocar la transformación.

¿Qué hace el step Dummy en Pentaho? Une dos o más flujos en uno solo sin transformar los datos. Es útil cuando partiste el proceso con un filtro y necesitas volver a juntarlo antes del siguiente paso.

Cómo escribir la fórmula condicional que decide el ID final

Después del Dummy agregas un step Formula para resolver qué ID queda como definitivo. Creas un campo llamado ID calculado que después reemplaza al max cliente ID, y escribes una condicional tipo:

IF([ID cliente]=-1; [max cliente ID]; [ID cliente])

La lógica es directa. Si el ID de cliente es -1, toma el valor generado por la secuencia. Si no, conserva el ID que ya existía en la dimensión. Con eso, cada fila sale con su identificador correcto sin importar si es nueva o vieja.

Qué revisar antes de correr el preview

Antes de ejecutar conviene validar que cada step esté bien conectado y que las asignaciones de valores no estén invertidas. En el ejemplo de la clase aparece un error típico: asignar el valor del insert update al ID cliente en lugar de hacerlo al revés [09:33]. Como el insert update se construye después, llega en nulo y rompe el campo destino.

La corrección es asignar el ID de cliente al indicador, no al contrario. Después de eso, el preview muestra los registros con:

ID cliente en -1 para todas las filas nuevas.
Indicador insert update en -1, marcando que van para inserción.
Max cliente ID con el consecutivo desde el valor inicial de la variable.

Qué reglas de negocio aplicaste en esta transformación

Al cerrar el flujo ya tienes los datos listos para cargar a la dimensión, y de paso aplicaste varias reglas que dan robustez al proceso:

Identificación de registros nuevos vs existentes mediante el cruce por código.
Trazabilidad temporal con la fecha de carga capturada del sistema.
Generación de IDs incrementales sin colisión, gracias a la variable de máximo registro.
Conservación del ID original para los registros que ya estaban en la dimensión.
Marcado explícito de cada fila con un indicador de insert o update.

Con esta base ya puedes pasar a la carga al data warehouse. Cuéntame en los comentarios qué reglas adicionales aplicas tú en tus ETL y qué dudas te quedaron sobre el manejo de la variable de máximo registro.

Mario Alexander Vargas Celis

Hace 2 años

Crear una dimensión de cliente en Pentaho implica extraer datos de una fuente, transformarlos según los requisitos del modelo dimensional y cargarlos en una tabla de base de datos de destino. A continuación te muestro cómo puedes crear esta transformación en Pentaho para construir una dimensión de cliente:

1. Diseño del esquema de la dimensión de cliente

Estructura típica de la tabla dim_cliente

CREATE TABLE dim_cliente ( id_cliente INT PRIMARY KEY, codigo_cliente VARCHAR(50), nombre VARCHAR(50), apellido VARCHAR(50), nombre_completo VARCHAR(100), telefono_celular VARCHAR(20), telefono_casa VARCHAR(20), telefono_trabajo VARCHAR(20), ciudad VARCHAR(50), estado VARCHAR(50), fecha_carga TIMESTAMP, fecha_actualizacion TIMESTAMP );

2. Creación de la transformación en Pentaho

a. Preparación

Abre Spoon en Pentaho.
Crea una nueva transformación: File > New > Transformation.

b. Fuentes de datos

Arrastra un paso de entrada:
- Input > Table Input.
- Configura la conexión a la base de datos de origen.
- Usa una consulta para extraer los datos relevantes:SELECT c.customerid AS id_cliente, c.customer_code AS codigo_cliente, p.firstname AS nombre, p.lastname AS apellido, p.firstname || ' ' || p.lastname AS nombre_completo, CASE WHEN pp.phonenumbertypeid = 1 THEN pp.phonenumber ELSE NULL END AS telefono_celular, CASE WHEN pp.phonenumbertypeid = 2 THEN pp.phonenumber ELSE NULL END AS telefono_casa, CASE WHEN pp.phonenumbertypeid = 3 THEN pp.phonenumber ELSE NULL END AS telefono_trabajo, a.city AS ciudad, a.state AS estado FROM customer c LEFT JOIN person p ON c.personid = p.businessentityid LEFT JOIN person.personphone pp ON p.businessentityid = pp.businessentityid LEFT JOIN address a ON c.addressid = a.addressid;

c. Transformaciones

Limpieza de datos:
- Data Cleansing:
  - Usa el paso de Data Validator para asegurarte de que los datos cumplen con las reglas de negocio, como:
    - Los nombres no son nulos.
    - Los códigos de cliente tienen formato válido.
- Replace Values:
  - Reemplaza valores inconsistentes, por ejemplo, ciudades mal escritas.
Generar columnas adicionales:
- Usa un paso de Calculator para calcular fecha_carga y fecha_actualizacion:
  - fecha_carga: Fecha actual.
  - fecha_actualizacion: Fecha actual o última modificación.

d. Salida de datos

Configura la salida:
- Arrastra un paso de Table Output.
- Configura la conexión a la base de datos de destino.
- Selecciona la tabla dim_cliente.
Mapeo de columnas:
- Asegúrate de mapear correctamente las columnas de la entrada a las columnas de la tabla de destino.

e. Manejo de dimensiones tipo 2 (opcional)

Si necesitas realizar un seguimiento de los cambios históricos en los datos de cliente, puedes usar un paso de Dimension Lookup/Update para manejar las dimensiones lentamente cambiantes (SCD Type 2).

3. Guardar y ejecutar

Guarda la transformación con un nombre descriptivo: transformacion_dim_cliente.ktr.
Ejecuta la transformación y verifica los resultados en la tabla de destino.

4. Validación

Comprueba que los datos en la tabla dim_cliente coincidan con los datos esperados.
Verifica la integridad referencial con las tablas relacionadas.
Si implementaste dimensiones tipo 2, asegúrate de que los registros históricos se gestionen correctamente.

Comentarios (11)11 comentarios

Marcos Vargas Mayta

Hace 3 años

Maximiliano Vazquez Massaro

John Alexander Peña Bedoya

jose gregorio medina salas

Gabriel Maxemin Ramirez

Norberto Iván Tolaba

Ian Cristian Ariel Yané

Manuel Juarez

Alex Mauricio Báez Espinosa

Cristian Pujota

Luis Ernesto Domínguez Velásquez

Leonardo Martin Mendez

Introducción a BI y Data Warehouse

Qué es Business Intelligence y Data Warehousing

Niveles de analítica y jerarquía de datos

Qué son data warehouse, data mart y dimensiones

OLTP vs OLAP: diferencias clave

Inmon, Kimball y Hefesto en BI

Modelos dimensionales

Data Warehouse, Data Lake y Lakehouse

Modelo estrella vs copo de nieve en datos

Tipos de dimensiones lentamente cambiantes

Dimensión tipo 1: sobrescribir sin guardar historia

Dimensión tipo 2

Dimensión tipo 3: historia en columnas

Tabla de hechos (fact)

Configuración de herramientas para Data Warehouse y ETL

Cómo extraer dimensiones de preguntas de negocio

Diseño de tablas en un modelo dimensional

ETL para inserción en Data Warehouse

Documento de mapeo en ETL para data warehouse

Creando tablas dimensionales en Redshift

Extracción: querys en SQL

Cruce de fuentes en Pentaho con Stream Lookup

Transformación ETL con Pentaho paso a paso

Carga de datos transformados a Redshift con Pentaho

Cómo cargar la tabla de hechos con Pentaho

Cómo calcular MaxID y MaxDate en Pentaho

Orquestar ETL en Pentaho: job

Solución ETL con dimensiones en paralelo en Redshift

Cierre

De datos crudos a modelo dimensional listo

Transformación ETL con Pentaho paso a paso

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos