El Rincon del BI

Descubriendo el Business Intelligence…

16.3.4. ETL Carga de hechos de ventas con PDI.

Posted by Roberto Espinosa en 30 mayo 2010


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.

2 comentarios to “16.3.4. ETL Carga de hechos de ventas con PDI.”

  1. Juan Saransig said

    Por favor enviar los achivos de descargar a mi mail juan_saransig@hotmail.com, pues al momento ya no se descargan

  2. esteban said

    alguien tiene el archivo de descarga… estebanomar.rojas@gmail.com

Responder

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

 
A %d blogueros les gusta esto: