JasperETL, Creación de un Data Warehouse

En este segundo post sobre Business Intelligence vamos a ver como usar la herramienta JasperETL, para la creación de un Data Warehouse. Para este tutorial, vamos a usar la base de datos relacional Northwind, y con el uso de JasperETL crearemos el data warehouse de esa base de datos.

Antes de anda abrimos JasperETL. Veremos una ventana que indica que se está iniciando.

Si es la primera vez que se inicia JasperETL aparecerá un acuerdo de licencia al que aceptaremos para seguir adelante. Después nos aparecerá una ventana para registrarse. Tendremos que introducir el e-mail y el país, a continuación pincharemos en "validate".

Sigue leyendo para aprender más sobre JasperETL, creación de un Data Warehouse.



1. Crear un Nuevo Proyecto en JasperETL

Para crear un Data Warehouse en JasperETL debemos crear un nuevo proyecto. Nos aparecerá la siguiente ventana en la que da la opción de comenzar a crear un nuevo proyecto o cargar uno ya existente para continuar trabajando.

En este caso queremos crear un nuevo proyeto, para ello, en "Connection" se mantiene la opción, por defecto, “Local”. A continuación hacemos clic sobre el botón "Create" y se abrirá la siguiente ventana:

En la cual hay que detallar el nombre del proyecto que en el presente caso es northwindDW y también habrá que indicar el lenguaje. Por recomendación de la propia comunidad, es más robusta la elección "perl". Una vez hecho esto clicamos en “Finish”. Volveremos a la pantalla anterior, pero ahora ya con un nombre de proyecto generado.

Hacemos clic en el botón“OK”. Una vez hecho esto se inicia JasperETL para el nuevo proyecto northwindDW. Esto puede tardar un rato, tened paciencia.

Una vez inicializado aparecerá la siguiente ventana:

Cerramos la pestaña "welcome" para poder visualizar el entorno de trabajo de JasperETL que nos ayudará a crear el data warehouse.

2. Trabajar con JasperETL

Antes de empezar a trabajar con JasperETL debemos preparar algunas cosas.

Requisitos previos
En este caso se parte de una base de datos relacional en MySQL que es northwind y se pretende generar un datawarehouse. Por tanto se entiende que es necesario tener cargada en el servidor MySQL la base de datos northwind:

Como hemos dicho queremos crear un DW para northwind, para ello debemos crear manualmente una base de datos que en este caso llamaremos northwindDW. Para ello será necesario teclear el siguiente comando en un terminal MySQL:

create database northwindDW;

Generar un nuevo trabajo
Ahora vamos generar un nuevo trabajo en JasperETL, para ello, volvemos a JasperETL y hacemos clic con el botón derecho sobre “Job Design” y elegir “Create Job”. Aparecerá la siguiente ventana:

Introducimos el nombre del nuevo trabajo, en este caso es northwindDW. A continuación hacemos clic en "Finish".
Aparecerá la siguiente ventana, donde podemos ver que ya ha generado un nuevo trabajo, además podemos ver que a la derecha aparece una paleta de herramientas.

ETL, generación de las dimensiones

A continuación vamos a hacer la carga de datos desde el origen hasta el destino, siendo el origen la base de datos relacional northwind y el destino el Data Warehouse northwindDW.

Para la extracción de datos desde el origen utilizaremos el objeto tMysqlInput y para la introducción de datos en el destino el objeto tMysqlInput. Ambos objetos están en la paleta de herramientas de la derecha en Databases | MySQL.

La relación de ETL-Tablas que se vamos a realizar se ve en la siguiente tabla. Por cada fila de la siguiente tabla debemos generar un objeto tMysqlInput y un objeto tMysqlOutput.

A continuación procedemos a hacer el ETL para la generación de la dimensión DimCustomer en northwindDW.
Escogemos los objetos tMysqlInput y tMysqlOutput:

Podemos cambiar el nombre a los objetos para que sean más intuitivos. Para ello elegir uno de los objetos, y en la pestaña Properties, en View, cambiar los nombre, de tal forma que el cambio sea el siguiente:

