Buena práctica en la realización de un proyecto BI.

Buena práctica en la realización de un proyecto BI.

Ing. Max Alí Jara Paredese-mail: maxjarap@gmail.com

M.Sc. Juan Carlos Herrera Mirandae-mail: juancahm@hotmail.com

RESUMEN: En el presente proyecto de investigación, se pretende establecer una guía práctica en la construcción de un proyecto BI usando un conglomerado de herramientas de uso libre, no obstante; cada herramienta debe configurarse adecuadamente siguiendo un esquema de Extracción Transformación y carga (Extract, Transform and Load - ETL) pasando a construir el Data Warehouse (DW), modelando su Cubo Dimensional, preparando la Metadata Informativa y finalizando la construcción de Análisis y Reportes que requiera preguntársele a dicho DW; esta integración de pasos irá utilizando herramientas de una suite que esta en boga en el entorno BI, llamado PENTAHO (suite Community Edition - CE). Con ello pretendemos que dicho proyecto sirva como recetario para tener una guía básica en la elaboración de proyectos BI.

PALABRAS CLAVE: BI, ETL, DW, Metada, PENTAHO.

1 INTRODUCCIÓN

Los proyectos de Business Intelligence (BI), son un conjunto de estrategias y herramientas enfocadas a la administración y creación de conocimiento mediante el análisis de datos existentes en una organización [1], estas estrategias facilitan la toma de decisiones anticipando acontecimientos futuros y ofreciendo conocimientos que respalden tales decisiones.

Dichos proyectos BI, deben de seguir una secuencia de fases (Fig. 1) por las cuales al analizarlas deberán tener una explicación puntual justificando el proceso metodológico a implementar

Figura 1: Fases del proceso BI Ahora, para comenzar a formular dichas

estrategias es necesario utilizar técnicas que permitan extraer datos de distintas fuentes, para luego homogenizarlas y cargarlas en un Data Warehouse (DW); justamente ahí viene el objetivo primordial de dicha investigación, ya que realizar estos pasos generales, tienden a veces a complicarse, debido a

inciertas razones tales como: ¿por dónde empezar?, ¿qué y cómo obtener?, ¿Cómo homogenizar?, ¿Cómo diseñar el DW?, ¿Cómo preguntarle al DW? y ¿Cómo mostrar lo obtenido?; para que dichas decisiones puedan solventar tales estrategias que ayuden a la organización a mejorar eficiente y productivamente su trayecto visionario.

Con las dudas vistas anteriormente, se pretende corroborar una guía metodológica para esquematizar de forma genérica pero puntual, la construcción de un proyecto BI, no obstante; debemos premeditar que en el mercado informático, existen herramientas que puedan ayudarnos a realizar los diferentes pasos que conglomeran tal proyecto, tanto de forma licenciada como también de forma libre de uso; para este caso, usaremos una suite llamada PENTAHO con licenciamiento de Edición Comunitaria (CE), que contiene complementos (algunos independientes) para construir un proyecto BI; cada complemento o herramienta, necesita la comprensión de técnicas para que, una vez integrado; se obtenga un resultado esperado y óptimo para la toma decisiones de la organización.

Entonces, dicha guía contendrá lo siguiente: Data mediante Tablas Intermedias Diseñando el Data Warehouse Creando el Cubo Dimensional Creando el modelo de negocios

(metadatos) Verificando consultas mediante el

analizador Creando las consultas mediante

WARQ(Web Ad-hoc Reporting Query)

Por último daremos algunas conclusiones y/o sugerencias para tener comprendida dicha guía metodológica.

2 DATA MEDIANTE TABLAS INTERMEDIAS

Uno de los objetivo de este proyecto es obtener el DW de una fuente diversa de datos (Hoja Ms. Excel y una a Base de Datos Ms. Access; ver Fig. 2 y Fig. 3); esta información deberá ser copiada a un conjunto de tablas dentro de una BD MySQL de nombre inter_mat

Ahora, para definir una Base de datos (BD) OLTP; estas son BD orientadas al procesamiento de

1

Buena práctica en la realización de un proyecto BI.

transacciones. Una transacción genera un proceso atómico (que debe ser validado con un commit, o invalidado con un rollback), y que puede involucrar operaciones de inserción, modificación y borrado de datos [2].

Figura 2: BD Matricula_MOD.mdb

Figura 3: BD EXAMENEXCEL_MOD.xls

Estas BDs son la fuente diversa, que deberá ser estudiada, a fin de obtener un mejor panorama de lo que se quiere obtener de ellas; pero para que dicha información extraída no tenga inconvenientes a la hora de procesarlas, se recomienda exhaustivamente que formen parte de un repositorio genérico (inter_mat) del cual se armaran las BD destino, por tanto necesitamos volcar estos datos fuente y con seguridad (sin perjudicar la data original), realizar los cambios respectivos en ellos o gestionar datos no muy claros, faltantes o innecesarios para su análisis respectivo. Se recomienda enfáticamente que el DW sea construido en el mismo gestor del repositorio genérico (MySQL).

El volcado de la BD origen hacia la BD repositorio, puede ser ejecutado usando una de las herramientas más destacables dentro de PENTAHO; esta cumple con la 2da fase dentro de la construcción de un proyecto BI y hará efectivo tal traslado; nos referimos la fase de “Recolección de Información” donde se hacen los procesos ETL que serán diseñados desde Pentaho Data Integration (PDI).

PDI, también conocido como Kettle, ofrece una potente capacidad en la Extracción, Transformación y Carga (ETL) de información, mediante un enfoque innovador, basado en metadatos. Con una interfaz intuitiva y gráfica, arrastrando y soltando elementos de un entorno de diseño y una arquitectura probada, escalable y basada en estándares.[3]

Esta herramienta permite la Extracción usando accesos a datos como JDBC, ODBC o JNDI, los cuales permiten la conectividad a las BDs origen y mediante un

conglomerado de pasos (íconos gráficos que cumplen funciones específicas) relacionados, vierten la información en BDs destinos; los cuales también usan los accesos anteriores mencionados.

Entones, el primer objetivo de la guía metodológica es tener, tablas intermedias de las diferentes BD del cual se extrae la información; el fin de este proceso es, tener la disposición de dicha información para moldearla a los requerimientos esenciales por el cual se desea construir el DW. Para ello PDI permite, una vez conectados a las BD destino; crear las estructuras de las tablas con procedimientos puntuales (generación de código SQL para la construcción de tablas) que comprenda el motor de BD destino, ver Fig. 4; por lo que PDI hace lo posible para que se tenga independencia casi total, en el manejo específico de las BDs origen y destino.

Figura 4: Editor SQL de PDI

Para el ejemplo propuesto en esta guía se asume tener un conglomerado de tablas, vertidos de sus orígenes; en la BD destino inter_mat; esto se pudo conseguir gracias a los procedimientos denominados transformaciones (ver Fig. 5); que vienen hacer los archivos específicos generados por PDI; cabe puntualizar que, usar estos pasos secuenciales aligeran el trabajo tedioso de migrar la información a un modo más flexible para su análisis en la construcción del DW.

Figura 5: Transformación en PDI Al ir creando las diferentes transformaciones para

con los datos es necesario tener presente lo siguiente: Es muy recomendable que las tablas destino no

tengan relaciones con otras tablas (independencia general).

El nombre de las transformaciones debe tener coherencia con lo que se quiere hacer, por tanto indicar: <nombre tabla>-<objetivo>.ktr

Homogenizar la información que pueda encontrarse en varias BD origen, teniendo así presente las tablas comprometidas con datos únicos.

2

Buena práctica en la realización de un proyecto BI.

Por último, PDI en su eficiencia y flexiblilidad;

permite ejecutar varias transformaciones de forma secuencial; creando archivos denominados Trabajos con extensión *.kjb.

El objetivo de un trabajo es automatizar todos los procesos especificados mediante transformaciones, y ejecutarlos desde un único paso (ver Fig. 6), ahorrando trabajo de ejecución detallado; para ello es necesario que cada transformación sea eficiente a pesar de que los trabajo puedan construirse con medidas de seguridad en caso de encontrar errores en su proceso.

Figura 6: Trabajo en PDI

3 DISEÑANDO EL DATA WAREHOUSE

Data Warehouse es un conjunto de datos integrados, históricos, variantes en el tiempo y unidos alrededor de un tema específico, que es usado por la gerencia para la toma de decisiones [4]

Y según Bill Inmon, este conjunto integrado necesita estar estructuradamente sólido para la toma de decisiones; por tanto las indicaciones mencionadas a continuación puntualizarán dicha estructura eficiente en la construcción del DW.

3.1 Diseñando el Modelo Conceptual

Al haber limpiado e integrado la información de las BD Origen; entonces, tenemos la primera vista conceptual de lo que será nuestro DW, para lo cual mediante herramientas para la creación de Modelos, ER (Entidad – Relación): creamos dicho diagrama (ver Figura 7).

Figura 7: Modelo Conceptual

