We will install Pentaho BI Suite on an Ubuntu 12.04 Server with PostgreSQL 9.2 server as a backend for Pentaho. We have chosen PostgreSQL server because this is the main database server used with OpenERP. So if you are planning to take a look at Pentaho to do some Analysis of OpenERP data. Check this How To and start using Pentaho.
1. Installation of the Server
Install PostgreSQL server 9.2 on te Server (instructions)
2. Prepare the java environment
Install the correct java jdk
sudo apt-get install openjdk-6-jdk
If there are more java environments on the server choose the correct default (openjdk-6-jdk)
sudo update-alternatives --config java
Set up JAVA_HOME environment
sudo su root -c "echo 'export JAVA_HOME=\"/usr/lib/jvm/java-1.6.0-openjdk-amd64\"' >> /etc/environment"
3. Install Pentaho BI Suite 5 CE
[AdSense-A]
Download the latest stable CE
wget http://heanet.dl.sourceforge.net/project/pentaho/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip
Unpack the tar file to the application folder
sudo unzip biserver-ce-5.0.1-stable.zip -d /opt/pentaho
Run the pentaho -postgresql scripts and if asked give the default password: password for the hibuser and pentaho_user. If you want to use a different password please adjust them in the sql files.
sudo -u postgres psql -a -f /opt/pentaho/biserver-ce/data/postgresql/create_quartz_postgresql.sql
sudo -u postgres psql -a -f /opt/pentaho/biserver-ce/data/postgresql/create_repository_postgresql.sql
sudo -u postgres psql -a -f /opt/pentaho/biserver-ce/data/postgresql/create_jcr_postgresql.sql
Download the PostgreSQL JDBC driver in the /usr/share/java folder
cd /usr/share/java
wget jdbc.postgresql.org/download/postgresql-9.2-1003.jdbc4.jar
Make a symbolic link for naming
ln -s postgresql-9.2-1003.jdbc4.jar postgresql-9.2-jdbc4.jar
Make a symbolic link in the /opt/pentaho/biserver-ce/tomcat/lib folder
cd /opt/pentaho/biserver-ce/tomcat/lib
ln -s /usr/share/java/postgresql-9.2-jdbc4.jar postgresql-9.2-jdbc4.jar
Make the .sh files executable
sudo chmod +x /opt/pentaho/biserver-ce/*.sh
4. Change Pentaho settings for using PostgreSQL database for backend
Change the pentaho tomcat context.xml file (/opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml)
sudo sed -i s/"org.hsqldb.jdbcDriver"/"org.postgresql.Driver"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
sudo sed -i s/"jdbc:hsqldb:hsql:\/\/localhost\/hibernate"/"jdbc:postgresql:\/\/localhost:5432\/hibernate"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
sudo sed -i s/"select count(\*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/"select 1"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
sudo sed -i s/"org.hsqldb.jdbcDriver"/"org.postgresql.Driver"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
sudo sed -i s/"jdbc:hsqldb:hsql:\/\/localhost\/quartz"/"jdbc:postgresql:\/\/localhost:5432\/quartz"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
sudo sed -i s/"select count(\*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/"select 1"/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
Change the hibernate config files (/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties)
sudo sed -i s/"org.hsqldb.jdbcDriver"/"org.postgresql.Driver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
sudo sed -i s/"jdbc:hsqldb:hsql:\/\/localhost\/hibernate"/"jdbc:postgresql:\/\/localhost:5432\/hibernate"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
[AdSense-B]
Change the hibernate config files (/opt/pentaho/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml)
sudo sed -i s/"system\/hibernate\/hsql.hibernate.cfg.xml"/"system\/hibernate\/postgresql.hibernate.cfg.xml"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
Change the hibernate config files (/opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties)
sudo sed -i s/"SampleData\/type=javax.sql.DataSource"/"#SampleData\/type=javax.sql.DataSource"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleData\/driver=org.hsqldb.jdbcDriver"/"#SampleData\/driver=org.hsqldb.jdbcDriver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleData\/url=jdbc:hsqldb:hsql:\/\/localhost\/sampledata"/"#SampleData\/url=jdbc:hsqldb:hsql:\/\/localhost\/sampledata"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleData\/user=pentaho_user"/"#SampleData\/user=pentaho_user"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleData\/password=password"/"#SampleData\/password=password"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Hibernate\/driver=org.hsqldb.jdbcDriver"/"Hibernate\/driver=org.postgresql.Driver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Hibernate\/url=jdbc:hsqldb:hsql:\/\/localhost\/hibernate"/"Hibernate\/url=jdbc:postgresql:\/\/localhost:5432\/hibernate"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Quartz\/driver=org.hsqldb.jdbcDriver"/"Quartz\/driver=org.postgresql.Driver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Quartz\/url=jdbc:hsqldb:hsql:\/\/localhost\/quartz"/"Quartz\/url=jdbc:postgresql:\/\/localhost:5432\/quartz"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Shark\/type=javax.sql.DataSource"/"#Shark\/type=javax.sql.DataSource"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Shark\/driver=org.hsqldb.jdbcDriver"/"#Shark\/driver=org.hsqldb.jdbcDriver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Shark\/url=jdbc:hsqldb:hsql:\/\/localhost\/shark"/"#Shark\/url=jdbc:hsqldb:hsql:\/\/localhost\/shark"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Shark\/user=sa"/"#Shark\/user=sa"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"Shark\/password="/"#Shark\/password="/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleDataAdmin\/type=javax.sql.DataSource"/"#SampleDataAdmin\/type=javax.sql.DataSource"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleDataAdmin\/driver=org.hsqldb.jdbcDriver"/"#SampleDataAdmin\/driver=org.hsqldb.jdbcDriver"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleDataAdmin\/url=jdbc:hsqldb:hsql:\/\/localhost\/sampledata"/"#SampleDataAdmin\/url=jdbc:hsqldb:hsql:\/\/localhost\/sampledata"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleDataAdmin\/user=pentaho_admin"/"#SampleDataAdmin\/user=pentaho_admin"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
sudo sed -i s/"SampleDataAdmin\/password=password"/"#SampleDataAdmin\/password=password"/g /opt/pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
5. Start your Pentaho BI Server
cd /opt/pentaho/biserver-ce
./start-pentaho.sh
Go to http://ipaddress-pentaho-server:8080
This is the error web page i got when i try to access http://ipaddrOfPentahoServer:8080
The following errors were detected
One or more system listeners failed. These are set in the systemListeners.xml.
org.pentaho.platform.api.engine.PentahoSystemException: PentahoSystem.ERROR_0014 – Error while trying to execute startup sequence for org.pentaho.platform.scheduler2.quartz.EmbeddedQuartzSystemListener
Please see the server console for more details on each error detected.
I followed the procedure step by step.
please help!
The script for the creation of the Quartz tabels in the PostgresSQL database creates the tables wrong. Check the following link to solve your issue.
http://forums.pentaho.com/showthread.php?153231-Pentaho-ce-5-Initialization-Exception
great article, but doesn’twork 🙁
some points are not complete, and give me some errors! can you post a “definitive” release of the article? 🙂 i really need it !
many thanks
nice article. appears i got hit with following error, for a pentaho 4.8 and postgresql 9. postgresql does not have system_sequences. which part of configuration that i might be wrong then. did a find -exec grep, i do not see any files contain the string of information_schema.system_sequences
thx for help, appreciated
log==>
17:50:12,212 ERROR [SchemaUpdate] could not get database metadata
org.postgresql.util.PSQLException: ERROR: relation “information_schema.system_sequences” does not exist
Position: 27
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:372)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
at org.hibernate.tool.hbm2ddl.DatabaseMetadata.initSequences(DatabaseMetadata.java:128)
at org.hibernate.tool.hbm2ddl.DatabaseMetadata.(DatabaseMetadata.java:46)
at org.hibernate.tool.hbm2ddl.DatabaseMetadata.(DatabaseMetadata.java:39)
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:129)
at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:314)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1300)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:814)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:732)
at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
found the reason, is due to hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
How do you fix this?
Just wanted to let you know, that you omitted Jackrabbit from your tutorial. That way all files are being stored in local h2 databases. The config files that need to be modified for postgres are:
pentaho-solutions/system/jackrabbit/repository/workspaces/default/workspace.xml
pentaho-solutions/system/jackrabbit/repository.xml
When I’m back from my holiday. I’ll provide a new script for installing on Ubuntu 14.04 with the latest Pentaho.
Thnx for the info.
Hello and thanks for the helpful guide. I did come across a few errors in Step 4 where the Postgres jdbc urls are being modified. The last colon ( : ) is missing on the and is show in the following example:
sudo sed -i s/”jdbc:hsqldb:hsql:\/\/localhost\/hibernate”/”jdbc:postgresql\/\/localhost:5432\/hibernate”/g /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
You will notice that “jdbc:postgresql\/\/localhost:5432\/hibernate”/g
should be
“jdbc:postgresql:\/\/localhost:5432\/hibernate”/g
This issue is present in most of the sed commands in Step 4, but are correct in Step 5 and beyond.
Hope this helps,
Marc
Thank you for the support. I’ll change the mistakes in the sed commands.
Hi!!
your instructions were so helpful for me to configure my own pentaho bi server, however, the section named:
“Change the startuo file to include the java environment”
you wrote the previous instructions, chmod +x /opt/pentaho/biserver-ce/*.sh
Cheers and thanks for your contribution
So, What is the correct stament and the path to edit the staruo file?