Pentaho and MySQL: connection lost after 8 hours

After configuring Pentaho to use MySQL as database backend you eventually discover that Pentaho will give some errors that only clear with a tomcat restart:

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error

This means that Pentaho has lost the connection to the repository. From the Pentaho wiki:

MySQL connections timeout by default every 8 hours. If Pentaho sits idle for more than 8 hours, its connection to the repository silently drops dead without Pentaho’s knowledge.  When something stirs Pentaho after an 8 hour or greater time lapse, Pentaho assumes that its connection is still alive and currently there is no default mechanism in place that handles bringing a connection back to life.

The solution turns out to be fairly easy.  What is required is a piece of software to manage the database connection pool and keep the connections alive.  The recommended method is to use c3p0.

Installation

We have to download the java binary and install it in your webapps/pentaho/WEB-INF/lib folder.

#stop tomcat
sudo /etc/init.d/tomcat5.5 stop

# download c3p0 somewhere and unzip, copy it to
wget http://downloads.sourceforge.net/project/c3p0/c3p0-bin/c3p0-0.9.1.2/c3p0-0.9.1.2.bin.zip?use_mirror=kent
unzip c3p0-0.9.1.2.bin.zip
sudo cp c3p0-0.9.1.2/lib/*.jar /usr/share/tomcat5.5/webapps/pentaho/WEB-INF/lib/

# give tomcat user ownership
sudo chown -R tomcat55\:  /usr/share/tomcat5.5/webapps/pentaho/WEB-INF/lib/

Configure

We have to configure Hibernate to use c3p0, therefore we have to edit the configuration file mysql5.hibernate.cfg.xml, located in the pentaho-solutions/system/hibernate folder.

You will insert the following text just after the <session-factory> tag and just before the <!– MySQL Configuration –> comment.

<!–  hibernate c3p0 settings  –>

<property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>
<property name=”hibernate.c3p0.acquire_increment”>3</property>
<property name=”hibernate.c3p0.idle_test_period”>10</property>
<property name=”hibernate.c3p0.min_size”>5</property>
<property name=”hibernate.c3p0.max_size”>75</property>
<property name=”hibernate.c3p0.max_statements”>0</property>
<property name=”hibernate.c3p0.timeout”>25200</property>
<property name=”hibernate.c3p0.preferredTestQuery”>select 1</property>
<property name=”hibernate.c3p0.testConnectionOnCheckout”>true</property>

Save the file and restart Tomcat.

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” />

Open Source BI Survey by Mark Madsen

Mark Madsen has publiced an Open Source BI Survey. Results will be published at the MySQL Conference April 23, 2009 (depends on sufficient participation). Spread the word and inform your connections.

The survey an be found here: http://www.surveymonkey.com/s.aspx?sm=wLGr_2bLGwTIFfHpPtuC9PVw_3d_3d

Pentaho releases BI Suite 2.0.0-stable

After the release of the enterprise edition there was some silence. But only 2 days ago you can download the 2.0 version of Design studio. This was the last 2.0 version of the BI suite, now you can download: Business Intelligence Server, Design Studio, Pentaho Metadata, Report Designer

What’s new? You can read about it, here.

I will post some reviews later on…

How to create and publish Pentaho reports on a MySQL datasource

This how-to will guide you through your first own made pentaho report. The documentation and the Pentaho-wiki aren’t very useful for the beginning Pentaho developer.

This how-to is developed on Ubuntu 8.10, MySQL 5.0 and Pentaho 1.7.1 GA. Most things will work on windows, offcourse with different commands.

In this guide I use the MySQL Sakila sample database. Installation is simple:

  1. download the tar or zip and extract it on your desktop
  2. Open a console and go to the created directory.
  3. connect to MySQL: mysql -u root -p
  4. import by execute two commands: source sakila-schema.sql , source sakila-data.sql
  5. Give the user ‘pentaho-user’ rights to this database

Now that we have data we have to create the Pentaho data sources. Open the server.xml with an editor:

sudo gedit /etc/tomcat6/server.xml

and add:

<Resource name="jdbc/sakila" 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/sakila" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>

Before we want to make any reports we have to make sure we can publish reports on the Pentaho BI server. Pentaho keeps this password in the file “publish_config.xml” found in pentaho-solutions/system/. Default this password is blank… but I will make it the same as the Pentaho default:

<publisher-config>
<publisher-password>password</publisher-password>
</publisher-config>

You should know that this file is in the pentaho-solutions directory which is usually made readable for everyone. In a production environment further action must be taken to secure the BI server.

Reportdesign

There are several ways to create Pentaho reports:

  1. Using the design studio (PDS)
  2. Using the report designer (PRD)
  3. Using the report design wizard
  4. Using the online AdHoc Reporting tool (WAQR)

Number 3 and 4 are maybe useful for end users, but have limited capabilities to create reports as an alternative against the commercial tools. The report design wizard can be executed from the design studio and report designer. In this example I will use the report design wizard only because it’s the quickest way to create a good looking example:

  1. Start the report designer
  2. Start Report Design Wizard under file menu or press CTRL-Shift-n
  3. The report design wizard opens with step 1. Choose a title and description (”films”, “list of films”), press next.
  4. choose connection type JNDI an click Add, make it something like this (fill in your specific  username/password and test it):
    New JNDI connection
  5. Specify Query String or use the query designer:
    SELECT title, category, price , length FROM film_list
  6. Make sure you select the created connection an click Next
  7. Add the available items to the Selected Items (default) and click Next
  8. Click Next again
  9. click OK

We now have a simple report design with a list and a total. You can preview the report by clicking the preview button or click one of the two play icons or press CTRL-r.

Save the report.

Publish it

Now we can publish it to the Pentaho BI server by pressing CTRL-shift-p. Fill in the right credential (defaults are: publish= password; username= joe; pw= password). Click OK.

Fill report name, description and choose a location ( pentaho-solution/samples ) an click publish… Ready!

Next?

This was just a very short howto and this example isn’t very useful, but it will guide you in the right direction. Next things todo can be: design (dynamic) parameter driver reports, create your own solution repository, dashboard design…

How to manually install Pentaho BI server (Linux)

This post is outdated, here you can find the 3.5 version

The goal is a working Pentaho BI server environment which include:

  • Tomcat6
  • MySQL 5.0
  • Pentaho BI server 1.7.1
  • Ubuntu 8.10

First we have to make sure we have a working JVM:

java -version

output:

java version "1.6.0_0"
IcedTea6 1.3.1 (6b12-0ubuntu6) Runtime Environment (build 1.6.0_0-b12)
OpenJDK 64-Bit Server VM (build 1.6.0_0-b12, mixed mode)

If you don’t have java you can install it directly:

apt-get install openjdk-6-jre openjdk-6-jdk

or as a dependency of Tomcat:

apt-get install tomcat6 tomcat6-user tomcat6-examples tomcat6-admin tomcat6-docs

After installing verify the installation on http://localhost:8080

The port on which tomcat is running can be adjusted:

1. stop tomcat
2. edit the configuration file
sudo /etc/init.d/tomcat6 stop
sudo nano /var/lib/tomcat6/conf/server.xml

search for <Connector port=”8080″ and make it whatever you want….

we must also change the tomcat users to manage Tomcat:

sudo nano /var/lib/tomcat6/conf/tomcat-users.xml

and add this line before </tomcat-users> and fill the dots:

<user username="...." password="o..." roles="admin,manager"/>

start tomcat with

sudo /etc/init.d/tomcat6 start

Test the user settings here by starting Tomcat manager: http://localhost:8080/manager/html

The next thing we have to do is to setup the MySQL databases needed by Pentaho. First we have to downloaded the pentaho file with the data. All Pentaho files can be found on SourceForge. Click on Business Intelligence Server and then click on 1.7.1-GA. Download  pentaho_demo_mysql5-1.7.1.tar.gz and save on it somewhere (Desktop). Unpack it and search for /data/SampleDataDump_MySql.sql and go this location in a console.

Load the sql file by:

mysql -u root  -p

source SampleDataDump_MySql.sql

….
….
Query OK, 0 rows affected, 1 warning (0.00 sec)

Check if import succeeded:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hibernate          |
| mysql              |
| quartz             |
| sampledata         |
+--------------------+
6 rows in set (0.00 sec)

Next thing to do is installing Pentaho BI server.

First download:

  1. pentaho_j2ee_deployments-1.7.1.zip
  2. pentaho_solutions-1.7.1.zip
  3. mysql-connector-java-5.1.6.tar.gz

Install MySQL connector

Go to the created directory and search for the file named  “mysql-connector-java-5.1.6-bin.jar” and copy this file to the tomcat lib directory:

sudo cp mysql-connector-java-5.1.6-bin.jar /usr/share/tomcat6/common/lib/

Deploy Pentaho J2EE packages

Go to the pentaho_j2ee_deployments directory and build the WAR files with the following commands :

ant zip-pentaho-style-war
ant zip-pentaho-steel-wheels-style-war
ant zip-pentaho-portal-layout-war
ant war-pentaho-tomcat-mysql

These commands should return “BUILD SUCCESSFUL” when succesful and created the following war files:

build/pentaho-wars/pentaho-portal-layout.war
build/pentaho-wars/pentaho-style.war
build/pentaho-wars/sw-style.war
build/pentaho-wars/tomcat/mysql5/pentaho.war

Copy this files to the tomcat directory:

sudo cp build/pentaho-wars/*.war /var/lib/tomcat6/webapps
sudo cp build/pentaho-wars/tomcat/mysql5/*.war /var/lib/tomcat6/webapps

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

  • pentaho/
  • sw-style/
  • pentaho-portal-layout/
  • pentaho-style/

Next thing to do is to install the Pentaho solutions directory. We can use the /opt directory. Then we need to give the tomcat user read access to this directory:

sudo mkdir /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:

sudo nano /var/lib/tomcat6/webapps/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:

sudo nano /var/lib/tomcat6/webapps/pentaho/WEB-INF/web.xml

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

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/tomcat6

# Use the Java security manager? (yes/no)
TOMCAT6_SECURITY=no

Here you can read more about the Tomcat security manager

Pentaho data sources configuration

stop tomcat:

sudo /etc/init.d/tomcat6 stop

edit the tomcat server.xml file and add this lines before </host>:

sudo nano /etc/tomcat6/server.xml

<Context path="/pentaho" docbase="webapps/pentaho/">
<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource" maxActive="20"
maxIdle="5" maxWait="10000" username="hibuser" password="password" validationQuery="Select 1"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/hibernate" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/SampleData" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<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"/>
<Resource name="jdbc/Shark" 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/shark" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/SampleDataAdmin" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/solution1" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/solution2" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/solution3" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/solution4" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/solution5" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/datasource1" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/datasource2" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/datasource3" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/datasource4" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
<Resource name="jdbc/datasource5" 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/sampledata" testOnBorrow="true" testWhileIdle="true" timeBetweenEvictionRunsMillis="10000"
maxEvictableIdleTimeMillis="60000"/>
</Context>

Also make sure the references and username/password to your database in /var/lib/tomcat6/webapps/pentaho/WEB-INF/classes/hibernate.cfg.xml are correct:

<!–  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.pentaho.repository.MySQL5InnoDBDialect</property>
<property name=”connection.username”>hibuser</property>
<property name=”connection.password”>password</property>

Now you can restart Tomcat and browse to your Pentaho installation:

sudo /etc/init.d/tomcat6 start

http://localhost:8080/pentaho

This post is outdated, here you can find the 3.5 version

SQLpower Power*Architect

Browsing the web I came acros a new open source modelling tool called Power*Architect. This java application is a cross-platform, open-source data modeling tool with a GUI and an embeddable API. The source is released under GPLv3 and the source is maintained in google code. The latest version 0.9.11 can be downloaded here.

After unpacking the tool you simply have to run the jar file to start the application. The application starts with an empty canvas, an object browser and a menu bar. The first thing we have to do is add some connections.

Power*Architect comes with 4 standard JDBC already built in (MySQL, Postgres, MS SQL, and HSQLDB). The second tab has some built in support for a Kettle repository. After saving the connection you can add the source connection to the project by clicking it in the menu item connections. For some reason all the database catalog appear in the object pane (left). Clicking a catalog will sometimes result in an error… but most times it works and shows the last level of the tabel properties.

Model design

Table creation is simple by clicking “insert table”. Columns are added by right-clicking on the table and select new column (or type ‘C’). Adding many tables can be a long process because PA misses the function to edit all columns without selecting one by one…

Relationships are simply added by selecting identifying or not identifying relationship from the right sidebar. Identifying the source table and point to the destination simply adds the relationship. The properties can be changed in a separate window.


Reverse enginering

After dragging a database to the canvas all tables appears on the canvas. There is an automatic layout button, which works pretty good.

Forward Enginering

Forward engineering can be done by the forward engineering menu item. It can be done for the supported databases. The forward engineering generates the target DLL in SQL with tables and constraints. Errors are captured by a rudimentary error handling without any feedback.

Profiling

Power*Architect has a simple profiling tool. Each table or all objects can be selected to be profiled. Th profiler starts with a profiling process overview window. This windows show the progress made and after clicking “view selected”. The profile details window shows some basic profiling results like row count, min/max length, % unique values per table column.The result can be exported to HTML, csv or pdf.

Other function included:

  • Comparison of data models
  • SQL runner (execute in database)
  • Copy table data
  • Some ETL functions… (needs further investigation)

Conclusion

I think Power*Architect is usable for small database design jobs. It has potential to be an indispensable tool for the database designer. I don’t like the strange errors when adding a database connection. Furthermore it misses a way to make submodel, because the model tend to become invissible when zooming out. What I like is the clear design of the application and some function like table data and shema comparison, although I didn’t test that all the way. I am going to use this in future open source projects.

Cons

  • strange errors
  • single column adding

Pros

  • Multi platform
  • Clear table and relationship design
  • Forward and reverse ingeneering
  • Profiling function
  • JDBC drivers included
  • It works :)

List of SQL query clients

SQuirreL SQL Client

MySQL Query Browser

List of example databases

employee data MySQL

world database MySQL

sakila database MySQL

menagerie databaseMySQL

List of data modeling tools

MySQL Workbench (DBDesigner 5)

DBDesigner 4

Clay Database Modeling

Mogwai ERDesigner NG

Power*Architect