34 Caso Práctico: Análisis

4 CASO PRÁCTICO: ANÁLISIS

“Without data, you are just another person with an

opinion”
- W. Edwards Deming -

E
ste capitulo tiene como objetivo analizar y describir el contexto general del proyecto que queremos llevar
a cabo en el seno de la empresa EUIGS y que consiste en el diseño y la implementación de un sistema de
toma de decisiones para ayudar a contestar las preguntas del negocio ofreciéndole la información y las
herramientas necesarias en que podrá apoyarse en la toma de decisiones.

34
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 35

4.1 Contexto
En un Mercado donde la competencia está siendo cada vez más grande y feroz las compañías de seguros se
encuentran con la necesidad de reinventarse para reducir costes, fidelizar sus clientes y seducir a clientes nuevos,
para ello adoptan nuevos sistemas de toma de decisiones que les puedan ayudar a cumplir con estos objetivos.

La problemática que afrontamos hoy es cómo podemos distribuir la flota de guras de la que dispone la compañía
de forma óptima para:
• Reducir costes de desplazamiento gasolina y desgaste de la grúa.
• Reducir el tiempo de respuesta de la grúa de forma que esta pueda llegar al lugar de la incidencia en el
mínimo tiempo posible.
• Satisfacer una de las necesidades más importante del cliente de cualquier seguro de coches y que
consiste en ser atendido en el menor tiempo posible en caso de que lo necesite.
Para dar solución a estas necesidades y otras mas preocupaciones vamos a intentar aprovechar el potencial que
nos ofrece el Open Data que será nuestra fuente principal de datos, además de aprovechar las herramientas que
nos brinda el Business Intelligence para diseñar e implementar un sistema decisional (Data Mart) que en un
futuro lo integraremos dentro de la base de datos Data Warehouse de la empresa.
Para ello deberíamos.
• Buscar el Dataset (conjunto de datos) que nos hace falta dentro de los miles disponibles. Esta tarea es
muy laboriosa debido al gran volumen de datos disponibles y muy importante al mismo tiempo ya que
una mala elección hará que el proyecto fracase.
• Estudiar la fuente de datos (Dateset), y los distintos formatos en los que esta fuente esta disponible.
Además de si es posible tecnológicamente su explotación.
• Diseñar el repositorio de datos (Data Mart) en el que iremos guardando los datos una vez estos han sido
tratados adecuadamente definiendo las tablas que lo componen (hecho, dimensiones, etc.).
• Diseñar los procesos ETL que tendrán la funcionalidad de extraer los datos de la fuente, transformarlos
y luego cargarlos en el Data Mart.
• Llegado a este punto se hace necesario la elección y el uso de las herramientas oportunas para
automatizar las ejecuciones de los procesos ETL anteriormente diseñados.
Una vez diseñado y cargado el Data Mart podemos proceder a explotarlo con la herramienta OLAP oportuna y
que mejor se adapte a nuestras necesidades, sacando la información que nos será útil de cara a la de toma de
decisiones sobre cómo debemos distribuir nuestra flota basándonos en el comportamiento de las incidencias de
tráfico.

4.2 Escenario
Debido a la innovación del proyecto y antes de proceder a implementarlo directamente dedicando recursos que
no sabemos si después seremos capaces de rentabilizar habrá antes que realizar una prueba de concepto (POC,
Proof Of Concept) y que consiste en desarrollar el proyecto con recursos mínimos (sin compra de servidores, ni
licencias, ni equipos dedicados…), para demostrar a los jefes de negocio (Business Analist) la viabilidad de esta
solución tecnológicamente.
Por lo cual al tratarse de un POC concentraremos nuestro esfuerzo en localizar y tratar los datos de la comunidad
de Euskadi para que una vez la viabilidad está demostrada y el proyecto aceptado, pues se podrá adoptar la
solución y escalarla a nivel nacional a todas las comunidades de España y también en un futuro a nivel
internacional a los diferentes países en los que nuestra empresa de seguros está presente (Francia, Italia, Reino
unido, Estados unidos).

35
36 Caso Práctico: Análisis

Además, partimos de que ya disponemos de un Data Warehouse desarrollado con sus correspondientes
DataMarts y que tiene la funcionalidad de historificar la información del sistema operativo que usa la empresa
“Guidewire” (a estos datos no vamos a poder acceder debido a las estrictas normas de confidencialidad de la
empresa).
Entonces en este proyecto centraremos nuestro esfuerzo en localizar fuente de información (Open Data) que nos
ofrezca datos sobre las incidencias de tráfico en la comunidad de Euskadi con el objetivo de tratar estos datos,
guardarlos y historificarlos construyendo un nuevo Data Mart para luego poder analizarlo y dar respuestas a las
preguntas que nos plantea el negocio ofreciéndole las herramientas oportunas que les puedan orientar en toma
de las decisiones correctas.

4.3 Vida útil del Proyecto (B.D.L)

The Business Dimensional Lifecycle (B.D.L) o ciclo de vida dimensional representa las etapas necesarias que
hay que llevar a cabo a la hora de desarrollar una solución DWH / Data Mart.

Mantenimien
to Y
Crecimiento
Diseño de la Selección de
arquitectura Productos e
Tecnica implementacion

Definición de Diseño e
Planificacion
Requerimientos Modelado Implementación
del Proyecto Diseño Fisico de subsistema
Despliegue
de Negocio Dimensional
ETL

Especificacion Desarrollo de
de Aplicaciónes Aplicaciones de
de Usuario B.I usuario B.I

Administración
del Proyecto
DWH/BI

Figura 21. Ciclo de Vida Dimensional (B.D.L, Business Dimensional Lifecycle).

En la figura 20 podemos apreciar la aproximación global de todas las etapas del proyecto y que consisten en:
• Planificación del Proyecto.
Significa hacer una planificación del proyecto definiendo su alcance y las correspondientes tareas y objetivos
que lo componen.
• Definición de requerimientos de negocio
Se trata de saber la necesidad del negocio para poder introducirla al proyecto e implementarla. Esta etapa es la
más importante y complicada ya que requiere experiencia o una formación aparte en el área del negocio.

36
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 37

De esta etapa depende una gran parte del éxito del proyecto además de que constituye el punto de partida de las
tres ramas paralelas que son Tecnología, Datos e Interfaz de usuario.
• Modelado dimensional
Se empieza construyendo una matriz que representa los procesos de negocio claves y su dimensionalidad y a
partir de eso un modelo dimensional debe estar desarrollado. Este modelo identifica la granularidad de las tablas
de hechos y las dimensiones asociadas.
• Diseño físico
Define las estructuras físicas necesarias para la implementación la base de datos lógica. Este proceso se inicia
mediante la determinación de reglas de nomenclatura y las particiones y luego configurando el entorno de base
de datos.
• Diseño e implementación de subsistema ETL
Se trata de diseñar los procesos de Extracción, Transformación y Carga de datos.
• Diseño de arquitectura técnica
Hay tres factores que deben tomarse en consideración y son las necesidades del negocio, el entorno técnico
actual existente y por ultimo las principales técnicas estratégicas futuras previstas.
• Selección de productos e implementación
Se hace una evaluación de componentes específicos, tales como la plataforma de hardware, el SGDB y las
herramientas de preparación y acceso a los datos. Una vez estos componentes evaluados y seleccionados, se
procede a su instalación.
• Especificación de aplicación de usuario B.I
Se definen las especificaciones exigidas a la aplicación B.I según los roles de los usuarios finales.
• Desarrollo de aplicación de usuario B.I
Se trata de construcción de tableros, reportes y aplicaciones necesarias para explotar el Data Warehouse.
• Despliegue
Es el punto de convergencia de datos, la tecnología y la aplicación de usuario.
• Mantenimiento y crecimiento
El Data Warehouse es una base de datos que está siempre bajo nuevos desarrollos y que acompaña a la evolución
de la empresa por lo cual siempre se van a necesitar incluir nuevas dimensiones, hechos, etc. Por lo cual un
servicio de mantenimiento es necesario.

4.4 Planificación del proyecto

Antes de empezar la realización de cualquier proyecto una buena planificación definirá una gran parte del éxito
de este. La planificación del proyecto consiste esencialmente en organizar las tareas que van a permitir alcanzar
los objetivos deseados. Por lo cual vamos primero a dividir el proyecto en varios sub objetivos asignando a cada
uno un conjunto de tareas a realizar y después vamos a estimar la duración que ocupará cada tarea para su
realización.
El diagrama de Gantt es el mejor adaptado para estructurar nuestras tareas organizándolas de forma cronológica
de modo que las tareas y los tiempos asignados a cada una, están presentados en el diagrama de Gantt como se
puede ver en la siguiente figura.

37
38 Caso Práctico: Análisis

Figura 22. Diagrama de Gantt que describe la planificación que se ha seguido en el proyecto

38
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 39

5 CASO PRÁCTICO: DISEÑO E

IMPLEMENTACIÓN

“La forma de empezar es dejar de hablar y empezar”

-Walt Disney-

A
Continuación en este capítulo procederemos a desarrollar nuestra solución Business Intelligence
presentando primero nuestra fuente de datos Open Data y sus características y analizando los datos que
nos ofrece para luego pasar a diseñar nuestro modelo conceptual de base de datos (Data Mart) de
incidencias de tráfico. Después nos dedicaremos a programar los procesos ETL necesarios que realimentarán
nuestra Data Mart y automatizarlos con el planificador de tareas anteriormente presentado Rundeck. Una vez
construido el Data Mart procederemos a explotar los datos y a dar respuestas a las preguntas planteadas.

39
40 Caso Práctico: Diseño e Implementación

5.1 Fuente de datos Open Data

Nuestro primer objetivo en este Proyecto es buscar una fuente de datos Open Data que cumpla los siguientes
requisitos
• Ofrezca datos de incidencias de tráfico en la comunidad autónoma de Euskadi.
• Base de datos abierta (de acceso libre).
• Que se actualice de forma periódica manteniendo los datos frescos.
• Que los datos estén disponibles en formatos que permitan su explotación tecnológica.

Después de varios días de búsqueda intensiva entre los miles de catálogos disponibles y bases de datos hemos
podido encontrar una base de datos que cumple los requisitos antes mencionados y que estudiaremos a
continuación.

5.1.1 Información de la base de datos: Incidencias de tráfico de la comunidad de Euskadi

Nombre de base de datos. Incidencias de tráfico de la comunidad de Euskadi.

Publicador. Comunidad Autónoma del País Vasco.

Nivel de administración. Administración Autonómica.

Licencia. [Link]

Catálogo. [Link]
real-en-euskadi

Formatos Disponibles. Zip, XML.

Descripción. Facilita los detalles de las incidencias en las carreteras de la Comunidad

Autónoma de Euskadi de forma actualizada y en tiempo real.

Tiempo de actualización. Cada 1h.

Español, euskera.
Idioma.

Cobertura geográfica. País Vasco.

Fecha de creación. 19/07/2010.

Acceso directo. [Link]

Uso. Público.

Tabla 7. Información de la base de datos Open Data a explotar

Una vez encontrada la fuente se dedicará un tiempo a estudiar los datos que esta ofrece, para saber cómo se
comportan y prevenir los posibles casos que pueden dar fallos a nivel de programación de los procesos o a nivel
de incoherencia de datos.

40
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 41

5.1.2 Estructura de la información proporcionada por el servicio

La estructura del fichero XML proporcionado es la siguiente:

➢ Tipo
o Meteorológica
o Accidente
o Retención
o Seguridad vial
o Otras incidencias
o Puertos de montaña
o Vialidad invernal tramos
o Pruebas deportivas

➢ Autonomía
o Euskadi

➢ Provincia
o Alava-Araba
o Bizkaia
o Gipuzkoa

➢ Matrícula
o BI
o VI
o SS
➢ Causa
o En caso de ser de Tipo Metereológica
• Agua
• Viento
• Nieve / Hielo
• Niebla
o En caso de ser de Tipo Accidente
• Alcance
• Atropello
• Salida
• Tijera camión
• Vuelco
o En caso de ser de Tipo Retención
• Fiestas
• Prueba deportiva
o En caso de ser de Tipo Seguridad Vial
• Aceite
• Avería
• Caída objetos
• Desprendimiento
• Gasoil
• Incendio
• Socavón
o En caso de ser de Tipo Puertos de montaña
• Agua nieve
• Hielo
• Nevando
• Niebla
• Nieve
• Nieve / Hielo

41
42 Caso Práctico: Diseño e Implementación

