16.4. Comparativa ETL Talend vs Pentaho Data Integration (Kettle).

(Read in English language here)

Vamos a intentar en esta ultima entrada de la serie de procesos ETL realizar una comparativa lo mas completa posible de las herramientas Talend Open Studio y Pentaho Data Integration (Kettle), que hemos estado utilizando en los últimos meses. Para que este estudio sea lo mas completo y riguroso posible, vamos a dividir esta tarea en 5 secciones:

 

Ejemplo de proceso ETL utilizando Talend

• Tabla de características.
• Ejemplos de Uso.
• Cuadro de puntos fuertes/puntos debiles.
• Links de recursos (comparativas e informacion adicional).
• Conclusiones.

Tabla de Características.

Producto	TALEND OPEN STUDIO ver.4.0	PENTAHO DATA INTEGRACION CE (KETTLE) ver 3.2
Fabricante	Talend – Francia	Pentaho – Estados Unidos
Web	www.talend.com	www.pentaho.com
Licencia	GNU Lesser General Public License Version 2.1 (LGPLv2.1)	GNU Lesser General Public License Version 2.1 (LGPLv2.1)
Lenguaje de desarrollo	Java	Java
Año de lanzamiento	2006	2000
Entorno gráfico	Herramienta gráfica basada en Eclipse	Herramienta de diseño (Spoon) basada en SWT
Entorno de ejecución	Desde la herramienta de diseño, o a nivel de línea de comandos con Java o Perl (independiente de la herramienta)	Desde la herramienta de diseño, o desde línea de comandos con las utilidades Pan y Kitchen.
Características	Con la herramienta de diseño construimos los Jobs, utilizando el set de componentes disponibles.Trabaja además con el concepto de proyecto, que es un contenedor de los diferentes Jobs que lo forman y sus metadatos y contextos.Talend es un generador de código, de forma que los Jobs definidos son traducidos al correspondiente lenguaje (podemos elegir Java o Perl al crear un proyecto), compilados y ejecutados.Los componentes se enlazan entre si con diferentes tipos de conexiones. Una son de paso de información (que pueden ser del tipo Row o Iterate, segun la forma de pasar los datos). Ademas, se pueden conectar unos con otros con conexiones disparadoras (Run If, If Component Ok, If Component Error), que nos permiten articular la secuencia de ejecución y su control.Una vez se termina el diseño de los trabajos, se exportan a nivel de SO, y se pueden ejecutar independientemente de la herramienta de diseño en cualquier plataforma que permita la ejecución del lenguaje seleccionado. Además, todo el código generado es visible y modificable (aunque lo modifica la herramienta al realizar cualquier cambio en los Jobs).	Con la herramienta de diseño Spoon construimos las transformaciones (mínimo nivel de diseño) utilizando los pasos o steps. En un nivel superior tenemos los Jobs, que permiten ejecutar las transformaciones y otros componentes, y orquestar los procesos.PDI no es un generador de código, es un motor de transformación, donde los datos y sus transformaciones están separadas.Las Transformaciones y Jobs son almacenadas en formato XML, donde se especifican las acciones a realizar en los datos.Para construir las transformaciones, se utilizan los pasos o componentes, que se enlazan entre si mediante saltos o steps, que determinan el flujo de datos entre los diferentes componentes.Para los jobs, tenemos otro set de pasos, en los que podemos realizar diferentes acciones (o ejecutar transformaciones). Los steps o saltos en este caso determinan el orden de ejecución o la ejecución condicional.
Componentes	Talend tiene una gran cantidad de componentes. El enfoque es tener un componente distinto según la acción a realizar, y para el acceso a   base de datos u otro sistemas, hay componentes diferentes según el motor de base de datos al que vayamos a atacar. Por ejemplo, tenemos un table input para cada fabricante (Oracle, MySQL, Informix, Ingres) o uno para la gestión de SCD para cada SGBDR. Podeis ver la lista de componentes disponibles aquí.	Set mas reducido de componentes, pero muy orientados a integración de datos. Para acciones similares (por ejemplo, lectura de tablas de BD), un único paso (no uno por cada fabricante), y el comportamiento según la base de datos lo define la conexión. Los elementos disponibles son: para las transformaciones ver aquí y para los jobs aquí.
Plataforma	Windows, Unix y Linux.	Windows, Unix y Linux.
Repositorio	Trabaja con el concepto de workspace, a nivel de sistema de ficheros. En ese lugar se almacenan todos los componentes de un proyecto (todos los Jobs, su definición de metadatos, código personalizado y contextos). El repositorio se actualiza con las dependencias de objetos al ser modificados (se expanden a todo el proyecto los cambios). Si modificamos el repositorio de una tabla, por ejemplo,  se actualiza en todos los Jobs donde se utiliza.	Los Jobs y transformaciones son almacenados en formato xml. Podemos elegir almacenar a nivel de sistema de fichero o en el repositorio de base de datos (para trabajo en equipo). No se actualizan las dependencias si modificamos una transformación llamada desde otra, por ejemplo. Si a nivel de componentes dentro de una misma transformación o job.
Metadatos	Completo metadata que incluye las conexiones a base de datos y los objetos de esta (tablas, vistas, querys). Al traer el metadata de los objetos de BD deseados, no se vuelve a acceder al sistema origen o destino, lo que agiliza los procesos. Además, podemos definir metadatos de estructuras de ficheros (delimitados, posicionales, Excel, xml, etc), que luego pueden ser reutilizados en cualquier componente. Se almacenan de forma centralizada en el repositorio (workspace)	EL metadata se limita a las conexiónes de base de datos, que si pueden ser compartidas por diferentes transformaciones y jobs.La información de metadata de base de datos (catalogo de tablas/campos) o de ficheros esta en los pasos y no se puede reutilizar. Además se lee en el momento de diseño.
Contextos	Set de Variables que se configuran en el proyecto y que se pueden utilizar luego en los Jobs para personalizar su comportamiento (por ejemplo para definir entorno de desarrollo y productivo).	Uso de variables en fichero de parámetros de la herramienta (fichero kettle. Properties). Paso de parámetros y argumentos a los procesos (similar a los contextos).
Versiones	Nos permite realizar una completa gestión del versionado de objetos (pudiendo recuperar versiones anteriores)	Funcionalidad prevista en la versión 4.0.
Lenguajes para definir componentes propios (scripting)	Talend nos permite introducir nuestro código personalizado utilizando Java y Groovy.Además podemos realizar scripting en SQL y Shell.	PDI utiliza JavaScript para los cálculos y formulas.Ademas podemos realizar scripting con SQL, Java, Shell y formulas Open Office.
Herramientas complementarias	Talend dispone de herramientas complementarias para Data Profiling y Gestión de Datos Maestros (MDM). En Open Studio tenemos una herramienta de modelado sencilla para dibujar procesos y modelos lógicos.	PDI ofrece en la versión 4.0 la funcionalidad Agile para modelado dimensional y su publicación en Pentaho BI.
Plugins	Descarga de nuevos componentes a través de Talend Exchange.	Incorporación de plugins adicionales en la web.
Soporte	Un completa comunidad online con Talend’s wiki, Talend Forum y bugtracker, para la gestión de incidencias y Bugs.	Incluye el Pentaho forum, Issue Tracking y la Pentaho Community.
Documentación	Completa documentación en pdf que incluye: Manual de Instalación, Manual de Usuario y Documentación de componentes.	Documentación Online en la web. Libros: Data Integration-Pentaho 3.2 Beginner’s Guide(M.C.Roldan), Pentaho Kettle Solutions (M.Casters, R.Bouman, J.van Dongen).

Ejemplos de Uso.

EJEMPLO	TALEND OPEN STUDIO	PENTAHO DATA INTEGRATION (KETTLE)
Carga de dimensión tiempo de un DW	Proceso ETL para la carga de la Dimensión Tiempo. Ejemplo de uso de la ETL Talend.	ETL Dimensión Tiempo con PDI.
Ejecución de sentencias sql dinámicas	Mas ejemplos de Talend. Ejecución de sentencias SQL construidas en tiempo ejecución.	Paso de parametros y operaciones dinamicas en una transformación de PDI.
Carga de dimensión producto de un DW	ETL para carga Dimension Producto. Mas ejemplos de Talend. Uso de logs, metricas y estadisticas.	ETL Dimensión Producto con PDI (I). Extraccion a Stage Area.ETL Dimensión Producto con PDI (II). Carga al DW.
Carga de dimensión cliente de un DW	ETL Talend Dimension Cliente.Tipos de Mapeo para lookup.	ETL Dimensión Cliente con PDI.
Tratamiento de dimensiones lentamente cambiantes	Gestión de SCD (Dimensiones lentamente cambiantes).	Tratamiento de Dimensiones Lentamente Cambiantes (SCD) con PDI.
Conexión al ERP Sap	Ejemplo Talend para conectarnos a Sap	Ejemplo Kettle para conectarnos a Sap (con el plugin ProERPConn)
Carga de tablas de hechos de ventas en un DW	Tabla Hechos Venta. Ajuste diseño fisico y procesos carga ETL. Contextos en Talend.	ETL Carga de hechos de ventas con PDI.ETL Carga de hechos de presupuestos con PDI.
Exportación de Jobs y planificación procesos	Exportación jobs en Talend.Planificacion procesos ETL.
Tratamiento de datos públicos	Modelo de Datos y Procesos de Carga del DW de datos públicos de Londres.
Explicación de la interfaz de usuario		Construccion procesos ETL utilizando Kettle (Pentaho Data Integration)
Proyecto de Grado (comparativa Pentaho/JasperETL). Realizado por Rodrigo Almeida y Mariano Heredia	Proyecto fin de carrera donde se detalla un proyecto de Business Intelligence, utilizando las herramientas de Pentaho y Jasper. En la parte de ETL´s, se compara Kettle con JasperETL (basado en Talend).	Descarga del libro en la web: http://sites.google.com/site/magm33332/bifloss. Incluye un magnifico detalle de las características de cada herramienta.

Como ejemplos adicionales, podeis consultar igualmente:

• Tutorial de Talend Open Studio 4 realizado por Victor Javier Madrid en la web adictosaltrabajo.com. Incluye otro ejemplo para procesar un fichero EDI con Talend.
• Libro blanco de las heramientas ETL Open Source, realizado en frances por Atol. Incluye una serie de ejemplos prácticos muy completos.

 

Ejemplo de proceso ETL utilizando Pentaho Data Integration

Cuadro de puntos fuertes/puntos debiles.

Desde mi punto de vista, con la experiencia de utilización de las dos herramientas y la información recopilada sobre las herramientas y la experiencia de usuarios, puedo destacar los siguientes aspectos:

TALEND OPEN STUDIO	PENTAHO DATA INTEGRATION (KETTLE)
Es un generador de código, y este aspecto hace que tenga una gran dependencia del lenguaje elegido en los proyectos (Java en mi caso). Al elegir Java, tenemos todas las ventajas e inconvenientes de este lenguaje. Se necesita tener un nivel alto de este lenguaje para sacarle el máximo partido a la aplicación.	Es un motor de transformación, y desde el principio se observa que ha sido diseñado por gente que necesitaba cubrir sus necesidades en la integración de datos, con gran experiencia en ese ámbito. Igualmente, es mas fácil gestionar los tipos de datos con PDI, pues no es tan riguroso como Java.
Herramienta poco intuitiva y difícil de entender, pero una vez superada esta dificultad inicial se observan las grandísimas posibilidades y la potencia de la aplicación.	La herramienta es muy intuitiva, y con unos conceptos básicos te puedes poner a hacer cosas. Conceptualmente muy sencilla y potente.
Interfaz de usuario unificada en todos los componentes. Basada en Eclipse, el conocimiento de la herramienta nos facilita el uso de la interfaz.	El diseño de la interfaz puede resultar un poco pobre, y no hay una interfaz unificada en todos los componentes, siendo en ocasiones la interfaz de los componentes confusa.
La empresa Talend esta invirtiendo gran cantidad de recursos en su desarrollo (gracias a aportaciones de capital de varios fondos de inversión), lo que esta produciendo una evolución muy rápida de la herramienta. Se observa un gran potencial de futuro. El producto además se esta complementando con otras herramientas para Data Profiling y MDM.	Evolución mucho más lenta de la herramienta e incierta, pues Pentaho tiende a abandonar la parte Open.
Gran disponibilidad de componentes para conectarnos a múltiples sistemas y orígenes de datos, y en continua evolución.	Disponibilidad de componentes más limitada, aunque más que suficiente para la mayoría de procesos ETL o de integración de datos.
Aunque no disponemos de un repositorio en base de datos para trabajo en equipo (si en las versiones de pago), el trabajo con el Workspace nos da muchas posibilidades, al trabajar con el concepto de proyecto. Muy útil el análisis y actualización de dependencias cuando se modifican elementos (que son distribuidos a todos los Jobs de un proyecto).	El repositorio en base de datos nos da muchas posibilidades para el trabajo en equipo. En este repositorio se almacenan los xml que contienen las acciones que realizan sobre los datos las transformaciones y los Jobs.
Enfoque un componente por cada fabricante en los elementos para trabajar con bases de datos.	Enfoque un único componente por tipo de acción sobre la base de datos (y las características de la conexión utilizada son las que determinan su comportamiento).
Ayuda contextual en la aplicación. Completa ayuda online de los componentes. Cuando diseñamos nuestros propios controles en Java, tenemos la ayuda contextual del lenguaje proporcionada por Eclipse.	Ayuda muy pobre, casi inexistente en la aplicación. La ayuda online en la web de Pentaho no es especialmente completa, y en algunos componentes es muy reducida, de forma que la única forma de averiguar el funcionamiento del componente es probándolo.
Logs: podemos configurarlos a nivel de proyecto o en cada uno de los Jobs. Indicando si queremos sobrescribir la configuración del proyecto en ese aspecto. El log se puede enviar a base de datos, a consola o a ficheros. La funcionalidad esta muy desarrollada, distinguiendo logs de estadísticas, de métricas de procesos y de los logs propiamente dichos (para gestionar los errores).	Logs: diferentes niveles de logs (desde el más básico, hasta el nivel de cada registro). Suficiente para analizar las ejecuciones de las transformaciones y jobs. Posiblidad de registrar log en base de datos, aunque muy limitado. Los logs se configuran a nivel de transformaciones y Jobs.
Debug: con la perspectiva Debug de Eclipse, podemos seguir la traza de la ejecución (viendo el código fuente) tal y como si estuviéramos programando en Eclipse. También se puede incluir visualización de estadísticas y datos de traza o tiempos de respuesta en la ejecución de la herramienta gráfica.	Debug: contiene una sencilla herramienta de debug, muy básica.
Versionado de objetos: en el Workspace tenemos una completa gestión de versionado de Jobs (con minus y major number). Nos permite recuperar las versiones anteriores en caso de problemas. Dispone de una herramienta para modificación masiva de versiones de objetos (puede ser muy util para realizar el versionado de distribuciones).	El versionado de objetos esta previsto que se incluya en la versión 4.0.
Paralelismo: muy reducido en las versiones Open. Funcionalidad avanzada en las versiones de pago (Integration Suite).	Paralelismo: es muy sencillo realizar paralelismo de procesos con la opcion Distribute Data en la configuración del paso de información entre pasos, aunque habrá que llevar cuidado con las incosistentencias según el tipo de proceso.
Generación de documentación automática en HTML de los Jobs. Incluye visualización gráfica de los diseños, tablas de propiedades, documentación adicional o textos explicativos que hayamos introducido en los componentes, etc. Podéis ver un ejemplo aquí.	No se puede generar documentación de las transformaciones y Jobs. A nivel gráfico, podemos incluir notas con comentarios en el dibujo de los procesos. Con el proyecto kettle-cookbook se puede generar documentacion en html.
Herramienta sencilla para modelado gráfico. En ella podemos dibujar de forma conceptual los diseños de nuestras Jobs y procesos.
Generación continúa de nuevas versiones, que incorporan mejoras y arreglo de Bugs.	La generación de nuevas versiones no es muy frecuente, y tenemos que generarnos nosotros mismos las versiones actualizadas con los últimos fuentes disponibles: Ver entrada del Blog de Fabian Schladitz.
Talend Exchange: lugar donde la comunidad elaborar sus propios componentes y los comparte con el resto de usuarios.	Pentaho también dispone de colaboradores que desarrollan y liberan plugins en su web, aunque con menos actividad que Talend.
Como punto negativo, en ocasiones se nota en demasía la lentitud ocasionada por el uso de Java.	Como punto negativo, algunos componentes no se han comportando de la forma esperada, al realizar transformaciones muy complejas o al enlazar llamadas entre diferentes transformaciones a Jobs. Los problemas se pudieron superar cambiando el diseño de las transformaciones.
Es una ventaja tener un repositorio en local, donde nos guardamos en local la parte que nos interesa de la información de bases de datos, tablas, vistas, estructuras de ficheros (texto, Excel, xml). Al estar en el repositorio, se pueden reutilizar en los componentes asociados y no se tiene que volver a leer de los orígenes de datos su metadata cada vez que se usa (para el caso de las bases de datos, por ejemplo). Dispone de un asistente de construcción de querys (SQL Builder) muy potente y con muchas funcionalidades.	Cuando trabajamos con bases de datos con catálogos muy grandes, es incomodo tener que recuperarlo entero para poder construir, por ejemplo, una sentencia sql para leer de una tabla (cuando utilizamos la opción de navegación por el catalogo).
Reutilización de código: podemos incluir nuestras propias librerías, que son visibles en todos los Jobs de un proyecto. Esto nos permite de alguna manera diseñar nuestros propios componentes.	El código escrito en JavaScript dentro de los componentes no se puede reutilizar en otros componentes. Bastante limitado para añadir nuevas funcionalidades o modificar las existentes.
Control del flujo en los procesos: tenemos por un lado flujo  de datos (row, iterate o row lookup) y por otro lado disparadores para control de ejecución y orquestación de procesos. La combinación row e iterate es muy útil para orquestar procesos del tipo bucle, con el objetivo de repetir tareas. Hay un inconveniente importante que nos puede complicar el diseño de los jobs, y es que no se pueden juntar varios flujos de datos que provengan del mismo origen (han de tener un diferente punto de partida), nunca se han de cruzar en un elemento padre común.	Control del flujo en los procesos: se pasa la información entre componentes (pasos) con los saltos, de una forma única, y el flujo resultante varía según el tipo de control. Esta forma de interactuar tiene limitaciones para el control de procesos iterativos. Como característica interesante, la encapsulación de transformaciones a traves de los mappings, que nos permite definir transformaciones para procesos repetitivos (similar a una funcion).
Gestión de errores: cuando se producen errores, podemos gestionar el log, pero perdemos el control.	Gestión de errores: con la gestión de errores en los pasos que lo permiten, podemos interactuar con esos errores y solucionarlos sin terminar el proceso (no es siempre posible).
Ejecución: bien desde la herramienta (que a veces es muy lenta, sobre todo si incluimos estadísticas y trazas de ejecución). Para ejecutar a nivel de línea de comandos, se exportan los Jobs. La exportación genera todos los objetos (librerias jar) necesarias para poder ejecutar el trabajo, incluyendo un .bat o .sh para ejecutarlo. Esta forma de exportar los Jobs nos permite llevarlos a cualquier plataforma donde se pueda ejecutar el lenguaje java o perl, sin necesidad de tener instalado Talend.	Ejecución: bien desde la herramienta (tiempos de respuesta bastante buenos) o bien a nivel de comandos con Pan (para las transformacionse) y Kitchen( para los jobs). Son dos utilidades muy sencillas y funcionales que nos permiten ejecutar los xmls de los diseños (bien desde fichero o desde el repositorio). Siempre es necesario tener instalado PDI para poder ejecutar las herramientas. También disponemos de la herramienta Carte, que es un sencillo servidor web que permite la ejecución remota de jobs y transformaciones.

