ANÁLISIS, DISEÑO E IMPLEMENTACIÓN DE UN DATAMART

UTILIZANDO HERRAMIENTAS OPEN SOURCE PARA LAS

UNIDADES ADMINISTRATIVA Y FINANCIERA DE LA ESPE.

Diego Esparza Montes1, Cristian Alvarez Calvopiña2, Lorena Duque3, Dorys Quiroz4
1 Universidad de las Fuerzas Armadas - ESPE, Ecuador, diegoira87@[Link]
2 Universidad de las Fuerzas Armadas - ESPE, Ecuador, dariocris1818@[Link]
3 Universidad de las Fuerzas Armadas - ESPE, Ecuador, lgduque@[Link]
4 Universidad de las Fuerzas Armadas - ESPE, Ecuador, dsquiroz@[Link]

RESUMEN
La Universidad de las Fuerzas Armadas ESPE almacena información de la Dirección Financiera y Talento
Humano en diferentes bases de datos y en archivos planos, lo que hace difícil su procesamiento para generar
conocimiento de acuerdo a las necesidades del usuario y presenta inconvenientes al realizar reportes de forma
manual que emplean mucho tiempo en obtener información para análisis gerencial. El proyecto propuesto fue
orientado al análisis, diseño e implementación de un conjunto de data marts (Data Warehouse) que servirá para
simplificar procesos, mejorar el manejo y la consolidación de la información gestionada por la Dirección
Financiera y Talento Humano. El proyecto se realizó empleando la metodología de Ralph Kimball y como
herramienta de Business Intelligence open source se empleó Pentaho Community Edition. Los resultados
mostraron que mediante la consolidación y depuración de información, se pudo reducir el tiempo de respuesta
en la generación de informes, logrando un análisis eficiente y simplificado de la información, disminuyendo el
tráfico generado en las consultas hacia las bases de datos, liberando recursos dentro del servidor. En
conclusión este proyecto permite la simplificación de procesos y un análisis dinámico e intuitivo para la toma de
decisiones.

Palabras Clave: Data Marts, Data Warehouse, Open Source, Pentaho Community Edition.

ABSTRACT
The University of the Armed Forces ESPE stores information of Financial and Human Resource in different
databases and flat files, making it difficult to generate knowledge processing according to user needs and
drawbacks to make such reports manual that spend much time in management information for analysis. The
proposed project was oriented analysis, design and implementation of a set of data marts (Data Warehouse)
that will simplify processes, improve the management and consolidation of the information managed by the
Finance and Human Resource. The project was made using the Ralph Kimball methodology and a tool for open
source Business Intelligence Pentaho Community Edition was used. The results showed that by consolidating
and debugging information could reduce the response time in reporting, achieving an efficient and simplified
data analysis, reducing the traffic generated queries to databases, freeing resources inside the server. In
conclusion, this project allows the simplification of processes and a dynamic and intuitive analysis for decision
making.

Key Words: Data Marts, Data Warehouse, Open Source, Pentaho Community Edition.
1. INTRODUCCION

La Dirección Financiera y Talento Humano de la Universidad de las Fuerzas Armadas - ESPE, busca un
cambio de la cultura organizacional hacia la administración del talento humano, preparando y potenciando las
competencias de los Servidores Públicos, Docentes, Militares y Trabajadores, acordes con el desarrollo
tecnológico y los requerimientos de la Institución, para lo cual se requiere la consolidación y procesamiento
adecuado de la información. Este cambio va de la mano con la aplicación de acciones de mejora continua en
los procesos y el trabajo conjunto y coordinado con todas las dependencias, en el fomento de una cultura de
calidad dentro de la Institución.

La Dirección Financiera y Talento Humano de la ESPE, maneja la información de manera aislada, utilizando
archivos planos y la base de datos Oracle, como un repositorio muy completo de los datos. Esta información no
se encuentra procesada de acuerdo a las necesidades institucionales o de los órganos de control universitario
como el CES, SENESCYT, CEACES; de tal manera que los reportes, informes estadísticos o tabulares
requieren ser realizados en forma manual.

