Documento de mapeo en ETL para data warehouse

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:0019:25

Documento de mapeo en ETL para data warehouse

Curso de Data Warehousing y Modelado OLAP

Clase 16 de 26

Clase 16 / 26

Recursos

Antes de mover un solo dato hacia tu data warehouse, necesitas saber de dónde sale cada campo y cómo llega a su destino. Ahí entra el documento de mapeo en ETL, una herramienta que te ayuda a rastrear el origen, el tipo de dato y las transformaciones necesarias para construir un modelo dimensional confiable. Es la guía que conecta tus fuentes con tu modelo final.

¿Qué es un documento de mapeo y para qué sirve en un proceso ETL?

Es el plano que documenta cada campo de tu modelo dimensional y su relación con las fuentes de datos. Sin él, cualquier proceso de extracción y transformación se vuelve adivinanza.

La propuesta de plantilla que se trabaja en la clase [00:32] divide la información en dos grandes bloques: target y source. El target representa el destino, es decir, la dimensión o tabla del data warehouse a la que llegará el dato. El source describe la fuente, con detalles como tabla, columna, si es primary key, qué transformación aplicar y observaciones relevantes.

Una ventaja clave: para un mismo target puedes tener varios sources. Si tu dimensión cliente se alimenta de tres bases distintas, agregas columnas adicionales de source y mantienes la trazabilidad.

¿Qué incluye un documento de mapeo de datos? Incluye el campo destino (target), su tipo de dato, la tabla y columna origen (source), si esa columna es llave primaria, la transformación a aplicar y observaciones. Sirve para documentar el flujo completo de un ETL.

¿Cómo mapear una dimensión cliente desde varias tablas?

El ejercicio parte del modelo dimensional ya construido sobre la base de datos de Adventure Works [02:00], con dimensiones de clientes, productos, vendedores (como dimensión lentamente cambiante tipo dos, con fecha de inicio y fin) y territorios.

Para la dimensión clientes se listan los campos target: ID cliente, código del cliente como varchar, nombre, apellido, nombre completo, números de celular, casa y trabajo, y ciudad de residencia. A partir de ahí, comienza la cacería del dato.

¿Dónde encuentro los datos del cliente en la base?

Al consultar la tabla Sales.Customer [03:30], aparece un CustomerID que se convierte en el código del cliente, y un PersonID que conecta con la tabla Person. La tabla de clientes no tiene nombres ni apellidos directamente; esos atributos viven en Person.

Para descubrir cómo se relacionan las tablas, basta con clic derecho sobre la tabla en el esquema correspondiente y seleccionar ver diagrama [05:10]. Ese diagrama muestra que Customer se une con Person a través de PersonID y BusinessEntityID.

FirstName y LastName salen de la tabla Person.
CustomerID sale de Customer y es la primary key.
El nombre completo no existe en la base, así que requiere una transformación de concatenación entre nombre y apellido.

¿Cómo manejo teléfonos cuando están en filas y no en columnas?

Esta parte es interesante. La tabla PersonPhone [07:45] guarda los teléfonos por filas, no por columnas. Cada fila tiene un PhoneNumberTypeID que distingue el tipo: 1 para celular, 2 para casa y 3 para trabajo.

Para llevarlo al modelo dimensional necesitas una transformación que pivotee esos valores: capturas el campo PhoneNumber tres veces, cada una filtrando por el tipo correspondiente. Esto convierte filas en columnas y deja los tres teléfonos disponibles como atributos del cliente.

¿Por qué un modelo dimensional facilita el análisis? Porque consolida en una sola dimensión los atributos que en la base operacional estaban dispersos en varias tablas. Quien analice los datos no tiene que reconstruir relaciones complejas cada vez.

¿Cómo documento las transformaciones y la ciudad del cliente?

Para la ciudad de residencia, el recorrido pasa por BusinessEntityAddress [10:30], que conecta a la persona con un AddressID y un AddressTypeID. Desde Address puedes obtener directamente el campo City, que es lo que pide la regla de negocio, y filtrar por el tipo de dirección que corresponde a la casa.

En el documento de mapeo quedan registradas todas las condiciones: la tabla origen, la columna específica y la transformación con la condición exacta. Por ejemplo, para celular la observación es "cuando PhoneNumberTypeID = 1".

¿Qué tipos de transformaciones puedo encontrar en un ETL?

Las transformaciones no se limitan a concatenar o filtrar. Aparecen varios escenarios típicos cuando trabajas con datos crudos:

Homologación de valores: dos textos que significan lo mismo pero se escriben distinto y deben unificarse.
Normalización de fechas: formatos diferentes que deben llegar a un único estándar.
Desagrupación de campos: valores agrupados que se separan en columnas o atributos adicionales.
Concatenación: combinar nombre y apellido para construir un nombre completo.
Pivoteo de filas a columnas: cuando un atributo está en filas con un tipo identificador, como los teléfonos.

Después de aplicar estas transformaciones, tu modelo queda mucho más robusto y listo para análisis.

¿Qué reto sigue después de mapear la dimensión cliente?

