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

17 gedachten over “Installing Pentaho 6.1 Community Edition on Ubuntu”

  1. I can’t seem to find the “your/path/to/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties” file,should I make a new one?

    1. I am not sure what to do best. I would go ahead and try to create a new one. However it could be that more is missing form you biserver.

    1. I am affraid I cannot help you. I would hope if you follow the step by step in the blog you might end up with a running server. At least the blog contains the steps I did in setting up a server.

    1. No I did not install Tomcat. I do think a webserver is included in the Pentaho (probably Tomcat).

  2. Thank you for you sharing. could you make tutorial to install pentaho 6.1 on windows?

    first time I using pentaho is pentaho BI CE stable 4.8, PDI-CE stable 4.4, and PSW-CE 3.5.. on windows 7 and Java 7.

    yesterday I try to using newest Pentaho BI 6.1, when start .bat fike with java 7, tomcat didn’t starting.

    I try to upgrade to java 8 and running it again, but tomcat still didn’t starting.

    and I found out that this problem not only happen on me.

  3. 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.

    1. Hi,

      I have the same error with the last version of Pentaho.

      This is systemListeners.xml :

      <!—->
      <!–

      –>

      86400

      false

  4. The following errors were detected:

    biserver-ce/start-pentaho.sh: 40: cd: can’t cd to biserver-ce
    WARNING: Using java from path
    DEBUG: _PENTAHO_JAVA_HOME=
    DEBUG: _PENTAHO_JAVA=java
    Neither the JAVA_HOME nor the JRE_HOME environment variable is defined
    At least one of these environment variable is needed to run this program

    Help please, i have any solution for 2 days.

  5. Hi,
    when i’ start pentaho, i show it

    DEBUG: Found JAVA one folder up
    DEBUG: _PENTAHO_JAVA_HOME=/usr/local/biserver-ce/../java
    DEBUG: _PENTAHO_JAVA=/usr/local/biserver-ce/../java/bin/java
    The BASEDIR environment variable is not defined
    This environment variable is needed to run this program

    Please help !!!

  6. Hi, please help. I have this error

    java.io.FileNotFoundException: Could not open ServletContext resource [/pentaho-spring-beans.xml]
    at org.springframework.web.context.support.ServletContextResource.getInputStream(ServletContextResource.java:140)
    at org.pentaho.platform.web.http.context.PentahoSolutionSpringApplicationContext.getResourceByPath(PentahoSolutionSpringApplicationContext.java:94)
    at org.springframework.core.io.DefaultResourceLoader.getResource(DefaultResourceLoader.java:100)
    at org.springframework.core.io.support.PathMatchingResourcePatternResolver.getResources(PathMatchingResourcePatternResolver.java:285)
    at org.springframework.context.support.AbstractApplicationContext.getResources(AbstractApplicationContext.java:1271)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:208)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:180)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:125)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:94)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:130)
    at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:539)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:451)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:410)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:112)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4811)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5251)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
    at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
    21:55:16,656 ERROR [ContextLoader] Context initialization failed
    org.springframework.beans.factory.BeanDefinitionStoreException: IOException parsing XML document from ServletContext resource [/pentaho-spring-beans.xml]; nested exception is java.io.FileNotFoundException: Could not open ServletContext resource [/pentaho-spring-beans.xml]
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:342)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:303)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:174)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:209)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:180)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:125)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:94)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:130)
    at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:539)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:451)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:410)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:112)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4811)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5251)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
    at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: java.io.FileNotFoundException: Could not open ServletContext resource [/pentaho-spring-beans.xml]
    at org.springframework.web.context.support.ServletContextResource.getInputStream(ServletContextResource.java:140)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:329)
    … 25 more
    26-Feb-2017 21:55:16.749 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal One or more listeners failed to start. Full details will be found in the appropriate container log file
    26-Feb-2017 21:55:16.749 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal Erreur de démarrage du contexte [/pentaho] suite aux erreurs précédentes
    21:55:16,853 WARN [PentahoSolutionSpringApplicationContext] Exception thrown from ApplicationListener handling ContextClosedEvent
    java.lang.IllegalStateException: ApplicationEventMulticaster not initialized – call ‘refresh’ before multicasting events via the context: Root WebApplicationContext: startup date [Sun Feb 26 21:55:16 GMT 2017]; root of context hierarchy
    at org.springframework.context.support.AbstractApplicationContext.getApplicationEventMulticaster(AbstractApplicationContext.java:347)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:334)
    at org.springframework.context.support.AbstractApplicationContext.doClose(AbstractApplicationContext.java:1051)
    at org.springframework.context.support.AbstractApplicationContext.close(AbstractApplicationContext.java:1012)
    at org.springframework.web.context.ContextLoader.closeWebApplicationContext(ContextLoader.java:586)
    at org.springframework.web.context.ContextLoaderListener.contextDestroyed(ContextLoaderListener.java:143)
    at org.apache.catalina.core.StandardContext.listenerStop(StandardContext.java:4858)
    at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5474)
    at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:224)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:159)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
    at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
    21:55:16,855 WARN [PentahoSolutionSpringApplicationContext] Exception thrown from LifecycleProcessor on context close
    java.lang.IllegalStateException: LifecycleProcessor not initialized – call ‘refresh’ before invoking lifecycle methods via the context: Root WebApplicationContext: startup date [Sun Feb 26 21:55:16 GMT 2017]; root of context hierarchy
    at org.springframework.context.support.AbstractApplicationContext.getLifecycleProcessor(AbstractApplicationContext.java:360)
    at org.springframework.context.support.AbstractApplicationContext.doClose(AbstractApplicationContext.java:1059)
    at org.springframework.context.support.AbstractApplicationContext.close(AbstractApplicationContext.java:1012)
    at org.springframework.web.context.ContextLoader.closeWebApplicationContext(ContextLoader.java:586)
    at org.springframework.web.context.ContextLoaderListener.contextDestroyed(ContextLoaderListener.java:143)
    at org.apache.catalina.core.StandardContext.listenerStop(StandardContext.java:4858)
    at org.apache.catalina.core.StandardContext.stopInternal(StandardContext.java:5474)
    at org.apache.catalina.util.LifecycleBase.stop(LifecycleBase.java:224)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:159)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
    at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)

Reacties zijn gesloten.