El proyecto propuesto se enfoca en procesar y reorganizar la información de la base de datos de la

Dirección Financiera y Talento Humano, logrando con esto la simplificación de procesos tales como la
elaboración de reportes, consultas determinadas y en general la toma de decisiones.

Existen muchas relaciones entre los datos que no podían ser utilizados para efectos de planificación,
organización, logística, etc., debido a que los sistemas que se utilizan (Olympo, Shifre) manejan la información
de forma desestructurada. Siendo por tanto indispensable el procesamiento adecuado de estos datos para
cubrir estas necesidades.

El articulo está estructurado de la siguiente manera, en la sección 2 se encuentra la metodología empleada,

en la sección 3 se encuentra el diseño y la implementación, en la sección 4 se encuentra los resultados, en la
sección 5 se encuentra los trabajos relacionados, en la sección 6 se encuentra las conclusiones y trabajo a
futuro, en la sección 7 se encuentra los agradecimientos, finalmente en la sección 8 se encuentran las
referencias, bibliografía y fuentes de información.

2. METODOLOGIA

Según (Bernabeu Ricardo Darío, 2010), actualmente existen 3 tipos de metodologías principales para el
desarrollo de una solución de business intelligence, entre las cuales tenemos las siguientes:

• Ralph Kimball: Esta metodología se caracteriza por la arquitectura bottom-up esto significa que parte de
un conjunto de datamarts y posteriormente se integra en un datawarehouse centralizado.

• Bill Inmon: Esta metodología se caracteriza por la arquitectura top-down esto significa que parte de un
datawarehouse y posteriormente los segmenta en distintos datamarts.

• Hefesto: Esta metodología se caracteriza por tener una arquitectura hibrida la cual combina la bottom-up
y top-down, adaptándose así a cualquier necesidad empresarial.

Tomando en cuenta las diferentes características de cada metodología y el análisis de las necesidades de la
Dirección Financiera y Talento Humano, se optó por la metodología de Ralph Kimball ya que se puede
encontrar suficiente documentación y nos permite implementar datamarts para cada departamento sin la
necesidad de contar previamente con un datawarehouse centralizado, por lo que conduce a una solución
completa en una cantidad de tiempo relativamente corto. La metodología de hefesto se adapta a las
necesidades del proyecto pero se descarta por encontrase en etapa de desarrollo.

2.1. METODOLOGIA RALPH KIMBALL

Según (Kimball, 1998), la metodología de Ralph Kimball se referencia como Bottom-up, pues al final el Data
warehouse Corporativo no es más que la unión de los diferentes data marts, que están estructurados de una
forma común a través de la estructura de bus como se puede observar en la Figura 1.
 Figura 1: Arquitectura bottom-up. Fuente (Bernabeu, 2010) 1

Esta característica le hace flexible y sencilla de implementar, pues se puede construir un Data Mart como
primer elemento del sistema de análisis, y luego ir añadiendo otros que comparten las dimensiones ya definidas
o incluyen otras nuevas.

La Metodología de Kimball realiza los pasos descritos en la Figura 2 para la construcción de los data marts.

Figura 2: Etapas de metodología de Ralph Kimball. Fuente (Kimball, 1998) 2

Etapas de la Metodología de Kimball.

• Planificación, Planificación del Proyecto.
• Requerimientos, Definición de requerimientos.
• Análisis, Arquitectura técnica.
• Diseño, Selección de herramientas, Diseño físico.
• Construcción, Diseño de la puesta en escena (Staging) y desarrollo.
• Despliegue, Implantación y Mantenimiento.

1
En la Figura 1 se muestra cómo funciona la arquitectura del bottom-up
2
En la Figura 2 se muestra el ciclo de vida que sigue la metodología de Ralph Kimball
2.2. Herramientas

Existen varias herramientas que se utilizan para soluciones de Business Intelligence como Oracle
Business Intelligence, ibm-cognos, Microsoft Analysis Services (SSAS), Pentaho, Spagobi, etc. En
este caso se utilizó pentaho por las siguientes razones:

- Licencia sin costo.

- Fácil instalación y configuración
- Multiplataforma (Linux, Windows, MAC)
- Herramienta que contiene los suficientes componentes para una implementación de Business
Intelligence.

Las razones antes descritas hacen que se descarte las herramientas con licencia pagada por su
alto costo mientras que las herramientas gratuitas en comparación con Pentaho, no ofrecen los
componentes necesarios para una correcta implementación de una solución de Business Intelligence.

2.2.1. Motor de Base de Datos

• Oracle

Según (Larry Ellison, 1979), Oracle soporta todas las funciones que se esperan de un servidor "serio": un
lenguaje de diseño de base de datos muy completo (PL/SQL) que permite implementar diseños "activos", con
triggers 3 y procedimientos almacenados, con una integridad referencial declarativa bastante potente.

Oracle es el motor de base de datos relacional más usado a nivel mundial y se lo destaca por su:
- Soporte de transacciones.
- Estabilidad.
- Escalabilidad.
- Soporte multiplataforma.

2.2.2. Herramientas BI 4

• Pentaho Community

Según (Bernabeu Ricardo Dario, 2010), es una herramienta open source, de fuente abierta que incluye el
análisis de los ETL 5, metadatos y capacidades de presentación de informes.
Pentaho ofrece varios productos como:

- PENTAHO DATA INTEGRATION (4.4.0): Es la herramienta que diseña y elabora el proceso de

extracción, transformación y carga (ETL). Además se realiza los Jobs (modo batch) 6, para
ejecutarlos automáticamente en intervalos regulares.

3
Es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar
una operación.
4
Business Intelligence
5
Proceso de extracción, transformación y carga
6
Ejecución de un programa sin el control o supervisión directa del usuario
 - PENTAHO REPORT DESIGNER (3.9.0): Es la herramienta que sirve para el diseño,
elaboración y publicación de reportes que serán visualizados dentro del servidor BI, para su
posterior ejecución y análisis.

- PENTAHO SCHEMA WORKBENCH (3.2.1): Es la herramienta que sirve para la construcción y

publicación esquemas y cubos en el servidor BI, para ser utilizados en las vistas de análisis por
los usuarios de la plataforma.

- PENTAHO COMMUNITY DASHBOARD EDITOR (13.09.1): Es la herramienta que sirve para el

diseño y la construcción de tableros de control que serán elaborados dentro del Pentaho
Biserver.

- PENTAHO BISERVER (4.8): Es el servidor OLAP 7 que permite la administración de roles y

usuarios, conexiones JNDI 8, elaboración de desbordas, visualizar reportes y cubos de
información.

3. DISEÑO E IMPLEMENTACION

3.1. Análisis De Requerimientos

Los distintos OLTP 9 necesarios para el desarrollo del proyecto son variados, entre estos destacan el servidor
de base de datos Oracle y archivos planos como csv y xls.

El objetivo de implementar una solución BI para la Dirección Financiera y Talento Humano de la ESPE,
consiste en poder analizar la información de manera consolidada y precisa, sin depender de los aplicativos con
los que trabajan estas unidades como por ejemplo: Olympo, Esigef o Shifre. Los usuarios podrán acceder a la
información a través de la consola de usuario de la herramienta BI.

Arquitectura Técnica

3.1.1. Entorno Back Room

Los datos para la población del datamart, fueron extraídos en parte del servidor de la base de datos Oracle y
también de archivos planos. El proceso ETL se lo realizó mediante la herramienta Pentaho Data Integration
(PDI), de la suite de Pentaho y el datamart fue alojado en un servidor de base de datos Oracle 10g como se
puede observar en la Figura 3.

Figura 3: Proceso ETL. Fuente (Esparza & Álvarez, 2012) 10

7
procesamiento analítico en línea
8
Permite a los clientes descubrir y buscar objetos y datos a través de un nombre
9
Procesamiento de Transacciones En Línea
10
En la figura 3 se muestra el esquema empleado para la carga de datos a la base de datos
temporal y al Datamart
3.1.2. Entorno Front Room