Según la Fig. 7, esta se asemeja a un tipo de modelamiento: Esquema Estrella, el cual es el más genérico de implementar en la construcción del DW, donde se destaca la desnormalización de esta BD, con fines de no complicar las consultas de selección, más adelante; se seguirá implementando dicha BD a fin de tener totalmente estructurado nuestro DW. Acto seguido nos evocaremos en analizar la data construyendo las claves subrogadas

3.2 Creando las tablas para Claves Subrogadas

Una Clave subrogada, es aquella que Permite que la construcción y mantenimiento de índices sea una tarea sencilla. El DW no dependerá de la codificación interna del OLTP. Si se modifica el valor de una clave en el OLTP, el DW lo tomará como un nuevo elemento, permitiendo de esta manera, almacenar diferentes versiones del mismo dato [5].

Por tanto siguiendo el ejemplo anterior se recomienda la creación de las tablas:

Ids_asignatura Ids_alumno Ids_departamento

Para la creación de estas tablas haremos uso de transformaciones, pero se debe sacar el mayor provecho a estos procesos con el objetivo de obtener las dimensiones para el DW. Un claro ejemplo molde es el que se muestra en la fig. 8; aquí se observa la asignación de la clave subrogada con el ID respectivo de la tabla origen (Alumno), y así mismo se intercambia dicha clave para adjuntarla con los demás campos necesarios, que integrará la tabla destino o tabla dimensión (Poblar).

Figura 8: dw_alumno Cargar DW.ktr

En un DW, la creación y el mantenimiento de una tabla de dimensión Tiempo es obligatoria, y la definición de granularidad y estructuración de la misma depende de la dinámica del negocio que se esté analizando [6].

Entonces, para tratar la dimensión tiempo, esta no requiere una clave subrogada; debido a que dicha dimensión debe ser construida entre intervalos que faciliten la relación tanto a las dimensiones dim_departamento, como fact_matricula.

Al hacer el análisis respectivo deducimos, que se debe tomar en cuenta la fecha más antigua y más reciente, entre las tablas departamento y

3

Buena práctica en la realización de un proyecto BI.

alumno_asignatura de la BD inter_mat. Con esto podemos concluir que la clave primaria (supuestamente la clave subrogada propiamente dicha) será la asignada de acuerdo al fórmula: Año+mes+día.

En la definición anterior para poder saber que campos debe integrar la dimensión Tiempo; se indica tener en cuenta, la dependencia dinámica del negocio; por ello, hemos visto la necesidad de ingresar los campos:

Año, Semestre, mes_nro, mes_letra, dia_nro, día_letra.

Los anteriores campos deben contener, como su propia definición indica; la información, según el intervalo que se proponga, y llegar a obtener esto mediante una sentencia SQL, dependerá mucho de la complejidad con la que se desee demostrar; no obstante podemos ayudarnos del uso de transformaciones. Pero a veces los pasos de tales transformaciones quedan limitados; como por ejemplo obtener el mes y día en letras; por ende PDI fue creado mediante código JavaScript, por lo que también este programa nos permite programar en JavaScript (ver Fig. 9); entonces podemos usar sentencias programables para obtener tal fin.

Figura 9. Id_tiempo, mes y dia en letras

Por último la transformación tiempo quedaría como se indica en la Fig. 10.

Figura 10. dw_tiempo cargar DW.ktr

Con respecto a la creación de la tabla subrogada Ids_departamento y la dimensión departamento; es importante recalcar que aquí, también se tiene el manejo del campo tiempo; por lo que este maneja un formato establecido: yyyy-MM-dd hh-mm-ss; y para ser parte de la tabla dimensional, dicho campo debe tomar la forma de un índice relacional con la dimensión Tiempo, por lo que debe ser convertido de yyyy-MM-dd hh-mm-ss a yyyyMMdd (de tipo numérico). Esto puede llegar a solucionarse usando también código JavaScript.

3.3 La tabla de Hechos

Como ya se ha cumplido con formular las posibles dimensiones y sus campos respectivos por los cuales se conformarán dichas tablas, se procede a diseñar del todo, el Modelo de Especificación de la BD destino (ver Fig. 11); para ello nos valemos nuevamente de las transformaciones que se han ido creando.

La tabla de hechos no solo debe consignar las relaciones con sus dimensiones, esta; debe contener medidas de información por lo que al hacer el análisis de esta data, deberá usarse 2 medidas esenciales:

La cantidad de Aprobados (aprobados) La nota Promedio Aprobada (promedio)

Figura 11. Modelo de Especificación: dw_matriculas

