El Rincon del BI

Descubriendo el Business Intelligence…

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

Posted by Roberto Espinosa en 25 mayo 2010


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.

About these ads

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 176 seguidores

%d personas les gusta esto: