El Rincon del BI

Descubriendo el Business Intelligence…

16.3.3. ETL Dimensión Cliente con PDI.

Posted by Roberto Espinosa en 27 mayo 2010


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).

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 191 seguidores

A %d blogueros les gusta esto: