Business Intelligence – Pentaho 3.5 com PostgreSQL

---

Quando tive que implementar o Pentaho (backend) em um grande cliente, a necessidade para performance, logo: PostgreSQL. Encontrei pouca documentação explicando como fazer deploy na combinação: JBoss/Tomcat + PostgreSQL. Isto era na recente versão 3 e as referências eram todas para a versão 2, quando o Pentaho vinha out-of-the-box preparado para JBoss.

Recentemente tive que implementar a versão 3.5 e lembrei de postar este documento para a comunidade, ainda postarei um pacote .deb para facilitar as coisas aos mesmos sysadmins como eu =)

CONFIGURAR O APT

Editar o arquivo /etc/apt/sources.list conforme as linhas abaixo:

1 #
2 # deb cdrom:[Debian GNU/Linux 5.0.1 _Lenny_ - Official i386 DVD Binary-1 20090413-00:33]/ lenny contrib main
3
4 #deb cdrom:[Debian GNU/Linux 5.0.1 _Lenny_ - Official i386 DVD Binary-1 20090413-00:33]/ lenny contrib main
5
6 deb http://security.debian.org/ lenny/updates main contrib
7 deb-src http://security.debian.org/ lenny/updates main contrib
8
9 deb http://volatile.debian.org/debian-volatile lenny/volatile main contrib
10 deb-src http://volatile.debian.org/debian-volatile lenny/volatile main contrib
11
12
13 deb http://ftp.us.debian.org/debian lenny main contrib non-free
14

Para instalar os pacotes providos pelo repositório Debian, basta executar:

apt-get update

apt-get install postgresql-8.3
apt-get install openssh-server
apt-get install sun-java6-jdk
apt-get install vim

Baixe o Pentaho Biserver-ce 3.5: http://sourceforge.net/projects/pentaho/

CONFIGURANDO A SUÍTE Pentaho (CE) 3.5

CONFIGURANDO O BANCO DE DADOS PostgreSQL

Após instalar o PostgreSQL, é recomendável que você mantenha seus dados (partição com dados ou sua tablespace) em um disco de alta performance, SSD, por exemplo. RAID, opte por xfs com RAID 1+0. Separe também o seu pg_xlog. Não existe receita para tuning do PostgreSQL, tudo depende dos dados que terá em seu data warehouse, consulte um especialista.

Criar usuários hibuser e pentaho_user para o banco, esse procedimento deve ser realizado como usuário postgres:

su – postgres
postgres@biserver:/> psql
postgres=# CREATE USER hibuser password ‘password’;
CREATE ROLE
postgres=# create DATABASE hibernate owner hibuser;
CREATE DATABASE

postgres=# CREATE USER pentaho_user password ‘password’;
CREATE ROLE
postgres=# create DATABASE quartz owner pentaho_user;
CREATE DATABASE

postgres=# create DATABASE sampledata WITH ENCODING ‘SQL_ASCII’ owner pentaho_user;
CREATE DATABASE

Por enquanto é isto. Para quem deseja ter os relatórios exemplo que vêm com a Suíte Pentaho, deve importar os bancos disponíveis no diretório biserver-ce/data/postgresql/.

IMPORTANTO BASES DE RELATÓRIO PADRÃO

NOTA: Caso você deseje os relatórios padrão do Pentaho, siga com estes passos, caso contrário, pule para o próximo ítem Pentaho.

Base de dados SampleData: Esta base contém os dados de exemplo dos relatórios do Pentaho, porém está incompleta. Clique aqui para baixar o pacote com todas as bases exemplo completas.

