How to manually install & configure Pentaho BI server 3.5 on Ubuntu

Since the last howto for Pentaho there has been a mayor change in the way Pentaho looks and some changes on the configuration. There is no documentation on the pentaho forum or wiki to get a working manual installation. Al documentation is based on the PCI version. I couldn’t figure out why the Pentaho guys didn’t put a simple readme or something in the archive…. This howto is for the manual installation of Pentaho and meant for a installation on a already working tomcat server. After this howto you will have a working Pentaho BI server based on Tomcat 5.5 or 6, MySQL 5.0 or 5.1, Pentaho BI server 3.5 and Ubuntu 9.04 Jaunty (or another debian based distro).

Make sure you have a Java virtual machine (openjdk or sun) and Apache ant (both auto installed with Tomcat in Ubuntu)

Download the pentaho manual installation archive in a temp folder via the console and extract (with unzip) the archive somewhere at the Desktop or /tmp

wget http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/3.5.0-RC2/biserver-manual-ce-3.5.0.RC2.zip/download

We want Tomcat to communicate with MySQL. Pentaho is delivered with a HSQLDB demo database so want that communication also available. Copy the needed connectors to the Tomcat installation folder:

sudo cp /tmp/biserver-manual-ce/pentaho-third-party/mysql-connector-java-5.0.7.jar /usr/share/tomcat5.5/common/lib/
sudo cp /tmp/biserver-manual-ce/pentaho-third-party/hsqldb-1.8.0.jar /usr/share/tomcat5.5/common/lib/

…and make sure the user tomcat (or whatever user runs tomcat) have the necessary rights to this jars.

Build and deploy the Pentaho WAR

open a console and go to the folder where you unpacked the archive and build the war files:

cd /tmp/biserver-manual-ce
ant war-pentaho-tomcat
ant zip-pentaho-style-war
ant zip-pentaho-steel-wheels-style-war
ant zip-pentaho-portal-layout-war

or

ant build-all

