Install Pentaho BI on Ubuntu


PLEASE NOTE THAT THIS IS INCOMPLETE AND DOES CONTAIN SOME ERRORS AT THE MOMENT.

I have run out of time to carry this on but hope to get back to it soon.  The basic principle is there though.  If you just want to get it working forget the MySQL addition and focus on the basic installation.

There are many references of how to install Pentaho on Ubunto.  This I hope is the exact way, including all the commands used.  I hope that this will create an exact script with no assumption of the use of a Linux distro.

Saying that the only caveat is that you must install ubuntu on your own. A good guide can be found here UbuntuGeeks

If you are new to Linux I would suggest getting a dummies guide to give you a quick overview.  Also a little understanding of vi never goes amiss.  vi is a text editor and has some peculiarities.  There is an overview of some of the basic commands in these guides.

This installation is based on Ubuntu 9.04 server which I believe is based on Debian 5.0

The kernel version is uname -r 2.6.28-11-server

Conventions used in this help italics higlight the command to type in, underline indicateds a screen output.

The install steps are

  1. Install Java
  2. Install MySQL
    1. Server
    2. Client
  3. Install Data Warehouse Design Tool (optional)
  4. Install SQL client tool (optional)
  5. Install Software
    1. Download unpack
    2. Create users and groups
    3. Configure Tomcat
    4. Create startup script
  6. Create Database schemas and sample data
    1. Run scripts

Install Java

Pentaho requires Java 6 to be installed

apt-get install update

apt-get install sun-java6-jdk

If you sit behind a firewall and proxy server you have too choices

Allow the server through the firewall for ports 80/443/20/21 or configure apt-get with proxy details.  I have never made apt-get go through a proxy server.  I believe a file called apt.conf is required and can be found in /etc/apt.

A google of apt-get through proxy server should get you the details you need. (cntlm is required, there is later posting that decribes how to set this up)

To check that Java installed properly

java -version

The out out should look something like this

java version “1.6.0_0″
OpenJDK Runtime Environment (IcedTea6 1.4.1) (6b14-1.4.1-0ubuntu7)
OpenJDK Client VM (build 14.0-b08, mixed mode, sharing)

I have never had Java not install so if tyou get an errot message I am affraid Google is your only help

We need to set the environment variable now, to find the correct syntax

ls -l /usr/lib/jvm

default-java -> java-6-openjdk
java-6-openjdk
java-6-sun -> java-6-sun-1.6.0.20
java-6-sun-1.6.0.20

This shows that java-6-sun is a symbolic link to java-6-sun-1.6.0.20.  This needs to be added to the environment file.

sudo vi /etc/environment

sudo is only required if you are not logged on as root. Insert this following line.

JAVA_HOME=“/usr/lib/jvm/java-6-sun“

This is where a little knowledge of vi is important.

Install MySQL

Server

apt-get install mysql-server-5.1

The following extra packages will be installed:
bsd-mailx libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmysqlclient15off libmysqlclient16 libnet-daemon-perl libplrpc-perl mailx
mysql-client-5.1 mysql-common postfix ssl-cert
Suggested packages:
dbishell libipc-sharedcache-perl tinyca procmail postfix-mysql postfix-pgsql
postfix-ldap postfix-pcre sasl2-bin resolvconf postfix-cdb mail-reader


The following NEW packages will be installed
bsd-mailx libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmysqlclient15off libmysqlclient16 libnet-daemon-perl libplrpc-perl mailx
mysql-client-5.1 mysql-common mysql-server-5.1 postfix ssl-cert
0 upgraded, 14 newly installed, 0 to remove and 81 not upgraded.
Need to get 25.4MB of archives.
After this operation, 59.6MB of additional disk space will be used.

Do you want to continue [Y/n]?

Don’t worry and enter Y

This will install MySQL

Part of the setup will include the setting of the mysql root password. DO NOT LOOSE THIS

It then will prompt for a Postfix Configuration (email).  If you don’t wish to try to configure this select No Configuration.  If you have mail server the pick the relevant one.  This guide does not cover this aspect.