• Desconocida
• Obras
• Otros
o En caso de ser de Tipo Vialidad invernal tramos
• Agua nieve
• Hielo
• Nevando
• Niebla
• Nieve
• Nieve / Hielo
• Desconocida
• Obras
• Otros
o En caso de ser de Tipo Obras
• Obra
• Otra actividad
o En caso de ser Tipo Pruebas deportivas
• Automovilismo
• Ciclismo
• Ciclocross
o Cross
• Maratón
• Biatlón
• Triatlón
• Pentatlón
• Motociclismo
• MotoCross
• Marcha ciclista
• Mixta
• Atletismo
➢ Población
➢ Fecha hora inicio
➢ Nivel
o Verde (Normal)
o Blanco (Fluido)
o Amarillo (Lento)
o Rojo (Muy lento)
o Negro (Parado)
o En el caso de Puertos de montaña se concatenan los valores del estado del puerto para Turismo (T),
Camión (C) y Articulados (A). Estos valores del estado son:
• Cerrado
• Abierto
• Cadenas
• Precaución
➢ Carretera
➢ Punto Kilométrico inicial
➢ Punto kilométrico final
➢ Sentido
➢ Nombre
➢ Longitud
➢ Latitud

42
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 43

5.1.3 Ejemplo

Figura 23. Ejemplo de fichero XML.

43
44 Caso Práctico: Diseño e Implementación

5.2 Modelado del Datamart

En este apartado modelaremos nuestra nueva Data Mart de incidencias de tráfico de la comunidad de Euskadi.
Partiremos desde el fichero XML que descargaremos cada 1h de la base de datos y de la información vista
anteriormente. Para crear nuestro modelo estrella (tablas de dimensiones y hecho). Crearemos también dos
esquemas que las llamaremos AC y es donde cargaremos los datos a lo bruto y el esquema DC que es donde
guardaremos los datos consolidados para ser explotados.

5.2.1 Tablas de Dimensiones

• d_autonomia

id_d_autonomia Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_autonomia Descripción de la autonomía.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 8. Dimensión d_autonomia.

• d_causa

id_d_causa Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_causa Descripción de la causa de incidencia.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 9. Dimensión d_causa.

• d_matricula

id_ d_matricula Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_matricula Descripción de la matrícula.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 10. Dimensión d_matricula.

44
Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 45

• d_nivel

id_d_nivel Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_nivel Descripción de nivel.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 11. Dimensión d_nivel.

• d_provincia

id_d_provincia Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_provincia Descripción de la provincia.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 12. Dimensión d_provincia.

• d_srce_syst

id_ d_srce_syst Índice interno de la tabla (PK).

date_load Fecha de carga del dato.

desc_srce_syst Descripción del sistema fuente de datos.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 13. Dimensión d_srce_syst.

• d_tipo

id_d_tipo Índice interno de la tabla (PK).

id_d_srce_syst Origen de la fuente de donde se cargan los datos.

date_load Fecha de carga del dato.

desc_tipo Descripción del tipo de incidencia.

swit_reti Indica si el registro de la dimensión ha sido retirado.

Tabla 14. Dimensión d_tipo.

45
46 Caso Práctico: Diseño e Implementación

5.2.2 Tabla de hechos

• H_INCI

id_h_inci Índice interno de la tabla (PK).

Id_d_srce_syst FK a la tabla de dimensión d_srce_syst.

id_d_tipo FK a la tabla de dimensión d_tipo.

id_d_autonomia FK a la tabla de dimensión d_autonomia.

id_d_provincia FK a la tabla de dimensión d_provincia.

id_d_matricula FK a la tabla de dimensión d_matricula.

id_d_causa FK a la tabla de dimensión d_causa.

poblacion Población del incidente.

fechahora_ini Fecha y hora de comienzo del incidente.

id_d_nivel FK a la tabla de dimensión d_nivel.

carretera Carretera donde tuvo lugar el incidente.

pk_inicial Punto kilométrico inicial del incidente.

pk_final Punto kilométrico final del incidente.

sentido Sentido del incidente.

longitud Longitud del punto del incidente.

latitud Latitud del punto del incidente.

date_load Fecha de carga del dato en el DWH.

date_cons Fecha de consolidación del dato.

nombre Nombre de incidente.

Tabla 15. De hechos h_inci.

46
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 47

5.2.3 Modelo en estrella

Nuestro modelo en estrella queda como muestra la siguiente figura:

Figura 24. Modelo en estrella desarrollado.

47
48 Caso Práctico: Diseño e Implementación

5.3 Concepción de la base de datos PostgreSQL

Después de instalar la herramienta pgAdmin3 procedemos a la creación de nuestra base de datos relacional de
incidencias de tráfico de la comunidad de Euskadi relacional.
• Creación de base de datos DWH con la siguiente configuración.

Figura 25. Configuración de la base de datos DWH.

• Después de la creación de base de datos creamos los esquemas AC, DC y TEMP.

