8. IMPLEMENTACIÓN DE LA SOLUCIÓN

Kettle es un proyecto belga open source adquirido por Pentaho que incluye un conjunto de herramientas para la realización de ETL. Uno de sus objetivos es que el proyecto ETL sea fácil de generar, mantener y desplegar.

Kettle se compone de 4 herramientas:

SPOON: permite diseñar de forma gráfica transformaciones ETL.

PAN: ejecuta las transformaciones diseñadas con SPOON.

CHEF: permite, mediante una interfaz gráfica, diseñar la carga de datos incluyendo un control de estado de los trabajos.

KITCHEN: permite ejecutar los trabajos batch diseñados con Chef.

Es utilizado como base para la versión de LiteIntegrator por sus características:

Yaestudio

• Producto sólido con un largo recorrido.
• Multiplataforma (java).
• Ampliamente conocido en el sector del Business Inteligence.
• Adaptable a las necesidades de Litebi (Open Source).
• Ampliamente usado en software empresarial.
• Basado en estándares (WSDL, SOAP, etc.).

La combinación de Kettle y el paso de Output a Litebi (Ver Anexo 1) conforman el módulo liteIntegrator de Litebi, gracias al cual, la definición y carga de procesos ETL se simplifica de gran manera respecto a otros sistemas de BI.

Devonn 

Las dimensiones compartidas son utilizadas en diferentes cubos de esta solución. Estas dimensiones contienen una serie de valores (a priori todos los posibles), los cuales se asocian a los valores del cubo en el campo que especifiquemos como una dimensión compartida a través de su identificador. Por tanto, es lógico que para producirse esa asociación tengamos que almacenar los valores previamente en algún lugar. De esta manera conseguimos una estandarización de la información, ya que, si se utiliza una variable, por ejemplo Tiempo, en diferentes cubos, cuando los analicemos ambos presentaran esa variable en el mismo formato, independientemente de cómo estuviese representada en el origen de datos. Aquí se muestran los procesos ETL que alimentan las dimensiones compartidas.

• Dimensión Tiempo: Utilizada en todos los cubos para diferentes tipos de fechas. Su ETL consiste, lógicamente, en generar una serie de fechas (tipo Date) y otros campos que se asocien con los diferentes niveles de las jerarquías (Año, Trimestre, Mes…etc.).

A continuación detallaremos la función de cada uno de los pasos que componen su ETL:

• Parámetros Dimensión: Se inician una serie de parámetros identificativos del tiempo a partir de una fecha inicial, en este caso 01 -01 -2005.
• Secuencia Dias: Añade una columna DaySequence que nos servirá para sumarla a la fecha inicial y así obtener fechas correlativas.
• Calcular Atributos: Proceso Java Script que calcula todos los atributos de una fecha, es decir, los niveles de las jerarquías definidos en la dimensión. Para ver el código ver Anexo 3.
• Filtrar Filas: Este paso hace que solamente pasen aquellas fechas menores que la fecha actual, por tanto, no existirán fechas seleccionables en el análisis que todavía no hayan ocurrido.
• Dummy: A este paso llegan aquellas filas que no cumplen la condición impuesta en el filtro.
• Selecciona/Renombra Valores: Con este paso desechamos aquellos valores que ya no son importantes en el resultado final. Así rebajamos la lectura de datos en este punto.
• Secuencia Dias 2: Este paso calcula horas para cada día (para este caso no es relevante ya que no miramos a nivel de horas, pero se suele reutilizar el código de la dimensión tiempo en varias soluciones diferentes).
• Selecciona/Renombra Valores 2: Cambiamos el nombre a los parámetros para que tengan un nombre más identificativo.
• Output a Litebi: En este paso realizamos la asociación de los campos generados para las fechas con los niveles diseñados en Litebi para esta dimensión (tanto identificadores de nivel, como sus descripciones). Al tratarse de la primera carga, elegimos como modo de carga “Standard’. A continuación podemos observar la asociación de campos entre Kettle y la dimensión Tiempo.