• tMysqlInput → customers
• tMysqlOutput → DimCustomer

A continuación comenzaremos a configurar los objetos tMysqlInput y tMysqlOutput para la generación de DimCustomer en northwindDW. Para ello seleccionamos el tMysqlInput de customers y preparamos la configuración. Rellenamos los campos con la configuración correspondiente del servidor MySQL. En este caso la configuración es la siguiente:

• Host → localhost
• Port → '3306'
• username → root

Ahora debemos detallar el script de extracción de los datos desde el destino. Para esto debemos hacer dos cosas:

1. Generar el Script propiamente dicho.
2. Generar el mapeo de datos.

Primero veremos cómo generar el script para la generación de la dimensión DimCustomers y a continuación cómo hacer el mapeo de datos.

Clicamos en el botón “•••” que se encuentra a la derecha de “Query”. Se abrirá la siguiente ventana:

En Databases (a la izquierda) se ve la base de datos northwind. Si desglosamos, sobre la tabla customers hacemos clic con el botón derecho y seleccionar “Generating Select Statement”.

De esta forma se carga a la derecha la tabla customers con todos sus campos seleccionados. En esta caso para la dimensión DimCustomer sólo nos van a interesar los siguientes campos:

• CustomerID
• CompanyName
• City
• Country

Véase la siguiente imagen:

Pinchamos en “OK”. Volvemos al trabajo ETL que se está realizando.

Una vez hecho el script de extracción de datos, debemos hacer el mapeo anteriormente mencionado.

El mapeo de datos es la estructura de los datos que entran en juego en la extracción modificación y carga desde el origen hasta el destino. Donde en este caso el origen va a ser la base de datos relacional northwind y el destino va a ser el data warehouse (DW).

La estructura del mapeo de los datos en este caso va a ser la misma que la del script de extracción de los datos del origen.
Para generar el mapeo, desplegamos "Metadata" que está dentro de la pestaña "Repository" (arriba a la izquierda). Sobre “Db Connections” hacemos clic con el botón derecho y elegimos “Create Connection”.

Rellenamos el nombre de la conexión, en este caso se le ha llamado northwind. Damos a “Next”.

Rellenamos los parámetros para configurar la conexión con la base de datos origen, en este caso northwind. Y a continuación pinchamos en "Finish".

Una vez hecho esto, volvemos al área de trabajo, donde ya vemos que se ha generado una conexión, en este caso llamada “northwind 0.1”. Clicamos con el botón derecho sobre esta nueva conexión y elegir “Retreive Schema”.

Elegimos la o las tablas para hacer el mapeo. En este caso sólo nos interesa la tabla customers. Clic en Next.
Se cargan todas las columnas de la tabla customers, sin embargo en este caso sólo interesan los campos anteriormente citados, por tanto, nos quedamos solamente con esos campos:

Ponemos un nombre significativo al mapeo que se está haciendo, en este caso le ponemos "DimCustomerSchema".

A continuación lo que se vamos a hacer es guardar el mapeo en un fichero xml para poder utilizarlo después desde el objeto "tMyqlInput". Por tanto clicar en el botón Export “all row into xml file”. (Abajo el segundo empezando por la derecha).
Una vez hecho el mapeo para la generación de la dimensión "DimCustomer", volvemos al área de trabajo, y seleccionamos el objeto "tMysqlInput" para customer para cargar el mapeo que se acaba de crear.

Clicamos en el botón “•••” que se encuentra a la derecha de “Edit Schema”. En la ventana que aparece, clicamos en el botón para importar el esquema desde un fichero xml (el primer botón de abajo empezando por la derecha). Clicamos en OK.

Ahora, seleccionamos el objeto I "tMysqlInput" para customer, hacemos clic con el botón derecho sobre él y elegimos “row | main”. Se crea una flecha que debemos arrastrar hasta el objeto "tMysq-lOutput" al que se hemos renombrado anteriormente como "DimCustomer".
A continuación procedemos a configurar el objeto tMysqlOutput DimCustomer. Para ello seleccionamos y en la pestaña "Properties" detallamos la configuración.

