Cómo configurar un repositorio de base de datos en PostgreSQL para Pentaho Data Integration

En ésta publicación explico el procedimiento para crear un repositorio de base de datos en PostgreSQL para la herramienta Spoon de Pentaho Data Integration (PDI)

Requisitos.-

1. Crear en PostgreSQL la base de datos PDI_REPO colocando como propietario al usuario postgres

Procedimiento.- 

1. Ir al directorio de PDI. Por ejemplo: c:\pentaho\pdi-ce-4.3.0\data-integration
2. Ejecutar Spoon.bat para Windows o Spoon.sh para Unix
3. Hacer clic en el botón de agregar un nuevo repositorio

4. Seleccionar Kettle Database Repository

5. Hacer clic en el botón New para crear una conexión a la base de datos PDI_REPO

6. Definir el mismo nombre para los campos ID y Name

7. Hacer clic en el botón Create or Upgrade para crear la estructura de base de datos con la que trabaja Spoon en la base de datos PDI_REPO

8. Finalmente, hacer clic en el botón OK

Consideraciones para el uso de Pentaho Metadata Editor

Una capa de metadatos de Pentaho (PML, Pentaho Metadata Layer) construida con la herramienta Pentaho Metadata Editor (PME) permite crear un modelo derivado de un DWH con definiciones orientadas al negocio, campos calculados y formateados que sirven para que los usuarios finales puedan generar reportes a demanda (Ad Hoc) usando alguno de los reporteadores: WAQR, WAQE o Saiku Reporting.

A continuación resumo los pasos a seguir cuando se construya un modelo de metadatos con PME:

1. Definir la localización es_EC

2. Crear una conexión al DWH (Capa Física)

3. Usando la conexión creada importar las tablas que se quieren representar en el modelo

4. A nivel de capa lógica (Business Tables) realizar lo siguiente:

• Ocultar las claves subrogadas (SKs), claves primarias(PKs), campos de versión, válido desde y válido hasta marcando la propiedad Hidden For the user?
• Reescribir los nombres de las tablas y campos en términos orientados al negocio
• Definir la función de agregación por defecto (Default Aggregation) para los indicadores de la tabla de hechos
• Dar formato a los campos numéricos o de fecha usando la propiedad Miscellaneous / Mask for Number or Date
• Para crear un campo calculado definir en la fórmula: SUM([nombreColumna1]+[nombreColumna2]), marcar la propiedad Is the Formula Exact? y definir la propiedad Default Aggregation como SUM.

5. A nivel de capa de presentación (Business View) marcar las tablas que se quiere mostrar al usuario final y  definir el orden de las mismas

6. Verificar que el modelo esté correcto usando Query Editor

7. Finalmente publicar el modelo de metadatos a Pentaho Server

Configuración de Pentaho BI Server para PostgreSQL

En ésta publicación explico el procedimiento para configurar los repositorios de metadatos de Pentaho: hibernate y quartz en la base de datos PostgreSQL

Por defecto, el biserver de Pentaho usa la base de datos HSQLDB, ésta base de datos no es recomendada para instalaciones en producción por lo que es conveniente instalarlo en un motor de base de datos más robusto. El biserver viene con los scripts para MySQL, Oracle y PostgreSQL.

Pre-requisitos.-

• Tener instalado y configurado el JDK v1.6 o superior, en la siguiente publicación se explica el procedimiento
• Tener instalado la base de datos  PostgreSQL v8.4 o superior
• Haber descargado la aplicación biserver-ce de Pentaho y haberla copiado y descomprimido en el directorio: C:\pentaho\biserver-ce-X.X.X
• Haber descargado el controlador JDBC Tipo 3 para  PostgreSQL

Configuración paso a paso.-

1. Ir al directorio C:\pentaho\biserver-ce-X.X.X\biserver-ce\data aquí se encuentran los scripts para MySQL, Oracle y PostgreSQL

2. Abrir el directorio postgresql y copiar los scripts: create_repository_postgresql.sql, create_quartz_postgresql.sql y create_sample_datasource_postgresql.sql al directorio C:\Program Files\PostgreSQL\8.4\scripts

3. Ejecutar el programa runpsql.bat y acceder con el usuario postgres

4. Ejecutar la siguiente secuencia de comandos

\i create_repository_postgresql.sql
password
\i create_sample_datasource_postgresql.sql
\connect postgres postgres
sa
\i create_quartz_postgresql.sql
password

NOTA: password es la contraseña por defecto para los usuarios hibuser del repositorio hibernate y pentaho_user del repositorio quartz, sa es la contraseña del usuario postgres.

Configuración de Tomcat

5. Copiar el driver JDBC Tipo 3 de PostgreSQL (postgresql-X.X-XXX.jdbc3.jar) al directorio C:\pentaho\biserver-ce-X.X.X\biserver-ce\tomcat\lib.

6. Editar el archivo context.xml ubicado en el directorio: C:\pentaho\biserver-ce-X.X.X\biserver-ce\tomcat\webapps\pentaho\META-INF. Colocar la siguiente configuración para cambiar la conexión a PostgreSQL

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/pentaho" docbase="webapps/pentaho/">
 <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
  factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
  maxWait="10000" username="hibuser" password="password"
  driverClassName="org.postgresql.Driver" url="jdbc:postgresql://127.0.0.1:5432/hibernate"
  validationQuery="select version();" />
  
 <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
  factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
  maxWait="10000" username="pentaho_user" password="password"
  driverClassName="org.postgresql.Driver" url="jdbc:postgresql://127.0.0.1:5432/quartz"
  validationQuery="select version();"/>
</Context>

Configuración del BI Server

Todos los archivos de configuración del servidor central de BI de Pentaho Community Edition (biserver-ce) se encuentran en el directorio: C:\pentaho\biserver-ce-X.X.X\biserver-ce\pentaho-solutions\system

El resto de configuraciones se realizarán en éste directorio.

7. Ir al subdirectorio /hibernate y editar el archivo hibernate-settings.xml, cambiar la etiqueta <config-file> al valor: system/hibernate/postgresql.hibernate.cfg.xml como se muestra a continuación:

<?xml version='1.0' encoding='utf-8'?>
<settings>  
    <!--config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file-->
 <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
    <managed>false</managed>
</settings>

8. Editar el archivo applicationContext-spring-security-jdbc.xml y cambiar la configuración de conexión del Bean dataSource a PostgreSQL como se muestra a continuación:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springsource.org/dtd/spring-beans.dtd">
<beans> 
 <!--...-->
 <bean id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url"
   value="jdbc:postgresql://127.0.0.1:5432/hibernate" />
  <property name="username" value="hibuser" />
  <property name="password" value="password" />
 </bean>
 <!--...-->
</beans>

9. Editar el archivo applicationContext-spring-security-hibernate.properties y cambiar la configuración de conexión a PostgreSQL como se muestra a continuación:

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

10. Reiniciar el servidor de BI de Pentaho (biserver-ce)