El ejercicio propuesto [16:20] es completar el documento mapeando las tablas de producto, territorio, vendedor y la tabla de hechos. La idea es que apliques el mismo método: identificar target, rastrear sources con el diagrama de la base, definir llaves primarias y describir cada transformación necesaria.

Una vez tengas ese mapeo, el siguiente paso es modelarlo físicamente en la base de datos y crear las tablas. Cuéntame en los comentarios cómo resolviste el mapeo de la tabla de hechos y qué transformaciones identificaste.

Mario Alexander Vargas Celis

Hace 2 años

Un documento de mapeo es una herramienta clave en procesos de ETL y modelado de datos, ya que especifica cómo se deben transformar y mover los datos desde las fuentes hacia los destinos, como un Data Warehouse. A continuación, se detallan los pasos para crearlo y cómo realizarlo:

1. Definir el objetivo del documento

¿Qué se necesita lograr?
- Consolidar datos en un Data Warehouse.
- Transformar datos para un reporte específico.
- Cargar datos en una tabla destino con un esquema definido.
¿Quiénes son los usuarios principales?
- Equipo de ETL, analistas de datos, o desarrolladores.

2. Identificar las fuentes de datos

Pasos:
- Listar las bases de datos, archivos, o APIs que contienen la información necesaria.
- Especificar detalles técnicos:
  - Nombre de la base de datos o archivo.
  - Tablas, columnas, o endpoints relevantes.
  - Tipos de datos en cada columna.
  - Reglas de acceso o autenticación.
**Ejemplo:**FuenteTabla/ArchivoColumnaTipo de DatoDetallesCRMClientesID_ClienteINTClave primariaERPVentasFecha_VentaDATEFecha de venta

3. Definir el destino

Pasos:
- Establecer las tablas destino y sus esquemas.
- Documentar las relaciones entre tablas si es un modelo dimensional.
- Identificar restricciones (índices, claves foráneas, etc.).
**Ejemplo:**Tabla DestinoColumnaTipo de DatoRestriccióndwh.dim_clientesid_clienteINTClave primariadwh.fact_ventastotal_ventaDECIMAL(10,2)No nulo

4. Mapear las transformaciones

Pasos:
- Especificar cómo se transformarán los datos:
  - Cambios de formato (fechas, números).
  - Unión de datos de múltiples fuentes.
  - Limpieza de datos (eliminar duplicados, manejar nulos).
- Incluir reglas de negocio que afecten las transformaciones.
- Definir validaciones requeridas.
**Ejemplo:**FuenteColumna OrigenTransformaciónColumna DestinoCRM.ClientesNombre, ApellidoCONCAT(Nombre, ' ', Apellido)Nombre_CompletoERP.VentasFecha_VentaFORMAT(Fecha_Venta, 'yyyy-MM-dd')FechaERP.Ventas, CRM.ClientesID_Venta, ID_ClienteJOIN por ID_ClienteCliente_Venta

5. Especificar los controles de calidad

Pasos:
- Definir qué verificaciones asegurarán la calidad de los datos.
  - Validar valores nulos o atípicos.
  - Verificar que las claves primarias son únicas.
  - Comparar totales entre origen y destino.
- Documentar los procedimientos de remediación para errores.
**Ejemplo:**ValidaciónReglaAcción en Caso de ErrorDuplicadosID_Cliente debe ser únicoEliminar duplicadosRango de FechasFecha_Venta > '2000-01-01'Ignorar registros fuera del rangoTipos de DatosTotal_Venta es DECIMAL(10,2)Notificar error al desarrollador

6. Diseñar el flujo ETL

Pasos:
- Dibujar un diagrama que muestre:
  - Extracción de fuentes.
  - Transformaciones aplicadas.
  - Carga en destino.
- Incluir herramientas o scripts utilizados.

7. Crear un cronograma

Pasos:
- Establecer el orden de ejecución de las tareas.
- Definir dependencias y tiempos estimados.

8. Documentar excepciones y reglas adicionales

Pasos:
- Incluir notas sobre:
  - Reglas de negocio específicas.
  - Limitaciones conocidas de las fuentes de datos.
  - Manejo de errores (logs, alertas, etc.).

9. Validar el documento

Pasos:
- Revisar el documento con los equipos involucrados (negocio, desarrollo, QA).
- Ajustar según sea necesario antes de iniciar el proceso ETL.

10. Ejemplo Completo de Mapeo

FuenteTabla DestinoColumna OrigenTransformaciónColumna DestinoReglas/NotasCRM.Clientesdwh.dim_clientesNombre, ApellidoCONCAT(Nombre, ' ', Apellido)Nombre_CompletoAplicar trim() para espacios extras.ERP.Ventasdwh.fact_ventasFecha_VentaFORMAT(Fecha_Venta, 'yyyy-MM-dd')FechaFechas en formato ISO 8601.ERP.Ventasdwh.fact_ventasTotal_VentaRedondear a 2 decimalesTotal_VentaIgnorar ventas negativas.

Este documento detallado servirá como guía durante la implementación del proceso de ETL y ayudará a todos los equipos a mantenerse alineados.

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

Documento de mapeo en ETL para data warehouse

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos