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.