Open Source ERP Guru

Pentaho is a leading Open Source BI suite. It contains all components required from an enterprise-grade BI solution-reporting,dashboards,analytics engine,ETL and data mining. The reason I am covering Pentaho here is that I believe an open source BI can complement the open source ERP solutions I cover here.

The Pentaho offering is a classical, ‘modern’ open source model – you can download,install and use a ‘basic’ edition of the software for free, but Pentaho charges for additional ‘enterprise class’ functionality, support and some advance administration options.

The installation itself (I am installing Pentaho 1.7 GA) was not as straight forward as I expected and required some manual work. Because of that, I will write a complete installation guide, hoping it will save some time and effort to others who plan to install Pentaho. I will run the Pentaho server on my Linux Ubuntu Box.

Pentaho is written in Java, running on Jboss application server. That makes integrating it with Openbravo or Compiere somewhat easier, both written in Java as well. However, BI solutions are platform agnostic – they only rely on the underlinying data layer and are independent of the programming language used to write the ERP system.

Pentaho is a BI solution and it requires a database that will contain the data to be analyzed or mined. For that purpose, I will use a Mysql database, provided by Pentaho, with some sample data.

BTW – Pentaho delivers several out of the box default users and passwords. They can be found under the ‘Valid Users’ drop down menu on the upper-right corner of your Pentaho installation homepage.

Sample Pentaho Report

Pentaho Installation Steps

Make sure you have an Ubuntu 8.04 Linux installation.

Install Java:

server01$ sudo apt-get install sun-java5-jdk

Install MySQL:

server01$ sudo apt-get install mysql-server mysql-client

Create a directory for your Pentaho installation

server01$ mkdir /opt/pentaho

server01$ cd /opt/pentaho

Download the Pentaho BI Suite:

server01$ wget http://dfn.dl.sourceforge.net/sourceforge/pentaho/pentaho_demo_mysql5-1.7.0.GA.tar.gz

gunzip and untar the Pentaho archive

server01$ gunzip -c pentaho_demo_mysql5-1.7.0.GA.tar.gz | tar xvf -

Your Pentaho startup script is:

/opt/pentaho/pentaho-demo/start-pentaho.sh

Create Pentaho Mysql Database

Before we can get Pentaho to work, we need to create the Mysql database. This database will contain Pentaho metadata information, users and access rights along with some sample data we will later use to create reports, analyze, etc. The archive we downloaded contains an sql script we will execute to create the database,users and sample data.

I was not able to use the supplied script ( if you were able to use with Mysql,please let me know how. It might work on database other than Mysql).

The Pentaho Mysql database creation script can be found at:

http://source.pentaho.org/svnroot/pentaho-data/trunk/mysql5/SampleDataDump_MySql.sql

This script would not run on my machine (Mysql 5.0.51a-3ubuntu5.1) as-is.

First, the script starts by dropping some users – if they do not exist, you get error messages. If this is your first installation of Pentaho on your MySQL database, comment the first 4 lines that drop existing users.

Another issue the script has is the MEMORY type table it tries to create. The syntax is just wrong. The script attempts to create a table called DATASOURCE of type MEMORY.

Replace the sql command that creates the DATASOURCE table to

CREATE TABLE DATASOURCE(JNDINAME VARCHAR(50) NOT NULL PRIMARY KEY,MAXACTCONN INTEGER NOT NULL,DRIVERCLASS VARCHAR(50) NOT NULL,IDLECONN INTEGER NOT NULL,USERNAME VARCHAR(50) NOT NULL,PASSWORD VARCHAR(50) NOT NULL,URL VARCHAR(100) NOT NULL,QUERY VARCHAR(100) NOT NULL,WAIT INTEGER NOT NULL) ENGINE=MEMORY;

That should do it. Now just run the script:

server01$ cd data

server01$ mysql -u root -p < SampleDataDump_MySql.sql

Your Mysql database should now be populated with Pentaho metadata and sample data.

Connecting to Pentaho server from remote computer

If you plan to use a separate computer as a Pentaho client, there is one more thing you need to take care of – you need to enable access from remote computers to the Pentaho server (by default,both Pentaho and the Jboss application server limit access to the servers’ localhost only.).

To do that, edit the following file:

1. In the file : /opt/pentaho/pentaho-demo/jboss/server/default/deploy/pentaho.war/WEB-INF/web.xml,change the value of the property ‘base-url’ from localhost to your Pentaho server hostname or IP address. You can also change the port Pentaho will listen on from the default 8080 to something else-that would be useful if you have other applications already listening on this port.
2. Change the Pentaho startup script (start-pentaho.sh) so that the Jboss server binds to your hostname or IP Address instead of the default binding to localhost. To do that, add your the Pentaho server hostname or IP Address to the -b switch. Your joss start command should look like : sh run.sh -b server01

Starting Pentaho

You are now ready to start Pentaho, by executing:

server01$ /opt/pentaho/pentaho-demo/start-pentaho.sh

Take a look at the trace on the screen, make sure you do not have any errors(you will get many WARN messages, saying default values are used).

Point your browser to the URL:

http://server01:8080/pentaho/