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.