8.3 Codificación geográfica

Para la generación de mapas con Litebi se generó una base de datos en Access siguiendo las indicaciones del departamento de sistemas.

Como podemos ver el en Anexo 1, Litebi se comunica con una aplicación SIG open source llamada Geoserver, la cual proporciona los servicios de generación de mapas a Litebi.

Se acordó llevar una codificación para los mapas basada en los códigos impuestos para cada municipio, provincia y comunidad autónoma por el Instituto Nacional de Estadística (INE). De esta manera la composición de la base de datos que daría soporte a los mapas sería mucho más sencilla de construir a partir de ficheros Excel obtenidos desde la web del INE (http://www.ine.es/).

Vamos a describir los diferentes elementos de la base de datos para que quede claro como Litebi utiliza estos ficheros para comunicarse con Geoserver. Existen tres tablas las cuales se describirán a continuación:

– Tabla Layers: Posee las diferentes capas en las que se puede dividir un mapa geográfico así como el código que identifica a cada capa.

– Tabla Polygons: Tabla maestra donde se recogen diferentes lugares geográficos distinguidos según la capa. Están identificados por un Litebiid que es el campo que se asocia con el nivel geográfico en Litebi y poseen un campo Polygonid que es aquel que identifica el polígono que Geoserver deberá mostrar por pantalla. El campo LitebiParentId identifica al padre inmediatamente anterior del elemento, por ejemplo si estuviésemos en una provincia el LitebiParentId identificaría la comunidad asociada a esa provincia.

– Tabla Padre_Hijo: Esta tabla se realizó para sacar con una simple consulta todas las capas padre que posee un elemento concreto. Por ejemplo buscando un municipio en concreto puedo obtener cuál es su comarca, su provincia y su comunidad en una misma consulta utilizando esta tabla y la de Polygons.

Por tanto queda claro que para alimentar de datos los niveles geográficos de las dimensiones y generar los mapas sólo basta con realizar la asociación a estas tablas, con los datos que estemos trabajando, a través de unas simples consultas donde obtengamos el LitebiId, que se comunicará con Geoserver haciendo la correspondencia internamente con el PolygonId asociado y mostrando el polígono por pantalla de la zona deseada.

Una vez construidos todos los procesos de extracción, transformación y carga de información sólo queda ejecutarlos para que tengamos los cubos y las dimensiones cargados con los datos que se poseen actualmente en el centro. Para ello cabe destacar que, al ser la primera vez que se cargan datos, es recomendable seleccionar en todos los pasos de “Output a Litebi” la opción de carga “Standard”. La ejecución de cada uno de los procesos se realiza pulsando sobre el botón “Play”, situado en la parte superior de la ventana de diseño de Kettle. La carga se debe realizar en este orden obligatoriamente, primero cargaremos las dimensiones y luego los cubos, ya que estos últimos dependen de las dimensiones.

Por fin tenemos nuestras estructuras de datos rellenadas con información. Este proceso de carga de datos suele durar bastante tiempo, y de momento, sólo se había conseguido cargar datos una vez, cosa que no es suficiente. ¿Cómo podríamos mantener los datos actualizados constantemente sin necesidad de recargar todos los datos cada vez? Esta pregunta es bien sencilla de responder, utilizando cargas incrementales. Las cargas incrementales vienen a ser pequeñas modificaciones sobre los procesos ETL que únicamente permitan cargar datos de los últimos 15 días, del último mes, del último año…etc. De esta manera con la primera carga poseemos los datos que ya existen en la actualidad y con las cargas incrementales podríamos actualizar los datos añadiendo aquellos que día a día se vayan generando, en cuestión de pocos minutos.

Explicaremos brevemente las pequeñas modificaciones que se realizaron en cada uno de los procesos ETL para conseguir las cargas incrementales que mejoraron el rendimiento de la actualización de datos.

• En las dimensiones compartidas “Determinación” y “Tiempo” se optó por dejarlas como cargas estándar, ya que se realizaban en un corto plazo de tiempo y se observaron una serie de problemas en los datos si estas dimensiones se cargaban de manera incremental.
• Dimensión Reg: En el paso de “Obtener Muestras” se añadió una condición en la consulta SQL que limitará los datos obtenidos a los del último mes según la fecha oficial. Esta expresión era: where add_months (sysdate,-i)<=fecha_oficial.
• Cubo Resultados Origen 1: En el paso de “Obtener Resultados Origen 1” se añadió una expresión a la consulta SQL para obtener la fecha de hace un mes y se le llamo como HOY (add_months(SYSDATE,-1) as hoy) . Como todavía no se tenía ninguna fecha con la que comparar en el primer paso, tuvimos que realizar este filtrado de información justo un paso antes de cargar los datos en Litebi por medio de un paso de filtrado y una condición FECHA_OFICIAL => HOY.
• Cubo Resultados Origen 2: Este caso fue el más complicado de tratar. A partir del nombre que tenía cada hoja de Excel, según el formato indicado al cliente para cada archivo, se obtuvo un nuevo campo con el nombre del fichero o de la hoja (era el mismo en ambos casos) llamado FECHA_HOJA. A partir de este campo se generó un paso de Java Script donde obtener campos para el año y el mes actual, y el año y el mes del fichero Excel. Una vez obtenidos estos campos, se realizaba un paso de filtrado dejando pasar aquellos ficheros de Excel que fuesen como mucho de hace un mes. Podemos observar el código y las condiciones en el Anexo 3.
• Cubo Resultados Origen 3: Se utilizó el mismo recurso que para el caso del Cubo de Resultados Origen 1.
• Cubo Resultados Origen 1 Global: Se utilizó la misma condición que para la dimensión Reg pero en el paso “Obtener Resultados Origen 1” where (add_months(SYSDATE,-1)<=FECHa).

Gracias a esto, las cargas de datos se realizaban en tiempos mucho menores, ya que únicamente se cargaban los datos del último mes en todos los cubos. El último paso a tener en cuenta era que para todos los pasos de “Output a Litebi” esta vez la opción de carga a escoger debería ser “Actualizar datos”, de esta manera los datos antiguos se mantienen en los data marts, los que ya estaban se sobreescriben y los nuevos se añaden.

Al realizarse los procesos de transformación de datos de manera incremental se solventó el problema del coste computacional que suponía trabajar con una gran cantidad de datos día tras día para mantenerlos actualizados. El próximo objetivo a conseguir era que la actualización se realizara de manera automática sin necesidad de acción humana y sobretodo que se realizara fuera de horas de trabajo para no sobrecargar el sistema.

Para conseguir este objetivo se diseñaron los Jobs, procesos que automatizaban las cargas periódicas de datos y que ejecutaban de manera secuencial cada una de las transformaciones de datos anteriormente diseñadas. Este paso se compone de tres jobs: uno para cargar todas las dimensiones compartidas, otro para cargar los cubos y otro que ejecutará a estos dos de manera secuencial, es decir, un job general. A continuación se muestra el diseño de estos tres procesos:

Como se puede observar en los diagramas, cada uno se comunica y ejecuta los procesos ETL que se han diseñado (pasos con un aspa verde) ya sean cubos o dimensiones. El último diagrama es el job general, el cual se comunica con los dos anteriores (pasos en naranja llaman a otros jobs). De esta manera tenemos comunicación con todos los procesos que engloban la solución en un solo fichero o diagrama de flujo.

Como se puede observar en el diagrama del job general, existen dos líneas de flujo de información, una activa y otra inactiva. La inactiva se refiere a las cargas estándar de datos (cargados los datos una vez, se eligió la opción de carga “Borrar datos primero” para estos casos en los “Output a Litebi”) la cual se dejó como recurso de emergencia por si en las cargas incrementales ocurría algún error. La línea de flujo activa hace referencia a las cargas incrementales realizadas en el apartado anterior y será la línea de flujo principal para cada carga diaria que se ejecute.

Estos procesos se pueden ejecutar de manera manual o de manera automática preferiblemente, utilizando la herramienta KITCHEN de Kettle en un pequeño script y ejecutando este script como tarea programada del sistema con la periodicidad que nosotros le asignemos.

Este fichero llamado li_run.bat se componía de la siguiente línea de script:

kitchen /file/:ETL/GENERAL.kjb /level:basic > li_run.log

Con este simple script se conseguía ejecutar el job general sin necesidad de abrir Kettle y además podíamos observar los fallos que se produjeran durante la carga de datos en un archivo log donde aparece toda la información de la ejecución del proceso.

Una vez realizado este script solamente nos quedaba darle una periodicidad de ejecución a través de una sencilla tarea programada de Windows. Para realizar una tarea programada vamos a Inicio>Programas>Accesorios>Herramientas del Sistema>Tareas Programadas y agregamos una tarea nueva diciéndole el archivo a ejecutar, la hora y los días que queremos que se ejecute. En este caso se definió a las 2:00 diariamente para tener así los datos actualizados de manera que se recargaran cuando nadie estuviese utilizando el sistema.

El último paso en la composición de esta solución de BI, fue definir en Litebi los miembros calculados y los conjuntos necesarios para la realización de los informes del centro. Como se explica en el Anexo 1, los miembros calculados y los conjuntos son datos que se pueden calcular a partir de los datos existentes en los cubos a través de fórmulas multidimensionales en lenguaje MDX.

Litebi posee una forma muy sencilla de generar estos elementos a través de un editor de fórmulas. De esta manera el usuario no necesita conocer todos aquellos aspectos técnicos del lenguaje, sino que gracias al editor podemos hacernos una idea de la construcción de las fórmulas y las operaciones que realizan cada una de sus funciones.

Para el caso de CECAV se definieron dos miembros calculados y dos conjuntos que explicaremos a continuación:

– Número Explotaciones Analizadas: Como se había definido al identificar las necesidades de información del cliente, este factor era uno de los importantes, pero no teníamos la posibilidad de obtenerlo a nivel de ETL dada su complejidad.

Esta fórmula se aplicó a todos los cubos, ya que era necesario obtener este dato para cualquier fuente de análisis de muestras. A continuación se muestra la fórmula generada en lenguaje MDX puro (los números hacen referencia a los identificadores en base de datos de Litebi para las dimensiones) para el cálculo de este elemento (cabe destacar que el nivel Explotación estaba presente en varias jerarquías y por tanto era necesario evaluarlas todas en la fórmula):

IIF( NOT (             (    [123557892_123636908_47.123636908].CurrentMember IS

[123557892_123636908_47.123636908].[T otal]         )             )                        ,           COUNT(

Filter(Descendants([123557892_123636908_47.123636908].CurrentMember,[1235 57892_123636908_47.123636908].[123634104]),         NOT       (            IsEmpty     (

[Measures].[123601004_Sum]      )           )     )        )           , IIF(             NOT     (     (

[123557892_123635163_47.123635163].CurrentMember                                        IS

[123557892_123635163_47.123635163].[T otal]     )           )              ,           COUNT(

Filter(Descendants([123557892_123635163_47.123635163].CurrentMember,[1235 57892_123635163_47.123635163].[123634104]),         NOT       (            IsEmpty     (

[Measures].[123601004_Sum]      )           )     )        )           , IIF(             NOT     (     (

[123557892_123636907_47.123636907].CurrentMember                                        IS

[123557892_123636907_47.123636907].[T otal]     )           )              ,           COUNT(

Filter(Descendants([123557892_123636907_47.123636907].CurrentMember,[1235 57892_123636907_47.123636907].[123634104]),         NOT       (            IsEmpty     (

[Measures].[123601004_Sum]      )           )     )        )           , IIF(             NOT     (     (

[123557892_123634103_47.123634103].CurrentMember                                        IS

[123557892_123634103_47.123634103].[T otal]         )             )                        ,           COUNT(

Filter(Descendants([123557892_123634103_47.123634103].CurrentMember,[1235

57892_123634103_47.123634103].[123634104]), NOT (               IsEmpty (

[Measures].[123601004_Sum]     )                 )        )           )                , COUNT(

Filter(Descendants([123557892_123636908_47.123636908].CurrentMember,[1235 57892_123636908_47.123636908].[123634104]), NOT (               IsEmpty (

[Measures].[123601004_Sum] ) ) ) ) ) ) ) )

– Número Registros Analizados: Para los casos en los que los registros se dividen en muestras, a nivel de ETL era posible calcular el número de muestras pero no podíamos distinguir y contar los diferentes registros que formaban los diferentes grupos de muestras. A continuación mostramos el código en MDX (se realizan productos cartesianos con las jerarquías que se seleccionarán en los informes que afecten al número de registros):

COUNT(                                               Filter(                                              Crossjoin(

Descendants([123557892_123636907_47.123636907].CurrentMember,[12355789 2_123636907_47.123636907].[123557893])           ,                  Crossjoin(          {

[123557892_123635181_47.123635181 ].CurrentMember                                         :

[123557892_123635181_47.123635181 ].CurrentMember }                       ,               {

[123557892_123634743_47.123634743].CurrentMember                                          :

[123557892_123634743_47.123634743].CurrentMember } ) ) , NOT ( IsEmpty ( [Measures].[123601004_Sum] ) ) ) )

Esta fórmula se utilizó unicamente para los casos de análisis de muestras en placas y así obtener el número de placas que se habían analizado (se definió en todos los cubos menos el de “Resultados Origen 1 Global” ya que trataba con muestras independientes).

– Conjuntos Últimos 12 meses y Últimos 3 meses: Estos conjuntos se definieron para obtener, siempre que el usuario lo deseara, la selección de forma sencilla del conjunto de meses que necesitara (fue solicitado por el usuario ya que esta era la periodicidad de la mayoría de los informes). A continuación se muestra la fórmula para los últimos 12 meses realizada con el editor de Litebi:

Cola (Miembros del nivel (Nivel de la jerarquía (Jerarquía del miembro (Miembro actual de la dimensión (Tiempo_Fecha Fin Análisis)) ’Mes’)) ,12)

La fórmula para los últimos 3 meses sería similar, únicamente cambiando el 12 por el 3. Esto supone una gran ventaja, ya que estos conjuntos se actualizan a medida que pasa el tiempo y de esta manera tendríamos informes actualizados independientemente del tiempo que pase.