To check if the server is working

mysql -u root -p      Enter password when prompted

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.1.31-1ubuntu2 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

exit

Client

To manage MySQL you need a client. There are two ways that you can administer the server and SQL client and a product called webmin.  Webmin is a web interface for administering linux.  The SQL client can either be installed on windows or Linux.

This guide will install Webmin and a windows SQL client.

To install Webmin see the Webmin guide

The windows client can be downloaded from MySQL GUI

Install Data Warehouse Design Tool (optional)

Install SQL client tool (optional)

Install Software

Download / Unpack

Obtain the sofwtare from Pentaho this will give you the latest download stable release.

At the time of writing 3.6.0 was stable.  Navigate to the software folder in root, if there is not a software folder create one.

either

cd /software

or

mkdir /software

cd /software

wget http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/3.6.0-stable/

This will download a gzip file called biserver-ce-3.6.0-stable.tar.gz

To unzip the file

gzip -d biserver-ce-3.6.0-stable.tar.gz

To unpack the file

tar -xvf  biserver-ce-3.6.0-stable.tar.gz

This will create two directories, these need to be move to a new area

sudo mkdir /opt/pentaho

mv administration-console /opt/pentaho/

mv biserver-ce /opt/pentaho/

Create users and groups

This part is not strictly necessary but for security reasons should be done, especially on a production environment.

In summary it creats a group, user and changes the permissions on the pentaho directory

addgroup pentaho

adduser –system –ingroup pentaho –disabled-login pentaho

chown -R pentaho:pentaho /opt/pentaho

Remeber sudo in front of the commands if you are not root user.

To start pentaho

cd /opt/pentaho/administration-console

sudo -u pentaho $JAVE_HOME ./start-pac.sh

Even if you are root use this command as then pentaho runs under the pentaho user. If you get and error saying the port 8080 in use then you need to reconfigure Tomcat to listen on another port

Configure Tomact

This only needs to be done if port 8080 is being used.  I have never had this so I can really help.  Here are some instructions though to help.  These have not been tested by me, but come form a reliable source.

cd /etc/tomcat6

vi server.xml

look for entries regarding port 8080 anc change to say 9090

My file looks like this

<Connector port=”8080″ protocol=”HTTP/1.1″
connectionTimeout=”20000″
redirectPort=”8443″ />
<!– A “Connector” using the shared thread pool–>
<!–
<Connector executor=”tomcatThreadPool”
port=”8080″ protocol=”HTTP/1.1″
connectionTimeout=”20000″
redirectPort=”8443″ />

You also need to change the web.xml file.  If you created a pentaho user this can be found in

/opt/pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF

if you are running as root

/opt/pentaho/biserver-ce/tomcat/webapps/ROOT/WEB-INF

vi web.xml

chnage the base-url paramater to the port number specified abouve

my file

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

Create Startup Script

I am not a expert in scripting so this is lifted for another source it is basic and works to get us started.

cd /opt/pentaho

vi pentaho-run.sh

#!/bin/sh
# go to the pentaho home
cd /opt/pentaho/biserver-ce
# set up command for pentaho user, set java environment
cmd=.sudo -u pentaho JAVA_HOME=usr/lib/jvm/java-6-sun
JAVA_OPTS=-Djava.awt.headless=true.
case .$1. in
start)
# run the original pentaho start script
$cmd ./start-pentaho.sh >> pentaho-demo.log &
;;
stop)
# run the original pentaho start script
$cmd ./start-pentaho.sh >> pentaho-demo.log &
;;
restart)
$0 stop
$0 start
;;
*)
echo .Usage: $0 {start|stop|restart }.
exit 1
esac
exit 0

Once the file is created then make it executable and copy into /etc/init.d so it can be autorun

chomd 775 pentaho-run.sh

cp pentaho-run.sh /etc/init.d/

The next line creates all the links so that when the system is started and stopped the services starts

update-rc.d pentaho-run.sh defaults