These commands should return BUILD SUCCESSFUL. The builded wars could be find in the /build/pentaho-wars folder. Copy the wars to tomcat webapps folder:
cd /build/pentaho-wars
sudo cp *.war /usr/share/tomcat5.5/webapps/
sudo cp tomcat/*.war /usr/share/tomcat5.5/webapps/

When copied this files Tomcat will deploy these wars automatically and create the pentaho directories:

ls -l /usr/share/tomcat5.5/webapps/ |grep ^d
/webapps/pentaho/
/webapps/sw-style/
/webapps/pentaho-portal-layout/
/webapps/pentaho-style/

Configure the Pentaho Solution folder

Next thing to do is to copy the Pentaho solutions directory. This directory is used for all reporting solutions Pentaho offers and is the place where all reporting files are stored fysically. We can use the /opt directory and we need to give the tomcat user read access to this directory:

sudo mkdir /opt/pentaho/
sudo cp -r /tmp/biserver-ce-3.5.0-STABLE/biserver-ce/pentaho-solutions /opt/pentaho/

and give ownership to the Tomcat user:

sudo chown -R tomcat5.5:tomcat5.5 /opt/pentaho/pentaho-solutions
sudo chmod -R 775 /opt/pentaho/pentaho-solutions

Pentaho configuration

First we have to tell pentaho where it can find the solution directory by editing the web.xml file and stop Tomcat, if you already didn’t:

sudo /etc/init.d/tomcat5.5 stop
sudo nano /usr/share/tomcat5.5/pentaho/WEB-INF/web.xml

Search for the solution-path entry and make sure that it looks like this:

<context-param>
<param-name>solution-path</param-name>
<param-value>/opt/pentaho/pentaho-solutions</param-value>
</context-param>

Also make sure that this entry points to the port Tomcat is listening at:

<context-param>
<param-name>base-url</param-name>
<param-value>http://localhost:8080/pentaho/</param-value>
</context-param>

If you want to make Pentaho available in your network or from the internet, you must replace localhost by the computer hostname or local IP, for the internet use the IP address provided by your ISP. Or you can use apache as reverse proxy…

Next to do is disable Tomcat security. For security reasons Tomcat forbids the execution of some jar files needed by Pentaho. It can be disabled by editing the Tomcat startup script and set security off:

sudo nano /etc/init.d/tomcat5.5

# Use the Java security manager? (yes/no)
TOMCAT5.5_SECURITY=no

Here you can read more about the Tomcat security manager…

Configure the Pentaho database connections and data

The Pentaho PCI and the manual installation comes with HSQLDB databases, we have to load that databases into MySQL:
- Quartz (Used for scheduling)
- Sampledata (the data used by the sample solutions)
- Hibernate (used for security and other settings)
You can find the datafiles in /tmp/biserver-manual-ce/pentaho-data/hsqldb

The archive delivers also datafiles in MySQL, Oracle10g and PostgreSQL format. But not all the data is completed, we miss all the sample data and some of the hibernate tables are incorrect for use by MySQL. So first we adjust the hibernate table which misses the user authentication tables:

cd /tmp/biserver-manual-ce/pentaho-data/mysql5
sudo nano create_repository_mysql.sql

and add at the end:

CREATE TABLE IF NOT EXISTS `GRANTED_AUTHORITIES` (
`USERNAME` varchar(50) collate latin1_general_ci NOT NULL,
`AUTHORITY` varchar(50) collate latin1_general_ci NOT NULL,
KEY `FK_GRANTED_AUTHORITIES_USERS` (`USERNAME`),
KEY `FK_GRANTED_AUTHORITIES_AUTHORITIES` (`AUTHORITY`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE IF NOT EXISTS `AUTHORITIES` (
`AUTHORITY` varchar(50) collate latin1_general_ci NOT NULL,
`DESCRIPTION` varchar(100) collate latin1_general_ci default NULL,
PRIMARY KEY (`AUTHORITY`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE IF NOT EXISTS `USERS` (
`USERNAME` varchar(50) collate latin1_general_ci NOT NULL,
`PASSWORD` varchar(50) collate latin1_general_ci NOT NULL,
`ENABLED` tinyint(1) NOT NULL,
`DESCRIPTION` varchar(100) collate latin1_general_ci default NULL,
PRIMARY KEY (`USERNAME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

import in MySQL (in this order):

mysql -u root -p
source create_repository_mysql.sql
source create_quartz_mysql.sql
source create_sample_datasource_mysql.sql
source load_sample_users_mysql.sql

Check if all queries succeeded without errors…
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hibernate          |
| mysql              |
| quartz             |
+--------------------+
6 rows in set (0.00 sec)
exit

Load the sampledata

The Pentaho sampledata for MySQL is missing for this version. You can find an older version somewhere on sourceforge but when I used that Pentaho gives some errors when trying the report samples. So I created a mysql script, download it here, and load this file with the source statement. You can check if the data loaded succesfully with this script:

+---------------------+------+
| table_name          | rows |
+---------------------+------+
| CUSTOMERS           |  122 |
| CUSTOMER_W_TER      |  122 |
| DEPARTMENT_MANAGERS |    4 |
| DIM_TIME            |  265 |
| EMPLOYEES           |   23 |
| OFFICES             |    7 |
| ORDERDETAILS        | 2996 |
| ORDERFACT           | 2996 |
| ORDERS              |  326 |
| PAYMENTS            |  272 |
| PRODUCTS            |  110 |
| QUADRANT_ACTUALS    |  148 |
| TRIAL_BALANCE       |   22 |
+---------------------+------+

Next thing todo is configure the datasources:

sudo nano /usr/share/tomcat5.5/webapps/pentaho/META-INF/context.xml

and adjust to settings to your MySQL settings:

<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/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/quartz"
validationQuery="select 1"/>

Make sure that the validation query above is right.

In older pentaho versions we had to define each datasource systemwide, but that’s reduced to one:
edit server.xml and add this lines before </host>:

sudo nano  /etc/tomcat5.5/server.xml


<Context path="/pentaho" docbase="webapps/pentaho/">
<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource" maxActive="20"
maxIdle="5" maxWait="10000" username="pentaho_user" password="password" validationQuery="select 1"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/quartz" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
</Context>

Security, changing to JDBC Security DAO

Out of the box Pentaho does security with Hibernate-based security data access object (DAO). In earlier steps we made the hibernate tables and we want to use them.
The information on the Pentaho wiki is outdated for 3.5. Follow this steps:

1. Edit the Spring XML files to use the JDBC DAOs instead of the Hibernate ones. Change every bean resource from hibernate to jdbc

cd /opt/pentaho/pentaho-solutions/system/
sudo nano pentaho-spring-beans.xml


<beans>
<import resource="pentahoSystemConfig.xml" />
<import resource="adminPlugins.xml" />
<import resource="systemListeners.xml" />
<import resource="sessionStartupActions.xml" />
<import resource="applicationContext-spring-security.xml" />
<import resource="applicationContext-common-authorization.xml" />
<import resource="applicationContext-spring-security-jdbc.xml" />
<import resource="applicationContext-pentaho-security-jdbc.xml" />

<import resource="pentahoObjects.spring.xml" />
</beans>

2. Edit applicationContext-spring-security-jdbc.xml:

sudo nano applicationContext-spring-security-jdbc.xml

At the end of the file you must change to this:

<bean id="dataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>

3. Next thing is to edit the hibernate setting file:

sudo nano hibernate/hibernate-settings.xml

and change config-file tho:
<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

sudo nano hibernate/mysql5.hibernate.cfg.xml

look for the lines beneath and change it to your settings:

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

4. Correct the password in the user table

We must realize that the sample users passwords are encrypted and needed to be known by you. We can store and read the password in plain text or use an encryption method. This can be changed by adjusting the spring configuration file.

sudo nano /opt/pentaho/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml

If you want to use MD5 hashing look for<bean id=”passwordEncoder”  and change class to:

<bean id="passwordEncoder" class="org.springframework.security.providers.encoding.Md5PasswordEncoder" />

Make sure that your user password in your database is also encoded by MD5.

Don’t ever use plain text passwords for production…

5. Change the datasource for the sample solutions based on the sampledata.

The datasource for the sampledata database is by default set to the HSQL database. We must update it by edit the table DATASOURCE in the database HIBERNATE:

DRIVERCLASS: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost:3306/sampledata
QUERY: SELECT 1

or by running this sql (you can use phpmyadmin or SQuirreL):

UPDATE DATASOURCE SET DRIVERCLASS = 'com.mysql.jdbc.Driver', URL = 'jdbc:mysql://localhost:3306/sampledata' , QUERY = 'SELECT 1' WHERE NAME = 'SampleData'

Finish the installation

Just in case we forget setting some rights:

sudo chown -R tomcat5.5:tomcat5.5 /opt/pentaho/pentaho-solutions
sudo chown -R tomcat5.5:tomcat5.5 /usr/share/tomcat5.5/webapps/pentaho$

Realize that the security level used here isn’t enough for a production environment. There are several weak spots.
First weakness is the users table in the Hibernate database. Passwords are stored there in a way that an user with read permissions can see if some passwords are the same by looking at the hash text. Instead use a salt or another more secure messure…
Second is all the default passwords are stored in the XML files we edited before. Change this passwords and maybe change even the usernames in the files and in the dabases.
Third is the file ownership and permissions. File permissions must be set to read and write only for the tomcat user and never for some other user.

Now restart tomcat:

sudo /etc/init.d/tomcat5.5 restart

And browse to your fresh installed Pentaho BI server

<bean id=”passwordEncoder” class=”org.springframework.security.providers.encoding.Md5PasswordEncoder” />

Comments (17)

Sebastian SingerSeptember 25th, 2009 at 10:36

All right, Pentaho is up and running.

But I cannot log in. The example users do not fit as expected after changing to JDBC DAO.
But new users I configured in mysql do not fit either. I setup a new user in hibernate.USERS with password encrypted in MD5 and did the same in mysql.user. Strange enough that the pentaho users like “pentaho_user”, “pentaho_admin” and “hibuser” appear in mysql.user but not in hibernate.USERS.

Any help appreciated.

yanSeptember 28th, 2009 at 02:43

hello,
I am seeking your help
I install pentaho as indicated but I can not connect with the accounts of utilisateurs.I ‘have the following message “error opening”

gaspardSeptember 28th, 2009 at 11:02

@ Sebastian Singer

Make sure that all database connections to the hibernate points to your MySQL database. The users “pentaho_user”, “pentaho_admin” and “hibuser” are database users and are used by Pentaho to access the database and not to log in Pentaho webserver.

Can you log in with “Joe” and did you edit that password with the MD5 hash?

gaspardSeptember 28th, 2009 at 11:07

@ yan

With user account did you use and what kind of password encryption?

can you post the error message from the tomcat log on http://pastebin.com/

yanSeptember 28th, 2009 at 12:31

Hello,
Thank you for your response. I use the user account “joe” and I add

in the file” / opt / pentaho / pentaho-solutions / system / ApplicationContext-spring-security-jdbc.xml”. I have the error that I sent you.

yanSeptember 28th, 2009 at 13:12

When I don’t define the resource “SampleData” in server.xml of tomcat, I have the second error message.

DMSeptember 29th, 2009 at 09:19

any .deb package?

gaspardSeptember 30th, 2009 at 12:14

@DM: nope, but it isn’t to difficult

ramkiOctober 3rd, 2009 at 11:53

These are few things that I had to change / do to get it work on my Ubuntu 9.04 and its pentaho biserver-ce-3.5.0.RC2 :

My First change:
————————————————————————–
2. Edit /opt/pentaho/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml:

This one needs a class attribute to be set:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/hibernate" />

<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>

————————————————————————–

My Second Change:
————————————————————————–

Configuring Data Sources:

putting the conf under /usr/share/tomcat5.5/webapps/pentaho/META-INF/context.xml did not work for me. I put it inside /etc/tomcat5.5/server.xml <Host> tag.

My /etc/tomcat5.5/server.xml ’s Host configuration:

<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true" xmlValidation="false" xmlNamespaceAware="false">

<Context path="/pentaho" docbase="webapps/pentaho/">
<!--<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>-->

<Manager pathname=""/>

<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/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/quartz"
validationQuery="select 1"/>-->

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource" maxActive="20"

maxIdle="5" maxWait="10000" username="pentaho_user" password="password" validationQuery="select 1"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost/quartz" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>

</Context>
</Host>

————————————————————————–

My Third Change:
————————————————————————–
In the pentaho/WEB-INF/web.xml need to add the following to avoid the
"WARNING: Security role name PENTAHO_ADMIN used in an <auth-constraint> without being defined in a <security-role>" error. While it says warning,
it will throw error next.

The following change should be done just before <security-constraint> tag.

<security-role>

<description>security role</description>
<role-name>PENTAHO_ADMIN</role-name>
</security-role>

————————————————————————–

My Fourth Change:
————————————————————————–
If you get an error in the tomcat logs like: "SEVERE: No Store configured, persistence disabled", you need to do the following in /etc/tomcat5.5/server.xml:

<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>

Change above line to:

<!--<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>-->
<Manager pathname=""/>

If you have copy & pasted, my second change, you would have this already.
————————————————————————–

My Fifth Change:
————————————————————————–
I got an error in the logs saying: "SEVERE: Error reading tld listeners java.lang.NullPointerException", all I had to do is, remove the commons-logging-1.1.jar from /usr/share/tomcat5.5/common/lib as it seems to be conflicting with that comes with pentaho. I guess you should have only one of it in there, so delete the one that you don’t need.
————————————————————————–

After all these changes, pentaho came up and I could see the application in 8080. But ehcache configuration is not right I guess, it keeps throwing

Error was libloader-data Cache: The Disk store is not active.
net.sf.ehcache.CacheException: libloader-data Cache: The Disk store is not active.

This ehcache error has filled up the log to the size of 33 megs in less than 10 requests in frontend!

Anyway, will dig deeper. Good Luck.

-ramki

gaspardOctober 6th, 2009 at 09:08

@ ramki thnx…

I don’t see any difference in your first change ;) Strange that the data connections didn’t work for you… Did you set all hibernate configuration files?

I didn’t get the SEVERE errors, which tomcat version do you use?

I will look into the cache problem which I solved by configuring thetomcat startup file… I will post this later…

ataOctober 7th, 2009 at 16:11

Hi,

Would your configuration work with tomcat 6?

Thanks

colorfoolOctober 8th, 2009 at 12:13

I followed your very nice instructions and nearly everything is working. Only the connection to SampleData doesn’t work (Access denied for user ‘pentaho’@'localhost’ (using password: YES)).

My user is called pentaho, so that is correct. Also the other two connections are using the exact same user, password and host and they work just fine, only the SampleData connection doesn’t work. I have checked the credentials many times. I also tried logging into MySQL from the command line with the same settings, no problem there.

Since the SampleData datasource is the only one which is defined in the database instead of in a config file I am thinking: Could it be that the password is somehow encrypted before it is sent to MySQL?

DaniloOctober 19th, 2009 at 19:26

Hello,

I use this tutorial but change all database configurations to use postgres. Pentaho is running but when I try to login it gives an error in a dialog. When I check the logs (pentaho, catalina) there are no errors.

Any ideas what could be wrong?

gaspardOctober 20th, 2009 at 16:06

@ata

I will try this soon and then update this post

gaspardOctober 20th, 2009 at 16:07

@danilo

Did you look in the system log, tomcat will sometimes put errors there…

MarioOctober 21st, 2009 at 04:37

I found this other howto guide at http://www.prashantraju.com/ and admittedly have only tried it on an windows machine, but I actually ended up with a working server.

sofenDecember 14th, 2009 at 19:10

Il faut supprimer ces 2 lignes :

et les remplacer :

Leave a comment

Your comment