Figura 26. Creación de los esquemas AC, DC y TEMP.

48
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 49

Usaremos los tres esquemas para:

• AC: Este esquema se usará para cargar los datos desde los ficheros XML facilitados por la base de
datos de servicios de incidencias de tráfico de la comunidad autónoma de Euskadi.
• DC: Este esquema guardará los datos cargados en el AC después de que estos estén tratados y
transformados haciendo las comprobaciones oportunas como el control de duplicidad. Este esquema
guardará los datos listos para ser explotados por el usuario final (Reporting) o por las herramientas
OLAP.
• TEMP: en este esquema guardaremos las tablas temporales que necesitaremos en un futuro para
resolver posibles incidencias o algo similar, las tablas de este esquema serán borradas con cierta
frecuencia para ahorrar espacio en el disco duro de nuestra base de datos.
Una vez creados los esquemas procederemos a crear nuestras tablas de dimensiones y hecho anteriormente
vistas en los correspondientes esquemas.

Figura 27. Tablas creadas en el esquema AC.

49
50 Caso Práctico: Diseño e Implementación

5.4 Diseño de procesos ETL

En este apartado explicaremos los procesos (Jobs) ETL diseñados para la extracción, carga y transformación de
los datos a partir de la fuente anteriormente vista.

5.4.1 LoadXmlFile job

[Link] Funcionamiento
• Encargado de la extracción de los ficheros XML de base de datos Open Data figura 28.
• Este proceso ETL se encarga de conectarse a la base de datos anteriormente descrita mediante web
service para extraer los ficheros XML correspondientes y guardarlos en un directorio local firgura 29.
• Los ficheros XML extraídos de la base de datos se irán extrayendo con frecuencia de 1extracción /
1hora por lo cual para que no haya perdida de información se guardarán en local con el nombre =
“file_YYYY_DD_HH_MM_SS.xml)” siendo Y: year (años) D: Day (Dia), H:Hour (hora), M:minute
(minutos), S:second(segundos) figura 30.

[Link] Componentes, funciones y variables de Talend usados

• Componente tPrejob (lanza la ejecución del job).
• Componente tPostjob (lanza la ejecución de un post job).
• Componente tjava (permite incrustar código java personalizado).
• Componente tFileFetch (recupera un fichero a partir de un protocolo en este caso el protocolo Https).
• Componente tFixedFlowInput (permite gestionar datos fijos a partir de variabes internas).
• Componente tBufferOutput (mete en buffer datos para que estos puedan ser recuperados más tarde).
• Variable de contexto file_name.
• Función [Link]().
• Función [Link]().

50
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 51

[Link] Diseño

Figura 28. LoadXmlFile job diseñado en Talend.

Figura 29. Resultado de ejecución del Job LoadXmlFile en local.

Figura 30. Fichero XML generado al ejecutar el job LoadXmlFile.

51
52 Caso Práctico: Diseño e Implementación

5.4.2 EUSKA_FACT_AC job

[Link] Funcionamiento
• El Job primero establece las conexiones con nuestra base de datos PostgreSQL tanto para el esquema
AC como para el esquema TEMP figura 31.
• Luego se encarga de extraer los datos de los documentos XML (cargados anteriormente con el Job
LoadXmlFile) parsearlos, mapearlos y cargarlos en nuestra tabla temp_inci del esquema TEMP en
nuestra base de datos PostgreSQL tabla 16.
• Luego procede a transformar los datos guardados en la tabla temp_inci en el esquema TEMP mapeando
las dimensiones correspondientes de los datos para finalmente guardarlos en la tabla h_inci del esquema
AC tabla 17.
• Y por último hace el commit de todos los cambios aportados a la base de datos dejando las conexiones
cerradas e imprimiendo un log con el resumen de numero de registros procesados.

[Link] Componentes, funciones y variables de Talend usados

• Componente tPrejob.
• Componente tPostgreSqlConection (establece la conexión a la base de datos PostgreSQL).
• Componente tjava.
• Componente tFileInputXM (permite recuprar un fichero XML y parsearlo).
• Componente tPostgreSqlInput (permite recuperar datos con consultas SQL de la base PostgreSQL).
• Componente tPostgreSqlOutput (permite guardar datos en la base de datos PostgreSQL).
• Componente tPostgreSqlcommit (realiza el commit de todas las transacciones y cierra la conexión).
• Componente tMap (dirige y transforma los datos a partir de varias fuentes de datos).
• Variable de contexto file_name.
• Variable de contexto date_load.
• Función [Link]().
• Función [Link]().