You can also use this to remove the service

update-rc.d -f pentaho-run.sh remove

Create Database schemas and sample data

You can connect to many database MySql and Oracle being the two that I am interested in. For now we will configure the MySQL and then latter Oracle.

Run scripts

MySQL

/opt/pentaho/biserver-ce/data/mysql5

mysql -h localhost -u root -p < /opt/pentaho/biserver-ce/data/mysql5/create_repository_mysql.sql

Next to configure is Quartz which is the scheduling part.  There is the assumption that the pentaho user was created above.

cd /opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/

cp context.xml.old

vi context.xml

Chnage the file to look like this

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

After Quartz something called Hibernate needs configuring

It uses the same config file as above, edit this so the entry is as follows

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

cd /opt/pentaho/biserver-ce/pentaho-solutions

cp hibernate-settings.xml hibernate-settings.xml.old

Change the line to read

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

There following is only needed for production environments where the server will be on 24/7.  There is a problem with a connection to MySQL that times out and stops pentaho working.  This means that the server needs rebooting.

vi mysql5.hibernate.cfg.xml

Add the following lines after the <session-factory>

This must be the first lines of code that are read.

<property name=“hibernate.c3p0.acquire_increment“>3</property>
<property name=“hibernate.c3p0.idle_test_period“>14400</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>

 


A file needs to be downloaded and the copied to several places this file is c3p0 andthe latest version can be found here SourceForge

At the time this was the current version

wget http://downloads.sourceforge.net/project/c3p0/c3p0-bin/c3p0-0.9.1.2/c3p0-0.9.1.2.bin.tgz?r=&ts=1281101795&mirror=ovh

gzip -d c3p0-0.9.1.2.bin.tgz

tar -xvf c3p0-0.9.1.2.bin.tar

cd /software/c3p0-0.9.1.2/lib

cp c3p0-0.9.1.2.jar /opt/pentaho/biserver-ce/tomcat/common/lib

cp c3p0-0.9.1.2.jar /opt/pentaho/administration-console/lib/

cd /opt/pentaho/biserver-ce/pentaho-solutions/system

cp applicationContext-spring-security-jdbc.xml applicationContext-spring-security-jdbc.xml.old

vi applicationContext-spring-security-jdbc.xml

Change the dataSource to match this

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

cp applicationContext-spring-security-hibernate.properties applicationContext-spring-security-hibernate.properties.old

vi applicationContext-spring-security-hibernate.properties

The file should look like this

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql: //localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQLDialect

Update the sample datasource

cd /opt/pentaho/biserver-ce/data/mysql5/

cp /opt/pentaho/biserver-ce/data/mysql5/create_sample_datasource_mysql.sql create_sample_datasource_mysql.sql.old

vi create_sample_datasource_mysql.sql

Change the inserst to look like this

INSERT INTO DATASOURCE VALUES(‘SampleData’,20,’com.mysql.jdbc.Driver’,5,’pentaho_user’,’password’,’jdbc:mysql://localhost:3306/sampledata’,’SELECT 1′,1000);

This then requires running

 



To enable publishing you must set a password.

vi /opt/pentaho/biserver-ce/pentaho-solutions/system/publisher_config.xml

set the follwoing line

<publisher-password></publisher-password>

e.g.

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

Administration Tasks

You need to configure the administration console before you can use it

vi /opt/pentaho/administration-console/resource/config/console.xml

Look for these two lines

<solution-path></solution-path>
<war-path></war-path>

They need to include the path to the solution and tomcat areas.  If you have not deviated from the installation notes then these lines should look like this

<solution-path>../biserver-ce/pentaho-solutions</solution-path>
<war-path>../biserver-ce/tomcat/webapps/pentaho</war-path>

You can now start the Admin console (PAC).  To start it

cd /opt/pentaho/administration-console

./start-pac.sh

You will now be able to launch the administrator client.

http://x.x.x.x:8099

This will start the client, you will get an error as the BI server has not started yet.


 

 

 



About these ads


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.