Principios de diseño para

procesos de ETL

Rafael Salas, SQL Server MVP

Rafael Salas

Data Warehouse & Inteligencia de Negocio+18 años – 3 Paises– multiple industrias

Microsoft Data Platform

MVPDesde 2007

Arquitecto de InformaciónAlly, Charlotte, NC

Charlotte BI GroupCo-Fundador

rafael-salas.com@RafSalassalas_rafael@hotmail.com

Agenda

1. ¿Qué tan sencillos son los procesos ETL?

2. Para empezar: Requerimientos y arquitectura

3. Errores comunes y soluciones

Advertencia

No demos, No Código, No formulas mágicas

Traducir PPT no fue tan fácil. Perdonen cualquier spanglish

¿Qué tan sencillos son los

procesos ETL?

ETL? Si, Eso es Fácil!

A Veces la Realidad es Otra

ETL Nunca Ha Sido Fácil

Cual son los mayores impedimentos para el éxito del manejo de información en su empresa?

59% Acceso a información confiable

46% Integración de datos (ETL)

41% Saneamiento y de-duplicación de datos

InformationWeek Reports, 2012 BI and Information Management Trends

The ETL Data Warehouse toolkit, 2004. Joe Caserta / Ralph Kimball

Por donde empezar:

Requerimientos y arquitectura

Requerimientos

FuncionalContenido, información

RegulatoriosRegulaciones legales

Calidad de información“Aptitud para su uso”

SeguridadBackups, datos en transito, staging, código, etc.

Archivado, retención y linajeTransparencia, auditoría, monitoreo

Interfaces de consumo “Aptitud para su uso”

Habilidades técnicas disponibles

A veces la mejor herramienta es aquella que sabemos

como usar

¿De que Depende un Buen Proceso ETL?

Extraer Cargar

Operatividad, Instrumentación

Transformar

RequerimientosFuncionales

Regulatorios

Calidad de Información

Seguridad

Etc.

ArquitecturaPatrones

Diseño

Buenas Practicas

Extraer

Detectar Cambios

Staging

Extraer

¿Que ha cambiado desde la ultima ejecución?Procese solo registros nuevos o cambiados es mas eficiente

Extraer Vs. RecibirMecanismos consistentes come publicar-subscribir tiende a reducir complejidad y aumenta estabilidad del sistema ETL

¿Almacenar o no almacenar? Archivos planos son simples y fácil de implementar

¿Cómo se van a ensamblar los registros?Ej. Vienen de diferentes sistemas? A diferentes intervalos?

¿Tenemos claras la reglas de de-duplicación?Mecanismos consistentes tipo publicar-subscribir tiende a reducir complejidad y aumenta estabilidad del sistema ETL

¿Cómo se evalúan y procesan registros defectuosos? Detección, manejo y auditoria son siempre necesarios

Otras transformacionesGeneración de PKs, Conversiones , etc, etc, etc

Transformar

Limpiar

Otras transformaciones

De-duplicar

Manejo de Excepciones

Entender estructuras de datos en el destino y sus dependenciasEs importante que el ETL entregue los datos en un formato y manera fácil y amigable

Rutinas post-procesoe.g. reconstruir indeces, procesar cubos, calentar memoria, etc

Cargar

Agregar

Cargar

Post-Procesos

Operatividad, InstrumentaciónHorario Ejecución Re-EjecucionesMonitor de Actividad

Auditoria

Backup

Notificaciones Seguridad Regulatorio

¿Proceso termino correctamente?

¿Podemos monitorear el proceso?

¿Tengo suficientes detalles en caso de un error de ejecución?

¿Tengo suficiente infromaciónpara auditoria o resolver problemas?

¿En caso de un error, puede el proceso re-iniciarse sin mayor intervención?

¿Podemos garantizar la seguridad del código fuente, y los datos en transito y en disco?

¿Puedo dormir tranquilo por las noches?

Beneficios De Una Buena Arquitectura

Consistencia y estabilidad

Reducción de costos: Mantenimiento, capacitación

Re-usabilidad

Infraestructura & Infromación

ETL App

ETL App

ETL AppETL App

Infraestructura & Infromación

ETL App

ETL Architecture

ETL App ETL App

Errores comunes y soluciones

