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