Installing Pentaho 6.1 Community Edition on Ubuntu

In this tutorial I will explain what I did to install Pentaho 6.1 business analytics platform on an Ubuntu server.

I will also explain what I did to install Pentaho Data Integration, Pentaho Schema Workbench and Pentaho Report Designer on an Ubuntu Desktop.

For this tutorial I wanted to use my CiviCRM database as the source for reporting and analytics and I will use a seperate mysql database for the data warehouse. The Civicrm database is called civicrm  and the data warehouse database is called datawarehouse

I will use JNDI to connect to both databases.

Pentaho Business Analytics Server (or BI-Server)

Before we start make sure you have an ubuntu server up and running.

First install mysql database server, java, and the zip utility.

On the server enter the following command:

# sudo apt-get install mysql-server zip openjdk-8-jre openjdk-8-jdk libmysql-java

This will install all the required prerequisites.

Now it is time to download the BI-Server. You can download it from http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/6.1/biserver-ce-6.1.0.1-196.zip/download

After the file is downloaded unzip it.

# unzip biserver-ce-6.1.0.1-196.zip

After you have unzipped it it is time to setup a mysql database for de BI Server and to set the connection for the mysql server.

I have followed the guideline from this blog: https://interestingittips.wordpress.com/2011/05/05/complete-pentaho-installation-on-ubuntu-part-2/

Enter the following commands to install the mysql databases.

# mysql --user root -p < /your/path/to/biserver-ce/data/mysql5/create_repository_mysql.sql
# mysql --user root -p < /your/path/to/biserver-ce/data/mysql5/create_quartz_mysql.sql
# mysql --user root -p < /your/path/to/biserver-ce/data/mysql5/create_jcr_mysql.sql

Now it is time to configure the BI-server to use those databases.

Open /your/path/to/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml and change the lines to match your mysql user and password also change it to use the mysql JDBC driver.

<?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="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/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="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>
</Context>

Make sure the hibernate component will use mysql. Do this by editing the file /your/path/to/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml and change it to:

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml

<!--  MySQL Configuration -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</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>
<!-- replaces DefinitionVersionManager -->

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Edit the file /your/path/to/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml

<!--  This is only for MySQL. Please update this section for any other database you are using -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>

Modify the JNDI file by editing /your/path/to/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties

Hibernate/type=javax.sql.DataSource
Hibernate/driver=com.mysql.jdbc.Driver
Hibernate/url=jdbc:mysql://localhost:3306/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=com.mysql.jdbc.Driver
Quartz/url=jdbc:mysql://localhost:3306/quartz
Quartz/user=pentaho_user
Quartz/password=password

Add the mysql connector to the lib folder by making a symlink:

# ln -s /usr/share/java/mysql-connector-java.jar /your/path/to/biserver-ce/tomcat/lib/mysql-connector-java.jar

Edit the file /your/path/to/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml to prevent HSQLDB to start. Remove or make a comment from the lines below.

....

<!-- [BEGIN HSQLDB DATABASES]
<context-param> <param-name>hsqldb-databases</param-name> <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value> </context-param>
[END HSQLDB DATABASES] -->

...


<!-- [BEGIN HSQLDB STARTER]
<listener> <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class> </listener>
[END HSQLDB STARTER] -->

...

That is it you are now ready to start pentaho.

# /your/path/to/biserver-ce/start-pentaho.sh

You can now access pentaho in your browser at http://your.server.name:8080

Install PDI on Ubuntu Desktok

Install the Java Runtime environment

sudo apt-get install openjdk-8-jre

Install the Java Development Kit

sudo apt-get install openjdk-8-jdk

After that run the following commands to check whether the installtion is correct:

# which java
/usr/bin/java
# java -version
openjdk version "1.8.0_91" 
...

Download PDI from [[http://sourceforge.net/projects/pentaho/files/Data%20Integration/6.1/pdi-ce-6.1.0.1-196.zip/download]]
After downloading install unzip

# sudo apt-get install unzip

After installing unzip extract the zip

# unzip pdi-ce-6.1.0.1-196.zip
# cd data-integration
# ./spoon.sh

 Install JDBC Driver

See [[http://stackoverflow.com/a/32873938/3853493]] for more information on how to install the mysql JDBC connector

# sudo apt-get install libmysql-java

This should install a file called mysql-connector-java in /usr/share/java

Now we need to create a symlink to this file in the data integration folder.

# ln -s /usr/share/java/mysql-connector-java.jar /your/path/to/data-integration/lib/mysql-connector-java.jar

Configure PDI to use JNDI

Open the file /your/path/to/data-integration/simple-jndi/jdbc.properties in an editor and add the following lines (settings are copied from above).  In the file below we define two connections one to the civicrm database (which is a data source) and one to the datawarehouse.

civicrm/type=javax.sql.DataSource
civicrm/driver=com.mysql.jdbc.Driver
civicrm/user=user
civicrm/password=*
civicrm/url=jdbc:mysql://your.civicrm.server:3306/civicrm
datawarehouse/type=javax.sql.DataSource
datawarehouse/driver=com.mysql.jdbc.Driver
datawarehouse/user=pentaho
datawarehouse/password=*
datawarehouse/url=jdbc:mysql://your.pentaho.mysql.server:3306/datawarehouse

Install Pentaho Schema Workbench on Ubuntu Desktop

Download schema workbench from [[http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.12.0/psw-ce-3.12.0.1-196.zip/download]]

Unzip the file

# unzip psw-ce-3.12.0.1-196.zip

Install the JDBC driver. See instructions from above for JDBC on Ubuntu for PDI.

# ln -s /usr/share/java/mysql-connector-java.jar /your/path/to/schema-workbench/drivers/mysql-connector-java.jar

Then start the workbench with

# /your.path/to/schema-workbench/workbench.sh

Install Pentaho Report Designer on Ubuntu Desktop

Download report designer from [[http://sourceforge.net/projects/pentaho/files/Report%20Designer/6.1/prd-ce-6.1.0.1-196.zip/download]]

Unzip the file

# unzip prd-ce-6.1.0.1-196.zip

Install the JDBC driver. See instructions from above for JDBC on Ubuntu for PDI.

# ln -s /usr/share/java/mysql-connector-java.jar /your/path/to/report-designer/lib/jdbc/mysql-connector-java.jar

Start report designer with the following command

# /your/path/to/report-designer/report-designer.sh

Setup JNDI for report designer

Open the file in /your/home/.pentaho/simple-jndi/default.properties and add the following lines:

civicrm/type=javax.sql.DataSource
civicrm/driver=com.mysql.jdbc.Driver
civicrm/user=user
civicrm/password=*
civicrm/url=jdbc:mysql://your.civicrm.server:3306/civicrm
datawarehouse/type=javax.sql.DataSource
datawarehouse/driver=com.mysql.jdbc.Driver
datewarehouse/user=pentaho
datawarehouse/password=*
datawarehouse/url=jdbc:mysql://your.pentaho.mysql.server:3306/datawarehouse