cp /pentaho/biserver-ce/data/postgresql/*.sql /tmp
chmod 777 /tmp/*.sql
su – postgres
psql sampledata < /tmp/postgresql_sampleData.sql
SET
SET
SET
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
(continua…)

Base de dados Quartz: Base de “time” utilizado pelo Pentaho.

psql < /tmp/create_quartz_postgresql.sql
ERROR: database “quartz” is being accessed by other users
ERROR: role “pentaho_user” cannot be dropped because some objects depend on it
DETAIL: owner of database quartz
ERROR: role “pentaho_user” already exists
ERROR: database “quartz” already exists
GRANT
You are now connected to database “quartz” as user “pentaho_user”.
BEGIN
NOTICE: table “qrtz_job_listeners” does not exist, skipping
DROP TABLE
NOTICE: table “qrtz_trigger_listeners” does not exist, skipping
DROP TABLE
NOTICE: table “qrtz_fired_triggers” does not exist, skipping
DROP TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “qrtz_triggers_pkey” for table “qrtz_triggers”
CREATE TABLE
(continua…)
Banco de repositórios:

psql < /tmp/create_repository_postgresql.sql

Por natureza de adaptação da transferência de banco de exemplos de relatórios do Pentaho HSQLDB para PostgreSQL, deve-se criar views no banco de dados pois o HSQLDB interpreta suas tabelas em letras maiúsculas, já os bancos PostgreSQL e MySQL deve ser explícito:

su – postgres
psql sampledata

create view customers as select * from “CUSTOMERS”;
create view customer_w_ter as select * from “CUSTOMER_W_TER”;
create view department_managers as select * from “DEPARTMENT_MANAGERS”;
create view employees as select * from “EMPLOYEES”;
create view offices as select * from “OFFICES”;
create view orderdetails as select * from “ORDERDETAILS”;
create view orderfact as select * from “ORDERFACT”;
create view orders as select * from “ORDERS”;
create view payments as select * from “PAYMENTS”;
create view products as select * from “PRODUCTS”;
create view quadrant_actuals as select * from “QUADRANT_ACTUALS”;
create view time as select * from “TIME”;
create view trial_balance as select * from “TRIAL_BALANCE”;

Pentaho

O processo de instalação e configuração da suíte pentaho deve ser realizada como usuário root, conforme sugerido:

mkdir /pentaho
cd /pentaho
tar -xvzf biserver-ce-3.5.0.stable.tar.gz

Após descompactar o pacotes obtido no site, será criado 2 diretórios:

• Diretório responsável pela administração:
  - administration-console
• Diretório responsável pelo biserver:
  - biserver-ce

Ajustes nos scripts de inicialização

Adicionar a seguinte linha definindo o PATH do JAVA_HOME no arquivo: /pentaho/biserver-ce/set-pentaho-java.sh

29
30 JAVA_HOME=/usr/lib/jvm/java-6-sun
31
Remover a inicialização do banco hypersonic, pois a instalação utiliza o banco PostgreSQL, arquivo: /pentaho/biserver-ce/start-pentaho.sh
25 if [ "$?" = 0 ]; then
26 # Desativando o hypersonic, pois temos tudo no PostgreSQL
27 ##cd “$DIR/data”
28 ##sh start_hypersonic.sh &
29 cd “$DIR/tomcat/bin”
30 export CATALINA_OPTS=”-Xms2048m -Xmx2048m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000″
31 JAVA_HOME=$_PENTAHO_JAVA_HOME
32 sh startup.sh
33 fi

Note a linha 30 o ajuste da quantidade de memória para JVM. Novamente, tudo depende do uso, faz-se necessário monitoramento constante para adequação. Uma indicação para monitoramento é zabbix + zapcat.
Realizar a mesma modificação (adicionar comentário) no arquivo: /pentaho/biserver-ce/stop-pentaho.sh

21 ## Hypersonic desabilitado, temos tudo no PostgreSQL
22 ##cd “$DIR/data”
23 ##sh stop_hypersonic.sh &
24 cd “$DIR/tomcat/bin”
25 JAVA_HOME=$_PENTAHO_JAVA_HOME
26 sh shutdown.sh

Eu acabei criando um script de inicialização da plataforma, ele inicia e finaliza o BIServer 3.5 e o Administration console, segue abaixo:

NOTA: Para a versão 3, basta modificar o start-pac.sh para o script start

1 #!/bin/bash
2 ### BEGIN INIT INFO
3 # Provides: start-pentaho stop-pentaho
4 # Default-Start: 2 3 4 5
5 # Default-Stop: 0 1 6
6 # Description: Pentaho BI Platform
7 ### END INIT INFO
8
9 # Flavio Torres, flavio.torres@4linux.com.br
10 # Script de inicializacao do pentaho e administration console
11 # Dezembro, 2009 – GPL
12
13 # Utilizacao do jdk e jre 64bits
14 export JAVA_HOME=/usr/lib/jvm/java-6-sun
15
16 cd /pentaho/biserver-ce
17
18 start(){
19 if [ "$1" == "adm" ];then
20 echo “Iniciando aplicacao administration-console”
21 cd ../administration-console
22 ./start-pac.sh > /tmp/pentaho_console.out 2>&1 &
23
24 else
25 echo “Iniciando aplicacao biserver”
26 ./start-pentaho.sh > /tmp/pentaho.out 2>&1
27 fi
28
29 echo “ok”
30
31 }
32
33 stop(){
34
35
36 if [ "$1" == "adm" ];then
37 echo “Finalizando aplicacao administration-console”
38 cd ../administration-console
39 ./stop-pac.sh
40 else
41 echo “Finalizando aplicacao biserver”
42 ./stop-pentaho.sh
43 fi
44
45 echo “ok”
46 }
47
48
49 case “$1″ in
50 start)
51 start $2
52 ;;
53 stop)
54 stop $2
55 ;;
56 *)
57 printf “\nUsage: $0 \n
58 start | stop : Inicia ou finaliza a aplicacao biserver\n
59 start adm | stop adm : Inicia ou finaliza o administration console\n\n”
60 ;;
61 esac
62 exit 0
63

CONFIGURANDO O pentaho-solutions

Para a configuração do pentaho-solutions, será necessário a modificação de 5 arquivos, as modificações estão destacadas e devem ser realizadas como usuário root:

• pentaho-solutions/
  • system/
  • • applicationContext-spring-security-jdbc.xml
  • • applicationContext-spring-security-hibernate.properties
  • quartz/
  • • quartz.properties
  • hibernate/
  • • hibernate-settings.xml
  • • postgresql.hibernate.cfg.xml

applicationContext-spring-security-jdbc.xml

---

Esse arquivo é responsável por setar a configuração de autenticação do sistema “Spring Security” utilizado pelo Pentaho BI Server.

Edite o arquivo e altere o seguinte bloco:

<!– This is only for Hypersonic. Please update this section for any other database you are using –>
<bean id=”dataSource”
>
<property name=”driverClassName” value=”org.postgresql.Driver” />
<property name=”url”
value=”jdbc:postgresql://localhost:5432/hibernate” />
<property name=”username” value=”hibuser” />
<property name=”password” value=”password” />
</bean>

As opções de configuração neste arquivo são:

• driverClassName: O driver da classe de conexão com o banco, org.postgresql.Driver
• url: Endereço url para o driver acessar a base de dados, jdbc:postgresql://<endereco ip>:<porta>/<banco>
• username: Nome do usuário para acessar a base especificada
• password: Senha do usuário informado

applicationContext-spring-security-hibernate.properties

---

Esse arquivo é responsável por setar as propriedades de segurança do “Spring”, habilitando a conexão com a base hibernate.

Edite o arquivo e altere o seguinte bloco:

jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

As opções de configuração neste arquivo são:

• jdbc.driver: O driver JDBC, org.postgresql.Driver
• jdbc.url: Endereço url para o driver acessar

a base de dados, jdbc:postgresql://<endereco ip>:<porta>/<banco>

• jdbc.username: Nome do usuário para acessar a base especificada
• jdbc.password: Senha do usuário informado
• hibernate.dialect: O dialeto que o hibernate necessita utilizar

quartz.properties

---

Edite o arquivo e altere o seguinte bloco na linha 300:

300 org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

As opções de configuração neste arquivo são:

• org.quartz.jobStore.driverDelegateClass: Configuração responsável por manter o agendamento de todas as configurações enviadas, como: jobs, triggers, calendários, etc.

hibernate-settings.xml

---

Esse arquivo é responsável por setar as configurações a serem utilizadas pelo hibernate. Edite o arquivo e altere o seguinte bloco:

15 <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
postgresql.hibernate.cfg.xml

---

Edite o arquivo e altere o seguinte bloco:

<property name=”connection.driver_class”>org.postgresql.Driver</property>
<property name=”connection.url”>jdbc:postgresql://localhost:5432/hibernate</property>
<property name=”dialect”>org.hibernate.dialect.PostgreSQLDialect</property>
<property name=”connection.username”>hibuser</property>
<property name=”connection.password”>password</property>
<property name=”connection.pool_size”>10</property>
<property name=”show_sql”>false</property>
<property name=”hibernate.jdbc.use_streams_for_binary”>true</property>

As opções de configuração neste arquivo são:

• connection.driver_class: O driver da classe de conexão com o banco, org.postgresql.Driver
• connection.url: Endereço url para o driver acessar a base de dados, jdbc:postgresql://<endereco ip>:<porta>/<banco>
• connection.username: Nome do usuário para acessar a base especificada
• connection.password: Senha do usuário informado

CONFIGURANDO O Tomcat

Será necessário a modificação de 2 arquivos:

• webapps/
  • pentaho/
    • WEB-INF/
      • web.xml
    • META-INF/
      • context.xml

web.xml

---

Esse arquivo é responsável pela configuração principal do tomcat e Pentaho, as configurações definem por onde ele será acessível.

<context-param>
<param-name>base-url</param-name>
<param-value>http://intranet.empresa.com.br:8080/pentaho/</param-value>
</context-param>
context.xml

---

Esse arquivo é responsável por modificar as configurações de conexão com o banco de dados.

<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://localhost:5432/hibernate” validationQuery=”select 1” />

<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://localhost:5432/quartz” validationQuery=”select 1“/>
</Context>

As opções de configuração neste arquivo são:

• driverClassName: O driver da classe de conexão com o banco, org.postgresql.Driver
• url: Endereço url para o driver acessar a base de dados, jdbc:postgresql://<endereco ip>:<porta>/<banco>
• username: Nome do usuário para acessar a base especificada
• password: Senha do usuário informado
• validationQuery: Query utilizada para testar a conexão com o banco.

CONFIGURANDO O administration console COM SUPORTE AO PostgreSQL

Esta foi uma parte que não encontrei em lugar algum, tive que fazer engenharia reversa do sistema e adaptá-lo. Existe uma contribuição minha na wiki do Pentaho.

Será necessário a modificação de 2 arquivos:

Copiar o driver responsável pela conexão ao postgresql para o diretório do administration-console:

Fazer o download do driver atual do PostgreSQL, note que há uma direfença entre usar a JDK 1.5 e 1.6, leia no site, entenda e baixe a correta.

cd /pentaho
wget http://jdbc.postgresql.org/download/postgresql-8.3-605.jdbc4.jar
rm -f /pentaho/biserver-ce/tomcat/common/lib/postgresql-8.2-504.jdbc3.jar
cp postgresql-8.3-605.jdbc4.jar /pentaho/biserver-ce/tomcat/common/lib/
cp biserver-ce/tomcat/common/lib/postgresql-8.3-605.jdbc4.jar administration-console/jdbc/

Configurar autenticação no PostgreSQL:

• administration-console/
  • resource/
    • config/
      • login.conf
      • console.properties

login.conf – JDBCLoginModule

---

O módulo JDBCLoginModule armazena as configurações de usuário e senha e regras de acesso na base de dados, que por sua vez é acessado via driver JDBC. As configurações personalizadas de usuário, senha, tabelas e drivers são armazenadas no arquivo login.conf.

cd /biserver/pentaho/administration-console/resource/config
vi login.conf

JDBCLoginModule {
org.mortbay.jetty.plus.jaas.spi.JDBCLoginModule required
debug=”true”
dbUrl=”jdbc:postgresql://localhost:5432/hibernate”
dbUserName=”pentaho_user”
dbPassword=”password”
dbDriver=”org.postgresql.Driver”
userTable=”admconsole_users”
userField=”username”
credentialField=”credential”
userRoleTable=”admconsole_roles”
userRoleUserField=”userrole”
userRoleRoleField=”role”;
};

Você terá que adicionar todas as linhas no arquivo, isto é, remover as antigas e adicionar estas.

As opções de configuração desse arquivo são:

• dbUrl: Endereço url para o driver acessar a base de dados, jdbc:postgresql://<endereco ip>:<porta>/<banco>
• dbUserName: Nome do usuário para acessar a base especificada
• dbPassword: Senha do usuário especificado
• dbDriver: O driver da classe de conexão com o banco, org.postgresql.Driver
• userTable: Nome da tabela de usuários
• userField: Nome do campo do usuário
• credentialField: Nome do campo de credenciais, senha.
• UserRoleTable: Nome da tabela de regras
• userRoleUserField: Nome do campo e regras do usuário
• userRoleRoleField: Nome do campo de regras administrativas

A regra do banco deve satisfazer o seguinte retorno de consulta:

select credential from admconsole_users where username = ?;
select role from admconsole_roles where userrole = ?;
Abaixo segue o esquema de criação das tabelas e campos, uma vez conectado ao banco hibernate com usuário com poderes administrativos, fazer:

create table admconsole_users (
username character varying(60),
credential character varying(60)
);

create table admconsole_roles (
userrole character varying (60),
role character varying (60)
);

grant select on admconsole_users to pentaho_user;
grant select on admconsole_roles to pentaho_user;
insert into admconsole_users values (‘admin’, ‘admin’);
insert into admconsole_roles values (‘admin’, ‘server-administrator’);

A consulta retornará os seguintes resultados:

hibernate=# select credential from admconsole_users where username = ‘admin’;
credential
————
admin
(1 row)

hibernate=# select role from admconsole_roles where userrole = ‘admin’;
role
———————-
server-administrator
(1 row)

console.properties

---

Esse arquivo é responsável por habilitar as configurações a serem utilizadas pelo JDBC para conexão do console-administration com o banco.

console.security.enabled=true
console.security.roles.allowed=Admin,server-administrator,content-administrator
console.security.roles.delimiter=,
console.security.realm.name=Pentaho
console.security.login.module.name=JDBCLoginModule
console.security.auth.config.path=resource/config/login.conf
console.security.callback.handler=org.mortbay.jetty.plus.jaas.callback.DefaultCallbackHandler

Aqui acaba a configuração, se você desejar SSL, o que não é nada mal para trafegar dados importantes sobre seu negócio, parta para a próxima etapa.

CONFIGURANDO SSL PARA PENTAHO/TOMCAT

Para garantir tráfego encriptado, foi configurado o suporte a SSL para o tomcat. Para criar o certificado deve-se proceder da seguinte forma, essa configuração deve ser realizada como usuário root:

cd /usr/lib/jvm/java-6-sun/bin
./keytool -genkey -alias pentaho -keyalg RSA -validity 3650
pass: 96957e0c81f921b7e9a36008652b11ad

What is your first and last name?
[4Linux]: Flavio Torres
What is the name of your organizational unit?
[4Linux]: IT
What is the name of your organization?
[4Linux]:
What is the name of your City or Locality?
[Sao Paulo]:
What is the name of your State or Province?
[SP]:
What is the two-letter country code for this unit?
[BR]:
Is CN=Flavio Torres, OU=IT, O=4Linux, L=Sao Paulo, ST=SP, C=BR correct?
[no]: yes

Enter key password for <tomcat>
(RETURN if same as keystore password):96957e0c81f921b7e9a36008652b11ad
Re-enter new password:96957e0c81f921b7e9a36008652b11ad

biserver:/usr/local/jre1.6.0_14/bin # ./keytool -list
Enter keystore password:96957e0c81f921b7e9a36008652b11ad

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 1 entry

pentaho, Dec 11, 2009, PrivateKeyEntry,
Certificate fingerprint (MD5): *************

Pronto, criamos o certificado, obviamente vocês irão gerar outra senha, este é um md5 NÃO utilize-o.

Habilitando o suporte a SSL no tomcat:

cd /pentaho/biserver-ce/tomcat/conf
vi server.xml
110 <!– DESCOMENTAR ESTA LINHA (REMOVE-LA)
111 <Connector port=”8443″ maxHttpHeaderSize=”8192″
112 maxThreads=”150″ minSpareThreads=”25″ maxSpareThreads=”75″
113 enableLookups=”false” disableUploadTimeout=”true”
114 acceptCount=”100″ scheme=”https” secure=”true”
115 clientAuth=”false” sslProtocol=”TLS” keyAlias=”pentaho“ keystorePass=”96957e0c81f921b7e9a36008652b11ad“/>
116 –> DESCOMENTAR ESTA LINHA (REMOVE-LA)

fonte: http://www.flaviotorres.com.br/fnt/artigos/pentaho_35_postgresql.php