Vemos que se ha seleccionado la base de datos destino northwindDW y la tabla destino DimCustomer. Además se ha seleccionado que la acción sobre la tabla destino sea “Drop and Create”. La acción "drop" no tiene efecto por lo que es neceseario borrar la tabla a mano desde la Shell de MySQL, solo si queremos volver a lanzar la acción, teniendo ya una tabla creada en northwindDW.

drop table DimCustomer;

Ahora, hacemos clic sobre el botón “Sync colums” para sincronizar el mapeo en la entrada de los datos. Para verificar que se ha hecho bien, hacemos clic sobre el botón “•••” que se encuentra a la izquierda de “Sync Columns”.

Con esto ya tendríamos terminada la configuración ETL para la generación de la dimensión Dim-Customer del Data Warehouse.
Las dimensiones restantes seguirían el mismo proceso anteriormente explicado, por lo que no se detalla en este manual ya que sino sería demasiado extenso.

Sin embargo sí vamos a explicar la generación de la Fack Table, a la que se le llamaremos FTSales.

ETL. Generación de la Fack Table
Para la generación de la Fack Table, seleccionamos, al igual que antes, los objetos tMysqlInput y tMysqlOutput, a los que en este caso llamaremos northwind y FT_Sales respectivamente.

Ahora procedemos a la configuración del objeto tMysqlInput. La configuración del servidor, puerto, base de datos origen (northwind) y usuario igual que antes.

Generamos el Script para la extracción de los datos. Para ello, hacemos clic al igual que antes en el botón “•••” que se encuentra a la derecha de Query.

En la ventana que se abre, clicamos con el botón derecho sobre la base de datos northwind y elegimos “Generating Select Statement”.

Veremos que se cargan todas las tablas de la base de datos, pero también veremos que las tablas no tienen relaciones. Tendremos que relacionarlas a mano, para ello, hacemos clic con el botón derecho sobre los ID que se vayamos a relacionar, elegimos “Equal” y arrastramos hasta el campo de otra tabla con la que este relacionado. Haciendo esto, la relación de las tablas en este caso quedaría así:

Elegimos los campos que se desean para la Fack Table como se muestra en la imagen, y al tener hechas ya las relaciones de las tablas, se genera automáticamente la sentencia. Pinchamos en OK y listo.

Volvemos al área de trabajo. Ahora tenemos que hacer el traspaso de los datos desde el objeto "tMysqlInput" hasta el objeto "tMysqlOutput", igual que antes. Para ello, botón derecho sobre el objeto "tMysqlInput" y elegimos “row | main” y arrastramos hasta el objeto "tMysqlOutput".
Para terminar de configurar el objeto "tMysqlInput" nos quedaría por hacer el mapeo. Para ello, al igual que antes, en “Metadata | Db Connections | northwind 0.1”, clicamos con el botón derecho y elegimos “Retreive Schema”.

Ahora tenemos que elegir las tablas de las que se van a extraer datos para la Fack Table FT_Sales. En este caso, estas tablas son:

• customers
• employees
• products
• shippers
• suppliers
• Orders

Pinchamos en "Next". Están seleccionamos todos los campos de las tablas seleccionadas, pero en este caso no nos interesan todos los campos, por tanto, se eliminan aquellos campos que no nos interesen.

Al igual que antes, guardamos o exportamos el mapeo a un fichero xml para poder después cargarlo desde el objeto tMysqlInput correspondiente a la Fack Table.

Ya sólo nos quedaría configurar el objeto "tMysqlOutput" correspondiente a la Fack table, que es el mismo procedimiento que hemos explicado para la generación de "DimCustomer".

Una vez que hemos hecho todas las dimensiones y la Fack Table, ya estaria todo listo para crear el Data Warehouse con JasperETL.

Para ejecutar todo lo que se hemos hecho, pinchamos en el botón “Run the Job”, el triángulo verde que se encuentra arriba a la derecha.

Una vez hecho, podemos ver en el servidor MySQL que se ha generado el Data Ware House:

Con esto habriamos terminado la creación del data warehouse con  JasperETL.

Artículos relacionados