1
Manual Pentaho Data Integration Grupo de Investigación Base de Datos Dra. Angélica Urrutia. Universidad Católica del Maule Chile
Integrantes: Héctor Valdés Amaro. Christian Ortiz González. Javier Jirón Orozco
2
Anexo 1: schema-workbench
2
Anexo 1: schema-workbench
3
Para la realización de cubos con la herramienta schema-workbench, se debe revisar que se cuente con los driver de conexión, con la base de datos que se seleccionó en este caso la conexión con SQL SERVER. Para esta tarea se debe ingresar a la carpeta drivers que se encuentra dentro de la carpeta de schema-workbench
Carpeta driver dentro de schema-workbench
Una vez que se ingresó a la carpeta, se debe revisar que exista el driver para realizar la conexión, en el caso del driver para la conexión a la base de datos SQL Server es SQLJDBC4, que como se aprecia en la siguiente imagen el driver esta contenido dentro de la carpeta.
4
Dentro de la carpeta driver cerciorando la existencia del driver de conexión SQL Server
Una vez que el driver de conexión esta dentro de la carpeta de driver, se procede a realizar la conexión a la base de datos fuente, que en el caso de la investigación es SQL Server. Para esto, una vez dentro de schema-workbench se debe presionar options que se encuentra en la parte superior de la ventana. Como se muestra en la siguiente imagen.
Creación de conexión con schema-workbench.
5
Luego de presionado el botón options, aparece en pantalla la ventana de configuración de la conexión a la base de datos fuente. Dentro de la ventana se deben configurar los siguientes parámetros:
Nombre de la conexión: se asigna un nombre a la conexión. Host name: el host que soporta la base de datos, en el caso de la investigación como se trabaja con un servidor local que soporta la base de datos, en el caso de no este, se debe especificar cuál es el host.
Nombre de la base de datos: nombre de la base de datos a la cual se quiere conectar.
Número de puerto: número del puerto por el cual se realizará la conexión.
Nombre del usuario: nombre del usuario por el cual se realizará la conexión.
Password: clave del usuario que se conecta.
En la siguiente imagen se muestra esta ventana.
6
Ventana de configuración de conexión con la base de datos fuente en el caso de estudio SQL SERVER.
Luego de realizada la configuración, se procede a probar si la configuración de la conexión fue bien realizada, para esto se presiona el botón TEST y si la conexión fue bien configurada, aparecerá un cuadro de dialogo que indica que la conexión con la base de datos esta OK, como se muestra en la siguiente imagen.
7
Comprobación de conexión con base de datos fuente.
Con la conexión a la base datos fuente realizada, se procede a la creación del cubo, para esto se debe seleccionar el menú file dentro de schema-workbench. Selecciona schemas para comenzar con la creación. Como se muestra en la siguiente figura:
8
Creación de schema dentro de schema-workbench.
Una vez creado el cubo, se posiciona sobre el schema y se presiona el botón secundario del mouse y se selecciona la opción add cube, con esto adjuntamos un cubo al nuevo Schema. Como se aprecia en la siguiente imagen:
9
Creación de cubo dentro del nuevo Schema.
Una vez que se creó el cubo, se procede a crear la tabla de hecho del cubo, para esto se presiona el botón segundario del mouse sobre el cubo, lo cual desplegará un menú segundario en donde se selecciona add tabla.
10
Creación de tabla de hechos dentro del cubo.
Creada la tabla de hechos, debemos especificar que tabla de la base de datos fuente corresponde a esta tabla de hecho. Para esto primero seleccionaremos el Schema de la base de datos fuente que contienen la tabla de hecho. Para el caso en estudio el Schema es dbo, que contiene el esquema de la tabla de hecho. En la siguiente imagen se aprecia este proceso.
11
Selección de Schema de base de datos fuente de tabla de hechos
Seleccionado el Schema se selecciona la tabla de hechos del cubo. En el caso en estudio la tabla de hecho corresponde a las ventas por internet.
12
Selección de tabla de hechos.
Seleccionada la tabla de hechos, se procede a la creación de la tabla de dimensiones del cubo, esta creación se realiza de una forma particular, ya que no se crea dentro del cubo que es una posibilidad, sino que se crea dentro del Schema, esta creación se realiza de esta manera, ya que si estas dimensiones son requeridas por otro cubo sólo se utilizan y no tienen que ser creadas cada vez que se crea un cubo. La creación de dimensiones dentro del cubo se posiciona sobre el Schema y se presiona el botón secundario del mouse, esto desplegará un menú segundario en donde se selecciona add dimensión, como se muestra en la siguiente figura.
13
Creación de dimensiones dentro del Schema.
Una vez creada la dimensión, se debe especificar cuál es la jerarquía que tendrá esta dimensión, esto se realiza para definir cómo será el nivel de navegación que se tendrá por cada dimensión, para agregar jerarquías se posiciona sobre la jerarquía y se presiona el botón segundario, el que desplegará el menú segundario y se selecciona add hierarchy, como se muestra en la siguiente imagen.
14
Creación de jerarquías.
Se debe definir la tabla que contendrá esta dimensión que se define, para esto se presiona sobre la jerarquía y se desplegará un menú segundario el que contendrá la add table. En el caso de estudio las tablas de dimensiones serán las tabla cliente y tabla date.
15
Incorporación de tabla a la dimensión.
Para la selección de la tabla que se incluye en la dimensión, se debe seleccionar el Schema que contiene a la tabla. En el caso en estudio el Schema que contienen a la tabla cliente es el Schema dbo, como se puede ver en la siguiente imagen.
16
Selección de Schema que contiene tabla de dimensión.
Seleccionado el esquema, se selecciona la tabla que estará en la dimensión, en el caso en estudio esta tabla será la tabla cliente. En la siguiente imagen se grafica esta selección.
17
Selección de la tabla cliente dentro de la dimensión.
Agregada la tabla a la dimensión, procederemos a definir como serán las jerarquías dentro de esta dimensión. En el caso en estudio se definió la jerarquía ocupación y nivel de estudios. Se comienza definiendo la jerarquía nivel de ocupación, para esto primero debemos seleccionar esta tabla cliente, como se muestra en la siguiente imagen.
18
Selección de tabla de cliente.
Definida la tabla dentro de la jerarquía, se define cual es la columna de esta tabla que será parte de la jerarquía, en el caso en estudio la columna ocupación.
19
Selección de atributo ocupación dentro de la jerarquía
Se realizó el mismo proceso para el atributo educación dentro de las jerarquías, si el proceso es realizado correctamente aparecerá dentro de las jerarquías los dos niveles que creamos.
Se creará otra dimensión llamada tiempo, la que tendrá como jerarquía año, semestre y cuarto que fue creada de la misma manera que fue creada la jerarquía cliente. En la siguiente imagen se muestra como el Schema con las dos dimensiones creadas con sus respectivas jerarquías
20
Cubo con las dimensiones cliente y tiempo.
Creadas la dimensiones hay que incluirlas dentro del cubo, para esto se posiciona sobre el cubo y se presiona el botón segundario del mouse y se selecciona add dimensión usage. Como se muestra en la imagen.
21
Incorporación de dimensiones al cubo.
Incorporada la dimensión seleccionamos las dimensiones que están disponibles, en este caso cliente y tiempo, sólo se mostrará el procedimiento para el cliente ya que para la dimensión tiempo es el mismo procedimiento. En la siguiente imagen se muestra la selección de la dimensión cliente que es incorporada al cubo.
22
Incorporación de dimensiones al cubo.
Se llega al punto en donde se tiene creado el cubo con su tabla de hecho definida y sus respectivas dimensiones, pero falta incorporar la métrica. Para esto, nos posicionamos sobre el cubo y presionamos el botón segundario del mouse, en el menú desplegable se selecciona add measure, como se muestra en la siguiente imagen.
23
Incorporación de métrica.
Agregada la métrica se especifica qué tipo de métrica es, en el caso de estudio se selecciona la suma de ventas por internet por lo que la definición del tipo de métrica corresponde a suma, en la siguiente imagen se muestra la definición de la métrica.
24
Definición del tipo de métrica.
Definida la métrica hay que especificar que atributo de la tabla de hecho correspondiente a este atributo, en el caso en estudio la métrica de la tabla de hecho es suma de ventas por internet, en la imagen siguiente se aprecia la asignación del atributo que corresponde a la métrica de la tabla de hechos.
25
Definición de métrica.
Con este último paso queda definido el cubo, pero la herramienta Schema workbench no permite la visualización de éste, por lo que se debe publicar el cubo, este procedimiento se realiza ingresando al menú publicación con los siguientes datos:
Password de publicación: password.
User: joe.
Password: password.
Como se muestra en la siguiente imagen.
26
Publicación del cubo.
27
ETL: Spoon
28
Pentaho Data Integration es una herramienta de Integración de Datos, el cual incluye un conjunto de herramientas para realizar ETL. Uno de sus objetivos es permitir que el proceso sea fácil de generar, mantener y desplegar. Pentaho Data Integration abre, limpia e integra la información y la pone en manos del usuario.
Incluye 4 herramientas principales (Spoon-Chef, Pan, Carte, Kitchen).
Spoon: Herramienta para diseñar transformaciones ETL usando el entorno gráfico. Realiza las funciones típicas de flujo de datos como la lectura, validación, refinación, transformación, escribir datos en una variedad de diferentes fuentes de datos y destinos. Transformaciones diseñadas en Spoon se pueden ejecutar con Kettle Pan y Kitchen.
Chef: Permite mediante una interfaz gráfica, diseñar la carga de datos incluyendo un control de estado de los trabajos. Es una herramienta para crear trabajos que automatizan el proceso de actualización de bases de datos en una forma compleja. En PDI 3.1 Chef no se presenta como un programa separado, sino que es ejecutado por Spoon para tomar todas sus funcionalidades.
La utilización del ETL la centraremos en la utilización con la base de datos que se encuentra en sql sever AdventureWorksDW2008. Para realizar esta conexión debe cerciorarse que el ETL spoon cuenta con el driver de conexión para SQLSERVER, el cual se encuentra en la siguiente ruta: Primero: dentro de la carpeta de spoon se encuentra una capeta llamada Libext. Como se muestra en la siguiente figura:
29
Carpeta Libext en spoon.
Segundo: una vez dentro de la carpeta LibExt se abre la carpeta JDBC la cual contiene todos los driver que tiene spoon para realizar conexiones, si se quisiese realizar una conexión distinta a la de SQL server se debe rectificar que el driver de conexión con la base de datos se encuentre en esta carpeta.
Carpeta JDBC contienen los driver de conexión a las diferentes bases de datos.
Tercero: una vez dentro de la carpeta, se busca en driver de conexión a la base de datos, de no encontrarse debe ser incorporado a esta carpeta con formato .jar. En el caso de la conexión de SQL Server es necesario el driver SQLJDBC4.jar.
Driver JDBC para SQL Server.
30
Ya se comprobó que el ETL cuenta con el driver de conexión a la base de datos, por lo que se procede a acceder a Spoon. Una vez que se ingresa aparece la siguiente pantalla:
Ingreso a ETL Spoon
En donde se debe seleccionar el signo + para agregar una nueva conexión con la base de datos. Al presionarlo se preguntará por el tipo de conexión que se desea realizar, estas pueden ser de dos tipos:
Conexión con una base de datos.
Conexión con archivos.
Seleccionar conexión con base de datos como se muestra en la siguiente figura.
Selección tipo de conexión de spoon.
Una vez seleccionada la conexión con la base de datos, se debe definir cuál es la base de datos para esto, spoon presenta la siguiente interface:
31
Interface que presenta spoon para la conexión con la base de datos.
En donde se debe definir:
Nombre de la conexión: se asigna un nombre a la conexión. Host name: el host que soporta la base de datos, en el caso de la investigación como se trabaja con un servidor local, es host que soporta la base de datos en localhost, pero en el caso de no ser ese el host, se debe especificar cuál es el host.
Nombre de la base de datos: nombre de la base de datos a la cual se quiere conectar.
Número de puerto: número del puerto por el cual se realizará la conexión.
Nombre del usuario: nombre del usuario por el cual se realizará la conexión.
Password: clave del usuario que se conecta.
32
Una vez especificado los datos de la conexión se procede a realizar el test de conexión, presionando el botón TEST, de estar correcta la conexión aparecerá en pantalla la notificación de conexión. Como se muestra en la siguiente imagen:
Interface de prueba de conexión con la base de datos con spoon.
Se sabe que la conexión esta correcta por lo que se procede a presionar el botón OK. Con esta acción aparecerá la información del repositorio, en este paso se debe asignar una id y un nombre para luego presionar el botón create or upgrade, como se muestra en la siguiente figura:
33
Creación de repositorio en ETL spoon. Creado el repositorio a la base de datos se tiene acceso a spoon con la siguiente interface:
Interface de inicio spoon.
Para realizar un nuevo trabajo o transformación se debe ir a la creación de un nuevo archivo, en donde se consultará que tipo de archivo será, como se muestra en la siguiente figura:
34
Creación de trabajo o transformación.
Seleccionada una de las opciones de archivos, aparecerá en el costado derecho de la pantalla el menú de herramientas, con el que cuenta el ETL Spoon de Pentaho. Como se muestra en la siguiente imagen:
35
Interface de trabajo de Spoon. A continuación se procede a especificar las características que tienen las diferentes herramientas que cuenta el ETL Spoon en las diferentes aéreas: Nombre Access Input
Icono
Descripción Este paso se lee directamente desde Microsoft Access "MDB" archivos.
Entrada XML
Este paso le permite leer la información almacenada en archivos XML.
Entrada Tabla:
Este paso se utiliza para leer la información de una base de datos y utilizando una conexión SQL. El paso mapeo de valores consiste en remplazar una cadena de valor fuente en otra cadena de valor destino. Se pueden convertir valores de campos nulos o vacios en otros valores. El paso de des-
Mapeo de Valores:
Desnormalización de fila
normalización de fila permite des- normalizar datos por búsquedas de pares clave-valor. Es necesario que la entrada para la des-Normalización de filas este ordenada por las claves de agrupación Row Flattener
Este componente permite aplanar datos secuencialmente, es decir determina un campo que
36
Agrupar
debe ser aplastado y este lo resume. Este componente se usa para agrupar, teniendo en cuenta ciertas reglas como; suma, promedio, primer valor nulo, etc. Donde el objetivo es organizar los valores o calcular los valores en un grupo definido de campos.
Filtrar filas
Esta opción se utiliza para filtrar filas utilizando fórmulas sencillas, es decir, puedes escoger los campos de la tabla que desees de tu base de datos o de cualquier elemento de entrada de datos, especificando las condiciones que desees que cumplan estos campos y enviarlos a un destino específico.
Append Stream
Este componente se utiliza para unir flujos de datos procedentes de dos entradas en un elemento de salida cualquiera. Hay que tener en cuenta que la entrada de datos procedentes de ambas medidas ha de ser idéntico,
37
es decir, el mismo tipo de dato, la misma longitud de la fila.
Switch / case
La funcionalidad del componente “Switch / Case” se basa en el mismo principio que se utiliza en los lenguajes de programación.
Calculadora:
El componente de calculadora tiene funciones predefinidas que se pueden ejecutar sobre los valores de los campos de entrada.
ADD a CKECKSUM
Este componente tiene como función principal realizar una suma, de control para detectar la alteración accidental de los datos durante la transmisión o el almacenamiento, para ello genera una suma de chequeo en el origen y pasa ese resultado como un nuevo campo en el destino.
Partir Campos
Esta transformación de Pentaho se encarga de tomar una tabla de entrada que tenga algún campo de
38
tipo Cadena, dividir el contenido de cada una de las tuplas de dicho campo, en nuevos campos de una tabla de Salida.
Clone Row
En este paso se crea una copia (clona) la fila y se inserta a continuación de la fila clonada en el próximo paso.
Ejecutar Script SQL
Con este paso se puede ejecutar una sentencia SQL, ya sea durante la fase inicial de la transformación o una vez por cada fila de entrada que el paso este dando.
Validador de datos
Validación de datos se suele utilizar para asegurarse que los datos tingan una cierta calidad.
Insert-Update:
El paso Insert/Update primero hace una búsqueda de la fila en la tabla usando una o más llaves de búsqueda, si no la encuentra inserta la fila, si la encuentra y contiene la misma información no hace nada y si no son iguales la
