96184254 ingmmurillo business intelligence y data warehousing con pentaho
TRANSCRIPT
Instructor:
Ing. Mauricio Murillo Rosero
CEC-EPN2012
Objetivos
Cronograma
Conceptos
Instrucciones SQL
Ejercicios
2
� Comprender los conceptos generales de Data Warehousing (DWH) e Inteligencia de Negocios (BI)
� Comprender y aplicar la metodología de Kimball para construir sistemas de DWH/BI
� Conocer la plataforma de BI Pentaho
� Construir procesos ETL (extracción, transformación y carga) usando la herramienta PDI (Pentaho Data Integration)
3
� Realizar actividades de Inteligencia de Negocios usando las herramientas de la plataforma Pentaho
� Construir reportes usando PRD (Pentaho Report Designer)
� Construir cubos de análisis usando PSW(Pentaho Schema Workbench)
4
Módulo Contenido Tiempo Aprox. (h)
Módulo I. Conceptos Generales
• Inteligencia de Negocios (BI)• Arquitecturas de BI• Plataformas de BI• Data Warehousing• Arquitectura Flujo de Datos
2
Módulo II. Caso de Estudio
• Análisis y comprensión de la naturaleza de los datos• Definición de Requerimientos
2
5
Módulo Contenido Tiempo Aprox. (h)
Módulo III. Técnicas de Modelamiento de Datos
• Dimensiones• Tablas de Hechos• Modelo Dimensional
2
Módulo IV. Diseño del Data Warehouse para el Caso de Estudio
• Modelo Lógico de Datos• Modelo Físico de Datos
2
Módulo V. Plataforma de BI Pentaho CE
• Arquitectura • Instalación y Configuración• Herramientas
4
6
Módulo Contenido Tiempo Aprox. (h)
Módulo VI. Construcción del Data Warehouse usando PDI
• Actividades ETL• Uso de Spoon• Creación de Transformaciones• Creación de Trabajos• Calendarización de Trabajos de Carga
10
Módulo VII. Construcción de reportes usando PRD(Pentaho ReportDesigner)
• Estructura de reportes• Uso de parámetros• Diseño y formato• Agrupación y agregación de datos
5
7
8
Módulo Contenido Tiempo Aprox. (h)
Módulo VIII. Construcción de Cubos de Análisis OLAP usando PSW(Pentaho SchemaWorkbench)
• Arquitectura Pentaho Analysis Services• Creación de esquemas Mondrian• Visualización de cubos Mondrian con JPivot
5
� Inteligencia de Negocios = BI
� BI corresponde al conjunto de actividades de análisis de datos históricos
� Permite comprender la situación pasada, presente y futura de una compañía
� Apoya a la toma de decisiones estratégicas, tácticas y operativas de una compañía
� BI no es Data Warehousing
9
� De acuerdo al nivel y a la complejidad de análisis, las actividades de inteligencia de negocios pueden ser agrupadas en tres categorías:
1. Reporteo
2. Procesamiento analítico en línea (OLAP)
3. Minería de datos
10
Pasado Presente Futuro
Análisis Descriptivo
• Reporteo (Reportes, Tableros)• Análisis OLAP (Cubos)
Análisis Predictivo
• Minería de Datos
tiempo (t)
Objetivo
• Resolver rompecabezasdel negocio
Objetivo
• Resolver misterios
11
� La principal fuente de datos para las actividades de análisis de BI es la bodega de datos (DWH, Data Warehouse)
� El conjunto de procesos que se encargan de crear, recuperar y consolidar los datos de los sistemas fuente al DWH se conoce como Data Warehousing
12
� Un DWH es un sistema que recupera y consolida datos periódicamente de los sistemas fuente a un almacén de datos dimensional o normalizado. Usualmente guarda años de historia y es consultado para inteligencia de negocios u otras actividades analíticas
� Un Data Mart (DM) es un DWH para un tema o aplicación específica (Ventas, Reclamos, etc.)
13
Recupera Datos
ConsolidaDatos
DWH
Inteligencia de Negocios
y Actividades Analíticas
Datos de Entrada Información de Salida
Sistemas Fuente Bodega de Datos
MATERIA PRIMA PROCESO PRODUCTO
14
� Toda la información está un solo lugar (Una sóla fuente de la verdad)
� Información actualizada� Acceso rápido� No hay límites de espacio (Ej. Archivos XLS)
� Contiene toda la historia de la compañía� Fácil de comprender (Modelada en términos del negocio)
� Contiene definiciones claras y uniformes
� Datos estandarizados
15
� Problemas de calidad de datos, entre ellos:◦ Datos Duplicados
◦ Datos Incompletos
◦ Datos Incorrectos
◦ Datos Conflictivos
◦ Datos Faltantes (Falla de Integridad Referencial)
◦ Valores nulos
� Volumen de datos (1Pb) y rendimiento (Sol. índices, índices binarios, particiones, agregaciones, vistas materializadas)
16
� Captura de Cambios de Datos (CDC)◦ Consiste en identificar qué datos han sido
insertados, modificados o borrados
◦ Puede ser Intrusiva (Triggers) y No Intrusiva (PDI, Merge Rows)
� Requerimientos cambiantes (Un DWH Evoluciona)
17
Sistema deSoftware
Arquitectura Monolítica,Cliente/Servidor,3 Capas, N Capas
Plataforma Java, MS .NET
SistemaDWH/BI
Arquitectura Flujo de Datos
Plataforma SAP BusinessObjects,Oracle Hyperion,IBM Cognos,Microsoft,Pentaho
18
19
Sistemas Fuente
ETL +
Calidad de Datos
DWHETL
Data Mart N
Aplicación BI
Control y Auditoria
METADATOS
Área de Almacenamiento
Data Mart 1
Aplicación BI
ETL
Fábrica de InformaciónCorporativa (CIF)
EnfoqueTOP - DOWN
Data MartsDependientes
20
Sistemas FuenteETL Área de
Almacenamiento(STAGE)
ETL +
Calidad de Datos
Data Mart 1
Aplicación BI
Aplicación BI
Data Mart N
Data Warehouse
Bus
Control y Auditoria
METADATOS
Arquitectura Multidimensional (MD)
EnfoqueBOTTOM - UP
Data MartsIndependientes
21
� Sirve para copiar únicamente los datos que se requiere tal y como se encuentran en la fuente
� No afecta el rendimiento de la base de datos fuente
� Permite trabajar en un subconjunto de datos
� Permite ordenar e indexar los datos para optimizar los procesos ETL
22
Metadatos: datos sobre los datos
� Linaje de datos (Data Lineage): información sobre el origen y destino de los datos
� Tiempo de los datos (Data Timeliness): información sobre cuándo cambió los datos y que “viejos” son
� Modelo de Datos
23
Característica Fábrica de Información Corporativa - Inmon
Arquitectura Multidimensional - Kimball
Técnica de modelamiento de datos
Relacional y Dimensional Dimensional
Enfoque TOP - DOWN BOTTOM – UP
Vista empresarial de datos Total Parcial
Provee datos detallados SÍ SÍ
Provee datos sumarizados SÍ SÍ
Tiempo de implantación Alto Bajo
Costo de implantación Alto Bajo
24
Planeación del Proyecto
Definición de Requerimientos del
Negocio
Diseño Técnico de la
Arquitectura
Modelamiento Dimensional
Especificación de Aplicaciones de
BI
Selección e Instalación del
Producto
Diseño FísicoDiseño y
Desarrollo ETL
Desarrollo de Aplicaciones de
BI
Implantación
Crecimiento
Mantenimiento
Gestión del Proyecto
25
� Conocer las fuentes de datos, alcance, preguntas del negocio a responder, formatos de entrega de información, etc.
� Por lo menos en ésta fase documentar lo siguiente:
Entrada Descripción
Tema Área o proceso de negocio
Audiencia Para quién es la solución
Propietario Quién será el propietario de la solución
Necesidad del Usuario Qué necesitan los usuarios y cómo usarán la solución
26
Entrada Descripción
Preguntas a responder Preguntas que serán respondidas por la solución
Beneficios del negocio Qué ganará el negocio construyendo el DWH
Mecanismo de entrega Emails, Gráficos, Reportes, Tableros de Mando, Cubos de Datos, etc.
Fuentes de información A qué personas se puede preguntar sobre el negocio
Fuentes de datos De qué sistemas y qué bases de datos se obtendrán los datos
Estimación Estimación de tiempo para desarrollar la solución
27
Modelo Estrella
� El centro de una estrella consiste de una tabla conocida como tabla de HECHOS y los puntos de la estrella se los conoce como tablas de DIMENSIONES
28
Modelo Estrella
29
Dimensiones
� Una dimensión representa información de una entidad del negocio (Ej. Cliente, Producto, Vendedor, Local)
� Cualitativas
Hechos
� Un hecho representa un evento del negocio (Ej. Valor Vendido, Comisión, Valor Flete)
� Cuantitativos� Las filas son almacenadas al nivel más bajo de granularidad
30
� Para poder consultar un modelo estrella se usa el siguiente SQL
SELECT COL1, COL2,…, COLN FROM HECHOS H JOIN DIM_1 D1 ON H.FK_DIM1 = D1.PK_DIM1 JOIN DIM_2 D2 ON H.FK_DIM2 = D2.PK_DIM2
31
Matriz de Hechos y Dimensiones
� Sirve para identificar dimensiones y hechos. Además, para identificar Dimensiones Conformadas / Compartidas
Tiempo Cliente Producto Distribuidor
Órdenes de compra X X X
Inventario X X X
Órdenes de clientes X X X
Devoluciones X X X
32
� Usar claves subrogadas (SK)◦ 1 Dimensión, 1 Clave con tipo de dato numérico)
◦ Puede ser generada por una secuencia de la base de datos o por la herramienta ETL
� Usar convenciones de nombres y de tipos
Prefijo Alcance
STG_ Para tablas de almacenamiento temporal
HIS_ Para tablas de archivos históricos
DIM_ Para tablas de dimensiones
FACT_ Para tablas de hechos
AGG_ Para tablas de agregaciones
33
� Granularidad y Agregación◦ Granularidad corresponde al nivel de detalle
almacenado en el DWH
◦ Almacenar los datos al nivel más bajo de detalle posible
◦ Las agregaciones mejoran el rendimiento
� Usar columnas de auditoria◦ Hora y fecha de inserción
◦ Proceso de inserción
◦ Hora y fecha de actualización
◦ Proceso de actualización
34
Las 5 funciones básicas de agregación de SQL
son:
� Máximo: max()
� Mínimo: min()
� Conteo: count()
� Suma: sum()
� Promedio: avg()
35
� Modelar una dimensión para el tiempo y otra para la fecha◦ Usar claves en el formato AAAAMMDD
� Tener 1 registro de DESCONOCIDO (N/D) en cada dimensión en vez de NULL
SK Nombre Teléfono Fecha Nacimiento
0 Desconocido Desconocido 01/01/1970
DIM_CLIENTE
36
� La técnica de Slowly Changing Dimensions permite identificar y guardar qué registros cambiaron. Existen los siguientes tipos:
Tipo Descripción
SCD – Tipo 1 Sobrescribe el registro. Soportada por PDI
SCD – Tipo 2 Agrega un registro con columnas que indican su validez. Soportada por PDI
SCD – Tipo 3 Consiste en agregar una columna porcada una que se quiera registrar el cambio
37
� Sobrescribe el registro de una dimensión
SK Identificación Nombre Ciudad
1 1728192211 Juan Pérez Quito
SK Identificación Nombre Ciudad
1 1728192211 Juan Pérez Cuenca
Situación Actual
Nueva Situación
38
� Agrega un registro a la dimensión y 3 columnas: válido desde, válido hasta y versión
SK Identificación Nombre Ciudad Válido Desde Válido Hasta Versión
1 1728192211 Juan Pérez Quito 2012-01-01 2099-12-31 1
Situación Actual
Nueva Situación
SK Identificación Nombre Ciudad Válido Desde Válido Hasta Versión
1 1728192211 Juan Pérez Quito 2012-01-01 2012-01-25 0
2 1728192211 Juan Pérez Cuenca 2012-01-25 2099-12-31 1
39
� Agrega una columna por cada cambio
SK Identificación Nombre Ciudad Ciudad Antigua
1 1728192211 Juan Pérez Quito Quito
Situación Actual
Nueva Situación
SK Identificación Nombre Ciudad Ciudad Antigua
1 1728192211 Juan Pérez Cuenca Quito
40
� Dimensiones Monstruo (Particionamiento)
� Dimensiones Degeneradas (Número póliza, número pedido, número de reclamo)
� Copos de Nieve (Snowflake)
� Tablas de Hechos Consolidadas (Presupuesto + Actual)
� Modelo Constelación
41
PDI
PRD
PSW
CDF
WEKA
42
� Plataforma de BI Open Source
� Provee de herramientas de apoyo para todo el proceso de construcción del sistema DWH/BI
� Existen dos ediciones de Pentaho: Community (CE) y Enterprise (EE)
� La versión CE es totalmente Open Source orientada al aprendizaje y PYMES
� Pentaho puede ser descargado del siguiente link: http://sourceforge.net/projects/pentaho/files/
43
44
Herramienta Versión Descripción
Pentaho BI Server 3.8.0 Servidor Central de Inteligencia de Negocios, Portal Web de Entrega de Información
Pentaho Design Studio (PDS) 4.0.0 Organización de soluciones, creación de xaction (Acciones de Secuencias)
Pentaho Data Integration (PDI)
4.2.1 Creación y ejecución de procesos ETL
Pentaho Metadata Editor (PME)
4.1.0 Creación y publicación de metadatos para reportes
Pentaho Report Designer (PRD)
3.8.2 Creación y publicación de reportes
Pentaho Schema Workbench (PSW)
3.3.0 Creación y publicación de cubos para análisis OLAP
45
46
47
� Integración de Datos: conjunto de actividades para llenar el DWH
� La integración de datos se descompone 3 actividades◦ Extracción: Adquirir datos de 1 o más fuentes
◦ Transformación: Cambiar la forma y contenido de los datos
◦ Loading (Carga): cargar los datos en los repositorios y el DWH
48
Actividades de Extracción� Captura de cambio de datos (CDC)� Almacenamiento temporal (Data Staging)
Actividades de Transformación� Validación de datos� Limpieza de datos
� Decodificar y Renombrar (1->Sí, 0->No)� Agregaciones� Gestión y generación de claves (SK)
49
Actividades de Carga (Loading)
� Carga de Tabla de Hechos
� Carga de tablas temporales
� Carga y mantenimiento de Tablas de Dimensiones
50
� Las soluciones de PDI son construidas sobre 2 tipos diferentes de objetos:◦ Transformaciones◦ Trabajos (Jobs)
� El corazón de PDI es el motor de integración de datos de Pentaho (Data Integration Engine)
� PDI contiene las siguientes herramientas◦ Spoon: GUI para crear transformaciones y trabajos◦ Kitchen: Comandos para correr trabajos◦ Pan: Comandos para correr transformaciones◦ Karte: Ejecutar transformaciones y trabajos de
forma remota
51
52
� Herramienta gráfica para crear, editar y publicar reportes al servidor de BI de Pentaho
� Puede usar como fuente modelos de metadatos
� Los reportes son almacenados usando el formato .prpt (Pentaho Report)
� Basado en bandas y muy similar a SAP CrystalReports
53
Las secciones (bandas) de un reporte en PRD
son:
� Page Header/Footer: Cualquier contenido de ésta sección será visto en cada página del reporte (Logos, Fecha, Nro. Página, etc.)
� Report Header/Footer: Cualquier contenido de ésta sección se mostrará una sola vez (Parámetros, Título, Totales)
� Group Header/Footer: Se tiene por lo menos 1 grupo (Etiquetas, Subtotales)
54
� Details: Aquí van las filas individuales del resultado de una consulta. Tiene cabecera y pié de página
� No Data: Sirve para mostrar información cuando el resultado de la consulta está vacío
� Watermark: Marca de agua que será impresa como fondo en cada página del reporte
� Para ocultar/mostrar bandas cambiar la propiedad hide-on-canvas que se encuentra en Attributes
55
Elemento Descripción
label Texto estático, etiquetas de columna
text-field Valores de texto de un conjunto de datos
number-field Valores numéricos de una conjunto de datos
date-field Valores de fecha de un conjunto de datos
message-field Puede combinar texto, campos y funciones (Cliente: ${nombre} ${apellido} )
image-field Muestra imágenes desde un conjunto de datos
image Muestra una imagen local o desde una URL
... El resto sirven para crear gráficos, bandas, sub-reportes, etc.
56
� PRD puede tener 1 o n Data Sets pero sólo 1 Data Set activo
� Comúnmente el Data Set es una consulta a la base de datos a través de JDBC
� Es posible crear la consulta (Query) de forma gráfica (SQLeonardo) o escribiendo el SQL
� El Data Set puede ser obtenido también a través de una consulta a los metadatos
57
� Para agregar un parámetro, en la sección Data hacer clic en “Add a New Master-reportParameters”
� Los parámetros puede ser traídos de la base de datos (JDBC) o creados manualmente (Table)
� Para valores simples en la condición WHERE de una consulta colocar = ${nombreparam}
� Para valores múltiples colocar IN (${nombreparam})
58
� El Row-Banding permite manejar 1 color diferente para las filas pares y otro para las impares
� Para habilitarlo ir a Format � Row-Banding
� Seleccionar los colores y colocar el estado como Invisible
Visible Color
Invisible Color
59
� Grupos, cabeceras y resúmenes
� Un Grupo permite organizar contenido en diferentes niveles
� En PRD siempre se crea un grupo por defecto
� Para editar grupos (Edit � Groups)
� Es necesario ordenar los datos en las consultas (ORDER BY)
60
� Para acceder a la funciones ir a Data �Functions
� Las funciones de agregación sirven para realizar funciones de agregación sobre los datos de un grupo
� Las funciones son Globales (Summary, Grupos) y de Ejecución (Running, Subgrupos)
� Deben tener un nombre único
� Algunas funciones son Page of Pages, Sum, Count, Maximum, Minimum…
61
� Para publicar un reporte en el servidor de BI
de Pentaho, se tiene que ir a File � Publish
URL: http://localhost:8080/pentaho
� Ingresar con el usuario Administrador
� Escoger la ubicación dentro de la solución
� Ingresar la clave de publicación (Publish Password)
� En el portal web de Pentaho, refrescar los
metadatos Herramientas � Actualizar �Actualizar Metadata de Reporte
62
� Pentaho Analysis Services (PAS) provee las capacidades OLAP a la plataforma Pentaho
� PAS está compuesto por 4 componentes1. JPivot: interfaz gráfica para trabajar con cubos
OLAP
2. Mondrian ROLAP Engine: motor ROLAP
3. Schema Workbench: Herramienta visual para diseñar y probar cubos
4. Aggregate Designer: Herramienta visual para generar tablas agregadas
63
Esquema:
Archivo XML, describe 1 o más cubos multi-dimensionales
64
� Multi Dimensional eXpressions
� Lenguaje creado para consultar bases de datos OLAP
� Estándar de facto desarrollado por Microsoft
� MDX es al modelo dimensional como SQL es al modelo relacional
65
Cubo
Un cubo es una colección de múltiples
Dimensiones
Cliente
Juan Pérez
José Quiroz
Pedro Ponce
MEDIDAS:• Ventas• Comisiones• Etc.
Tupla (Intersección Dim.)
66
Analogía con el Modelo Estrella
� Dimensiones Cubo = Dimensiones Modelo Estrella
� Medidas Cubo = Hechos Modelo Estrella
Visualización de un Cubo
� Se lo visualiza como una tabla cruzada (crosstab) o tabla pivot
67
Cliente
Juan Pérez
José Quiroz
Pedro Ponce
Nokia LG Samsung
2010 2011 2012 2010 2011 2012 2010 2011 2012
Juan Pérez Z
José Quiroz X
Pedro Ponce N
CuboRepresentado comoTabla Cruzada
68
Jerarquías
� Las dimensiones de un cubo son organizadas en 1 o más jerarquías (Hierarchy)
� Estructura tipo árbol
� Permite retraer datos del cubo de diferentes niveles de agregación
� Se pueden tener múltiples jerarquías
� Ejemplo: Año, Semestre, Mes (Dimensión Fecha)
69
Todas Fechas
2010
S1 S2
2011
S1
1 2 3…
S2
2012
S1 S2
…10 11 12
MIEMBROSNIVELES
TODOS
ANIO
SEMESTRE
MES
[FECHA].[MES].[2]MDX
Relación Padre / Hijo
70
� Conexión a la base de datos ir a Options �Connection...
� A través de File � New � JDBC Explorer se podrá visualizar las tablas y columnas del DWH
� Para crear un nuevo esquema ir a File � New �Schema
� Colocar un nombre que no tenga espacios en blanco ni caracteres especiales y guardar
� En View � View XML se podrá visualizar la representación XML del esquema
� Sólo se permite 1 esquema por conexión
71
Las tareas para crear un cubo OLAP usando
PSW pueden resumirse en las siguientes:
� Crear un esquema
� Crear un cubo◦ Escoger la tabla de hechos
◦ Agregar medidas
� Crear dimensiones◦ Editar la jerarquía por defecto
◦ Definir niveles de jerarquía
◦ Opcionalmente, agregar más dimensiones
72
Creación de un cubo
� Para crear un cubo, clic derecho sobre
Schema � Add Cube
� Colocar el nombre (Name) y el título (Caption, lo que se mostrará en el portal)
� Dejar seleccionado cache, enabled y visible
� A un cubo se le asocia una tabla de hechos. Clic derecho sobre el cubo, Add Table para seleccionar la tabla de hechos
73
Creación de medidas (Measures)
� Sobre la tabla de hechos se agregan una o más medidas, haciendo clic en el botón
� La primera medida, es la medida por defecto
� En la medida colocar el nombre (name), columna (column), aggregator (sum, max, min, avg, count, distinct count), tipo de datos (datatype) y título (caption)
74
Creación de dimensiones
� Clic derecho sobre el cubo, Add Dimension
� Para dimensiones fechas usar el tipo: TimeDimension para el resto usar StandardDimension
� Colocar el nombre (name) y la clave foránea (foreignKey)
75
Creación de dimensiones
� En clave foránea seleccionar la clave foránea de la tabla de hechos (sk_fecha)
� Cuando se crea una dimensión, se crea una jerarquía por defecto
76
Edición de la jerarquía por defecto
� Sobre la jerarquía, hacer clic derecho, AddTable, seleccionar la dimensión
� En la jerarquía colocar el nombre (name), marcar hasAll, nombre de todos los miembros (allMemberName), título de todos los miembros (allMemberCaption) y la clave primaria de la dimensión (sk_fecha)
77
Definir niveles de jerarquía
◦ Sobre la jerarquía, clic derecho, Add Level
◦ En el nivel definir el nombre (name), columna (column), tipo (type), tipo de nivel (levelType)
◦ Para niveles de fecha el tipo de nivel puede ser: TimeYears, TimeHalfYear, TimeHalfYears, TimeQuarters, TimeMonths, TimeWeeks, TimeDays
◦ Para el resto de niveles se usa Regular
◦ Crear las dimensiones, jerarquías y niveles que se requiera
78
� Para probar que el esquema y cubo fueron
creados correctamente, ir a File � New �MDX Query
� Colocar la siguiente consulta:select NON EMPTY {[Measures].[nombre_medida]} ON COLUMNS,
NON EMPTY Hierarchize({[dim_fecha.default].[todos_los_anios]}) ON ROWS
from [cubo_olap]
79
� Para publicar un esquema, seleccionar el esquema e ir a File � Publish...
� Colocar lo siguiente:URL = http://localhost:8080/pentahoPublish Password = pentahoUser = joePassword = password
� Seleccionar la solución, colocar el nombre JNDI de la conexión
� Marcar Register XMLA Data Source para poder acceder desde el portal de Pentaho (New Analysis View)
80
� Para visualizar un esquema de Mondriancreado desde PSW, en el portal de Pentaho, hacer clic sobre New Analysis View
� Las principales operaciones que se puede hacer sobre un cubo OLAP son:◦ Drill Down/Up (Excavar)
◦ Slice (Cortar / Filtrar)
◦ Pivot (Pivotear, Intercambiar Ejes)
81
82
1. Crear el directorio c:\pentaho
2. Copiar biserver-ce-3.8.0.zip
3. Descomprimir biserver-ce-3.8.0.zip
4. Asegurarse tener la siguiente estructura:
c:\pentaho\biserver-ce-3.8.0\administration-console (PAC)
c:\pentaho\biserver-ce-3.8.0\biserver-ce(Servidor BI)
PENTAHO_HOME = c:\pentaho\biserver-ce-3.8.0
83
5. Ir al directorio c:\pentaho\biserver-ce-3.8.0\biserver-ce
6. Ejecutar el archivo start-pentaho.bat
7. Abrir un navegador e ir al siguiente URL:
http://localhost:8080/pentaho/
84
1. Abrir la consola de comandos (cmd)
2. Ir al directorio %PENTAHO_HOME%\biserver-ce\tomcat\bin
3. Ejecutar el comando:
service.bat install Pentaho
4. Ir a services.msc para verificar que se instaló el servicio Apache Tomcat Pentaho
85
1. Ir a %PENTAHO_HOME%\ biserver-ce\data\postgresql
2. Copiar los scripts: create_repository_postgresql.sql, create_sample_datasource_postgresql.sql, create_quartz_postgresql.sql al directorio C:\Program Files\PostgreSQL\8.4\scripts\runpsql.bat
86
3. Ejecutar el programa runpsql.bat
4. Ingresar las credenciales y ejecutar en la consola de comandos de Postgres el comando:
\i create_repository_postgresql.sql
5. Luego el comando \i create_sample_datasource_postgresql.sql
6. Finalmente ejecutar los comandos:
\connect postgres
\i create_quartz_postgresql.sql
87
7. Ir al directorio %PENTAHO_HOME%\biserver-ce\tomcat\webapps\pentaho\META-INF
8. Editar el archivo context.xml
9. Cambiar las cadenas de conexión para el nuevo repositorio
driverClassName = org.postgresql.Driver
url = jdbc:postgresql://127.0.0.1:5432/hibernate
validationQuery = select version();
88
10. Copiar el archivo postgresql-8.4-703.jdbc3.jar a los directorios:
%PENTAHO_HOME%\biserver-ce\tomcat\lib
%PENTAHO_HOME%\ administration-console\jdbc
11. Ir al directorio %PENTAHO_HOME%\biserver-ce\pentaho-solutions\system\hibernate
89
12. Editar el archivo hibernate-settings.xml y cambiar la etiqueta <config-file>
<configfile>
system/hibernate/postgresql.hibernate.cfg.xml
</config-file>
13. Ir al directorio %PENTAHO_HOME%\biserver-ce\pentaho-solutions\system y editar los archivos applicationContext-spring-security-jdbc.xml y applicationContext-spring-security-hibernate.properties
90
14. Para JDBC y Hibernate usar las siguientes configuraciones:
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://127.0.0.1:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
15. Reiniciar Pentaho para aplicar la configuración
91
1. Ir al directorio %PENTAHO_HOME% \biserver-ce\pentaho-solutions\system
2. Editar el archivo publisher_config.xml
3. En la etiqueta <publisher-password>colocar:
<publisher-password>pentaho</publisher-password>
92
1. Ir al directorio %PENTAHO_HOME%\biserver-ce\pentaho-solutions\system\smtp-email
2. Editar el archivo email_config_gmail.xml3. Asignar los valores para las etiquetas:
<mail.from.default>, <mail.userid> y < mail.password>
4. Renombrar el archivo a email_config.xml5. Reiniciar Pentaho6. Ir al reporte Burst Sales Report para probar
(Usar IE)
93
1. Ir al directorio %PENTAHO_HOME%\administration-console\resource\config
2. Editar el archivo console.xml
3. Asignar los siguientes valores a las etiquetas <solution-path> y <war-path><solution-path>../biserver-ce/pentaho-solutions</solution-path>
<war-path>../biserver-ce/tomcat/webapps/pentaho</war-path>
94
1. Ir al directorio %PENTAHO_HOME%\administration-console
2. Ejecutar el archivo start-pac.bat
3. Abrir un navegador de internet e ir al siguiente URL
http://localhost:8099
95
1. Crear base de datos pdi_repo, usuario pentaho_user (Todos los permisos)
2. Copiar el archivo pdi-ce-4.2.1.zip al directorio c:\pentaho
3. Descomprimir el archivo y ejecutar Spoon.bat
4. Agregar un nuevo repositorio
5. Seleccionar Kettle Database Repository
6. Crear una nueva conexión de nombre pdi_repo
7. Crear repositorio de metadatos (Dry Run? No)
8. Usar usuario admin / admin
96
1. En el directorio c:\pentaho, crear la carpeta src
2. Crear un hola_mundo.txt con los siguientes datos:
NombreLeonardoHenryÁlvaro
3. Ir a File � New � Transformation4. Ir a Design/Input Arrastrar Text File Input
5. Renombrar Text File Input a Agregar Archivo
6. En File, Agregar el archivo hola_mundo.txt
97
7. En Transform arrastrar Add Constants. Definir 2 Constantes:
mensaje (String) = Bienvenido a Pentahoexclamacion (String) = !!!
8. Ir a Output arrastrar Text File Output, renombrar a Escribir Archivo Salida
9. En FileName colocar c:\pentaho\src\hola_mundo_salida
10. Get Fields y ordenar (mensaje, nombre, exclamación)
11. Verificar, Guardar y Ejecutar
98
1. Ir a Tools � Repository � Explore. En Connections agregar conexión a la base de datos stage. Usuario pentaho_user / password (Conexión Global)
2. Crear nueva transformación TRS_HOLA_MUNDO_BDD
3. Ir a Design/Input arrastar Table Input y renombrar a Leer Tabla Entrada
4. Insertar el SQL: SELECT * FROM alumno
5. Arrastrar Add Constants y Crear 1 Constante saludo(String) = Bienvenido
99
6. Ir a Output y arrastrar Table Output
7. Seleccionar la conexión stage, marcar Truncate Table y Specify Database Fields
8. En Target Table colocar holamundo
9. En Database Fields hacer clic en Get Fields
10. Hacer clic en SQL y ejecutar
11. Validar transformación, guardar y ejecutar
100
1. Ir a File � New � Job
2. Colocar el nombre JOB_EJECUCION_HOLA_MUNDO
3. Ir a General, Arrastrar START
4. Luego Arrastrar Transformaciones, SpecifyBy Reference y Seleccionar TRS_HOLA_MUNDO
5. Repetir el paso 4 para TRS_HOLA_MUNDO_BDD
6. Arrastrar Success y Mail
101
7. En Mail Configurar lo siguiente:
SMTP Server = smtp.gmail.com
Port = 587
Use Authentication = Sí
Auth User = ingmmurillo.capacitacion
Password = ………
Use Secure Auth = Sí
Secure Connection Type = TLS
102
1. Abrir una consola de comandos (cmd)
2. Ir a c:\pentaho\pdi-ce-4.2.1\data-integration
3. Ejecutar el siguiente comando:
Kitchen.bat /rep pdi_repo /useradmin /pass admin /jobJOB_EJECUCION_HOLA_MUNDO
4. Finalmente, crear Tarea Calendarizada del SO (Ejecutar Kitchen con Argumentos Paso 3)
103
1. En Input, arrastar Generate Rows
2. En Limit colocar 3652 (10 años)
3. Crear un campo de tipo Date de nombre fecha_inicial, formato yyyyMMdd y valor 20090101
4. En Transform arrastrar Add Sequence para generar las SKs. Name of Value =secuencia_dias, Counter Name =SEQ_DIM_FECHA, Start at Value = 0
5. Ir a Transform arrastrar Calculator
104
7. Crear los campos sk_fecha, anio, mes, día y fecha como se muestra en la siguiente tabla:
105
Campo Calculation Field A Field B Value Type ConversionMask
v_fecha Date A + B Days
fecha_inicial secuencia_dias Date yyyyMMdd
v_fecha_str
Create a copyof field A
v_fecha String yyyyMMdd
sk_fecha Create a copyof field A
v_fecha_str Integer #
anio Year of date A v_fecha Integer #
mes Month of date A v_fecha Integer #
dia Day of monthof date A
v_fecha Integer #
fecha Create a copyof field A
v_fecha Date dd/MM/yyyy
106
8. Ir a Output y arrastrar Table Output
9. Seleccionar la tabla DIM_FECHA de la conexión al DWH, marcar Specify databasefields para mapear los campos e ir a EnterField Mapping
10. Verificar y Ejecutar
107
1. Crear una nueva transformación de nombre TRS_CARGA_DIM_SUCURSAL
2. De Input, arrastrar Table Input. Usando la conexión stage, colocar el SQL: SELECT ID, NOMBRE, RESPONSABLE FROM STG_SUCURSAL_CIA
3. Arrastrar de Data Warehouse, Combinationlookup/update
4. Usar la conexión dwh, y seleccionar Target Table = DIM_SUCURSAL
108
5. Hacer clic en Get Fields. El campo pk_sucursal asociarlo a id. Deben quedar pk_sucursal, nombre y responsable
6. En Technical Key Field ingresar sk_sucursal7. Dejar el resto por defecto y clic en OK8. Arrastrar un Table Input, y con la conexión
al DWH colocar el siguiente SQL: SELECT SK_SUCURSAL FROM DIM_SUCURSAL WHERE SK_SUCURSAL = 0;
9. Arrastrar un Filter Rows con la condiciónSK_SUCURSAL IS NULL
109
10. Arrastrar un SQL Script, usando la conexión al dwh, colocar el siguiente SQL: INSERT INTO DIM_SUCURSAL VALUES (0,'0','N/D','N/D');
11. Marcar Execute for each row y clic en OK
12. Unir el filtro con el SQL Script para la condición Result is TRUE
13. Arrastrar un Dummy y unirlo con el filtro para la condición Result is FALSE
14. Guardar, validar y probar
110
111
1. Crear una nueva transformación de nombre TRS_CARGA_DIM_CLIENTE
2. Arrastrar un Table Input, usando la conexión stage colocar el siguiente SQL: SELECT ID,IDENTIFICACION,NOMBRE,APELLIDO,DIRECCION FROM STG_CLIENTE
3. De Data Warehouse, arrastrar el componente Dimension lookup / update y unirlo con el Table Input
112
4. En Dimension lookup/update, usar la conexión dwh, Target Table = dim_cliente
5. En Technical Key Field, colocar SK_CLIENTE6. En Date range start field colocar
FECHA_DESDE7. Marcar Use an alternative start date? Y
seleccionar System Date8. En Table daterange end colocar
FECHA_HASTA9. Hacer clic en Get Fields, en la pestaña de
Key dejar mapeado sólo pk_cliente con id
113
10. Ir a la pestaña de Fields y hacer clic otra vez en Get Fields
11. Para identificacion, nombre y apellido seleccionar Update en Type of dimensionupdate
12. Para direccion dejar Insert
13. Hacer clic en SQL y ejecutar el script
14. Arrastrar un Filter Rows y
15. Validar, Guardar y Ejecutar
114
1. Copiar el archivo prd-ce-3.8.2.zip al directorio c:\pentaho
2. Descomprimir el archivo
3. Ir a c:\pentaho\prd-ce-3.8.2\report-designer
4. Copiar el archivo postgresql-8.4-703.jdbc3.jar al directorio \lib\jdbc
5. En \report-designer ejecutar report-designer.bat
115
1. Copiar el archivo psw-ce-3.3.0.14703.zip al directorio c:\pentaho
2. Descomprimir el archivo
3. Ir a c:\pentaho\psw-ce-3.3.0.14703\schema-workbench
4. Copiar el archivo postgresql-8.4-703.jdbc3.jar al directorio \drivers
5. Ejecutar workbench.bat
116
1. Asegurarse que Pentaho BI Server esté iniciado
2. Abrir una consola de comandos (cmd)
3. Ir a C:\pentaho\biserver-ce-3.8.0\administration-console
4. Ejecutar start-pac.bat
5. Abrir un navegador e ir a http://localhost:8099 (admin/password)
6. Ir a Administration / Database Connections
117
7. Hacer clic en (+) Add Database Connection
Name = dwh
Driver Class = org.postgresql.Driver
User Name = dwh
Password = pentaho_user
URL = jdbc:postgresql://localhost:5432/dwh
8. Finalmente hacer clic en Test
NOTA: el mismo nombre se tiene que usar en todas las aplicaciones de Pentaho
118
� Wiki de Pentaho CE:http://wiki.pentaho.com/display/COM/Community+Wiki+Home
� [Adrián Sergio Pulvirenti, María Carina Roldán] Pentaho Data Integration 4 Cookbook. Packt Publishing Ltd. 2011
� [María Carina Roldán] Pentaho 3.2 Data Integration Beginner's Guide. Packt Publishing Ltd. 2010
119