[Link] Diseño
Ejecutado el Job para procesar los datos del XML extraído anteriormente con el Job LoadXmlFile obtendremos
el siguiente resultado de ejecución.

52
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 53

Figura 31. EUSKA_FACT_AC job diseñado en Talend.

Como se puede apreciar en las siguientes figuras el resultado de haber ejecutado el job en las tablas del esquema
AC.

Tabla 16. Resultado de ejecutar el Job EUSKA_FACT_AC, tabla temp_inci.

53
54 Caso Práctico: Diseño e Implementación

Tabla 17. Resultado de ejecutar el Job EUSKA_FACT_AC, tabla h_inci.

5.4.3 EUSKA_FACT_DC Job

[Link] Funcionamiento
• El Job después de establecer las conexiones con la base de datos se encarga de hacer un control de
duplicidad de los datos que se han cargado en el AC así evitamos redundancia en los datos además se
encarga de borrar los datos duplicados en el AC una vez detectados figura 32.
• El Job también se encarga de mapear los datos cargados del AC con las dimensiones correspondientes
en el DC figura 32.
• Después realiza el commit e imprime un resumen de los registros insertados y duplicados.

[Link] Componentes, funciones y variables de Talend usados.

• Componente tPrejob.
• Componente tPostgreSqlConection.
• Componente tjava.
• Componente tPostgreSqlInput.
• Componente tPostgreSqlOutput.
• Componente tPostgreSqlcommit.
• Componente tMap.
• Variable de contexto date_load.
• Varibale tPostgresqlInput _NB_LINE, tPostgresqlOutput_NB_LINE_INSERTED.
• Funcion (Integer)[Link]().
• Función [Link]().
• Función [Link]().

54
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 55

[Link] Diseño

Figura 32. EUSKA_FACT_DC job diseñado en Talend.

55
56 Caso Práctico: Diseño e Implementación

Tabla 18. Resultado de ejecutar el Job EUSKA_FACT_DC, tabla h_inci.

5.4.4 ControlMaster Job

[Link] Funcionamiento
• Este Job es el que se encarga de orquestar el funcionamiento de los otros Job. Es el Job padre y los
demás son Jobs hijos figura 33.
• Este Job también se encarga de pasar las variables de contexto a sus Jobs hijos y cargarlos de nuevo con
la ayuda del componente tContextLoad figura 33.
• Es el job que estará compilado y ejecutado.
• Imprime un log con un resumen de todos los registros procesados en los job hijos figura 34.

[Link] Componentes, funciones y variables de talend usados

• Componente tPrejob.
• Componente tPostgreSqlConection.
• Componente tjava.
• Componente tContextLoad (modifica dinámicamente los valores del contexto activo)
• Componente LoadXmlFile.
• Componente EUSKA_FACT_AC.
• Componente EUSKA_FACT_DC.

56
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 57

[Link] Diseño

Figura 33. ControlMaster job diseñado en Talend.

Figura 34. Resultado de ejecutar el Job ControlMatser en local.

5.4.5 EUSKA_PRO

Figura 35. EUSK_PRO Proyecto contenedor de todos los Job anteriormente descritos.

57
58 Caso Práctico: Diseño e Implementación

5.5 Automatización de los procesos

Una vez compilado el proyecto necesitaremos una herramienta con la que podremos automatizar la ejecución
de los Jobs para que estos se lancen 1vez/hora. De allí el uso de Rundeck
En este apartado veremos cómo crear un proyecto en Rundeck y como configurarlo para que lanze nuestros Jobs
anteriormente diseñados. Instalaremos Rundeck en windows10 que es el sistema operativo que estamos usando
para desarrollar este proyecto.

5.5.1 Pasos para crear un proyecto en Rundeck

Una vez instalado y configurado y lanzado Rundeck, se accede a la aplicación web que ofrece este último
mediante la url: [Link]
Una vez en la aplicación web, aparecerá un cuadro de autentificación.

Figura 36. Cuadro de autentificación de Rundeck.

Rundeck permite configurar varios roles dando a cada uno ciertos privilegios de gestión dentro del proyecto. Por
defecto y a la hora de instalarlo Rundeck permite la autentificación mediante las siguientes credenciales.
• Username: admin.
• Password: admin.
Una vez dentro procederemos a crear el proyecto EUSK_PRO.