Comparativas e Información adicional.

Benchmark entre Pentaho Data Integration y Talend realizado por Matt Caster.	http://www.ibridge.be/?p=150. Opiniones sobre la prueba en el blog de Gobán Saor y Nicholas Goodman
Comparativa entre Kettle y Talend, realizada por Vicent McBurnety (año 2007)	http://it.toolbox.com/blogs/infosphere/wiki-wednesday-comparing-talend-and-pentaho-kettle-open-source-etl-tools-16294
Comparativa entre Kettle, Talend y CloverETL.	http://www.cloveretl.org/_upload/clover-etl/Comparison%20CloverETL%20vs%20Talend%20and%20Pentaho.pdf
Benchmark entre PDI, Talend, Datastage e Informatica	http://it.toolbox.com/blogs/infosphere/etl-benchmark-favours-datastage-and-talend-28695
Comparativa de funcionalidades y benchmark entre Talend y PDI	http://www.atolcd.com/fileadmin/Publications/Atol_CD_Livre_Blanc_ETL_Open_Source.pdf
Comparativa entre PDI e Informatica	http://www.jonathanlevin.co.uk/2008/03/pentaho-kettle-vs-informatica.html
Tabla comparada de características en la web Openmethodology.org	Talend vs PDI (Kettle).
Ejemplo sencillo comparado Kettle y Talend	http://forums.pentaho.org/showthread.php?t=57305
Comparativa de herramientas ETL realizada por la empresa Adeptia.	http://www.adeptia.com/products/etl_vendor_comparison.html
Pentaho Kettle Open Source Review	http://www.datawg.com/pentaho-kettle-open-source-etl-tool-review.html
Benchmark entre Pentaho Data Integration y Talend realizado por Marc Russel.	http://marcrussel.files.wordpress.com/2007/08/benchmark-tos-vs-kettle.pdf
Comparativa entre Datastage, Talend, Informatica y PDI (Manapps).	http://marcrussel.files.wordpress.com/2009/02/etlbenchmarks_manapps_090127.pdf
Presentación PowerPoint comparativa Kettle vs Talend	http://svn2.assembla.com/svn/bbdd_dd/Presentaciones/Kettle%20Vs%20Talend.pptx
EOS: Open Source Directory.	http://www.eosdirectory.com/project/397/Talend+Open+Studio.html vs http://www.eosdirectory.com/project/202/KETTLE++pentaho+data+integration+.html

Conclusiones.

Desde mi punta de vista, creo que ambas herramientas son complementarias. Cada una con un enfoque, pero permiten realizar las mismas tareas de transformación e integración de datos. El producto Talend tiene para mi mas recorrido, pues se estan poniendo muchos recursos en su desarrollo, y se esta complementando con otras herramientas para crear una verdadera suite de integración de datos. También se utiliza en el proyecto de Jaspersoft. Igualmente, el hecho de ser mas abierto y poder ser complementado con el uso del lenguaje Java también le da ciertas ventajas con respecto a Pentaho.

Por otro lado, Pentaho Data Integration es una herramienta muy intuitiva y facil de utilizar. Se nota desde el principio cuando se empieza a usar, como ya mencione, que esta desarrollada bajo el prisma de las problematicas de procesos ETL y transformación de datos. En algunas aspectos se le ve más rápida y ágil que Talend, al no tener que estar moviendo generación de código Java en todo momento. Aunque si se echa de menos la gestión de un repositorio de proyectos verdaderamente integrado, como en Talend, y con un metadatos independiente de los origenes/destinos de datos.

A nivel de rendimiento, y revisando las diferentes comparativas, tampoco se ve un claro ganador. Una herramienta es mas rapida en algunas cosas (Talend en calculo de agregaciones o Lookups), mientras Pentaho es mas rápido, por ejemplo en el tratamiento de SCD o con los procesos de paralelización. En mis procesos ETL tampoco ha habido unas grandes diferencias de rendimiento, aunque si me ha parecido ligeramente más ágil Pentaho a la hora de realizar los procesos masivos.

Teniendo en cuenta todo lo visto (y todo lo detallado en los puntos anteriores), yo me decanto ligeramente a favor de Talend, aunque antes de elegir una herramienta para un proyecto, yo realizaría un estudio profundo del tipo de trabajos y casuisticas a las que nos vamos a enfrentar en el diseño de nuestros procesos antes de decantarme por una u otra herramienta. Seguramente habrá factores especificos que pueden recomendar el uso de una u otra (como la necesidad de conectarnos a determinada aplicación o las plataformas donde ejecutar los procesos). Y lo que esta claro en ambos casos, es que cualquiera de las dos nos podría valer para los procesos de construcción de un DW en un entorno real (como he demostrado en este blog con toda la serie de ejemplos publicados).

Si habeis trabajado con alguna de las herramientas, o ambas, quizas tengais algo que añadir a esta comparativa. Espero vuestras opiniones.

Actualización 18.06.10

Os dejo el link al ultimo estudio de Gartner sobre Integración de Datos, publicado en noviembre de 2009:

Magic Quadrant for Data Integration Tools.

En el ultimo estudio se ha incluido a Talend como un proveedor emergente de herramientas de Integración de Datos. Si estáis pensando en trabajar con Talend, se dicen cosas interesantes (tanto Pro´s como contras) sobre la evolución del producto y su futuro:

Puntos Fuertes:

• Dos niveles: nivel de entrada con herramienta Open Source gratuita (Talend Open Studio) y nivel superior con herramienta de pago con mas funcionalidades y soporte (Talend Integration Suite).
• Talend esta consiguiendo casi por unanimidad resultados positivos en las empresas. Aunque el factor determinante inicial puede ser su precio, sus características y funcionalidades son el segundo factor determinante de su exito.
• Buena conectividad en general. Complementada con las herramientas de Data Profiling y Data Quality. El paso de las versiones Open a las de pago no requiere curva de aprendizaje adicional.

Precauciones:

• Hay escasez de expertos en la herramienta, aunque se esta desarrollando una red de alianzas con otras empresas y desarrollando su red comercial (aunque no estan en todas las regiones).
• Existen algunos problemas en las herramientas de pago con el repositorio central (que no esta en la versión  Open), cuando se trabaja para coordinar trabajos de desarrollo. Parece que lo estan intentando solucionar en las nuevas versiones.
• Algunos clientes han reportado que la documentación es errónea y algún problema en la gestión de los metadatos. Igualmente, es necesario disponer de un experto en Java o Perl para sacarle todo el partido a la herramienta (como indicamos en la comparativo con Pentaho).

Os recomiendo la lectura del informe, se dicen cosas muy interesantes en general sobre las herramientas de integración de datos y en particular si estais buscando información de alguna en concreto (como es Talend, el objeto de esta comparativa de productos).

ETL´s: Talend Open Studio vs Pentaho Data Integration (Kettle). Comparative.

(Read in Spanish language here)

Let’s try in this latest entry of the ETL processes series to make a comparison as complete as possible of Tools Talend Open Studio and Pentaho Data Integration (Kettle), which we have been using in last months. For this study to be as comprehensive and rigorous as possible, we will divide the task in 5 sections:

 

Using Talend Open Studio in ETL Process Design

• Property table.
• Examples of Use
• Strengths / weaknesses table.
• Resource Links (comparative and additional information.)
• Final opinion.

Property table.

