Installation of Pentaho 5.0.1 CE with MySQL db (in Windows 7 / Ubuntu) || Integrating Pentaho 5.0.1 CE with MySQL database

Pentaho, an awesome tool which is making me to play around either in office or at home.

On they way of exploration today(20th Jan 2014) below is the out come of integrating Pentaho 5.0.1 CE BA server with MySQL.

I'd like to say thanks to the Pentaho team for building this awesome tool with highly professional recommended open source BI plat form.

Below description is the step by step procedure for integrating Pentaho with MySQL which will work on both Windows and Linux family of servers.

Environment:
1) OS: Ubuntu /Windows 7
2) Database : MySQL 5.5(Community)
3) Web browser : Mozilla Firefox(Preferable)

Quick Steps : 
1) Installing Java Software and setting JAVA_HOME,JRE_HOME ( Windows/Ubuntu)
2) Downloading Pentaho (Ubuntu/Windows)
3) Downloading MySQL 5.5 Community
4) Executing MySQL .sql scripting files for Pentaho Integration
5) Modifying various configuration files to build the communication of Pentaho Server with MySQL db.

1) Installing Java Software and setting JAVA_HOME,JRE_HOME ( Windows/Ubuntu)
In windows:
Down load java from oracle site (here) - You need to have oracle account to download.
For setting PATH,CLASS_PATH, JAVA_HOME, JRE_HOME  read this blog
In Ubuntu
You can either download sun jdk or open jdk
Find downloading,installing here for open jdk
For ubuntu you no need to set the PATH, CLASS_PATH, JAVA_HOME,JRE_HOME for java open jdk.

2) Downloading Pentaho (Ubuntu/Windows)
In Windows
 http://ncu.dl.sourceforge.net/project/pentaho/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip
For Ubuntu
You just need to provide wget command before the link on command prompt
i.e.,
1) create a folder with name "pentaho"
2) navigate to that folder
    for example : home/pentaho
3) Give the below command at pentaho folder
wget  http://ncu.dl.sourceforge.net/project/pentaho/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip

4) Unzip this the downloaded .zip file
Command : unzip biserver-ce-5.0.1-stable.zip

5) You will find bi-server folder after unzipping.

3)Downloading MySQL 5.5 Community
In Ubuntu 
Go through the below post of mine to configure MySQL in Ubuntu machine

http://pentaho-bi-suite.blogspot.in/2014/01/mysql-installation-in-remote-ubuntu.html

In Windows
In Windows it's just to run and configure the .exe file and a general like software installation
MySQL down link : Clickbelow(You need to have oracle account)

http://dev.mysql.com/downloads/file.php?id=450445

4) Executing MySQL .sql scripting files for Pentaho Integration
From here I'm going to explain only on Ubuntu server as it is simple to do the same in windows machine.

File you need to execute are :
1) create_jcr_mysql.sql
2) create_quartz_mysql.sql
3) create_repository_mysql.sql

Connect to mysql 
root@SAD-AKAR-POC167:/usr/bin# mysql -u root -p
Enter password:password
  
Exectuing .sql files 
1) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_jcr_mysql.sql

2) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_quartz_mysql.sql

3) mysql>source   home/pentaho/biserver-ce/data/mysql5/create_repository_mysql.sql

Once you execute the .sql files check the databases using show databases command.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hibernate          |
| jackrabbit         |
| mysql              |
| performance_schema |
| quartz             |
| test               |
+--------------------+
7 rows in set (0.01 sec)

5) Modifying various configuration files to build the communication of Pentaho Server with MySQL db(Preparing MySQL BA Repository Database)

I)  Configure Quartz on MySQL BA Repository Database
     (quartz.properties)

II) Configure Hibernate Settings for MySQL
     ( applicationContext-spring-security-hibernate.properties
       hibernate-settings.xml
       mysql5.hibernate.cfg.xml )

III)  Modify JDBC Connection Information in the Tomcat context.xmlFile
     (context.xml)
     Delete/Remove pentaho.xml file in  Tomcat directory 
    (pentaho.xml)

IV) Modify Jackrabbit Repository Information for MySQL 
    (repository.xml)

V)  Stop HSQL db start up with pentaho start up
    (web.xml)
 

I)  Configure Quartz on MySQL BA Repository Database
     (quartz.properties)

Location of the file : 
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/quartz$nano quartz.properties



Original Code:
  org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modify above Code like below:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

NOTE:
In nano editor use Ctrl+W to find the line of code

II) Configure Hibernate Settings for MySQL
     (  applicationContext-spring-security-hibernate.properties

        hibernate-settings.xml

       mysql5.hibernate.cfg.xml )

applicationContext-spring-security-hibernate.properties
Location of the file :  

sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system$nano applicationContext-spring-security-hibernate.properties


Comment/delete the exiting hsqldb code
Actual code
jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate jdbc.username=hibuser jdbc.password=password hibernate.dialect=org.hibernate.dialect.HSQLDialect