Figura 37. Cuadro de creación de un nuevo proyecto en Rundeck.

Una vez dentro del proyecto EUSK_PRO procederemos a crear el Job que se encargará de lanzar nuestros Jobs
compilados.

Figura 38. Cuadro para acceder a crear un nuevo Job en Rundeck.

58
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 59

Una vez dentro del cuadro de configuración, primero le pondremos un nombre al Job y lo llamaremos
ControlMaster en referencia a Job padre compilado de Talend. En este proyecto solo tendremos un Job, pero en
proyectos más grandes suele haber muchos más y si el nombre no hace referencia al compilado que lanza será
muy fácil equivocarse y lanzar Job de forma errónea.

Figura 39. Cuadro de configuración de Job en Rundeck 1.

Después elegimos la forma en la que queremos que nuestro compilado sea lanzado, donde será ejecutado (en
local o en servidor) y con qué frecuencia (fecha y hora de ejecución).

Figura 40. Cuadro de configuración de script en Rundeck.

• C:\Users\simo\Desktop\ControlMatser_0.1\ControlMatser\ControlMatser_run.bat es el
directorio donde hemos colocado nuestro script que estará lanzado por Rundeck.
• El Job será ejecutado en local lanzando el comando anterior.
• Configuraremos también la frecuencia de ejecución para que sea cada 1h.

Figura 41. Cuadro de configuración de hora de ejecución de Job ControlMatser en Rundeck.

Rundeck también permite configuración de notificaciones en caso de fallo, Tiemout de ejecución y más opciones

Figura 42. Cuadro de configuración de Rundeck.

59
60 Caso Práctico: Diseño e Implementación

Una vez creado el Job y configurado procederemos a lanzarlo manualmente (también podremos esperar a que
se ejecute automáticamente en este caso serían dentro de 13minutos como aparece en la figura).

Figura 43. Cuadro de ejecución de Job en Rundeck.

Una vez ejecutado el Job podremos acceder a los logs para ver el resultado de ejecución.

Figura 44. Log de ejecución de Job de ControlMaster en Rundeck.

En la figura 44 podemos ver que el Job se ha ejecutado correctamente, además podemos ver en el log que se ha
extraído el fichero XML de la base de datos con éxito y que este XML disponía de 138 líneas a procesar 137 ya
estaban cargadas en nuestra base de datos PostgreSQL (tabla dc.h_inci) cuando hacíamos pruebas en Talend y
1 línea es nueva y se ha insertado en dc.h_inci.
Rundeck también ofrece un cuadro con todas las últimaa ejecuciones de los Jobs así se puede rastrear los fallos
cuando han empezado y su por qué. Este cuadro suele ser de gran utilidad para analizar el comportamiento de
nuestros Jobs programados en Talend.

Figura 45. Cuadro de Rundeck que demuestra el historial de ejecución de un Job.

60
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 61

5.6 Explotación de los datos

Una vez definidos y automatizados nuestros procesos ETL, iremos cargando datos en nuestra base de datos
PostgreSQL. Estos datos los analizaremos con la ayuda de la herramienta Tableau que a continuación veremos
cómo funciona, para que finalmente saquemos conclusiones y demos respuestas a las preguntas que nos hemos
planteado al principio de este proyecto.
Una vez instalada la herramienta Tableau en nuestro ordenador procedemos a conectarnos con la misma a la
base de datos PostgreSQL (DWH). Para ello elegimos la conexión correspondiente a la base de datos
PostgreSQL como se puede ver en la siguiente figura.

Figura 46. Conectores a bases de datos disponibles en la herramienta Tableau.

Luego introducimos los datos de nuestra base de datos PostgreSQL (DWH).

Figura 47. Cuadro con la configuración del conector de Tableau a la base PostgreSQL.

61
62 Caso Práctico: Diseño e Implementación

Una vez conectado a la base de datos podemos visualizar todas las tablas de las que esta dispone.

Figura 48. Tablas del esquema DC visualizadas en Tableau.

Montamos nuestro modelo con la herramienta de arrastrar y soltar que ofrece Tableau eligiendo el tipo de join
según convenga.

Figura 49. Modelo DWH montado en Tableau.

Una vez definido el modelo accedemos a diseñar los cuadros de mando que nos interesan. Pero debido a la
limitación económica (y que el proyecto como se ha mencionado antes es una prueba de concepto) no se ha

62
 Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 63