• Dimensión Determinación: Esta dimensión compartida se utiliza en los cubos de Origen 1 y Origen 1 Global, ya que ambos comparten la misma fuente de origen y por tanto las enfermedades serán las mismas y estarán contenidas en el mismo lugar.

Mostremos en detalle qué función realiza cada uno de los pasos:

• Obtener enfermedades: En este caso leemos los datos de una tabla a través de una consulta SQL para obtener las enfermedades (los datos no se generan de la nada como en el caso anterior). Para ver el código SQL asociado ver Anexo 3.
• Enfermedad Des Null: Comprobar si la enfermedad posee un nombre y si no lo tiene ponerle un valor por defecto.
• Obtener Métodos: Lectura de todos los métodos que se realizan para obtener las enfermedades.
• Añadir Método: Asociamos a cada una de las enfermedades el método de análisis que le corresponde.
• Concatenar código- nombre: Se crea una nueva variable para obtener la enfermedad como “código-nombre” (demandado este formato por el cliente). Para ver el código ir al Anexo 3.
• Método Des Null: Si no existe un método asociado a una enfermedad le ponemos como método “Desconocido”.
• Selecciona Valores: Seleccionamos los valores que finalmente nos interesan para la dimensión.
• Tipado: Le damos un tipo de datos a los valores o se lo cambiamos, le damos una longitud, un nuevo nombre…etc.
• Output a Litebi: Asociamos los valores obtenidos con los niveles de la dimensión Determinación, teniendo en cuenta que los valores a asociar deben tener el mismo tipo que se le puso a los niveles en el diseño. Aquí podemos observar cómo queda la asociación de los campos.

• Dimensión Reg: Esta dimensión compartida se considera la más importante de todas, ya que deberá contener los registros de muestras y todos sus atributos o características. Esta dimensión será utilizada en todos los cubos. Se trata de un proceso ETL complejo que trataremos de exponer de la forma más sencilla posible:

Detallemos sus pasos brevemente:

• Obtener Muestras: Lectura de la tabla “Muestras” de los datos asociados a cada una de las muestras, así como los datos del cliente que solicita el análisis y los datos de la explotación de donde proviene.
• Obtener Tipo de Ave: Lectura de todos los tipos de aves existentes.
• Agregar Tipo de Ave: Asociación del tipo de ave correspondiente a cada muestra.
• Obtener cod municipio: Código Java Script donde se extrae el código del municipio de donde proviene la muestra a partir del código de la explotación según el formato REGA. Además al código de la explotación se le concatena el nombre de su dueño. Código en Anexo 3.
• Filtrar: Dejar pasar sólo aquellos registros que cumplen con el formato REGA del código de explotación.
• Dummy: Desecho de los registros que no cumplen el formato REGA.
• Tipo Ave Des: Si el tipo de ave no se asocia con ninguno de los existentes en la base de datos lo trataremos como “Desconocido”.
• Origen Des: Mismo caso que el anterior pero para el campo Origen.
• Tipado 1: Se formaliza el tipo de dato y la longitud para cada uno de los campos seleccionados.
• Obtener Municipio: Lectura del fichero de Access que contiene la codificación geográfica a través de una conexión ODBC (se intentó primero utilizando el paso de Input Access que viene el Kettle pero ocurrían errores de lectura). En este paso obtenemos los datos necesarios para relacionar los municipios. La consulta la podemos observar en el Anexo 3.
• Obtener Comarca: Mismo caso que el anterior pero obtenemos los datos de las comarcas.
• Obtener Provincia: Obtenemos los datos de las provincias del fichero Access para los mapas.
• Obtener Comunidad: Lectura de los datos de comunidades autónomas.
• Añadir Municipio: Relacionar el código de municipio obtenido a través del código d explotación con el código de municipio que se tiene en los mapas para asignarle a cada muestra su nombre.
• Añadir Comarca: A partir del municipio obtenemos la comarca asociada a él (cabe destacar que solamente se desarrollaron los mapas a nivel de comarca para la Comunidad Valenciana).
• Añadir Provincia: Añadimos la provincia de cada muestra a partir de su municipio.
• Añadir Comunidad: De la misma manera se obtiene la comunidad asociada al municipio.
• Municipio Des, Comarca Des, Provincia Des, Comunidad Des: Si alguno de estos campos no existe lo trataremos como “Desconocido”
• Formato: Renombramos los campos con nombres más significativos.
• Tipado 2: Volvemos a dar formato únicamente a los campos que hemos ido añadiendo nuevos.
• Profesional Des: Si el campo “Profesional” es nulo lo tratamos como “Desconocido”.
• Obtener Profesional: Obtenemos los datos de los profesionales que analizan las muestras.
• Añadir Nombre Profesional: Se relaciona el código de profesional (NIF) para obtener el nombre completo de la persona.
• Output a Litebi: Igual que en los casos anteriores, relacionamos todos los campos obtenidos con los niveles de la Dimensión. Cabe apuntar que los niveles asociados a municipio, comarca, provincia y comunidad tienen lo que se llama un identificador geográfico que es necesario para la generación de los mapas (esto se definió a la hora de definir la dimensión en Litebi).