Problema: Ignorar Realidad de los Datos

Diseño del ETL basado en opiniones y

conocimiento cotidianoacerca de los

datos

Ensayo y error!

Profiling Mapeo

Planeamiento & Diseño

Extraer Limpiar Transformar Cargar

Implementación

Entender el nivel de Calidad de la informacion

Correcta

A tiempo

Completa…

Validación de las reglas de ETL

Podemos estimar mejor tiempo y

esfuerzo

°Data Quality: The Accuracy Dimension, Jack E. Olson

Problema: Mal Manejo de Excepciones

Sistemas Inestables

Malos requirementos, información questionable , mala programación

Sistemas inestables

Infrastructura inestable

Ejemplo de Manejo de Excepciones

Excepción detectada

Capture detalles del error

Envíe notificación

Aborte ejecución, reporte error

ETL Ejecutando

Procesandoregistros

Registro invalidodetectado

Límite de errores

alcanzado?0

Se puedecorrejir?

CorríjaloExcluyaregistro

Procesesiguienteregistro

Si

No

NoSi

Problema: Insuficiente información de Ejecución de

Procesos

Resolver errores de ejecución en

producción

Monitoreo de los procesos

Optimizar tiempo de ejecución

Auditoria

23

Evalue opciones disponiblesProvistas por la herramienta, desarrollada a la medida, ambas

Considere crear reportes

Logging

24

Problema: Procesos No Re-Iniciables

Despues de un error tenemos queIntervencion manual para re-iniciar proceso

Correr el riesgo de omitir registros

Correr el riego de duplicar registros

Re-iniciar desde el principio

Ejemplo: Lógica de Re-Inicio

1. Definir unidad de re-inicioProceso Principal: ej. Paquete maestro

Sub-Proceso: ej. Paquete subordinado

2. Definir estados del programaProceso Principal:

Ejecutando, Exito, Error

Sub-Proceso:

Ejecutando, Exito, Error, No ejecutado

Obejetivo: Re-inicia donde falló la última vez

No se omite registros

No duplica registros

No require intervención manual

Proceso Principal

Obtener estado de ejecución anterior

Ejecuciónanterior exitosa?

Emita nuevo identificadorde ejecución para proceso

principal

Re-use identificador de ejecución de proceso

principal

Ejecute sub-procesos

Registre inicio de procesoprincipal

Si

Registre re-inicio de procesoprincipal (+1) & Actualize

variables the ejecución

No

A

Registre final de Proceso

Inicio ProcesoPrincipal

B

Obtener estado de ejecucionanterior para esta

combinacion de Procesoprincipal y sub-proceso

Sub-proceso

antes ejecutado

?

Retorne control a procesoprincipal

Prepare re-ejecución de sub-proceso (cleanup)

Ejecute sub-proceso

Sub-procesoexitoso?

registre ejecucion de sub-proceso

no

si

no

si

Sub-ProcesoA

Inicio Sub-Proceso

registre re-inicio de sub-proceso (+1) & Actualize variables the ejecución

Registre final de sub-proceso

B

En Conclusión…

ETL Requiere Arquitectura

Extraer

Detectar Deltas

Staging

Transform

Limpiar

Deduplicar

Manejo de Exceptiones

Load

Agregar

Cargar

Procesar

Operatividad, InstrumentaciónHorario Ejecución Re-EjecucionesMonitor de Actividad

Auditoria

Backup

Notificaciones Seguridad Regulatorio

Extraer

30

ETL sigue siendo complicado y costosoInvierta tiempo en su architectura.

ETL no es solo mover datos: mantenimiento, capacitacion, estabilidad, seguridad,

auditoria, etc

No olvide que calidad de la información es un asunto del

negocio mas que de tecnología

31

Referencias

The Data Warehouse Toolkit, 3rd edition - ETL Subsystems & Techniques.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-

techniques/etl-architecture-34-subsystems/

SSISDB + Custom Logging – Rafael Salas

rafael-salas.com/2012/08/ssis-2012-and-my-old-etl-framework.html

SSIS Reporting Pack + Restart – Jamie Thomson

http://ssisreportingpack.codeplex.com/

Preguntas?

Gracias por participarFollow @pass24hop

Share your thoughts with hashtags

#pass24hop & #sqlpass