Product	TALEND OPEN STUDIO ver.4.0	PENTAHO DATA INTEGRACION CE (KETTLE) ver 3.2
Manufacturer	Talend – France	Pentaho – United States
Web	www.talend.com	www.pentaho.com
License	GNU Lesser General Public License Version 2.1 (LGPLv2.1)	GNU Lesser General Public License Version 2.1 (LGPLv2.1
Development Language	Java	Java
Release Year	2006	2000
GUI	Graphical tool based on Eclipse	Design Tool (Spoon) based on SWT
Runtime Environment	From design tool, or command line with Java or Perl language (independent of the tool)	From design tool, or command line with utilities  Pan and Kitchen .
Features	With the design tool build the Jobs, using the set of components available.Work with project concept, which is a container of different Jobs with metadata and contexts. Talend is a code generator, so Jobs are translated into corresponding defined language (Java or Perl can choose when create a new project), compiled and executed .Components bind to each other with different types of connections.  One is to pass information (which may be of Row or Iterate, as how to move the data).  Also, you can connect with each other triggering connections (Run If, If Component Ok, If Component Error) that allow us to articulate the sequence of execution and ending time control.Jobs are exported at SO, and can run independently of the design tool on any platform that allows the execution of the selected language.  In addition, all generated code is visible and modifiable (although you modify the tool to make any changes to the Jobs).	With the design tool built Spoon transformations (minimum design level) using the steps. At a higher level we have the Jobs that let you run the transformations and other components, and orchestrate process. PDI is not a code generator, is a transformation engine, where data and its transformations are separated.The transformations and Jobs are stored in XML format, which specifies the actions to take in data processing. In transformations use steps, which are linked to each other by jumps, which determine the flow of data between different components. For the jobs, we have another set of steps, which can perform different actions (or run transformations). The jumps in this case determine the execution order or conditional execution.
Components	Talend has a large number of components. The approach is to have a separate component as the action to take, and access to databases or other systems, there are different components according to the database engine that we will attack. For example, we have an input table component for each manufacturer (Oracle, MySQL, Informix, Ingres), or one for the SCD management  for each RDBMS. You can see available components list here.	Smallest components set, but very much oriented towards data integration. For similar actions (eg reading database tables), a single step (no one from each manufacturer), and behavior according to the database defined by the connection. You can see available elements for transformations here and for jobs here.
Platform	Windows, Unix and Linux.	Windows, Unix and Linux.
Repository	Works with the workspace concept, at filesystem level.  In this place you store all the components of a project (all Jobs, metadata definitions, custom code and contexts). The repository is updated with the dependencies of changend objects (expand to all project changes.) If we change the table definition in repository, for example, is updated in all the Jobs where it is used.	The Jobs and transformations are stored in XML format. We can choose to store at  file system level or in the database repository (for teamwork). Dependencies are not updated if you change a transformation who is called from another. If the level of components within a single transformation or job.
Metadata	Full metadata that includes links to databases and the objects (tables, views, querys).  Metadata info is centrally stored in workspace and its not necesary to read again from source or destination system, which streamlines the process. In addition, we can define metadata file structures (delimited, positional, Excel, xml, etc), which can then be reused in any component.	The metadata is limited to database connections, which metadata can be shared by different transformations and  jobs.Database information (catalog tables / fields) or files specifications (structure) is stored in steps and can not be reused. This info is read in design time.
Contexts	Set of variables that are configured in the project and that can be used later in the Jobs for your behavior (for example to define productive and development environment).	Using Variables in tool parameters file (file kettle.properties). Passing parameters and arguments to the process (similar to the contexts), both in jobs and transformations.
Versions	It allows us to perform a complete management of objects versions (can recover previous versions)	Functionality provided in version 4.0.
Languages to define their own components (scripting)	Talend allows us to introduce our custom code using Java and Groovy.Additional custom SQL and Shell.	JavaScript used for the calculations and formulas.Aditional custom SQL, Java, Shell and open office formulas.
Additional tools	Talend offers additional tools for Data Profiling and Master Data Management (MDM). Open Studio have too a simple modeling tool to draw logical processes and models.	PDI 4.0 offers Agile functionality  for dimensional modeling and models publication in Pentaho BI.
Plugins	Download new components through Talend Exchange .	Incorporation of additional plugins in the web .
Support	An complete online community with Talend’s wiki , Talend Forum and bugtracker for the management of incidents and Bugs.	Includes forum Pentaho , Issue Tracking and Pentaho Community .
Documentation	Complete documentation in pdf format that includes: Installation, User Manual and Documentation of components.	Online Documentation  on the web. Books: Data Integration-Pentaho 3.2 Beginner’s Guide(M.C.Roldan), Pentaho Kettle Solutions (M.Casters, R.Bouman, J.van Dongen).

Examples of Use (in Spanish)

EXAMPLE	TALEND OPEN STUDIO	PENTAHO DATA INTEGRATION (KETTLE)
Charging time dimension of a DW	ETL process to load the time dimension. Example use of the ETL Talend.	Time Dimension ETL with PDI.
Implementation of dynamic sql statements	More examples of Talend. Execution of SQL statements constructed in execution time.	Passing parameters and dynamic operations in a transformation of PDI.
Loading a product dimension DW	Product Dimension ETL load. More examples of Talend. Using logs, metrics and statistics.	Product Dimension ETL with PDI (I).Extraccion to Stage Area. ETL Product Dimension with PDI (II). Loading to DW.
Charging a customer dimension DW	Talend ETL Mapping Types. Customer Dimension ETL.	Customer Dimension ETL with PDI.
Treatment of slowly changing dimensions	Management of SCD (slowly changing dimensions.)	Treatment Slowly Changing Dimensions (SCD) with PDI.
Connecting to ERP Sap	Connecting to Sap with Talend	Connecting to Sap with Kettle (plugin ProERPConn)
Charging sales fact tables in a DW	Sales Facts table. Talend contexts.	Loading ETL sales made with PDI. ETL load PDI budgeting facts.
Exporting Jobs and planning processes	Export Talend.Planification jobs in ETL processes.
Treatment of public data	Data Model and Process Load DW London’s public data.
Understanding the user interface		Building ETL processes using Kettle (Pentaho Data Integration)
Graduation Project (comparative Pentaho / JasperETL). Developed by Rodrigo Almeida, Mariano Heredia.	Thesis which details a Business Intelligence project using the tools of Pentaho and Jasper.  In the ETL’s, compared Kettle with JasperETL  (based on Talend).	Download the book on the web: http://sites.google.com/site/magm33332/bifloss. It includes a magnificent detail of the features of each tool.

As further examples, you can also consult:

• Tutorial Talend Open Studio 4 by Victor Javier Madrid on the web adictosaltrabajo.com. Includes another example to process an EDI file with Talend.
• White Paper on Open Source ETL tools conducted in French by Atol. It includes a series of practical examples very complete.

 

Using Pentaho Data Integration (Kettle) in ETL Process Design

Table of strengths / weaknesses.

From my point of view, with the user experience of the two tools and the information collected about tools and others user experience, I can highlight the following aspects:

TALEND OPEN STUDIO	PENTAHO DATA INTEGRATION (KETTLE)
It is a code generator and this issue implies a heavy dependence of the project chosen language (Java in my case). By choosing java, we have all the advantages and disadvantages of this language. You need a high level of this language to get the most out of the application.	It is a transformation engine, and notes from the outset has been designed by people who needed to meet their needs in data integration, with great experience in this field. It is also easier to manage the datatypes with PDI, it is not as rigorous as Java.
Tool unintuitive and difficult to understand, but once you overcome this initial difficulty, we observe the very great potential and power of the application.	Very intuitive tool,  with some basic concepts can make it works. Conceptually very simple and powerful.
Unified user interface across all components. Based on Eclipse, the knowledge of the tool enables us to use the interface.	The design of the interface can be a bit poor and there is no unified interface for all components, being sometimes confusing.
Talend is investing significant resources in its development (through capital injections from various funds), which is producing a very rapid development of the tool. With a great potential, the product also is being supplemented with other tools for MDM and Data Profiling.	Much slower tool evolution and uncertain because Pentaho tends to leave the OpenSource focus.
Greater availability of components to connect to multiple systems and data sources, and constantly evolving.	Limited availability of components, but more than enough for most ETL or data integration process.
There is not a database repository (only in paid versions),but work with Workspace and the project concept gives us many opportunities. Very useful dependency analysis and update when elements are modified (which is distributed to all the Jobs of a project).	Database repository gives us many opportunities for teamwork. In this repository is stored xml, containing the actions that Transformations and Jobs take on the data.
A separated component by each database vendor.	A single component by database action type (and the characteristics of the connection used are those that determine their behavior).
Help shortcut in the application. Comprehensive online help components. When we designed our own code in Java, we have the context assistance of language  provided by Eclipse.	Help poor, almost nonexistent in the application.  The online help in the Pentaho website is not particularly full, and in some parts is very small, so that the only way to determine the functioning of the component is test it.
Logs: We can configure at project level or in each Job, indicating if we want to overwrite the configuration of the project in this regard.  Log can be sent to database,  console or file.  The functionality is very developed, distinguishing logs of statistics, metrics and process logs (to handle errors).	Logs: different logs levels (from the most basic to the row detail in data flow). Sufficient to analyze the execution of the transformations and jobs. Possibility of record logs in database, but very limited. Log configuration is set at the level of transformations and Jobs.
Debug: Debug perspective in Eclipse, we can keep track of implementation (see the source code) as if we were programming in Eclipse.  You can also include statistics and data display trace or response times in the execution of jobs using the graphical tool.	Debug: Contains a simple debug tool, very basic.
Versioning of objects: in the Workspace we can manage a complete versioning of the Jobs (with minus and major number). It enables us to recover earlier versions if problems occur.  It has a massive tool to change versions of lots of objects (can be very useful for versioning of distributions).	Object versioning is scheduled to be included in version 4.0.
Parallelism: very small in Open version. Advanced functionality in paid versions (Integration Suite).	Parallelism: parallelism is very easy to make,using the Distribute Data option in the configuration of the information exchange between steps, but will have to take care with not consistent, depending type of process.
Automatic generation of HTML jobs documentation. Includes graphical display of the designs, tables of properties, additional documentation or explanatory texts that we have introduced in the components, etc.  You can see an example here .	Unable to generate documentation of changes and Jobs. Using the graphics tool, we can include notes with comments on the drawing process. With project kettle-cookbook you can generate html documentation.
Simple tool for modeling chart. With it  we can conceptually draw our Jobs designs and processes.
Continuous generation of new versions, incorporating improvements and bug fixes.	The generation of new versions is not very common and we need to generate versions ourseles updated with the latest available sources: see blog entry of Fabin Schladitz .
Talend Exchange : place where the community develop their own components and share them with other users.	Pentaho also offers fans who develop and release plugins on their website , with less activity than Talend.
As a negative point, sometimes excessively slow caused by the use of Java language.	As a downside, some components are not behaving as expected, to perform complex transformations or by linking calls between different transformations in Jobs. The problems could be overcome by changing the design of transformations.
It is an advantage to have a local repository where we store locally  information about database, tables, views, structures, files (text, Excel, xml). Being in the repository can be reused and associated components not need to re-read of the  data source metadata every time (for the case of databases, for example).  It has a query assistant (SQL Builder) with many powerful features.	When working with databases with very large catalogs, it is inconvenient to have to recover the entire building, for example, a sql statement to read from a table (when we use the option of browsing the catalog).
Code reuse: we can include our own libraries, which are visible in all Jobs in a project.  This allows us a way to design our own components.	JavaScript code written in the step components can not be reused in other components. Fairly limited to adding new functionality or modify existing ones.
Control of flow processes: on the one hand we have the data flow (row, iterate or lookup) and on the other triggers for control execution and orchestration of processes. The row and iterate combination is useful to orchestrate the  loop process, with the aim of repetitive process.  There is a major inconvenience we may complicate the process: you can not collect several data streams coming from the same origin (must have a different point of departure).	Flow control processes: passing information between components (steps) with jumps, in a unique manner, and the resulting flow varies with the type of control used.  This approach has limitations with the control of iterative processes.  As an interesting feature, encapsulation of transformations through the mappings, which allows us to define transformations for repetitive processes (similar to a function in a programming language).
Handling errors: when errors occur, we can manage the log, but we lose control. We cant reprocess rows.	Error management: errors management in the steps allow us to interact with those mistakes and fix them without completing the process (not always possible, only in some steps).
Execution: either from the tool (which is sometimes very slow, especially if you include statistics and execution traces). To run at command line level, is necesary to export Jobs. The export generates all objects (jar libraries) necessary to perform the job, including a .bat or .sh file to execute it. This way allow us to execute the job in any platform where you can run java or perl language, without needing to install Talend.	Execution: either from the tool (pretty good response times) or at command level with Pan (for transformations) and Kitchen(for jobs) . They are two very simple and functional utilities that allow us to execute XMLs  specifications of jobs or transformations (either from file or from the repository). It is always necessary to run the process have installed the PDI tool. We have also Carte tool, a simple web server that allows you to execute transformations and jobs remotely.

Comparatives and additional information.

Benchmark between Talend and Pentaho Data Integration by Matt Caster.	http://www.ibridge.be/?p=150. Reviews about in Goban Saor and Nicholas Goodman blogs.
Comparison between Kettle and Talend, by Vicent McBurnety (2007)	http://it.toolbox.com/blogs/infosphere/wiki-wednesday-comparing-talend-and-pentaho-kettle-open-source-etl-tools-16294
Comparison between Kettle, Talend and CloverETL.	http://www.cloveretl.org/_upload/clover-etl/Comparison% 20CloverETL 20vs%%%% 20and 20Talend 20Pentaho.pdf
Benchmark between PDI, Talend, Datastage e Informatica	http://it.toolbox.com/blogs/infosphere/etl-benchmark-favours-datastage-and-talend-28695
Compare features and benchmark between Talend and PDI	http://www.atolcd.com/fileadmin/Publications/Atol_CD_Livre_Blanc_ETL_Open_Source.pdf
Comparison between PDI and Informatica	http://www.jonathanlevin.co.uk/2008/03/pentaho-kettle-vs-informatica.html
Comparative table of features on the web Openmethodology.org	Talend vs PDI (Kettle).
Simple example comparing Kettle and Talend	http://forums.pentaho.org/showthread.php?t=57305
Comparison of ETL tools Adepti made by Adeptia company.	http://www.adeptia.com/products/etl_vendor_comparison.html
Pentaho Kettle Open Source Review	http://www.datawg.com/pentaho-kettle-open-source-etl-tool-review.html
Benchmark between Talend and Pentaho Data Integration by Marc Russel.	http://marcrussel.files.wordpress.com/2007/08/benchmark-tos-vs-kettle.pdf
Comparison between Datastage, Talend, Informatica and PDI (Manapps).	http://marcrussel.files.wordpress.com/2009/02/etlbenchmarks_manapps_090127.pdf
PowerPoint Presentation comparative Talend vs. Kettle	http://svn2.assembla.com/svn/bbdd_dd/Presentaciones/Kettle%
EOS: Open Source Directory. EOS: Open Source Directory.	http://www.eosdirectory.com/project/397/Talend+Open+Studio.html vs http://www.eosdirectory.com/project/202/KETTLE++pentaho+data+integration+.html

Summary (final opinion).

From my point of view, I think both tools are complementary. Each one with a focus, but allow the same tasks of transformation and data integration. The product Talend has more future, since they are putting many resources in its development, and is being supplemented with other tools to create a true data integration suite. Also used in the  Jaspersoft project, the fact of being more open and can be complemented with the use of Java gives certain advantages over Pentaho.

By the other hand, Pentaho Data Integration is a very intuitive and easy to use. You can see from the beginning when you start to use, as I mentioned, which is developed through the prism of the problems of ETL processes and data transformation. In some aspects it is faster and more agile than Talend, not having to be moving Java code generation all the time. We misses the management of a truly integrated project repository, such as Talend, and an independent metadata of source/target systems.

A level of performance, and reviewing the different comparations and benchmarks, not see a clear winner. A tool is faster in some things (Talend in calculating  additions or Lookups), while Pentaho is faster, for example in the treatment of SCD or the parallelization process. In my ETL processes there have been no large differences in performance, although I found slightly more agile Pentaho when performing mass processes.

Taking into account all seen (and everything detailed in the above), I opted for Talend slightly, but before choosing a tool for a project, I conduct a thorough study of the type of work and casuistic to which we will face in the design of our processes before opting for one or other tool. You may have specific factors that may recommend the use of one or another (such as the need to connect to a particular application or platform in which to run the process). What is clear in both cases, is that either we could hold for the processes of construction of a DW in a real environment (as I have shown in this blog with the whole series of published examples.)

If you have worked with some of the tools, or both, maybe have to add something to this comparison. I hope your opinions.

Updated 18/06/10

I leave the link to the last Gartner study about Data Integration Tools, published in November 2009:

Magic Quadrant for Data Integration Tools.

In the latter study was included Talend  as a emerging provider of data integration tools. If you are considering working with Talend, they say interesting things (both Strengths and Cautions) on the evolution of the product and its future:

Strong Points:

• Two levels: entry-level Open Source tool free (Talend Open Studio) and higher with a payment tool with more features and support (Talend Integration Suite).
• Talend is getting almost unanimously positive results in business. Although the initial factor may be its price, its features and functionality are the second factor in its success.
• Good connectivity in general. Complemented with the tools of Data Profiling and Data Quality. The passage of the versions to pay Open requires no extra learning curve.

Precautions:

• There is a shortage of experts in the tool, although it is developing a network of alliances with other companies and develop its commercial network (although they are not in all regions).
• There are some problems with the central repository (which is not in the Open version), when working to coordinate development works. Looks like they are trying to solve in the new versions.
• Some customers have reported that the documentation is wrong and some problems in  metadata management. It is also necessary to have an expert in Java or Perl to take full advantage of the tool (such as indicated in comparison with Pentaho).

I recommend reading the report, there is a lot of information about data integration tools and particularly if you are looking for information on any individual (such as Talend, the purpose of this comparative product).

16.3.5. ETL Carga de hechos de presupuestos con PDI.

En esta ultima entrada de la serie construiremos el proceso ETL para la carga de la tabla de hechos de presupuestos. Vamos a simular que los datos no están en el ERP, sino en unas hojas excel de usuario, que ademas van a tener una estructura un tanto peculiar para introducir el uso de otros pasos de Kettle. Las tablas de hechos a llenar son las siguientes:

Para llenar estas tablas, disponemos de unos ficheros en formato Excel (uno para cada año de presupuesto). Por ejemplo, para la carga de la Previsión de Ventas por Tipo Cliente, el fichero se llamara tipoclienteYYYY.xls, donde YYYY será el año de presupuesto que incluye el fichero. Vamos a dividir el job en tres etapas, que realizaran las siguientes acciones:

• Pre proceso de carga: leemos los ficheros (para uno o varios años), depositados en un directorio. Para cada fichero, recuperamos de su nombre el año y borramos de la tabla de hechos los registros existentes en ese periodo.
• Proceso ETL: leemos el contenido de cada fichero, hacemos el tratamiento de la casuistica de su estructura, y denormalizamos, dejando al final una estructura como la de la tabla, realizando los procesos de inserción en la base de datos.
• Post proceso: movemos todos los ficheros tratados a un directorio de procesados (para que no se vuelvan a tratar).

Teniendo en cuenta todo esto, tendremos el siguiente diseño de job:

Job carga presupuestos

Vamos a ver en detalle cada una de las transformaciones, pues hemos incluidos elementos nuevos no vistos hasta ahora y que os pueden resultar interesantes:

Transformación para borrado de datos anteriores.

En esta transformación hemos incluido el uso del paso Get File Names (categoría Input). Con este control podemos recuperar una lista de ficheros, para poder realizar posteriormente un tratamiento sobre ellos (pues recuperamos su nombre, directorio y características). Ademas, el control nos permite utilizar expresiones regulares para determinar que regla han de cumplir los nombres de los ficheros.

Configuracion del Get File Names

A partir de cada nombre de fichero, obtenemos mediante una formula el año, y realizamos el borrado de los registros de ese año, utilizando el paso Delete y enviando información al log (como vimos en los ejemplos anteriores). El diseño de la transformación teniendo en cuenta todo lo visto, sería el siguiente:Transformacion para procesado de fichero, transformación y carga.

Esta transformación es la principal de este Job, y antes de verla en detalle, vamos a hacer algunas consideraciones sobre la estructura del fichero que vamos a procesar, pues ello va a determinar que problemática vamos a tener que abordar para su procesamiento.

Estructura Hoja Excel

Como vemos, tenemos dos lineas en la hoja excel para cada tupla mes/tipo cliente. Además, el mes y el tipo de cliente solo se indican en la primera linea de cada grupo. Esto nos va a obligar a procesar cada linea del fichero por un lado para que todos los registros tengan valor en el mes y tipo de cliente, y por otro lado, tendremos que desnormalizar el contenido del fichero, para generar un único registro por mes y tipo de cliente (en lugar de los 2 que llegan en el fichero). Vamos a ver en detalle cada uno de los pasos de la transformación y la manera en que hemos solucionado la problemática descrita:

• Excel Input: es un paso de la categoría Input que nos permite recuperar el contenido de una o varias hojas excel (o de sus libros), y pasar su contenido al flujo de datos de las transformaciones. Ademas, igual que en el Text File Input que ya vimos, podemos utilizar expresiones regulares para indicar los patrones que han de seguir los nombres de los ficheros que queremos recuperar.

Configuración del Excel Input

• Proceso Campos Blanco: como hemos visto antes, esta casuística es un tanto especial y para tratarla, no nos queda mas remedio que utilizar código Javascript utilizando el control Modified Java Script Value. Con una pocas lineas de código solucionamos el problema, tal y como podeís ver. Hemos utilizado una variable auxiliar, que llenamos en la primera linea ( sentencia getProcessCount(‘r’) == 1 ), y luego la vamos llenando conforme nos llegan las siguientes lineas. Si el campo esta en blanco, utilizamos el valor de la variable auxiliar (en caso contrario, nos guardamos en la variable el valor). Asi evitamos dejar los campos en blanco:

//Script here

var mes_aux;
if (getProcessCount('r') == 1) mes_aux = Mes;
if (Mes == null)
mes_aux = mes_aux;
else
mes_aux = Mes;

var tipo_aux;
if (getProcessCount('r') == 1) tipo_aux = Tipo_Cliente;
if (Tipo_Cliente == null)
tipo_aux = tipo_aux;
else
tipo_aux = Tipo_Cliente;

• Denormalizo Registros: el otro problema de nuestro ejemplo es que tenemos dos lineas para cada mes y tipo de cliente. Para juntarlas, PDI nos ofrece un paso para el tratamiento de este tipo de casuisticas (Row De-normaliser). Para poder utilizarlo, es necesario que los registros se pasen ordenados por las claves de grupos, pues en caso contrario no funciona correctamente. Podeis ver en la imagen como se configura el paso y la explicación de cada una de las secciones.

Configuracion del step Denormaliser

En el caso de querer generar varios registros a partir de uno único, utilizaremos el step Row Normaliser, definido en PDI para ese tipo de cometidos. Para entender mejor el ejemplo, podemos ver como queda el flujo de datos despues de pasar por el step. De cada dos lineas de la hoja excel, hemos pasado a una unica linea, y han aparecido dos nuevas columnas (VENTAS_PREV Y MARGEN_PREV), que antes teniamos en el campo Valor en cada una de las líneas:

Datos tras utilizar el Denormaliser

• Preparo Campos e Inserto DW: una vez tenemos desnormalizados los registros, ya solo nos queda preparar los campos que queremos mandar al Table Output, utilizando para el ello el Select Values que tanto hemos utilizando en nuestros anteriores ejemplos.

La estructura completa de la transformación sería esta:Transformación para mover ficheros.

Para terminar, volvemos a recorrer la lista de ficheros, construimos el nombre del fichero destino con una Formula, y lo movemos utilizando el step Process files (categoría Utility). De esta forma, movemos todos los ficheros que hemos tratado a otro directorio donde ya no se tendrán en cuenta. El diseño de la transformación será el siguiente:Con el paso Process files podemos básicamente realizar tres tipos de acciones sobre los ficheros: Mover, Copiar o Borrar. Para las dos primeras acciones, será necesario indicar el nombre de fichero origen, y el destino, que determinara igualmente el directorio o carpeta destino. Ademas, tenemos disponibles otros flags para crear el directorio destino en el caso que no exista, sobrescribir el fichero destino o la posibilidad de utilizar el control en modo test.

Configuracion del Process Files

Os dejo el link a todos los ficheros utilizados en el ejemplo aquí.

Job final para la ejecución de todos los procesos de carga de nuestro DW.

Para terminar, podriamos haber construido un job que realizara la ejecución de forma secuencial de todos los jobs definidos hasta ahora. Con su lanzamiento realizariamos la actualización periódica y completa de nuestro DW. El fichero del job lo ejecutaremos utilizando la herramienta Kitchen y lo planificaremos en nuestro sistema con la cadencia deseada para su automatización (utilizando cron o at, según estemos en Unix/Linux o Windows, o bien utilizando cualquier otra herramienta que nos permita planificar la ejecución de procesos).

Conclusiones.

Hemos terminado el diseño de los procesos de carga de nuestro DW. En este ultimo ejemplo hemos visto la forma de tratar uno o multiples ficheros, y que pasos tiene PDI para desnormalizar (o normalizar) el contenido de estos. Ahora toca hacer un poco reflexión y recopilación, y resumir todo lo visto hasta ahora sobre Talend Open Studio y Pentaho Data Integration para preparar la comparativa. Hemos intentado construir ejemplos completos y complejos para intentar sacar todo el partido a la herramienta, a la vez que hemos ofrecido ideas y técnicas para diseñar los procesos de carga de un DW. Todo lo visto es simulado y corresponde a un entorno figurado, pero que hemos intentado que se acerque lo más posible a las problemáticas reales que nos podemos encontrar en cualquier organización.

16.3.4. ETL Carga de hechos de ventas con PDI.

Estamos ya mas cerca de concluir los procesos de llenado de nuestro DW. Como proceso critico e importante para luego utilizar los datos en nuestros análisis, vamos a proceder al llenado de la tabla de hechos de ventas. Veremos otro ejemplo práctico de Kettle (Pentaho Data Integration) para llenar la tabla de hechos que tiene la siguiente estructura:

Estructura física de la tabla de hechos de Ventas

La configuración del proceso ETL va a ser la siguiente:

• Lectura de periodos a procesar: disponemos en el area de stage de una tabla de control, donde se insertan los periodos que se han de tratar en los procesos de traspaso. El llenado de esta tabla de control puede ser automático (a través de algún proceso programado) o bien manual (a petición del usuario). Cuando se lanza el proceso de carga, se leeran todos los periodos pendientes de tratar de esta tabla y se iran procesando uno por uno, marcandolos como procesados en el caso de que todo funcione correctamente.

Tabla de control de los periodos a procesar

• Borrado de datos anteriores del periodo en el DW: se borran del DW los datos del periodo a procesar, antes de comenzar su tratamiento.
• Lectura de pedidos: para cada periodo de los recuperados, vamos recorriendo los pedidos de ventas existentes en Sap (y sus correspondientes lineas) y los pasamos a los procesos de transformación.
• Transformación y carga al DW:preparamos los datos leidos para insertar en el DW. Eliminamos errores, transformamos campos, hacemos cálculos y recuperamos las claves subrogadas para los campos que las necesiten (recordar que en los datos recuperados del ERP tendremos los códigos del sistema operacional, y en las dimensiones con SCD hemos cambiado esos códigos por claves propias o subrogadas).
• Actualización del status del periodo: cuando se completa cada periodo, se actualiza el status a OK, incluyendose la fecha de proceso y el job que lo realizo.

Teniendo en cuenta esto, vamos a disponer de un único job, sencillo, que hará la llamada a la transformación donde incluiremos toda la lógica aquí descrita:

Job ETL Tabla Hechos Ventas

El diseño de la transformación será el siguiente:

Transformacion ETL Tabla Hechos Ventas

Vamos a analizar algunos de los pasos, haciendo hincapie en aquellos pasos que no hemos visto hasta ahora o que estamos utilizando de una forma diferente en esta transformación:

• Escribir mensajes al log con información de ejecución: cuando utilizamos el step del tipo Write to log, podemos seleccionar las variables del flujo de datos que queremos que sean mostradas en el log de ejecución, así como el nivel del log en el que aplicara. Este control puede ser util para insertar información de proceso, de verificación de valores de variables o de testigo/semaforo de estado. En mi ejemplo, muestro, despues del borrado de la tabla del DW, el número de registros borrados por periodo.

Configuracion de la escritura en el log

• Ordenar registros/eliminar duplicados: con los pasos Sort rows y Unique rows podemos ordenar por una clave los registros de nuestro flujo de datos y eliminar registros duplicados. En el ejemplo, he utilizado el Sort rows para evitar que se pasen a la lectura de pedidos rangos de fechas duplicados.
• Ejecución de dos sentencias sql consecutivas pasando parametros de valores de una a otra: al hacer la lectura de pedidos de venta desde nuestro ERP, podiamos haberlo hecho de dos maneras: construyendo una unica sentencia sql con el join de las dos tablas que necesitamos para la lectura, o bien hacer primero la lectura de las cabeceras, y a continuación, para cada pedido, leer sus correspondientes lineas en otra lectura (paso Table Input). Hemos elegido este segundo enfoque para ver la forma de pasar parámetros a la lectura de datos de tablas desde el flujo de datos. El funcionamiento es sencillo. El paso Tabla Input recibe del paso anterior (indicado en la configuración del paso en la casilla Insert data from step), los valores que queremos utilizar. En la sentencia SQL indicamos el caracter ? donde queremos que se utilicen esos argumentos. Cuando se ejecute la sentencia SQL (con la casilla Replace variables in script marcada), esos valores serán sustituidos por los valores del flujo de datos recibidos. Es muy importante el orden en que se recibe en el paso los campos, pues ese orden va a determinar el orden de sustitución de los correspondientes ? en la sentencia SQL. En el caso de no estar recibiendo los valores del flujo de datos de la transformación (la casilla Insert data from step vacia), los ? se sustituyen por los valores de los argumentos pasados a la transformación (igualmente en el orden indicado al ejecutar la transformación). Con este procedimiento podemos encadenar varios Table Input (como es el caso de nuestro ejemplo), utilizando los resultados de uno en la ejecución del siguiente.

Table Input con paso de parametros

• Recuperación de claves subrogadas en dimensiones con SCD2: como requirimiento importante al estar recuperando los datos de ventas, hemos de sustituir antes de insertar en la tabla de hechos de ventas los valores de las claves (de la dimensión cliente y producto en nuestro caso), cambiando el codigo del sistema operacional (cliente_id y material_id) por sus correspondientes claves subrogadas. Ademas, el valor de estas ha de ser el activo en la fecha del pedido. Para hacer esto, tenemos la posibilidad de utilizar el paso Dimension lookup/update (que ya utilizamos para el llenado de la dimensión cliente y producto), pero en este caso solo en modo lectura. El funcionamiento es sencillo: configuramos el paso como si fueramos a gestionar el tratamiento de SCD, pero desmarcamos el flag «Update the dimension«. En la pestaña Fields tampoco hace falta indicar nada. Ademas, al step le pasamos la fecha de validez donde queremos recuperar el valor de la clave subrogada (ese valor lo tenemos en la fecha de pedido recuperada del ERP, en el campo FECHA_ID del stream de datos). El control se comporta de forma que va a buscar en Base de Datos con los parámetros indicados, y me devuelve el valor de la clave subrogada valida para el código que le hemos pasado en la fecha indicada. Podeis ver un ejemplo de configuración del step en la imagen siguiente:

Subsitucion de claves por claves subrogadas

• Identifica ultimo registro en un flujo de datos: en el caso de que nos interese realizar algún tipo de operación o acción cuando se completa un flujo de datos, tenemos el paso Identify last row in a stream. Este paso nos permite poner una marca en el flujo de datos (del tipo boolean), que tendrá el valor true (Y) en el ultimo registro que se procese. Despues de él podemos insertar un paso del tipo Switch/Case, para verificar su valor y realizar la acción deseada.

Os dejo el link a los ficheros xml utilizados en este diseño de procesos ETL. Igualmente, os dejo el link a otra versión de los procesos utilizando 2 transformaciones en lugar de una (desde una transformación se realiza la lectura de periodos y con un mapping vamos haciendo llamadas a la otra subtransformación para realizar todo el procesamiento de los pedidos de ventas del periodo como hemos descrito).

Construcccion de Proceso ETL Alternativo con 2 Transformaciones

Con este proceso hemos concluido el llenado de la tabla de hechos de ventas. Seguimos avanzando en complejidad, conociendo mas en profundidad las posibilidades y la forma de funcionar de Kettle, a la vez que también vemos las limitaciones que presenta en algunos aspectos. El tema de las variables que se utilizan en muchos sitios nos ha dado algún que otro quebradero de cabeza, al igual que la forma de pasar la información de una transformación a otra  o la forma de construir bucles para procesos repetitivos. La forma de orquestar los procesos con los componentes Job/Transformación es muy flexible, pero desde mi punto de vista tiene algunas limitaciones cuando estamos hablando de procesos complejos, perdiendose un poco el control de la secuencia de acciones. Seguramente estos problemas se puedan solucionar dividiendo los jobs/transformaciones en otras mas sencillas, pero si comparamos con Talend, este tipo de problemáticas se resuelven mejor y de una forma integrada (sin tener que recurrir a dividir los procesos en otros mas sencillos y pasar la información de uno a otro, pues esto dificulta el proceso de diseño y la validación). También he detectado casos en los que los componentes no se comportan de la forma esperada, y es dificil encontrar documentación sobre la configuración de los pasos que sea completa y didáctica.

Ya tenemos muchisima información para poder hacer una comparativa profunda entre Talend y Kettle (PDI), que os mostraremos en una entrada posterior del blog.

Paso de parametros y operaciones dinamicas en una transformación de PDI.

Antes de continuar con el llenado de las tablas de hechos, vamos a ver un ejemplo de una transformación con paso de parametros, y como con dichos valores, podemos interactuar de forma dinámica contra la base de datos. Igualmente, veremos la forma de escribir mensajes de log o de abortar una transformación cuando no se cumplan determinadas condiciones.

Paso de parametros al ejecutar la transformacion

El objetivo de la transformación es el siguiente:

Pasaremos como parametros una tabla de bd de entrada, una tabla de bd de salida, un texto con las condiciones del where a aplicar en la lectura de la tabla de entrada y el modo de ejecución del proceso. Con dichos valores, la transformación realizará las siguientes acciones:

• Comprobación de parametros: todos los parametros han de tener valores. En el caso que no se indiquen, se cancelara la transformación y se mandara un mensaje al log de ejecución.
• Se prepara la sentencia SQL para borrar todos los registros existentes en la tabla  DESTINO. A continuación, se ejecuta dicha sentencia (solo en ejecución en modo REAL). Si hay errores, se pasa el mensaje al log.
• A continuación, se realiza la lectura de la tabla origen indicada en los parámetros, añadiendo las correspondientes condiciones del where.
• Como paso final, todos los registros recuperados de la tabla origen se insertan en la tabla destino (si es en modo REAL) o se descargan los datos a un fichero (en modo TEST). También se gestionan los errores en la carga de la tabla destino. Adenas, en todos los pasos de actualización de tablas hemos configurado el control de errores (Define Error Handling), que nos permite en el caso de errores pasar un mensaje al log (vimos la forma de configurarlo también en la entrada anterior del blog).
  

Transformacion con Paso Parametros

Vamos a ver un poco mas en detalle los pasos utilizados y algunas cosas interesantes que nos pueden ser utiles para el tratamiento de determinadas casuísticas:

• Uso de formulas para validar condiciones: en la entrada anterior del blog vimos como utilizar el step Formula para hacer calculos sobre los valores de los campos. También lo podemos utilizar para comprobar que se cumplan determinadas condiciones (como que los valores de los parametros pasados a la transformación tengan un valor). En nuestro caso, con las formulas calculamos el valor de un campo llamado CORRECTO que tendra el valor Y si se han pasado los parámetros correctamente, o el valor N en caso contrario. En el paso anterior a este hemos sustituido con un control del tipo If field value is null el valor nulo de los parametros por el texto «VACIO» para hacer más fácil la comprobación. La formula será la siguiente:

IF (OR([TORIGEN]=»VACIO» ; [TDESTINO]=»VACIO» ; [WORIGEN] = «VACIO») ; «N» ; «Y»)

• Escritura de mensajes de log y cancelación de procesos: en el caso de que los parametros pasados a la transformación no esten bien definidos, se muestra un mensaje de log y se aborta la ejecución de la transformación. Para ello, utilizamos los pasos del tipo Write to log y el del tipo Abort. En este caso, los pasos se ejecutan desde un Switch / Case, donde comprobamos el valor del campo CORRECTO, que hemos preparado en la formula anterior. Como ejemplo del texto que hemos sacado en log en esta situación, el siguiente:

2010/05/28 16:49:52 – ESCRIBO LOG 2.0 –
2010/05/28 16:49:52 – ESCRIBO LOG 2.0 – ————> Linenr 1——————————
2010/05/28 16:49:52 – ESCRIBO LOG 2.0 – MENSAJE = REVISE PARAMETROS TRANSFORMACION:TABLA ORIGEN: VACIO,TABLA DESTINO:VACIO
2010/05/28 16:49:52 – ESCRIBO LOG 2.0 –
2010/05/28 16:49:52 – ESCRIBO LOG 2.0 – ====================

• Ejecución de sentencias SQL dinámicas: en el paso EJECUTA_SQL_BORRADO hemos utilizado un step del tipo Execute SQL script. Este paso nos permite pasarle una sentencia SQL (cuyo contenido hemos construido de forma dinámica en el paso anterior de la transformación con un paso del tipo Formula, y dejado en una variable del tipo texto). Otra forma de ejecutar sentencias sql construidas en tiempo de ejecución es el paso Execute row SQL script, donde podemos utilizar tanto parametros o variables de la transformación, como campos del flujo de datos, tal y como vimos en la entrada anterior del blog (en la imagen se explica su funcionamiento).

Sentecias SQL Dinámicas

• Uso de table input con nombres de tabla, campos o condiciones variables: con este paso recuperamos los registros de la tabla de base de datos especificada en los parametros de la transformación. Tal y como veis en la imagen, sustituimos el nombre de tabla y la condición del where por las correspondientes variables. Estas determinan el comportamiento de la sentencia  sql y que registros son recuperados de la base de datos. Ademas, no estamos especificando los campos a recuperar en la sentencia sql (hemos indicado el valor * ).

Table Input con uso de variables

• Uso de table output con nombre de tabla variable: los registros recuperados en el paso anterior son grabados en base de datos, pero esta vez en la tabla especificada en los parametros de la transformación como tabla de salida. Podeis ver como hemos configurado el control para indicar el nombre de la tabla a partir del contenido del parámetro. Igualmente, hemos desmarcado el flag «Specify database fields» para no indicar los campos que queremos insertar, y que estos se recuperen del flujo de datos. La lista de campos a insertar aparece vacía, como podeís apreciar en la imagen.

Table Output con nombre de tabla en variable y sin especificar campos

En ninguno de los pasos Text File Output ni Table Output hemos indicado los campos con los que estamos trabajando (como hemos visto). Esto nos permite olvidarnos del catalogo de campos, y que todo sea gestionado de forma dinámica (en tiempo de ejecución). Aunque tendremos la restricción, en el caso del table output, que los campos destino (de la tabla donde vamos a insertar la información) han de tener las mismas propiedades que los origen (y el mismo número de campos). Para el caso de los ficheros de texto, no habrá ningun problema, pues se recuperan del metadata los nombres de los campos y el contenido del fichero se construira de forma dinámica (tal y como veis en la imagen). Como hemos configurado el step para que genere una linea dentro del fichero con los nombres de los campos, la primera linea del fichero incluye esta información (linea de cabeceras). Teniendo en cuenta esta restricción, podremos utilizar la transformación para cualquier tabla origen/destino que cumpla ese requisito.

Estructura de fichero de texto generado en el Text File Output

Para indicar los campos que queremos recuperar realmente, podiamos haber añadido otro parametro más a la transformación para indicar los campos que queremos que se lean (e introducirlos en la sentencia SQL de lectura). Este parametro consistiria en una lista de nombres de campo separados por coma. Esto nos permite introducir el uso de otros tres pasos o steps de los que tenemos disponibles en kettle: Split Fields, Split Fields to Rows y Row Flattener.

Uso de Split Fields

Partimos de un campo cuyo contenido tiene la siguiente estructura: «valor1, valor2, valor3, valor4, valor5». Cada valor representa el nombre de un campo de la tabla. Tenemos dos opciones para normalizar o procesar el contenido del campo:

• Split Fields: el contenido de un campo lo multiplico en n campos, indicando un caracter que determina donde «cortar» cada uno de los nuevos campos a partir del original. Seguimos teniendo un único registro o fila, pero que ha pasado de tener 1 campo a n.
• Split Fields to Row: el contenido de un campo lo multiplico en n registros, indicando igualmente el caracter de delimitación. Pasamos de 1 registro o filas a n.

Configuración de los pasos Split Fields

Como paso final (despues del Split Fields to Row), podriamos utilizar el paso Row Flattener para juntar los diferentes registros, en uno único (sería equivalente a utilizar el Split Fields, pero  utilizando dos pasos).

Os dejo el link al  fichero xml de la transformación por si quereis ver en detalle los pasos aquí descritos. El ejemplo se ha centrado en la lectura e inserción en tablas de bd indicadas de forma dinámica, pero lo podiamos haber utilizar para cualquier otro cometido donde estemos utilizando variables ( y que los parametros indicados a la transformación configuran el valor de estas variables y su comportamiento).

16.3.3. ETL Dimensión Cliente con PDI.

Estructura física de la dimensión cliente.

El siguiente objetivo de nuestros procesos ETL es el llenado de la dimensión cliente. La estructura física de la tabla es la que veis en la imagen siguiente (incluyendo las subdimensiones para el tipo de cliente y el canal de cliente, debido a la diferente granuralidad de las tablas de hechos de presupuestos). Vamos a seguir la misma filosofía que hemos utilizado para el llenado de la Dimensión Producto (ver entrada del blog). En primer lugar, extraeremos los datos del ERP a nuestra área de Stage. A continuación, realizaremos el procesamiento y la carga en el DW en una segunda fase.

Esquema Físico Dimensión Cliente

Haremos hincapié en aquellos pasos o características que no hayamos visto hasta ahora, pues básicamente vamos a utilizar los mismos componentes (y a reutilizar transformaciones, como las de lectura de secuencias o registro de resultados de procesos). Veamos como quedaría el diseño de los jobs:

Job de Extraccion desde el ERP y Carga al Stage.

Los pasos o steps definidos en la construcción del job son los siguientes (con la misma filosofia de la dimensión Producto):

• Start: paso del tipo Start (categoría General), con el que iniciamos la secuencia de ejecución del job.
• Dim Cliente Pre: paso del tipo Transformation (categoría General), con el que ejecutamos la transformación que inicia el proceso de extracción. Basicamente inicia el proceso y recupera la secuencia de ejecución que se va a utilizar para orquestar el resto de procesos.
• Dim Cliente Extraccion:  Realiza la extraccion de los datos del maestro de clientes del ERP, con la transformación oportuna.
• Dim Cliente Extraccion (Resto Tablas): continuamos la extracción de datos con el resto de tablas, ejecutando la transformación correspondiente. Extrae del ERP el resto de tablas necesarias para llenar la dimensión cliente (datos adicionales del cliente, tipos de cliente, canales, paises, provincias, etc).
• Dim Cliente Extraccion Final: concluyo el job, registrando en base de datos (tabla dw_stage_secuencia), el status de ejecución del job.
• Status = ERROR y Status = OK: pasos del tipo Set Variable, con el que establezco el valor de la variable de resultado de ejecucion del job para actualizar la base de datos en el job Dim Cliente Extraccion Final. El paso Status = ERROR se ejecuta cuando se produce un fallo en la ejecución de las transformaciones (flecha roja). El paso Status = OK se ejecuta cuando todas las transformaciones se ejecutan correctamente (flecha verde).

Dimension Cliente - Job de Extraccion y Carga en el Stage

• Envio Correo Aviso Error: como componente adicional hemos incluido el del tipo Mail (categoría Mail), que nos va a permitir enviar un email en el caso de que se produzca algún error en la ejecución del job. El paso lo configuraremos de la siguiente manera:

Paso para envio de emails

Observar como hemos configurado el asunto y el texto del mensaje para que nos informe de el nombre del job que fallado, en que dimensión y en que secuencia de proceso. En todos los campos donde tenemos el icono de un rombo con el interior rojo, podemos utilizar variables. Pulsando las teclas Ctrl-Space nos aparecerán las variables disponibles en ese lugar. En el step del envio de email podiamos tener en variables el servidor SMTP, el puerto, el usuario y contraseña a utilizar, el destinatario, etc. Esto nos da una gran potencia para automatizar los envios, con un alto grado de personalización.

Tratamiento de errores en los pasos.

Otra funcionalidad que hemos utilizando en el diseño de la carga del stage de la dimensión Cliente (en la transformación Dim Cliente Extracción ) y que antes no habiamos utilizado, es el manejo de errores de un paso (Define Error Handling). En algunos steps (no en todos), cuando seleccionamos el menú contextual del botón derecho, aparece una opción configurable que se llama como hemos indicado, y que nos permite gestionar que se hace cuando se produce un error dentro de un paso. En lugar de abortar el proceso, podemos pasar los registros erróneos a otro paso y decidir que hacemos con ellos (corregir los errores o simplemente registrar en el log información adicional). El manejo de errores se configura en la siguiente pantalla:

Manejos de errores en un paso

Básicamente, se indica el paso al que vamos a pasar los registros con errores, y una seríe de campos que se puede llenar (si le ponemos un nombre), para que los valores sean pasados al paso siguiente. En mi ejemplo, los valores erroneos los he pasado a un step del tipo Write to log (de la categoría Utility).

Podeis acceder al fichero xml del job aquí. Igualmente, podeis acceder al fichero zip donde incluyo todas las transformaciones utilizados en el diseño del job.

Job de lectura del Stage, transformación y carga en la dimensión.

Veamos un poco en detalle los pasos que conforman este job:

• Start: paso del tipo Start (categoría General), con el que iniciamos la secuencia de ejecución del job.
• Dim Cliente Load Pre: en la primera transformación del job, recuperamos la ultima secuencia de ejecución de carga (la ultima que este pendiente), y este será el punto de partida para lanzar el resto de procesos. En el caso de que no haya ninguna secuencia pendiente de procesar, se detiene el job y se muestra información en el log.
• Dim Cliente Transform: es el proceso principal de este job. Los datos de los clientes leidos del stage son completados (mediante lookups contra las tablas), se verifican, sustituyen y modifican valores, y finalmente se actualiza la dimensión, realizando la gestión de las SCD.

Dimension Cliente - Job Transformación y Carga al DW

• Dim Cliente Load Post: esta transformación recibe las claves subrogadas de los registros creados o actualizados, y actualiza los campos de auditoria que hemos creado en la tabla. Igualmente, también se actualiza el campo que indica que un registro es el activo para un determinado código de cliente.
• Dim Cliente Load Final: actualizamos la tabla de control de procesos, indicando el status de procesamiento de la secuencia procesada tratada. Si todo ha ido correctamente, se deja en un status finalizado que impide que se vuelva a procesar.
• Envio Correo Aviso Error: en el caso de que se produzca un error en algunas de las transformaciones del job, se envio un correo con información (tal y como hemos visto antes).

El acceso al fichero xml del job lo podeís descargar aquí.

La mayoria de transformaciones de nuestro job son similares a las definidas en la dimensión producto (os dejo el link a la correspondiente entrada del blog). Nos vamos a centrar en la transformación Dim Cliente Transform, que hemos ampliado utilizando nuevas funcionalidades de PDI. Vamos a ver un poco mas en detalle dicha transformación. El diseño de la transformación es el que podeis ver en la imagen siguiente. Basicamente, realizamos las siguientes acciones:

• Leemos los datos de las tablas del stage y renombramos los campos. A continuación realizamos los lookups contra base de datos y contra ficheros para completar las descripciones de los diferentes atributos de la dimensión cliente.
• A continuación, transformamos los valores de los campos, eliminando nulos, sustituyendo valores e insertando en el flujo de datos la fecha de procesamiento.

Dim Cliente - Transformacion de carga al DW

• Dejamos los tipos de datos de los campos conformes a la tabla de la base de datos y miramos si estamos en una ejecución en real, o de test. Si es  test, mando los registros a un fichero excel para validar. En caso contrario, ejecuto la actualización de la dimensión con un Dimension/Lookup Update, y paso los valores de la clave subrogada y el código de cliente al job para completar la actualización de la dimensión.

De todos los steps que hemos utilizado en ese caso (y no explicados hasta ahora), destacar los siguientes:

• Maestro Regiones: es un paso del tipo Text File Input(categoría Input), que nos permite realizar la lectura de uno o varios ficheros de datos, e incorporar su contenido al fujo de datos. El step se configura en varias pestañas, cada una con un cometido especifico.
  • Ficheros o expresiones regulares que determinan los ficheros a recuperar: en la pestaña File indicamos los ficheros que vamos a recuperar. Podemos hacer que los nombres de fichero se reciban de un paso anterior, o bien indicar la lista de ficheros con los que queremos trabajar. Esta lista la podemos construir enumerando uno a uno todos los ficheros, o bien utilizando expresiones regulares que se evaluaran en el momento de realizar la ejecución del step y recuperaran todos los ficheros que cumplan las condiciones. Para configurar el paso, siempre habrá que indicar en primer lugar un nombre de fichero que exista (desde el cual recuperaremos sus propiedades y estructura del contenido).

Configuracion del paso Text File Input (I)

• • Contenido del fichero: en la pestaña Content indicamos información sobre la estructura interna del fichero. Por ejemplo, indicaremos el delimitador que separa los campos, el caracter para contener campos del tipo caracter (por ejemplo, las comillas), el caracter de escape, información de cabeceras, formato, codificación, lenguaje, etc. Igualmente, podemos configurar si queremos pasar los nombres de ficheros en el flujo de salida o el número de linea. Todo lo indicado en esta fase se utilizara para la recuperación automatica del formato o metadatos del fichero.

Configuracion del paso Text File Input (II)

• • Metadatos de la estructura del fichero: en la pestaña fields identificamos el contenido del fichero asociandolo a los diferentes campos en los que se extraeran los valores de cada linea. Para cada uno de ellos se podrá indicar su tipo de datos, formato, posicion(en el caso de ficheros con formato fijo), longitud, precisión, valores por defecto, eliminación de blancos, etc. Con el botón Get Fields se recupera de forma automatica los valores, que podremos modificar para adaptarlo a nuestras necesidades o para corregir posibles incorrecciones.

Configuracion del paso Text File Input (y III)

• Preparo campos y cambio tipo datos: ambos pasos son del tipo Select values (categoría Transform). Este paso se puede utilizar de tres formas diferentes (y no simultaneas).
  • Select & Alter: de todos los campos del flujo de datos que llegan al control, seleccionamos aquellos que queremos pasar al siguiente paso. Ademas, podemos establecer el orden de salida de los campos, alterando el que se recibe del paso anterior. También se pueden renombrar y cambiar su longitud y precisión. Solo se pasan los campos que estan descritos aquí, en el caso de que se utilize esta forma de tratar el flujo de datos. Los campos no indicados son suprimidos.
  • Remove: si nos decidimos por configurar la pestaña Remove, indicamos los campos del flujo que queremos eliminar, de forma que se pasaran todos los campos, excepto los especificados.
  • Meta-Data: la tercera opción es modificar el metadata de los campos. Podemos renombrar los campos y cambiar sus propiedades de tipo de datos (ademas de la longitud y precisión).

Paso Select Values

En nuestro ejemplo, hemos utilizando el step dos veces. Primero, hemos seleccionado los campos que queremos pasar a la actualización de la dimensión, y en una segunda ejecución hemos cambiado el tipo de datos de alguno de los campos (pasandolos a numéricos para que coincida con los tipos existentes en la tabla de base de datos).

• Otros cambios: es un paso del tipo Formula (categoría Scripting). Con este paso podemos definir operaciones para construir nuevos valores de campos ( o sustituir los valores de campos existentes). Utiliza la librería Libformula, cuya sintaxis esta basada en el estandar Openformula. En la ventana principal del step definimos el nombre del campo que queremos crear, y al hacer doble click aparece el editor de fórmulas (tal y como se ve en la imagen inferior). En la parte izquierda tenemos la lista de las sentencias disponibles, organizadas por categorías. Al seleccionar una de las sentencias, nos aparece en la parte inferior derecha la sintaxis del comando (con ejemplos de utilización incluidos). En la parte superior, vamos construyendo la fórmula que queremos evaluar. La formula introducida se va evaluando online mientras la vamos introduciendo, hasta que la sintaxis es totalmente correcta. Dentro de las formulas podemos utilizar los campos del flujo con la notación [nombre_campo]. El resultado de la formula puede crear un campo nuevo (o sustituir el valor de un campo existente si utilizamos el campo REPLACE VALUE).

Paso del tipo Formula

Como ejemplo de formulas, los siguientes:

• IF (OR ( [AGRUPADOR_ID] = «999» ; [AGRUPADOR_ID] = » » ) ;[CLIENTE_DESC] ; [AGRUPADOR_DESC] ) : implementamos una sentencia IF. La parte de la formula en color azul es la condición del if (hemos utilizado el operador OR para incluir dos condiciones). La condición se evalua. En el caso de que sea correcta, se ejecuta la parte indicada en color verde. La parte en rojo se ejecuta en el caso de que no se cumpla la condición.
• LEFT ([PROVINCIA_id];2) : nos permite devolver una subcadena de un campo existente. En concreto, devolvemos los dos primeros carácteres por la izquierda.
• MONTH (now()) : con el now() recuperamos la fecha actual del sistema, aplicandole la función MONTH, que nos devuelve el mes de la fecha.

Este step es muy potente, pues con una sencilla sintaxis de sentencias podemos realizar multiples operaciones. Los componentes, operadores y sentencias se pueden anidar unos dentro de otros sin ningun problema.

Igualmente, destacar una funcionalidad muy interesante de PDI. En la parte inferior derecha de la transformación, hemos incluido un paso del tipo Switch /Case (llamado ¿Comercial Genérico? ). Esto nos permite «mandar» los registros del flujo de datos a un paso diferente u otro según los valores de un campo determinado. Hasta aquí todo normal. A continuación incluimos en ambos casos un step del tipo JavaScript, donde realizaremos diferente operaciones. En el paso siguiente, llamado Fecha Sistema (del tipo Get Sytem Info), juntamos los dos flujos.

Juntando dos flujos de datos en un step

La secuencia de datos que llega por un lado y por otro son juntadas, y procesadas a partir de ese momento como un único flujo. Esto se puede realizar contra cualquier step, con la única limitación (lógica) de que ambos han de tener la misma estructura y el mismo número de campos. Esta funcionalidad es muy potente y nos da mucha flexibilidad a la hora de construir las secuencias de acciones dentro de las transformaciones.

Os dejo el link a la transformación aquí. En este otro link teneis en formato zip todos los ficheros xml utilizados en este segundo job.

Llenado de las Dimensiones Roll-Up

Cuando la dimensión cliente esta completada, realizaremos el llenado de las dimensiones Roll-Up (o subconjuntos) de esta. Es tan sencillo como la ejecución de una sentencia SQL (que podriamos haber incluido como un paso mas dentro de alguna transformación). Las sentencias son las siguientes:

insert into dwd_cliente_canal
select distinct canal_id,canal_desc from enobi.dwd_cliente

insert into dwd_cliente_tipocl
select distinct tipo_cliente_id,tipo_cliente_desc from enobi.dwd_cliente

Tambien lo podriamos haber incluido como un paso mas al leer las tablas originales del ERP (cargadas en el stage), donde estan definidos los atributos de esta dimensión. Pero la sentencia sql es la forma mas sencilla (y fácil de ejecutar desde cualquier job).

Conclusiones

Hemos concluido el poblado de la dimensión Cliente utilizando PDI. Hemos visto otras funcionalidades interesantes, como el uso del step Formula, la lectura de datos de ficheros de texto con el paso Text File Input y la posibilidad de juntar dos flujos de datos en cualquier paso (siempre que ambos tengan la misma estructura, con el mismo numero de campos). Esto ultimo no lo podiamos realizar, por ejemplo utilizando la herramienta Talend Open Studio.

A continuación vamos a ver un ejemplo de paso de parametros a una transformación y como, con esos valores, vamos realizando acciones contra la base de datos de forma dinámica. Posteriormente concluiremos los procesos de carga del DW con la carga de las tablas de hechos (tanto de ventas como de presupuestos).

16.3.2.2. ETL Dimensión Producto con PDI (II). Carga al DW.

Estructura física de la Dimensión Producto.

Una vez extraidos los datos y depositados en al area stage, el objetivo es llenar (actualizar) la tabla de la Dimensión Producto, cuya estructura física podeis ver a continuación. Como podeis observar, vamos a gestionar los cambios de las SCD del tipo II, de la forma vista en anteriores entradas del blog.

Esquema Fïsico Dimensión Producto

Ademas, nos vamos a guardar unos campos de auditoria, para registrar el proceso y la fecha tanto de inserción como de actualización de cada uno de los registros (campos INSERT_DATE, INSERT_PROC, UPDATE_DATE y UPDATE_PROC). Nos podrán ser de utilizar para las comprobaciones o para realizar un debug en el caso de errores en los procesos de carga.

Job de lectura del Stage, transformación y carga en la dimensión.

Al igual que en el job de extracción, vamos a dividir las acciones a realizar en trozos mas pequeños que nos van a permitir controlar mejor la secuencia de procesos. El diseño del job sería el que veis en la imagen:

Job para la Transformacion y Carga en el DW (Dimension Producto)

Veamos un poco en detalle los pasos que conforman nuestro job:

• Start: paso del tipo Start (categoría General), con el que iniciamos la secuencia de ejecución del job.
• Dim Producto Load Pre: en la primera transformación del job, recuperamos la ultima secuencia de ejecución de carga (la ultima que este pendiente), y este será el punto de partido para lanzar el resto de procesos. En el caso de que no haya ninguna secuencia pendiente de procesar, se detiene el job y se muestra información en el log.
• Dim Producto Transform: es el proceso principal de este job. Los datos de los productos leidos del stage son completados (mediante lookups contra las tablas y ficheros correspondientes), se verifican, sustituyen y modifican valores, y finalmente se actualiza la dimensión, realizando la gestión de las SCD.
• Dim Producto Load Post: esta transformación recibe las claves subrogadas de los registros creados o actualizados, y actualiza los campos de auditoria que hemos creado en la tabla. Igualmente, también se actualiza el campo que indica que un registro es el activo para un determinado código de material.
• Dim Producto Load Final: actualizamos la tabla de control de procesos, indicando el status de procesamiento de la secuencia procesada tratada. Si todo ha ido correctamente, se deja en un status finalizado que impide que se vuelva a procesar.

El acceso al fichero xml del job lo podeís descargar aquí.

Detalle de transformaciones.

Dim Producto Load Pre.

Lo primero que hacemos en esta transformación es recuperar la ultima secuencia de ejecución en el estado EXTROK (Extracción OK), que nos indica de donde tenemos que leer en el stage. En el caso de que todas las secuencias esten procesadas correctamente (estado LOADOK), el proceso termina sin hacer nada. Los steps que hemos utilizado son los que veis en la imagen.

Transformacion - Dim Producto Load Pre

• Recuperar secuencia proceso: llama a una subtransformación (como vimos en la entrada anterior del blog), a la que paso como parametro la dimensión que estoy procesando y un status de proceso a buscar, y me devuelve el  número de secuencia de ejecución de procesos ETL que esta pendiente de procesar para cargar a la dimensión. Este número nos determina los datos maestros, que hemos almacenado previamente en el stage, y que vamos a leer en el proceso de carga.
• Es nulo?: con un paso del tipo Filter Rows ( de la categoría Flow), comprobamos si el valor devuelto en el paso anterior es nulo o no. Si es nulo, procedere a cancelar el job, mandando un mensaje descriptivo del motivo de la parada en el log. En caso contrario, preparo las variables para pasarlas al job.
• Paso variables a Job: con un control del tipo Set Variable pasamos al job las variables que nos interesa que esten disponibles para el resto de transformaciones del Job. En nuestro caso, le vamos a dar un ambito de valor del tipo “Valid in the parent job”, pues no vamos a tener mas jobs por encima.

Os dejo el link a la transformación aquí.

Dim Producto Transform

Esta transformación es la mas importante en nuestro proceso de carga. De forma general, vamos a realizar las siguientes acciones:

• Con la secuencia de proceso recuperada, leo la tabla del maestro de materiales del stage y me quedo con los campos que me interesan (según la especificación de origenes de datos que tendremos que tener definida).
• A continuación, completo las descripciones del resto de campos que me hacen falta, realizando un mapeo tanto contra otras tablas de la base de datos como contra ficheros (de texto y hojas excel), que contienen las descripciones de los códigos.
• La tercera parte importante del proceso es depurar y transformar los datos. Eliminamos nulos, completamos valores vacios con datos por defecto o genéricos, cambiamos el valor de determinados campos e insertamos la fecha de proceso en la secuencia. Los datos estan preparados para realizar la carga en la base de datos.
• La parte final es la actualización de la dimensión. En nuestro caso, hemos añadido una funcionalidad interesante a los procesos. Al llamar a la transformación, podemos indicar si la ejecución es en modo TEST o en modo PROD. En modo TEST, no se actualiza la base de datos y solo se genera un fichero Excel con los registros transformados. Si estamos en modo PROD, si se realiza la actualización real de los datos.
• La transformación termina con el traspaso al Job de los valores de las claves subrogadas (MATERIAL_SK) y el codigo del material en el ERP (MATERIAL_ID), campos que nos van a valer para terminar de completar la información de auditoría y el campo de control SCD_ACTIVE (que indica si un registro es activo o no dentro de un determinado material) en la transformación siguiente.

Teniendo en cuenta todo esto, la transformación tendría el siguiente esquema:

Dim Producto Transform

Veamos en detalle alguno de los pasos para entender mejor su funcionamiento y como se configuran:

• Selec y renombro campos: paso del tipo Select Values (categoría Transform). Nos permite seleccionar los campos que queremos pasar al siguiente paso de una transformación. Esto se puede hacer de dos maneras. O bien seleccionamos que campos queremos utilizar (eleminandose el resto que no se indiquen) o bien indicar cuales queremos quitar (se pasaran todos los demas). También podemos cambiar el metadata de los campos (tipo de datos, formato, longitud, etc).

Configuracion del step Select Values

• Lee Desc Material: paso del tipo Database Lookup (categoría Lookup). Nos permite realizar una recuperación, contra base de datos, de valores adicionales. Pensar en el ejemplo de un código de material, cuya descripción se encuentra en otra tabla de la base de datos. Podeís ver en la imagen la forma de realizar el Lookup. Por un lado, indicamos la tabla donde buscar los valores, por otro, los campos clave para realizar la busqueda y en la parte inferior, los valores que queremos recuperar. El mismo paso lo hemos utilizado en nuestra transformación varias veces para recuperar valores de varias tablas.

Configuración del step Database lookup

• Lee Desc Varietal: paso del tipo Stream Lookup (categoría Lookup). Nos permite realizar una recuperación, contra valores existentes en el flujo de datos, de valores adicionales. Sería similar al database lookup, pero en lugar de buscar contra una tabla, buscamos en un flujo de datos (que puede tener diferentes origenes). En nuestro ejemplo, el origen es un fichero de texto delimitado o una hoja excel (donde tenemos las secuencia de tuplas valor_id,descripcion_id). De este flujo de datos generado desde el fichero recuperaremos las descripciones que nos hacen falta. Igual que el paso anterior, lo hemos utilizado varias veces.

Configuración del step Stream lookup

• Elimina nulos: paso del tipo If field value is null (categoría Utility), que nos permite analizar que campos tiene un valor nulo, y sustituir dicho valor otro que indiquemos. Util para llenar valores no inicializados a valores por defecto.

• Sustituye valores: paso del tipo Replace in string (categoría Transform), que nos permite realizar sustitución de valores. Es un paso muy potente, pues con el uso de expresiones regulares podemos establecer secuencias de valores a buscar complejas y sustituir dichos valores por los elementos deseados.
• Trat Resto Campos: paso del tipo Modified Java Script Value (categoría Scripting), que nos permite la ejecución de código Javascript para realizar operaciones sobre los datos. Es muy potente, pues se puede hacer casi de todo, aunque siempre es aconsejable utilizarlo cuando no tengamos mas remedio (al querer realizar alguna operación que es imposible realizar con los steps disponibles en el sistema). En el ejemplo, hemos cambiado los valores a mayusculas  de varios campos (con la funcion upper), y ademas hemos realizado sustitución de valores utilizando la sentencia switch. En la instalación de PDI, en el directorio samples, hay un variado repertorio de ejemplos de este paso (y del resto de funcionalidades de Kettle).

Ejecución de JavaScript

• Fecha Sistema: paso del tipo Get System Info (categoría Input) que nos permite incluir campos en el flujo de datos con valores recuperados del sistema. En nuestro caso, lo utilizamos para incluir la fecha del sistema, aunque podriamos incluir el nombre del host donde ejecutamos los procesos, su ip, los argumentos que se han pasado como parámetros a la ejecución del job/transformación, nombre de la transformación, etc (podeis ver los valores disponibles en la imagen siguiente).

Get System Data

• ¿Modo Update (Test o real)?: con un paso del tipo Switch/Case (categoría Flow), validamos el valor del campo MODO. El valor de este campo lo hemos establecido a llamar a la transformación, y en el se indica si estamos lanzando el proceso en modo prueba (valor TEST) o en modo real (valor PROD). Según el valor, los registros del flujo de datos son pasado a un paso u otro de los siguientes. En nuestro caso, si el valor es TEST, se pasan los datos un paso oportuno para sacar los registros a un fichero excel, en lugar de actualizar contra base de datos. Podeis ver la configuración del step en la siguiente imagen:

Switch - Case

• Act Dimensión: con un paso del tipo Dimension lookup/update gestionamos la actualización de la tabla de la dimensión, implementando el tratamiento de las dimensiones lentamente cambiantes. El paso recibe el flujo de datos con toda la estructura de campos montada, y se realiza la verificación contra base de datos. Os recomiendo leer la entrada del blog donde vimos como se configuraba este step en PDI de una forma detallada.

Dimension Lookup

Basicamente, esta es la configuración de la transformación. Os dejo el link a los fichero xml aquí, por si quereís ver en mas detalle toda la definición de pasos que hemos explicado y el resto de steps que la conforman.

Dim Producto Load Post

El proceso esta casi terminado. Solo nos queda por actualizar los datos de la dimensión con algunos detalles. Para ello, hemos de saber que registros se han insertado o actualizado. Para ello, utilizamos el paso Get rows from result (categoría Job), que nos permite recuperar los registros procesados en la transformación anterior. Vimos que esta era otra de las formas de pasar información entre las diferentes transformaciones que forman un job (utilizando una especie de buffer común).

Dim Producto Load Post

Con los datos recuperados, lanzamos tres actualizaciones contra base de datos, utilizando en todas ellas el step Execute SQL script (categoría Scripting). El paso nos permite preparar una sentencia Sql personalizada para ejecutar la acción deseada. En este caso, tendremos tres:

• Actualizo registro activo (Insercion): actualizo en los registro recien creados el campo de flag de registro activo (SCD_ACTIVO).  Igualmente, en los campos de auditoría, registro el Job que ha hecho la inserción  (la sentencia introducida es la siguiente: update enobi.dwd_producto set SCD_ACTIVO = ‘Y’, INSERT_PROC = ‘${Internal.Job.Name}’ where material_sk = ? and SCD_ACTIVO is null; ).
• Actualizo registro activo (Update): similar a la anterior, pero en este caso actualizo los registros que han sido actualizados (con la sentencia: update enobi.dwd_producto set SCD_ACTIVO = ‘Y’, UPDATE_PROC = ‘${Internal.Job.Name}’ where material_sk = ? and SCD_ACTIVO is not null; ).
• Actualizo registro no activo: solo puede haber un registro con el flag de activo a Y (Yes). Los registros de un material que no son el ultimo actualizado/modificado, ha de estar a N. Para ello, ejecuto la sentencia: update enobi.dwd_producto set SCD_ACTIVO = ‘N’ where material_sk <> ? and material_id = ?;

Vamos a ver como se configura el control para que entendais mejor como hemos pasado la información a las sentencias SQL. En la configuración del step (en la imagen) hemos añadido comentarios acerca del significado de cada elemento de el. Esto paso nos da una gran potencia para preparar sentencias SQL personalizadas utilizando tanto variables del entorno de ejecución, así como campos del flujo de datos, como ha sido en nuestro caso.

Ejemplo del paso Execute SQL script

Respecto a los argumentos (los ? que utilizamos en la sentencia SQL), remarcar que hemos de definirlos en el orden que queremos que luego se puedan utilizar (en la sección Parameters). Luego el primer ? correspondera al primer campo de la sección parameters, el segundo ? al siguiente, etc. Un campo se puede definir en esta sección tantas veces como se quiera si se ha de utilizar varias veces.

Os dejo el link a la transformación aquí.

Dim Producto Load Final

Como en el ejemplo anterior del blog, es la ultima transformación que ejecutamos. Si todo ha ido correctamente, actualizamos la tabla de control de stage, finalizando la secuencia de carga, que ya no volvera a ser procesada. El siguiente procesamiento se realizara cuando se lanze un nuevo proceso de Extracción desde nuestro ERP.

Dim Producto Load Final

Os dejo el link a la transformación aquí.

Conclusiones

Os dejo el link en formato zip de todos los ficheros xml utilizados en este ejemplo. Hemos concluido el primer trabajo realmente complejo utilizando Pentaho Data Integration. Las sensaciones son bastante buenas. La herramienta es muy potente, es intuitiva y fácil de utilizar y parece ser que esta pensada y diseñada desde el principio para abordar los problemas y tareas mas frecuentes a realizar cuando estamos hablando de integración de datos y procesos ETL. Y sin duda, el libro de Maria Carina Roldan, nos ha sido de gran utilidad para abordar el conocimiento de la herramienta. Su enfoque didáctico, su estructuración desde los temas mas sencillos a los mas complejos, y el hecho de que sea eminentemente práctico nos ha servido de mucho.

16.3.2.1. ETL Dimensión Producto con PDI (I). Extraccion a Stage Area.

Area Stage temporal.

Vamos a suponer que tenemos muy poca ventana de tiempo para extraer los datos de nuetro sistema operacional y que tenemos que interferir con los procesos ETL lo minimo posible en dicho sistema. Para ello, vamos a realizar una extracción de todas las tablas que necesitamos para nuestros procesos, las cargaremos en el area de Stage, y desde ahí lanzaremos los procesos de transformación y carga contra el DW.

De esta forma vamos a dividir los procesos ETL en dos fases: Extraccion y Carga al Stage Area (que veremos en esta entrada del Blog), y Transformación y Carga al DW (que veremos en la siguiente entrada). Aprovecharemos los ejemplos para ver diferentes funcionalidades de PDI de una forma detallada, y asi ir ampliando conceptos, viendo las posiblidades que nos ofrece el producto (he complicado el ejemplo con la gestión de Stage para intentar profundizar mas en el uso de Kettle y no quedarnos en un ejemplo sencillo, de los que ya hay suficiente literatura).

Antes de empezar, vamos a realizar algunas consideraciones sobre Pentaho Data Integration.

Uso de parametros, argumentos y variables.

Vimos al explicar las características de PDI que disponiamos de dos elementos principales para construir los procesos ETL:

• Transformación: es el elemento básico de diseño de los procesos ETL. Se compone de pasos o steps entrelazados entre si a traves de los saltos o hops, a traves de los cuales va fluyendo la información. Tenemos pasos para realizar multiples actividades, como ya vimos.
• Job: es un conjunto complejo o sencillo de tareas para realizar una acción determinada. Igualmente disponemos de un conjunto de pasos (que son diferentes a los de las transformaciones) y los saltos (que en este caso determinan el orden de ejecución, y la gestión de resultados de la ejecución de cada paso). Dentro de los jobs podemos ejecutar una o varias transformaciones, los que nos permite ir dividiendo los procesos en partes y luego orquestar su ejecución mediante los jobs.

En ambos casos, disponemos de varios mecanismos para pasar «informacion» o «valores» a los procesos:

• Argumentos: son los valores que indicamos cuando ejecutamos un comando. No van identificados y se reciben en el job o transformación según el orden de inserción. En la imagen, vemos que se pueden indicar cuando estamos ejecutando una transformación (o job) desde Kettle. Si ejecutaramos desde linea de comandos (utilizando Pan o Kitchen), también los podriamos indicar en el comando. Los argumentos luego se pueden utilizar en algunos pasos, refiriendonos a ellos como «?». Al no estar identificados, si los queremos utilizar en el flujo de datos de forma identificada tendremos que asignarlos utilizando el paso Get System Info (de la categoría Input).

Transformacion - Paso de parametros y argumentos ejecutando desde Spoon

• Parametros: son valores pasados a las transformaciones o jobs, pero si tienen un nombre de parametro que los identifica (y permiten que pueda ser utilizando como variable en cualquier sitio de los permitidos dentro de los jobs y transformaciones). Tambien se podran utilizar en el step Get Variables para pasar los valores de los parametros al flujo de datos (como si fuesen variables de entorno o del job).
• Variables: Ademas, disponemos de otro mecanismo para pasar información entre los jobs y en las diferentes transformaciones dentro de un job. Son las variables. Por un lado, podríamos estar utilizando variables asociadas con el step Get System Info, de la categoría Input (fecha del sistema, host, dirección Ip, argumentos de la linea de comandos, etc), que podremos recuperar mediante este step e introducir en el flujo de datos. Igualmente, podremos configurar nuestras propias variables en el fichero kettle.properties  (que esta en el directorio .kettle del usuario con el que estemos trabajando). Estas variables estaran disponibles en todas las transformaciones y jobs que utilicemos. Podemos usarlas, por ejemplo, para definir los parametros de conexión a nuestros sistemas origen (ERP):

Definicion de variables en el fichero de parametros de Kettle

Las variables definidas en el fichero podrán ser utilizadas directamente en todos los sitios donde este permitido, usando la notación ${VARIABLE}. También se podrán utilizar en la construcción de sentecias SQL (por ejemplo en el step Tabla Input) o recuperar el valor de las variables para insertarlo en el flujo de datos con el step Get Variables como vimos anteriormente.

A nivel de jobs, podemos pasar igualmente la información entre las diferentes transformaciones que lo forman (o entre diferentes jobs), a través de las variables (aunque hay otros mecanismos de traspaso de información, como veremos mas adelante). Para este traspaso se utilizan los steps Set Variables y Get Variables. Con el Set cambiamos el valor de las variables, y con el Get lo recuperamos para introducirlo en el flujo de la transformación. Tener en cuenta el ambito de las variables (que podremos establecer al ejecutar estos steps), y que al cambiar el valor de una variable, el cambio no es visible en la transformación que estemos ejecutando. El ambito de las variables puede ser el siguiente:

1. Valid in the parent job: las variables son visibles en el job que llama la transformación, y en cualquier transformacion llamada dentro del mismo job.
2. Valid in the grand-parent job: igual que el anterior, y ademas visibles en el job que llama al job actual.
3. Valid in root job: visible en cualquier job desde el job principal, y en cualquier transformacion de dichos jobs.
4. Valid in the Java Virtual Machine: visible por todos los jobs y transformaciones ejecutados de la misma máquina virtual Java.

Para que veais un ejemplo de la utilidad del uso de variables (en concreto las definidas en el fichero de propiedades de Kettle), he configurado la conexión a la base de datos del ERP utilizando las variables. En Hostname y en Database Name utilizo la variable creada en el fichero. En el momento quiera dejar de trabajar con el sistema de pruebas y pasar al de productivo, cambiare las variables a utilizar en la conexión (o el valor de las variables en el fichero).

Conexion a BD usando variables

Control de Errores y ejecución.

Es fundamental para orquestar bien los procesos y para controlar la correcta ejecución de los procesos gestionar un log de todas las acciones y resultados que vayamos obteniendo. En nuestro caso, hemos establecido los siguientes mecanismos:

• Habilitación del log en base de datos para Transformaciones y Jobs: aparte de visualizar los logs de ejecución, bien desde Kettle o desde las herramientas de lineas de comandos, podemos establecer un registro en base de datos de los logs de ejecución. Para ello, ha de existir una tabla para las transformaciones, y otra para los jobs, y configurar el registro de log en sus propiedades, tal y como vemos en la imagen siguiente. Se registrará una información básica de los jobs y transformaciones, pero que nos puede ser util para depuración o para automatizar procesos y control de errores.

Configuracion de Log Automatico en BD para una transformación

• Tabla de control y secuencias: en nuestro ejemplo he añadido una tabla de control para gestionar las diferentes extracciones que vayamos realizando. La tabla la utilizaremos para todas las dimensiones. La idea es sencilla. Cada vez que realizemos un proceso ETL sobre la dimensión en cuestión, generaremos un número de secuencia (con el que ademas se registraran los datos recuperados del ERP en el Stage Area). En cada secuencia de ejecución, iremos actualizando el status en el que se encuentra el proceso, así como la fecha/hora y el job que lo actualiza. Los posibles status son: EXTRFAIL (fallo en la extraccion), EXTROK (extraccion terminada, preparado para traspaso a DW), LOADFAIL (fallo en la carga al DW desde el Stage) y LOADOK (carga de la dimensión concluida). En los procesos incluiremos los pasos necesarios para ir realizando estas actualizaciones. Un número de secuencia se reutiliza hasta que no se llega al status LOAD OK.

Tabla Gestion Extracciones

Para almacener en el stage los datos recuperados del ERP, hemos creado unas tablas llamadas dw_erp_nombretabla. Cada tabla origen tiene su tabla en el stage. En la clave de la tabla, hemos añadido el campo secuencia_ejecucion (ademas de la clave original), con la que nos vamos a guardar un historico de todos los datos extraidos.

Uso de subtransformaciones. Otras formas de pasar información entre transformaciones (Copy rows to result).

Como una funcionalidad muy interesante cuando estemos construyendo las transformaciones, existe la posibilidad de llamar desde una transformación a otra (lo que se llama subtransformación), definiendo ademas, si fuese necesario, una interfaz de entrada y de salida, ademas del paso de parámetros. Esto nos permite encapsular determinadas acciones y reutilizar procesos ya definidos. En nuestro ejemplo lo utilizaremos varias veces para procesos repetitivos, a los que le pasamos parametros para indicarles que tipo de acciones y sobre que objetos se han de realizar. Para implementar esta forma de funcionar se utilizan tres pasos (pensar en una función de un programa donde por un lado hago la llamada a la función, desde un nivel superior; por otro lado tengo la entrada de datos dentro de la funcion (input) y la salida de datos desde la función una vez he terminado las acciones que se realizan dentro de ella (output)):

• Mapping (sub-transformation): con este paso implementamos la «llamada» a la subtransformación de la transformación principal. Se indica la transformación a la que se llama, los parametros que se le pasan, y como se configura el intercambio de datos (input y output). Estos pasos son opcionales. Cuando se configura el input u output, se realiza un mapeo entre los pasos relacionados de la transformación llamante y la llamada, indicandose igualmente los valores que se pasan y donde se pasan. Cuando se configura ya ha de existir la subtransformación(con sus correspondientes pasos Mapping Input y Output si son necesarios) para realizar correctamente la configuración de intercambio de datos.

Llamada a la subtransformación

• Mapping input: este paso se inserta en la subtransformacion llamada para recibir los parametros y valores de la transformación superior. Los valores configurados en este paso, como vemos en la imagen, han de estar mapeados en la llamada (en la pestaña Input) y llenados con el flujo de datos de la transformación «padre».

Definicion de Input en la subtransformacion

• Mapping output: este paso se inserta en la subtransformacion llamada para devolver los valores a la transformación «llamante».

Esta funcionalidad de Kettle nos da muchisimo juego para reutilizar componentes y diseños. Digamos, es una forma de «reutilizar código».

Existe una forma adicional de pasar información de una transformación a otra dentro de un job, que es mediante el uso de los steps Copy Rows to Result y Get Rows from Result. Como paso para exportar datos desde una transformación utilizaremos el primero, y recuperaremos la información de la transformación destino con el Get Rows from Result. Estamos hablando de algo parecido a un buffer común que utilizamos para compartir los datos entre diferentes transformaciones.

Job de Extraccion.

Una vez realizadas todas las consideraciones, vamos a ver como quedaría nuestro Job para la extracción desde el ERP Sap de los datos de la dimensión Producto. El diseño del job sería el que veis en la imagen:

Job para extraccion de datos del ERP y carga en el Stage Area

Los pasos o steps definidos en la construcción del job son los siguientes:

• Start: paso del tipo Start (categoría General), con el que iniciamos la secuencia de ejecución del job.
• Dim Producto Pre: paso del tipo Transformation (categoría General), con el que ejecutamos la transformación que inicia el proceso de extracción (lo veremos en detalle a continuación). Basicamente inicia el proceso y recupera la secuencia de ejecución que se va a utilizar para orquestar el resto de procesos.
• Dim Producto Extraccion:  Realiza la extraccion de los datos del maestro de productos del ERP, con la transformación oportuna.
• Dim Producto Extraccion (Resto Tablas): continuamos la extracción de datos con el resto de tablas, ejecutando la transformación correspondiente. Extrae del ERP el resto de tablas necesarias para llenar la dimensión producto.
• Dim Producto Extraccion Final: concluyo el job, registrando en base de datos (tabla dw_stage_secuencia), el status de ejecución del job.
• Status = ERROR y Status = OK: pasos del tipo Set Variable, con el que establezco el valor de la variable de resultado de ejecucion del job para actualizar la base de datos en el job Dim Producto Extraccion Final. El paso Status = ERROR se ejecuta cuando se produce un fallo en la ejecución de las transformaciones (flecha roja). El paso Status = OK se ejecuta cuando todas las transformaciones se ejecutan correctamente (flecha verde).

Podeis acceder al fichero xml del job aquí.

Transformaciones de Extraccion.

Veamos en detalle el diseño de cada una de las transformaciones que conforman el job.

Transformacion Dim Producto Pre.

Es la transformación de inicio de los procesos, pues en ella se recupera la ultima secuencia de ejecución y el valor se pasa al resto de transformaciones que realizaran la extraccion efectiva de datos y su carga en el area de stage.

Transformacion - Dim Producto Previo

• Recuperar secuencia proceso: llama a una subtransformación (como hemos explicado antes), a la que paso como parametro la dimensión que estoy procesando y un status de proceso a buscar, y me devuelve el siguiente número de secuencia de ejecución de procesos ETL libre, que vamos a utilizar para realizar toda la gestion de control y el almacenamiento temporal de los datos en el area stage.
• Paso variables a Job: con un control del tipo Set Variable pasamos al job las variables que nos interesa que esten disponibles para el resto de transformaciones del Job. En nuestro caso, le vamos a dar un ambito de valor del tipo «Valid in the parent job», pues no vamos a tener mas jobs por encima.

Transformación Dim Producto Extraccion.

En esta transformación se realiza la extracción del maestro de materiales desde el ERP SAP. El diseño de la transformación es el siguiente:

Transformacion - Dim Producto Extraccion

Las acciones que realizamos en la transformación son:

• Recupero secuencia: con un control del tipo Get Variable recupero el valor de la secuencia de ejecución con la que estamos trabajando (que se habra generado en la transformación anterior).
• Construye SQL Borrado: para el caso de que hubiera una ejecución anterior erronea (no completada), borro de la tabla del area de stage los datos correspondientes a la ejecución. Para ello, construyo una sentencia SQL en una variable de texto utilizando un step del tipo Formula. En la formula combino texto con la sustitución de variables ( «delete from enobi_stage_area.dw_erp_mara where sec_number = » & [sec_number] & «;» ), que son los valores de campos en el flujo de datos.
• Ejecuta SQL Borrado: ejecuta contra base de datos la sentencia construida anteriormente, utilizando el step Execute Sql Script.
• Extrae Materiales ERP: recupero del ERP los datos de los materiales con un paso del tipo Table Input.
• Junta Materiales + Numero Secuencia: para añadir la secuencia de ejecución al flujo (lo he perdido al realizar las acciones anteriores), hago un producto cartesiano de los valores recuperados en Extrae Materiales ERP con el Recupero Secuencia (contiene un único registro con la secuencia). Así dejo los datos preparados para la inserción en BD.
• Carga Materiales Stage: grabo en la tabla temporal del stage, con un paso del tipo Table Output. La clave de cada registro será la tupla secuencia,código material.

Podeis descargaros el fichero xml de la transformación en este link.

Transformacion Dim Producto Extraccion (Resto Tablas).

Una vez es completada la transformación anterior, se llama a la siguiente para continuar la extraccion. El diseño es el siguiente:

Transformacion - Dim Producto Extraccion (Resto Tablas)

La filosofia es la misma que en el paso anterior. Primero borro los datos que hubieran para la ejecucion en el area de stage (para cada una de las tablas), a continuación recupero los datos de la base de datos del ERP, añado la secuencia (con el producto cartesiano) y grabo en el temporal de Stage los datos extraidos. En este caso, para realizar el borrado, en lugar de construir una sentencia SQL y ejecutarla, llamo a una subtransformación, a la que le paso el nombre de la tabla como parametro y el numero de secuencia como input. Dentro de la transformación se realiza el borrado de datos con el paso Delete.

En la transformación realizo el proceso de 3 tablas (MATK, T023T y TWEWT). Observar como desde el paso RECUPERA SECUENCIA ( que lee de memoria la variable de la secuencia de ejecución ), hemos añadido tantos saltos como nos ha hecho falta. En el ejemplo, todos los valores recuperados en el paso son pasados a todos los pasos con los que esta conectado (esto se llama movimiento de datos del tipo Copy Data ). PDI nos permite realizar otra forma de movimiento de datos llamada Distribute Data, en el que para el caso de tener varios pasos vinculados a otro, los registros que este genera se reparten de forma equitativa (se hace una distribución de ellos). En ese caso, no se pasan todos los registros al paso siguiente. Nos puede ser util para realizar procesamiento en paralelo.

Os dejo el link a la transformación aquí.

Transformacion Dim Producto Extraccion Final.

Este paso completa la ejecución del job, realizando un registro en Base de datos del resultado de ejecución de los procesos. En las variables ${DIMENSION}, ${SECUENCIA}, ${STATUS} y ${Internal.Job.Name} tengo los valores que necesito para actualizar. En status tendremos el resultado de ejecución del job (segun haya habido o no errores). Con dichos valores, llamo a la subtransformación pasandole estos parametros y en ella se registra la actualización en BD.

Transformacion - Dim Producto Extraccion Final

Os dejo el link a la transformación aquí.

Transformaciones de Stage (utilidades de control).

En las transformaciones anteriores hemos utilizado 3 subtransformaciones, que hemos encapsulado de esta forma para su reutilización y para ser poder llamadas tanto en los procesos de carga de la Dimensión Producto como del resto de dimensiones de nuestro DW.

• Recuperar secuencia proceso: la utilizamos en la transformación Dim Producto Pre. Cuando se llama desde la transformación superior, se le pasa como parametro la dimensión que estamos procesando y el tipo de status a buscar. Hace sus operaciones y devuelve el valor recuperado con un paso del tipo Mapping Output. El valor devuelto sera recuperado en la transformación padre en el flujo de datos.

Transformacion - Stage Secuencia Proceso

• Borrar Tabla: la hemos utilizado en la transformación Dim Producto Extraccion – Resto Tablas. Recibe un nombre de tabla y una secuencia, a traves del paso Mapping Input, y con esos valores ejecuta un borrado de datos de la tabla con el paso Delete.

Transformacion - Stage Vaciado Tabla

• Actualiza Status: la hemos utilizando en la transformación Dim Producto Extraccion Final. Igualmente recibe los parametros con el paso Mapping Input, completa los valores con el paso Get System Info (para recuperar la fecha/hora actual) y finalmente actualiza la tabla de control con el paso Insert/Update.

Transformacion - Stage Graba Status ETL

Como hemos indicado antes, todas estas transformaciones las vamos a reutilizar en los procesos de carga del resto de dimensiones, pues vamos a enfocar los procesos de extracción de la misma manera. Toda accion que se repita de una forma regular, y que pueda ser configurada para pasarle parametros y que este determinen su comportamiento, es candidata a ser utilizada de esta forma.

Ejecución del Job desde linea de comando con Kitchen.

El job esta preparado para ser ejecutado de una forma automatica y programada. Esto sera tan sencillo como ejecutar el job utilizando la herramienta Kitchen, con la siguiente sintaxis:

kitchen.bat /file:C:\Pentaho\EnoBI\DimProducto\Dim_Producto_Extraccion.kjb /level:Basic > C:\Pentaho\ENOBI\LOG\trans.log

Estamos ejecutando el fichero, con un nivel de debug básico, y pasando la salida del comando a un fichero, para tener registrados los resultados. De la misma manera, y con la sintaxis adecuada, podriamos estar ejecutando transformaciones guardadas en el repositorio de PDI en base de datos. Os recomiendo la lectura de la ayuda online de Kitchen donde se explica muy bien el funcionamiento del comando y la sintaxis a utilizar en cada caso (incluyendo ideas para la planificación de jobs a nivel de sistema operativo con at y cron).

Conclusiones.

Os dejo el link en formato zip de todos los ficheros xml utilizados en este ejemplo, para que vosotros mismos podais jugar y analizar el diseño realizado. Como comentario final, indicaros que, como en toda herramienta, Kettle dispone de multiples formas de realizar las mismas acciones y en este ejemplo he intentado combinar varias de ellas para hacerlo lo mas didactico posible. Espero que os sea de utilidad.

Tratamiento de Dimensiones Lentamente Cambiantes (SCD) con PDI.

Antes de continuar con la construcción de los procesos ETL para la dimensión producto, vamos a ver la forma de gestionar las dimensiones lentamente cambiantes con Kettle (Pentaho Data Integration). En concreto, veremos en detalle el paso Dimension lookup/update. En este step se implementa de forma automatica el algoritmo para el tratamiento de las SCD del tipo I y II. Hagamos un poco de memoria:

• SCD Tipo 1: Sobreescribir: cuando hay un cambio en los valores de un atributo, sobrescribimos el valor antiguo con el nuevo sin registrar una historia. Esto significa perder toda la historia del dato, y cuando hagamos un análisis veremos la información histórica desde el punto de vista actual.
• SCD Tipo 2: Añadir fila: cuando hay un cambio, creamos un nuevo registro en la tabla. El nuevo registro tiene una nueva clave subrogada, de forma que una entidad de sistema operacional (por ejemplo, un cliente), puede tener varios registros en la tabla de la dimensión según se van produciendo los cambios. Estamos gestionando un versionado, que ademas puede incluir unas fechas para indicar los periodos de validez, numerador de registros o un indicador de registro activo o no.

Con un sencillo ejemplo entenderemos mejor el funcionamiento del paso o step. En nuestro caso, tenemos una tabla en Oracle, llamada dwd_cliente_pruebas, que contiene los datos extraidos del ERP, ya normalizados y elaborados para actualizar la tabla. Sera el origen de datos. Por otro lado, tenemos una tabla destino, la tabla de la dimensión, llamada DWD_CLIENTE_SK, cuya estructura física es la siguiente:

Ejemplo SCD - Tabla Dimension

La tabla incluye, al final, 3 campos para gestionar la versión y las fechas de validez del registro (campos VERSION, DATE_FROM y DATE_TO). En nuestro ejemplo, leeremos los datos de la tabla original, le aplicaremos algunas transformaciones a los datos recuperados (no relevantes en este caso, pues estamos modificando descripciones), y finalmente pasaremos la información al paso Dimension lookup/update (llamado Actualización Dimension Cliente), que se encargara de realizar la actualización de la dimensión cliente. El esquema de la transformación en PDI será el siguiente:

Transformacion con tratamiento de SCD

Vamos a ver en detalle cada uno de los pasos:

Lee_Clientes_ERP: con un paso del tipo Table Input (categoría Input) recuperamos todos los registros de la tabla origen, y pasamos los datos al flujo de la transformación. En la tabla dwd_clientes_pruebas ya tenemos los datos elaborados (podría ser algo así como la tabla temporal del area de Stage).

Lectura de datos de clientes del sistema Origen

Modifica Descripciones, Eliminar Campos, Renombrar Campos: irrelevantes en este ejemplo. Utilizamos los pasos Calculator y Select Values para modificar las descripciones de los clientes y así «ocultar» las denominaciones reales para este ejemplo. Estos pasos los vamos a utilizar para forzar cambios en los campos y asi ver el comportamiento del algoritmo de SCD.

Fecha Proceso: con un paso del tipo Add Constants (categoría Transform) introducimos en el flujo de datos una constante del tipo fecha, que se va a utilizar en el paso siguiente para realizar la busqueda de valores en la dimensión. Vamos a explicar esto un poco. Tal y como observamos en la imagen siguiente, tenemos una tabla origen (nuestro ERP), donde tenemos asociado a cada cliente un código (el campo CLIENTE_ID), que lo identifica en todas las transacciones que realizamos con el. Se van realizando cambios sobre el (su agente comercial asociado, el canal de venta en el que lo incluimos, el tipo de cliente o el domicilio, etc). Aunque vamos haciendo cambios, estos van siempre actualizando los datos maestros y siempre trabajamos con el mismo código.

Ejemplo SCD - Tabla Origen Vs Tabla Destino

Por otro lado, tenemos nuestro sistema DW que se alimenta de los datos anteriores. Para poder analizar mejor la información, nos queremos ir guardando la historia de los cambios en el cliente. Para ello nos inventamos las claves subrogadas, que es una numeración independiente de los clientes. Ademas, establecemos que cuando haya un cambio en alguno de los campos que consideremos importantes (SCD del tipo 2), vamos a crear un nuevo registro para el cliente. Esto generara una nueva clave subrogada (campo CLIENTE_SK en nuestro ejemplo), con el mismo código de cliente del sistema original (CLIENTE_ID) y sus correspondientes atributos. Además, tenemos el campo VERSION (que se ira incrementando conforme vayamos creando «versiones» de los datos del clientes), y los campos DATE_FROM y DATE_TO (que indican la validez temporal de los datos), que también se iran generando y actualizando conforme vayamos generando registros. Es aquí donde tiene sentido la fecha que vamos a pasar al flujo de datos. Esta fecha nos va a permitir recuperar el registro «valido», «activo» o «vigente» en ese momento, según el valor de la fecha, buscando el registro cuya date_from (fecha inicio) sea menor o igual que la fecha indicada y aquel cuya date_to (fecha_fin) sea mayor o igual. Solo ha de haber un registro valido o activo en una fecha determinada si todo esta definido correctamente.

Actualiza Dimensión Cliente: con un paso del tipo Dimension lookup/update (categoría Data Warehouse) realizaremos de forma automática la gestión de los cambios que acabamos de indicar. Veamos en detalle como se configura el step. En primer lugar, en la parte superior, indicaremos las siguientes propiedades:

• Update the dimension: este flag nos permite configurar el paso en modo lectura (solo para recuperar la clave subrogada y sus valores ) o en modo escritura (se realiza una actualización de los datos de la dimensión).
• Connection: conexión de base de datos que vamos a utilizar para el proceso.
• Target schema: esquema en la base de datos donde esta la tabla (a rellenar o no según el motor de base de datos que estemos utilizando).
• Target Table: tabla destino (sera la de la dimensión en el DW).
• Commit size: realización de un commit en base de datos tras el número de registros insertados o modificados indicado aquí.
• Enable the cache: habilitación de la cache de memoria para los procesos.
• Cache size in rows: tamaño de la cache en registros.

En la parte de enmedio de la pantalla, en la pestaña Keys, indicamos que clave vamos a utilizar para identificar al cliente (es la clave del sistema operacional). Siempre indicamos un tupla, por un lado el valor Dimension field (como se llama el campo en la tabla de la dimensión en el DW) y por otro lado el valor Field in Stream (como se llama la variable del flujo de datos de kettle que contiene el valor a analizar). Esta ultima esta llenada con los datos recuperados de la tabla origen.

Ejemplo SCD - Configuracion Dimension Lookup / Update (Paso I)

En la parte inferior, indicamos los nombres de los campos de la tabla de la dimensión que se van a utilizar para la gestión de las claves subrogadas y el versionado:

• Technical key field: es el campo de la tabla de la dimensión que contiene la clave subrogada.
• Creation of technical key: aquí indicamos la forma de creación de la clave subrogada (valor maximo de la tabla +1, una secuencia de valores que pasamos al flujo o un campo autoincrementable (según la base de datos que estemos utilizando)).
• Version field: aquí indicamos el campo de la tabla de la dimension donde se almacenará el numero de versión de registro (correlativo entre los diferentes registros de un cliente).
• Stream Datefield: campo del flujo de datos que contiene la fecha con la que obtener el registro valido (tal y como hemos explicado anteriormente).
• Date range start field: campo de la dimensión que contiene la fecha de inicio de validez del registro.
• Min year: año de referencia minimo para la creación de la fecha de inicio (por ejemplo, si aquí indicamos 1900, el primer registro se creara con fecha inicio 01/01/1900).
• Table daterange end: campo de la dimensión que contiene la fecha de fin de validez del registro.
• Max year: año de referencia maximo para la creación de la fecha de fin (por ejemplo, si aquí indicamos 2099, el primer registro se creara con fecha inicio 31/12/2099).

Toda la gestión de la actualización de estos campos (clave subrogada, version, date_from, date_to) se va a realizar de una forma automatica por parte del algoritmo que incorpora el paso Dimensión Lookup.

A continuación, en la pestaña Fields vamos a configurar sobre que campos queremos que se realize la gestión de las dimensiones lentamente cambiantes del tipo I o II. Para cada campo de la dimensión que querramos gestionar, indicaremos los siguientes valores:

• Dimensión field: nombre del campo en la tabla de la dimensión a analizar.
• Stream field to compare with: campo en el flujo de datos cuyo contenido analizaremos contra el valor existente en base de datos para determinar si hay cambio o no, y realizar la acción oportuna (que indicaremos en el siguiente campo).
• Type of dimension update: comportamiento del algoritmo en el caso de que haya un cambio en el valor del campo. Podemos establecer tres tipos de comportamientos (tal y como vemos en la imagen):

Ejemplo SCD - Configuracion Dimension Lookup / Update (Paso II)

Insert: se genera un nuevo registro en base de datos (y su correspondiente clave subrrogada). En el caso de ser la primera inserción, los registros tendrían el aspecto de la imagen siguiente (una clave subrogada en el campo cliente_sk, el valor 1 en el campo version, y las fecha inicio y fin inicializadas a los valores minimo y maximo, en este caso, 01/01/1900 y 31/12/9999. Recordar que los años los hemos definido nosotros en la configuración del step).

Ejemplo SCD - Creación de registros de la dimensión en la primera carga

En el caso de que ya existiera un registro para el cliente, se va a generar uno nuevo, con una nueva clave subrogada. El nuevo registro incrementa su numero de versión en 1, y las fechas son ajustadas (el registro existente limita su fecha final) y el registro nuevo tiene fecha inicio la indicada en el proceso y fecha final la máxima indicada (tal y como vemos en la imagen). Sería el tratamiento de las SCD del tipo II. En el ejemplo hemos puesto que el campo CLIENTE_DESC, cuando tenga un cambio, tenga un tratamiento del tipo INSERT.

Ejemplo SCD - Creación de un segundo registro (o superior ) para un cliente en SCD Tipo II

Update: no se genera un nuevo registro para el cliente cuando hay un cambio en el campo indicado. Simplemente actualizamos el valor del campo que ha sdio modificado en base de datos con el valor del flujo (el recuperado del sistema origen). Sería el tratamiento de las SCD del tipo I. Solo se actualiza el registro de base de datos activo en la fecha de referencia.

Punch through: es una variante del Update. El comportamiento es similar, pero se actualizan todos los registros que tuviera el cliente (no solo el activo). Esto puede ser util cuando queramos actualizar determinados valores (por ejemplo, el nombre del cliente, que es corregido por estar erróneo). En la imagen vemos un ejemplo de este tipo de actualización. Hemos cambiado el comportamiento del campo CLIENTE_DESC para que sea un punch through. Y al cambiar su valor se han actualizado todos los del cliente (no solo el activo como en el caso del Update).

Ejemplo SCD - Tratamiento SCD del tipo I (variante Update Through)

Esta es basicamente la forma de trabajar con este control de Pentaho Data Integration. Con un único paso nos hemos evitado todo el tratamiento e implementación del algoritmo de las SCD, que esta encapsulado dentro del componente.

Os dejo el link al fichero xml de la transformación para que vosotros mismos podais ver como quedaría configurado todo el proceso. Igualmente, os dejo el link a la entrada del blog donde vimos la forma en que Talend gestionaba el tratamiento de las dimensiones lentamente cambiantes, por si os interesa comparar una herramienta con la otra.

16.3.1. ETL Dimensión Tiempo con PDI.

La dimensión tiempo es una dimensión estática, pues se crea una vez (por ejemplo, para un periodo de fechas de 10 o 20 años), y no se vuelve a tocar, a no ser que queramos añadir algún atributo adicional. Podemos tener dos enfoques para la creación de la dimensión:

• Uso de procedimientos almacenados: según el motor de base de datos que vayamos a utilizar, crearemos un procedimiento almacenado (o similar), que incluirá el código necesario para el llenado de la dimensión tiempo. En Dataprix.com tenemos un ejemplo de este enfoque para trabajar con Oracle, Sql Server y MySql. Roland Bouman también incluye un ejemplo de procedimiento almacenado en Mysql para calcular el día del año en que cae la Pascua. Esta forma de construir la dimensión puede ser útil mientras no tengamos cálculo de atributos complejos que requieran el uso de otro tipo de herramientas.

• Uso de un proceso ETL: por otro lado, podemos construir un proceso ETL totalmente desvinculado del motor de base de datos elegido. Además, este proceso lo podremos reutilizar para todos los sistemas DW que diseñemos, solo ajustandolo a la posible elección de atributos que se decida. He encontrado dos interesantes ejemplos de construcción de la dimensión tiempo en el blog de Roland Bouman de nuevo y también en el de Fabian Schladitz, ambos utilizando Kettle. Nos van a ser de gran utilidad para diseñar nuestra propia transformación.

Transformacion para crear la dimensión tiempo en PDI - Roland Bouman

Transformación para el llenado de la dimensión tiempo.

La estructura física de la dimensión tiempo es la siguiente. Tendremos dos tablas, una principal y una derivada para los meses, debido al nivel de granuralidad de la información de presupuestos, como ya vimos.

Esquema Fïsico Dimensión Tiempo

Los calculos a realizar para llenar cada uno de los campos son los siguientes:

• Fecha Clave: fecha en la notacion yyyymmdd para procesarla como enteros. Será la clave de la dimensión. Convertiremos la fecha a este formato (seria una especie de clave subrogada).
• Fecha Id: fecha en la notación habitual de tipo date.
• Fecha Desc: fecha formateada en texto ( por ejemplo: 15 de abril de 2009).
• Dia de la semana Id: dia 1,2,3…7.
• Dia de la semana Desc:  dia de la semana en texto (Lunes, Martes, Miercoles,..).
• Dia del Mes Id: numero de dia de la fecha en el mes (dia 14, dia 28, dia 31).
• Dia del Año Id: numero de día de la fecha en el año (dia 234, dia 365).
• Semana Id: notacion año-semana para comparativas, cabeceras (YYYYSS, 200845). Es la clave que identifica cada semana.
• Semana Desc: semana del año en formato descriptivo ( Semana 45 de 2008).
• Numero de Semana Id: numero de semana donde se incluye la fecha (1, 12, 23). Desvinculada del año para otro tipo de análisis.
• Numero de Semana Desc: descripción en texto del numero de semana (Semana 1, Semana 23).
• Festivo: indicador de si la fecha es un festivo o no (S,N).
• Fin de semana: indicador si la fecha es fin de semana o dia entre semana (S,N).

• Mes Id: notación año-mes para comparativas,cabeceras (YYYYMM, 200811). Es la clave que va a identificar cada mes.
• Mes Desc: mes del año en formato descriptivo ( Enero de 2008, Abril de 2010, etc).
• Número de Mes Id: número del mes en el año (Enero = 1, Febrero = 2, etc). Desvinculado del año para otro tipo de análisis.
• Número de Mes Desc: descripción en texto del mes (Enero, Febrero, Marzo,…).

• Trimestre Id: notacion año-trimestre para comparativas, cabeceras (YYYYT, 20081). Es la clave que va a identificar cada trimestre.
• Trimestre Descripcion: trimestre en formato descriptivo ( 1 Trim 2008, 2 Trim 2008, etc.).
• Numero de Trimestre Id: trimestre donde se incluye la fecha (1,2,3 o 4). Desvinculado del año para otro tipo de análisis.
• Numero de Trimestre Desc: descripcion en texto del trimestre (1 Trimestre,2 Trimestre, etc).

• Año Id: año de la fecha, con 4 digitos. Es la clave que va a identificar cada año.
• Año Ant Id: año anterior al actual, con 4 digitos.

Teniendo en cuenta todo esto, vamos a preparar el proceso ETL utilizando PDI. El proceso va a generar todos los datos vistos para cada fecha, desde el 01 de Enero de 2005 hasta el 31 de Diciembre de 2020. El diseño de nuestra transformación será el siguiente:

Dim Tiempo - Transformacion PDI

Vamos a ver un poco en detalle cada uno de los pasos.

• Generar Fecha: con un paso del tipo Generate Row (categoría Input), generamos 10 mil veces un valor del tipo fecha, con el valor 20050101, que corresponde a la fecha inicial que queremos procesar. Esta fecha es el punto de partida de la generación de toda la secuencia de fechas hasta llegar al 31 de diciembre de 2020.

Dim Tiempo - Genera Fecha

• Añadir 1 dia: con un paso del tipo Add Secuence (categoría Transform) generamos un valor númerico, que empieza por 1, y que se va incrementando en cada ejecución. Este valor será el que sumaremos más adelante a la fecha de partida del paso anterior para generar cada una de las fechas deseadas.

Dim Tiempo - Añadir 1 Dia

• Calcular Fechas: con un paso del tipo Calculator (categoría Transform) empezamos a realizar varias operaciones sobre la fecha. La mas importante, como hemos indicado, es sumar la secuencia del paso anterior a la fecha inicial, para generar las fechas. Ademas, vamos a realizar otras operaciones, como la conversión de la fecha a número para generar la clave subrogada (pasos 2 y 3, utilizando una variable auxiliar). Ademas, con las diferentes operaciones que nos proporciona el paso, obtenemos el año de una fecha, el año anterior, el dia del mes y del año, la semana, el  número de mes de la fecha, etc.

Dim Tiempo - Calcular Fechas

• Filter Rows: con un paso del tipo Filter Rows (categoría Flow) filtramos los registros generados por la fecha, para evitar que se genere ninguna fecha superior al 31 de diciembre de 2020. Las fechas que no cumplen la condición son desechadas a un paso Dummy (que no hace nada).
• Calculo de Textos: con un paso del tipo Formula (categoría Scripting), calculamos algunos campos más, como el identificador de la semana y su descripción, el mes, el dia de la semana, etc.  El paso Formula lo utilizaremos cuando los cálculos son mas complejos, y no nos vale el step Calculator. Como bien indica Maria Carina Roldan en su libro sobre PDI, ese paso utiliza la librería LibFormula, cuya sintaxis esta basada en el estandar OpenFormula standard, del que podeís ampliar información en el siguiente link. Como podeís ver en la imagen, el  paso tiene un montón de formulas disponibles, y cuando seleccionamos alguna tenemos una pequeña ayuda con la sintaxis de cada una.

Dim Tiempo - Calcular Textos

• Calculo Resto Atributos: igualmente,  para aquellos cálculos mas complejos, siempre nos queda la opción de «picar» nuestro propio código. Para ello, con el paso Modified Java Script Value (categoría Scripting) podemos definir nuestro propio código Javascript. En nuestro caso, hemos utilizado la localización en español para trabajar con las fechas y asi poder extraer las descripciones de los meses del año y de los días de la semana, así como para obtener otros valores como los trimestres. Podeís ver la documentación  Online de Java para el formateo de fechas y obtención de los diferentes atributos de estas. Como información adicional, indicar que PDI utilizar como motor JavaScript el producto Rhino, de Mozilla. Es una implementación Open Source del nucleo del lenguaje JavaScript. Podeís ampliar información en la web de Mozilla.

Dim Tiempo - Calcular Resto Atributos

• Ordena Valores: con el paso Select / Rename values (categoría Transform), seleccionamos los campos que queremos pasar al siguiente paso, y el orden. Estamos ordenando los registros para que esten igual que los campos de la base de datos. Con este paso también podriamos seleccionar que campos queremos eliminar ( y no pasar en el flujo al siguiente paso).

Dim Tiempo - Ordenar Campos

• Carga DWD_TIEMPO: como paso final en la transformación, utilizamos el step Table Output (categoría Output) para insertar en la correspondiente tabla de la base de datos los registros generados. En concreto, insertamos en la tabla DWD_TIEMPO. Previamente, hemos definido en PDI la conexión a base de datos para poder seleccionar la tabla destino (también podemos realizar  navegación entre las diferentes tablas del catalogo).

Dim Tiempo - Carga en Tabla BD

Os dejo el link al fichero xml de la transformación para que vosotros mismos la podaís analizar o utilizar en el caso que os sea de utilidad.

Como paso final, llenaremos la tabla DWD_TIEMPO_MES, que no será mas que la ejecución de la siguiente sentencia Sql (recordar que esta dimensión no es mas que un subconjunto de datos de la tabla principal de la dimensión tiempo). La sentencia sería la siguiente:

insert into dwd_tiempo_mes
    select distinct mes_id, mes_desc, mesn_id, mesn_desc,
                    trim_id, trim_desc,
                    trimn_id, trimn_desc,
                    anyo_id, anyo_ant_id from dwd_tiempo order by mes_id;

Otras consideraciónes a tener en cuenta.

Os recomiendo la lectura del Blog de BI Facil donde se habla de los diferentes enfoques para realizar la numeración de semanas. Igualmente, en el blog Enfoque Práctico de Jose Cano también se hacen algunas reflexiones interesantes sobre la dimensión tiempo.