En este apartado presentaremos el proceso que alimentará de datos al cubo asociado al origen de datos 1.

Este proceso es sencillo y se compone de los siguientes pasos:

• Obtener Resultados Origen 1: Lectura de los datos de las placas analizadas. Ver Anexo 3 para observar el código de la consulta de datos.
• Validación: Validar el campo “Valor” y dejar pasar sólo aquellos que sean numéricos (demandado por el cliente).
• Dummy: Desechar los registros que no cumplen la condición antes expuesta.
• Formato: Damos formato y longitud a los campos.
• Obtener muestras: Se obtienen datos de las muestras para relacionarlos con las dimensiones compartidas (fechas sobretodo)
• Tipado 2: Se da el formato adecuado a los datos de las muestras
• Añadir datos muestra: Se añaden datos asociados a las muestras analizadas en este origen.
• Obtener determinación: Obtenemos la enfermedad que se asocia a cada muestra analizada en todo el centro.
• Añadir determinación: Añadimos la enfermedad que se está analizando para cada muestra.
• Tipado: Se renombran los campos con nombres más identificativos.
• Output a Litebi: Como en las dimensiones, se asocian los campos entre el cubo y los registros leídos. Cabe destacar que para el caso de los cubos se asocian las métricas y los niveles de las dimensiones embebidas, y para el caso de dimensiones compartidas, únicamente se asocia su nivel base, ya que el resto de niveles ya están rellenados en el ETL de la dimensión propia. Este es el momento donde Litebi sabe por medio de esta asociación que registros de esa dimensión están presentes en el cubo.

Figura 39: Carga a Litebi del Cubo Resultados Origen 1

Como se había comentado en apartados anteriores, la lectura de este origen de datos se llevaría a cabo a través de diversos ficheros de Excel.

Coaching

Comentamos los sencillos pasos de extracción, transformación y carga de los datos de esta fuente:

• Obtener Históricos: Lectura de los ficheros de Excel ubicados en el directorio acordado con el cliente.
• Filtrar nulos: Dejar pasar sólo aquellos registros que el código de registro de la muestra no sea nulo.
• Pasar a integer: Como todos los datos leídos del Excel tienen tipo String, necesitamos tener el código de registro de la muestra como entero para poder compararlo y obtener así datos de la muestra posteriormente. Esto se consigue con un simple var Muestra= parseInt (REGISTRO) ;
• Obtener muestras: Lectura de la base de datos donde están contenidas todas las muestras y sus datos.