Code after Modification

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

hibernate-settings.xml

Location of the file :  
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/hibernate$nano hibernate-settings.xml


Actual code

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

Code after Modification

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

mysql5.hibernate.cfg.xml
You need NOT to change any code in this file, but you just need to confirm every thing is proper as per your database settings.
Location of the file : 
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/hibernate$nano mysql5.hibernate.cfg.xml


Actual code

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

Code after Modification
NO need to change any thing from actual code until and unless you have any modified database details at the time of MySQL installation such as host,port. 

III)  Modify JDBC Connection Information in the Tomcat context.xml file
      (context.xml)
      Delete/Remove pentaho.xml file in  Tomcat directory 
      (pentaho.xml)

context.xml
Location of the file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/webapps/pentaho/META_INF$nano context.xml
Actual Code:

<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />

<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

Code after Modification

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

pentaho.xml

Remove/delete this file
Location of this file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/conf/Catalina/localhost$ls
pentaho.xml

Use rm command to delete this file
i.e.,
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/conf/Catalina/localhost$rm pentaho.xml

Reason for removing/deleting this file
Pentaho BA server  creates a copy of context.xml file with  the name of pentaho.xml when we start the server.
Now when the server starts the newly generated pentaho.xml file consists of the modified code of context.xml
(In simple words context.xml=pentaho.xml and pentaho.xml file should have the modifications done in context.xml file, that's the reason we are deleting pentaho.xml file)

IV) Modify Jackrabbit Repository Information for MySQL 

    (repository.xml)

You need to disable/comment some code and need to enable some code to work with MySQL
You need to disable and enable the code in the categorized list of locations in the file.
Location of the file
sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/pentaho-solutions/system/jackrabbit$nano repository.xml

 i)  FileSystem part
ii) data store configuration part
iii) security configuration part
iv) persistence manager of the workspace part
v) Configures the versioning
vi) PersistenceManager Versioning part

i)  FileSystem part
Comment this code
 <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
     <param name="path" value="${rep.home}/repository"/>
  </FileSystem>

Activate this code
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="driver" value="com.mysql.jdbc.Driver"/>
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="schema" value="mysql"/>
    <param name="schemaObjectPrefix" value="fs_repos_"/>
  </FileSystem>

ii) data store configuration part
Comment this code
   <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>

Activate this code
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <param name="databaseType" value="mysql"/>
    <param name="driver" value="com.mysql.jdbc.Driver"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="3"/>
    <param name="copyWhenReading" value="true"/>
    <param name="tablePrefix" value=""/>
    <param name="schemaObjectPrefix" value="ds_repos_"/>
  </DataStore>

iii) security configuration part
Comment this code(line number 225 to 227(5.0.1 CE pentaho - it may vary depending on release)
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${wsp.home}"/>
    </FileSystem>
Activate this code( 197-204 lines)
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="fs_ws_"/>
    </FileSystem>

iv) persistence manager of the workspace part
Comment this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
      <param name="url" value="jdbc:h2:${wsp.home}/db"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
    </PersistenceManager>

Activate this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user" />
      <param name="password" value="password" />
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
    </PersistenceManager>

v)Configures the versioning 
Comment this code
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${rep.home}/version" />
    </FileSystem>
Active this code 
 <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user"/>
      <param name="password" value="password"/>
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="fs_ver_"/>
    </FileSystem>

vi) PersistenceManager Versioning part
Comment this code
 <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">
      <param name="url" value="jdbc:h2:${rep.home}/version/db"/>
      <param name="schemaObjectPrefix" value="version_"/>
    </PersistenceManager>
Active this code
<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
      <param name="user" value="jcr_user" />
      <param name="password" value="password" />
      <param name="schema" value="mysql"/>
      <param name="schemaObjectPrefix" value="pm_ver_"/>
    </PersistenceManager>

V)  Stop HSQL db start up with pentaho start up

    (web.xml)

 Comment or delete the code from below code from web.xml file so that you can stop the start up hsqldb when you start the pentaho server, at the same time instead of hsqldb pentaho server will pick the connections of MySQL .

Location of the file

sadakarit@SAD-AKAR-DEM167:/home/pentaho/biserver-ce/tomcat/webappas/pentaho/WEB-INF$nano web.xml

Commenting code at 1st place - Comment the below code

 <!-- [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] -->

Commenting code at 2nd place - Comment the below code

  <!-- [BEGIN HSQLDB STARTER] -->
  <listener>
    <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
  </listener>
  <!-- [END HSQLDB STARTER] -->



You have done with integrating Pentaho 5.0.1 with MySQL database.

NOTE:
1) Check logs  for errors and pentaho server log for startup with MySQL
2)  Login with Admin/password credential.
3) You will find no example will work with this installation as all the pentaho given examples with work with hsqldb.
4) You need to execute the sample database scripts for that and you need to give jdbc connections in the BA server. 

Sadakar

I believe in "Learning never exhausts the mind"