El Data Mart actualmente contiene la información necesaria para satisfacer los requerimientos solicitados por
el usuario y estos podrán ser visualizados mediante cubos, reportes y dashboards alojados en la consola de
usuario de Pentaho. La Figura 4 muestra la consola de usuario pentaho, un cubo de información y los gráficos
que la herramienta permite generar.

Figura 4: Usuario Final. Fuente (Esparza & Álvarez, 2012) 11

3.2. Modelado Dimensional

El modelo lógico para La Dirección Financiera y Talento Humano de la ESPE, fue realizado siguiendo el
esquema estrella que optimiza el tiempo de respuesta en consultas complejas, siguiendo los pasos del modelo
dimensional de Ralph Kimball. En este caso fueron creados modelos para cada uno de los módulos que
intervienen en La Dirección Financiera y Talento Humano, entre estos constan los siguientes:

• Presupuestos
• Activos fijos
• Roles de pago
• Matriculación
• Inventario
• Cuentas por cobrar

Se creó dimensiones que contienen las características de las entidades del negocio y tablas de hechos con
medidas o cantidades numéricas para el análisis del negocio.

3.3. Diseño de la puesta en escena (Staging) y desarrollo.

Siguiendo la metodología de Ralph Kimball y tomando como ejemplo el módulo de Presupuestos de la

Dirección Financiera se tiene que la información empleada para realizar el módulo de presupuestos es obtenida
de archivos planos en Microsoft Excel, la estructura de estos archivos se muestra en la figura 5.

11
En la figura 4 se muestra las diferentes herramientas que ofrece Pentaho
 Figura 5: Esquema de Archivos Planos – Presupuestos. Fuente (Esparza & Álvarez, 2013) 12

Una vez procesada la información de los archivos planos, se realizó el modelado de un diagrama entidad
relación, como lo muestra la Figura 6 y fue alojado en la base de datos temporal, este diagrama contiene la
información necesaria y depurada que se requiere para realizar un cubo de información que actualmente
satisface los requerimientos solicitados por el usuario.

Figura 6: Diagrama Entidad Relación – Presupuestos. Fuente (Esparza & Álvarez, 2013) 13

La figura 9 muestra la relacion que tienen los archivos planos con las nuevas tablas de la base de datos
temporal, acompañada de la tabla 1 que contiene un cuadro con la descripcion general de la informacion que
almacena cada tabla.

12
En la figura 5 se muestra los archivos planos extraídos del sistema gubernamental esigef
13
En la figura 6 se muestra el diagrama entidad relación del módulo de presupuestos
 Figura 7: Esquema de la fuente a la base de datos temporal. Fuente (Esparza & Álvarez, 2013) 14

Tabla 1: Mapeo de tablas de la fuente a la base de datos temporal. Fuente (Esparza & Álvarez, 2013) 15
FUENTE DE DATOS ORIGINAL BD.
DESCRIPCION
(archivos .xls) TEMPORAL
Tabla que contiene todos los valores de los
[Link] szptpresup
distintos programas
Tabla que contiene los códigos y los nombres
EJECUCION_PRESUPUESTARIA.xls de los programas, actividades, ítems, szptejepre
subprogramas, geográfico, fuente, proyecto.

Una vez creada las tablas en la base de datos temporal y pobladas con la informacion depurada, se realizó
el modelamiento multidimensional. En este caso es un diagrama estrella y se realizó un análisis de los
requerimientos, tomando en cuenta el modelo entidad realción que se muestra en la figura 6. En la figura 8 se
detalla las dimesiones y tabla de hechos que intervienen en el módulo de presupuestos.

Figura 8: Diagrama multidimensional – Presupuestos. Fuente (Esparza & Álvarez, 2013) 16

14
En la figura 7 se muestra como se carga la base de datos temporal con los datos de los
archivos planos
15
En la tabla 1 se muestra la relación que existe entre los campos de origen y los cargados a la
base de datos temporal
 En la figura 9 se muestra un esquema que detalla las relaciones que tienen las tablas de la base temporal