Para el llenado de la tabla fact debemos saber con qué tablas interactuar para armar de forma no compleja, la consulta. Hasta aquí la BD intermedia (inter_mat) cumplió con su función, obteniendo así el llenado de información del DW.

Por último integramos todas las transformaciones u trabajos, con la finalidad de realizar dichos procesos en una acción general (un solo paso).

4 CREANDO EL CUBO OLAP

OLAP es el acrónimo en inglés de procesamiento analítico en línea (On-Line Analytical Processing). La principal característica que potencia a OLAP, es que es lo más rápido a la hora de ejecutar sentencias SQL de

4

Buena práctica en la realización de un proyecto BI.

tipo SELECT, en contraposición con OLTP que es la mejor opción para operaciones de tipo INSERT, UPDATE Y DELETE [7].

Un cubo OLAP, es una base de datos multidimensional, en la cual el almacenamiento físico de los datos, se realiza en un vector multidimensional. Los cubos OLAP se pueden considerar como una ampliación de las dos dimensiones de una hoja de cálculo [8].

Mondrian Schema Workbench es una interfaz de diseño que te permite crear y probar el esquema visual de un cubo OLAP. El motor Mondrian procesa las solicitudes MDX con los esquemas ROLAP (OLAP Relacionales). El esquema de estos archivos tiene el modelo de metadatos XML [9].

Con las anteriores definiciones se enfatiza la importancia elemental de construir un cubo OLAP mediante Schema Workbench que ahora forma parte de de Pentaho (PSW); ya que nuestra información está inmersa en una BD relacional, por lo que solo queda construir el modelo de metadatos.

Para la construcción de un cubo OLAP es necesario tener presente con que BDs trabajaremos, para integrar los controladores respectivos, si es que fuera necesario. Recordemos que ya se tiene implementado las dimensiones y tabla de hechos que formarán parte de dicho análisis por lo que deberá irse ingresando a la interfaz (ver Fig. 12).

Figura 12. Cubo OLAP: EsquemaMatricula.xml

Para el diseño de una dimensión se debe tomar en cuenta que, los objetos más importantes que se pueden incluir en un cubo multidimensional, son los siguientes:

Indicadores: sumarizaciones que se efectúan sobre algún hecho o expresiones basadas en sumarizaciones, pertenecientes a una tabla de hechos.

Atributos: campos o criterios de análisis, pertenecientes a tablas de dimensiones.

Jerarquías: representa una relación lógica entre dos o más atributos.

En la siguiente representación matricial se puede ver más claramente lo que se acaba de decir: [10]

Figura 13. Cubo multidimensional

Por tanto para agregar las dimensiones respectivas tomaremos como ejemplo la dimensión Tiempo (Ver Fig.14); esta enfatizará sus jerarquías según Año, Semestre, (mes_nro, mes_letra), (dia_nro, día_letra).

Figura 14. Dimensión Tiempo

Otra característica que podemos destacar, es respecto a la dimensión Departamento que por motivos didácticos utilizará un tipo de modelamiento esquema Copo de nieve [11] como se aprecia en la Fig. 11, entre las dimensiones: dim_tiempo y dim_departamento; este tipo de esquema, respeta la normalización de un BD, pero; comprometer su uso con grandes cantidades datos, corroborará una demora exorbitante en las consultas multidimensionales (ver Fig. 15).

Figura 15. Dimensión Departamento

Uno de los objetos destacados, mencionados anteriormente; en la construcción de una dimensión, es la de los indicadores que vienen hacer sumarizaciones; este término engloba características propias de suma, conteo, promedio, etc… como también funciones matemáticas, estadísticas, operadores matemáticos y lógicos. Como se mencionó en la definición de la tabla

5

Buena práctica en la realización de un proyecto BI.

de hechos se integraran a dicha tabla 2 indicadores (cantidad de aprobados y nota promedio aprobada).

Construido el cubo OLAP, para poder ver su funcionamiento este debe ser publicado, es decir; que el modelo XML debe ser integrado en la Consola de Usuario (otra herramienta destacada de Pentaho para el análisis y creación de reportes), el cual gracias a esta, se podrá navegar por el cubo y generar consultas MDX que, luego de forma genérica; puedan ser exportadas a un tipo de reporte.

5 CREANDO EL MODELO DE NEGOCIOS (METADATOS)

http://churriwifi.wordpress.com/2010/06/

6 VERIFICANDO CONSULTAS MEDIANTE EL ANALIZADOR

7 CREANDO LAS CONSULTAS MEDIANTE WARQ(WEB AD-HOC REPORTING QUERY)

6