• Tipado: Damos formato a los datos de las muestras.
• Añadir datos muestra: Asociación entre los datos de las muestras totales y las muestras analizadas en este origen de datos.
• Filtrar asignados: Dejar pasar aquellos registros que se hayan asociado con alguna muestra.
• Agregar count: Se agrega un campo contador para llevar la cuenta de todas las muestras que se analizan.
• Formato: Dar formato a los valores que se van a cargar.
• Formato indicadores: Este Java Script consiste en seleccionar aquello que nos interesa de cada una de las medidas existentes en este tipo de análisis. Ver Anexo 3.
• Selecciona valores: Nos quedamos con los valores que nos van a ser útiles y el resto se desechan.
• Output a Litebi: Como en todos los casos asociamos los campos con los indicadores y las dimensiones que componen el cubo.

La fuente principal de este origen de datos es un fichero Access al que accedemos a través de una conexión ODBC, ya que se detectaron fallos a la hora de utilizar el paso de Input Access que viene en Kettle.

Detallemos los pasos que se observan en el diagrama:

• Obtener Casilla Placa: Gracias a los modelos de las tablas facilitados por el cliente que pudo componer una consulta SQL (ver Anexo 3) para obtener todos los datos necesarios presentes en esta aplicación.
• Obtener muestras: Como en los casos anteriores se obtienen los datos de las muestras.
• Tipado: Damos formato a los datos obtenidos.
• Añadir datos muestra: Se asocian los códigos de las muestras para añadir más datos relevantes.
• Agregar Count: Se añade un campo contador para llevar la cuenta de las muestras analizadas, algo muy importante para el cliente.
• Formato: Seleccionamos y formateamos aquellos valores que se usarán finalmente.
• Output a Litebi: Volcamos los datos necesarios en nuestra estructura de data mart definida y diseñada en Litebi.

Figura 43: Carga a Litebi del Cubo Resultados Origen 3

Este cubo se debe alimentar de datos contenidos en el origen 1 pero que hacen referencia a todas las muestras que pasan por el centro y varias determinaciones que se analizan para cada una en esta aplicación.

Vamos a explicar brevemente cada uno de los pasos de este complejo diagrama:

• Obtener Resultados Origen 1: Consulta SQL para obtener los registros de las muestras con sus determinaciones analizadas así como los resultados obtenidos. Ver el código en Anexo 3.
• Ver si es numérico: Filtrar según el resultado obtenido es un número o no.
• Ver si tiene /: Observar si el resultado es de la forma xx/xx.
• Ver si tiene e, Ver si tiene e 2: Comprobar si el resultado está en formato científico (Ej. 6e2).
• Con /: Paso intermedio.
• Separar e: Separar el número en base y exponente.
• Separar /: Por delante de la barra son los positivos, por detrás los totales.
• Multiplicar base: Obtener el resultado numérico en coma flotante. Ver código en Anexo 3.
• Numérico puro, Numérico puro 2: Juntamos todos los registros que tienen un resultado transformado ya como un número.
• Es textual: Guardar el resultado si es un texto en una variable. var RESULTADO_TEXTUAL = RESUL_VALOR.getStrÍng();
• Selecciona valores, Selecciona valores 2…. Selecciona valores 5, Ordenar: Renombrar, seleccionar, dar formato y seleccionar el tipo de los campos que sean necesarios.
• Añadir constante, Añadir constante 2,., Añadir constante 4: Añadir constantes como si los resultados son positivos o totales. Esto se hace porque hora de unir todas las ramas de flujo de información en un mismo punto estamos obligados a que por todas las ramas vengan los mismos campos. En el paso 4 es donde se añade la variable contador para llevar el número de muestras.
• Obtener muestras: Leer los datos asociados a las muestras.
• Añadir datos muestras: Completar los registros añadiendo más datos relacionados con las muestras.
• Tipado: Seleccionar los valores necesarios para la carga.
• Output a Litebi: Asociamos los datos con las dimensiones y los indicadores del cubo.

Figura 45: Carga a Litebi del Cubo Resultados Origen 1 Global