con las dimensiones y tabla de hechos del datamart, acompañado de la tabla 2 que contiene un cuadro con las
descripciones de cada dimensión y tabla de hechos del modelo multidimensional de presupuestos.

Figura 9: Esquema de la base de datos temporal al datamart. Fuente (Esparza & Álvarez, 2013) 17

Tabla 2: Mapeo general de tablas de presupuestos. Fuente (Esparza & Álvarez, 2013) 18
FUENTE DE BD. DIMENSION
DESCRIPCION HECHOS (FAC)
DATOS ORIGINAL TEMPORAL (DIM)
EJECUCION_PRES Tabla que contiene los códigos szptejepre
DIM_PROYECTO
[Link] y los nombres de los proyectos.
Tabla que contiene los códigos szptejepre
EJECUCION_PRES
y los nombres de los DIM_PROGRAMA
[Link]
programas.
Tabla que contiene los códigos szptejepre
EJECUCION_PRES
y los nombres de las DIM_ACTIVIDAD
[Link]
actividades.
Tabla que contiene los códigos szptejepre
EJECUCION_PRES DIM_SUBPROGR
y los nombres de los
[Link] AMA
subprogramas.
EJECUCION_PRES Tabla que contiene los códigos szptejepre
DIM_ITEM
[Link] y los nombres de los items.
EJECUCION_PRES Tabla que contiene los códigos szptejepre DIM_GEOGRAFIC
[Link] de la ubicación geográfica. O
EJECUCION_PRES Tabla que contiene los códigos szptejepre
DIM_FUENTE
[Link] de las fuentes.
Tabla que contiene las medidas szptpresup FAC_PRESUPUE
[Link]
para presupuestos. STO

16
En la figura 8 se muestra el diagrama dimensional tipo estrella
17
En la figura 9 se muestra que tablas de la base temporal se usan para la carga de
dimensiones y tablas de hecho
18
En la tabla 2 se muestra los archivos planos que fueron usados para cargar la base temporal
4. RESULTADOS

Los resultados satisfacen los objetivos que se plantearon en el proyecto, cumpliendo las necesidades
solicitadas por el usuario final en el análisis de la toma de requerimientos.

Se realizó cubos de información estandarizados para la Dirección Financiera y Talento Humano, esto redujo
ampliamente la intervención manual en la generación de reportes, lo cual consumía tiempo considerable para la
obtención de información que necesitaba el usuario.

De acuerdo a los resultados obtenidos con el proyecto finalizado, la tabla 3 presenta el tiempo en segundos
que le tomaba al usuario la obtención de información de cada módulo de La Dirección Financiera y Talento
Humano, también se presenta el porcentaje en el que ha mejorado el tiempo de respuesta gracias a la
implementación de este proyecto.

Tabla 3: Valores de la extracción de información en minutos. Fuente (Esparza & Álvarez, 2013) 19

ANTES DESPUES PORCENTAJE

CUENTASXCOBRAR 480 2,4 100%

PRESUPUESTO 240 3 99%
ACTIVOS FIJOS 120 4,8 96%
INVENTARIO 120 1,8 99%
MATRICULAS 180 9 95%
ROLES SHIFRE 60 15 75%
ROLES BANNER 60 12 80%

En la figura 10 podemos apreciar el porcentaje en el que se ha mejorado el tiempo de respuesta al momento

de obtener información útil para el usuario, se presenta la información para cada uno de los módulos de La
Dirección Financiera y Talento Humano.

PORCENTAJE

100%
80%
60%
40%
20%
0%

Figura 10: Porcentaje de mejora por módulo. Fuente (Esparza & Álvarez, 2013) 20

19
En la tabla 3 se muestra el tiempo anterior y actual para la obtención de información, en
cada uno de los módulos.
20
En la figura 10 se puede apreciar el porcentaje en el que mejoró cada uno de los módulos, a
la hora de obtener información.
 Con el cubo implementado que se muestra en la figura 11 se evidencia que se puede obtener información