podido comprar un servidor para alojar Rundeck y nuestra base de datos PostgreSQL (DWH) para dejar que
cargue todo el tiempo la información de la base de datos de Open Data. Por lo cual para superar esta limitación
y seguir adelante con el proyecto hemos dejado el ordenador que aloja el servidor Rundeck y la base de datos
PostgreSQL encendido los 48h para cargar la información de dos días completos en nuestra base de datos
PostgreSQL. Así que trabajaremos sobre esta información disponible teniendo en cuenta que los cuadros de
mandos a desarrollar serán perfectamente válidos para el futuro también.
La tarea de análisis de datos requiere mucha destreza y capacidad analítica además de un conocimiento profundo
de las reglas y términos de negocio de la empresa por la cual se está desarrollando el proyecto B.I. A esta tarea
normalmente se suelen dedicar perfiles del tipo Business Analyst (analista de negocio), mientras que a las tareas
de diseño ETL, y mantenimiento de base de datos se suelen dedicar perfiles de Business Intelligence developer
(desarrollador de inteligencia de negocio).
A la hora de diseñar cuadros de mandos hay infinitas opciones e infinitas preguntas a las que se puede dar
respuestas, pero debido al carácter del proyecto formularemos las siguientes preguntas que nos van a servir de
ejemplo de cómo se diseñan cuadros de mando y que nos permitirán hacer un análisis inicial de los datos de los
que disponemos.
• ¿Cuál es la distribución del número de incidencias por población en 48h?
• ¿Qué población tiene el mayor número de incidencias en 48h?
• ¿Cuál es la distribución del número de incidencias por provincia en 48h?
• ¿Qué tipo de incidencias es el más frecuente en 48h?
• ¿Cuál es la causa que provoca el mayor número de incidencias en 48h?

A continuación, presentamos los Cuadros de mando desarrollados.

Figura 50. Distribución del número de incidencias por población en 48h.

63
64 Caso Práctico: Diseño e Implementación

Figura 51. Población con mayor número de incidencias en 48h.

Figura 52. Distribución de número de incidencias por provincia en 48h.

64
Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 65

Figura 53. Tipo de incidencia más frecuente en 48h.

Figura 54. Distribución de causa de incidencias en 48h.

65
66 Caso Práctico: Diseño e Implementación

Figura 55. Distribución de todas las incidencias registradas en función de provincia en 48h.

5.7 Resultados
Las figuras anteriores responden a las preguntas que nos hemos planteado. De modo que la población con mayor
número de incidencias en 48h es Idiazabal seguida de Aretxabaleta y Markina_xemein respectivamente como
se puede ver en la figura 51 además el mayor tipo de incidencias es “vialidad invernal tramos” seguida de
“seguridad vial” y “pruebas deportivas “como se puede ver en la figura 53.
Y analizando las causas principales de las incidencias en los 48h podemos identificar de la figura 54 que las
“obras” seguidas de “ciclismo” y “alcance” representan las causas principales de incidencias en la comunidad
de Euskadi. También podemos ver que hay un gran número de incidencias que vienen con causa no identificada
o desconocida.
Y a nivel de provincia queda claro de la figura 52 que el mayor número de incidencias se ha registrado en
“Bizkaia” con 43 incidencias seguida de “Gipuzkua” con 38 incidencias y por último “Alava-araba” con 34
incidencias y 73 incidencias sin identificar.
Como también vemos hay un gran número de incidencias que vienen de la base de datos Open Data con
información incompleta, en estos casos se puede poner en contacto con el servicio técnico que facilita estos datos
para comunicarles las incidencias de datos y ver si se puede llegar a alguna solución para evitar que estos
problemas se repitan en el futuro.
Y finalmente y a partir de la tabla 19 y de la figura 56 podemos dar respuesta a la pregunta principal del proyecto
como distribuir la flota de grúas de la empresa basándose únicamente sobre la información obtenida del análisis
de nuestro Data Mart de incidencias y teniendo en cuenta que solo disponemos de información de los últimos
48h.

66
Fundamentos y caso práctico de diseño de una solución B.I para explotar datos abiertos 67

Provincia Número de incidencias Porcentaje de flotas a distribuir

Bizkaia 43 37,39%

Gipuzkua 38 33,04%

Alava-araba 34 29.56%

Tabla 19. Distribución de flotas por provincia y en porcentaje.

Figura 56. Distribución de flotas por provincia y en porcentaje.

67