con gran facilidad, en corto tiempo e información que no existía. Estos cubos son un gran aporte al mostrar
información con gráficos de acuerdo a los requerimientos del usuario.

21
Figura 11: Cubo de información. Fuente (Esparza & Álvarez, 2013)

En este proyecto se realizó un dashboard como requerimiento para el módulo de matrículas y se ilustra en la
figura 12 mediante gráficos de barras. Este dashboard muestra al usuario información por secciones y esto sirve
para la toma de decisiones acerca de deudas y pagos por periodo.

Figura 12: Dashboard del módulo de matrículas. Fuente (Esparza & Álvarez, 2013) 22

21
En la figura 11 se puede apreciar una vista de análisis, generada a partir del cubo de
información de presupuestos y un gráfico que permite un mejor análisis de la información.
22
En la figura 12 se puede apreciar un dashboard, el mismo que consta de 4 paneles, cada uno
indica información clave para la toma de decisiones.
5. TRABAJOS RELACIONADOS

Según la investigación realizada existe el proyecto de tesis (Mencias, 2010), donde se muestra el desarrollo
de un aplicativo de Business Intelligence para el área financiera administrativa de la empresa eléctrica de Quito
S.A. Según los autores una solución BI mejora la visualización de la realidad de la empresa y un mayor
empoderamiento de la información por parte de los directivos.

Lo importante de este trabajo es que se realizó con éxito con la misma metodología y herramienta que se
aplicó para el proyecto de tesis de grado “análisis, diseño e implementación de un datamart utilizando
herramientas open source para las unidades administrativa y financiera de la ESPE” (Esparza & Álvarez).

En ambos proyectos se identificó que el problema es que la información se encuentra desestructurada y sin
procesar, lo cual afecta en la toma de decisiones.

6. CONCLUSIONES Y TRABAJO FUTURO

Gracias a la implementación del proyecto de Business Intelligence se puede contar con una herramienta
eficiente al momento realizar el análisis de la información optimizando así tanto el tiempo de respuesta en la
presentación de datos como el tiempo en la elaboración de reportes, vistas, etc.

El proyecto fue desarrollado en su totalidad tomando en cuenta los requerimientos iniciales del usuario, sin
que esto signifique que no exista la posibilidad de expandir el proyecto a los departamentos y unidades
restantes que existen dentro de la Universidad de las Fuerzas Armadas – ESPE ya que actualmente se trabaja
únicamente con la parte Administrativa y Financiera.

7. AGRADECIMIENTOS

Agradecemos a la Universidad de las Fuerzas Armadas – ESPE, por habernos brindado la oportunidad de
implementar el proyecto dentro de la institución. El agradecimiento especial a la Ingeniera Lorena Duque
Directora de Tesis, Ingeniera Dorys Quiroz Codirectora de Tesis, ya que sin su guía y apoyo no hubiera sido
posible culminar con éxito el proyecto. A nuestros padres por su apoyo y comprensión a lo largo de todo este
tiempo. A los integrantes del departamento administrativo y financiero quienes colaboraron en todo momento
con nuestras dudas y necesidades que se presentaron a lo largo de todo el desarrollo de la tesis.

8. REFERENCIAS BIBLIOGRAFÍA Y FUENTES DE INFORMACIÓN

[1] Bernabeu, Ricardo Darío. (2010). HEFESTO. Córdoba

[2] Kimball, Ralph. (1998).The Data Warehouse Lifecycle Toolkit. Ed. John Wiley

[3] Pentaho Corporation. (2008). Introducing the Pentaho BI Suite Community

Edition. Orlando, FL

[4] Alvarez, Cristian y Esparza, Diego. (2012). Análisis, Diseño E Implementación De Un Datamart
Utilizando Herramientas Open Source Para Las Unidades Administrativa Y Financiera De La
ESPE. Quito

[5] Mencias, James y Mencias, Amy. (2010). Desarrollo de un aplicativo Business Intelligence para el
área financiera administrativa de la Empresa Eléctrica “Quito” S.A. Quito