Pentaho - 3 : Connecting MySQL with Pentaho

In previous post I showed you how to set up pentaho BI server. In this post I am going to tell you how to configure MySQL database to your BI server.

Out of the box Pentaho comes with a HSQLDB database. That is why you were able to run BI server without any database configuration in the previous post. But HSQLDB is a temporary useful database. We cannot use it with production. Therefore, we need to configure MySQL to pentaho.

First of all, you need to know about the databases needed to run BI server and databases needed to run samples Pentaho has by default.

Pentaho uses two system databases – hibernate and quartz
Pentaho uses two sample databases – sampledata and foodmart

Those SQL scripts can be downloaded from here. Put all SQL scripts inside directory named 'sql-scripts' inside Pentaho directory.

• Next load these databases into our mysql server as follows;

:~/Pentaho/sql-scripts$mysql -u root -p<password>
:~/Pentaho/sql-scripts$source 1_create_repository_mysql.sql
:~/Pentaho/sql-scripts$source 2_create_quartz_mysql.sql
:~/Pentaho/sql-scripts$source 3_create_sample_datasource_mysql.sql
:~/Pentaho/sql-scripts$source 4_load_sample_users_mysql.sql
:~/Pentaho/sql-scripts$source 5_sampledata_mysql.sql

• Navigate to ../Pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF and edit context.xml file as follows.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/pentaho" docbase="webapps/pentaho/">
    <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
        maxWait="10000" username="root" 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="root" password="password"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>
</Context>

Important: Change username="root" password="password" as according to your mysql server user and password.

• Then save the file.

• Navigate to ../Pentaho/biserver-ce/pentaho-solutions/system/hibernate and edit hibernate-settings.xml file and save it as follows.

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

• Edit and save the mysql5.hibernate.cfg.xml file in the ../Pentaho/biserver-ce/pentaho-solutions/system/hibernate folder as follows.

<!--  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">root</property>
<property name="connection.password">password</property>
<property name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>

• Change applicationContext-spring-security-hibernate.properties file in ../Pentaho/biserver-ce/pentaho-solutions/system folder.

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

• Edit the header and two lines of  applicationContext-spring-security-jdbc.xml file in ../Pentaho/biserver-ce/pentaho-solutions/system folder.

<!--  This is only for MySQL. Please update this section for any other database you are using --> 

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

   <property name="password" value="password" />

</bean>

• Edit and add following bold texts from the ../Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi folder's jdbc.properties file.

SampleData/type=javax.sql.DataSource 

SampleData/driver=com.mysql.jdbc.Driver 

SampleData/url=jdbc:mysql://localhost:3306/sampledata

SampleData/user=root 

SampleData/password=password 

Hibernate/type=javax.sql.DataSource

Hibernate/driver=com.mysql.jdbc.Driver 

Hibernate/url=jdbc:mysql://localhost:3306/hibernate

Hibernate/user=root 

Hibernate/password=password 

Quartz/type=javax.sql.DataSource

Quartz/driver=com.mysql.jdbc.Driver 

Quartz/url=jdbc:mysql://localhost:3306/quartz

Quartz/user=root 

Quartz/password=password 

SampleDataAdmin/type=javax.sql.DataSource

SampleDataAdmin/driver=com.mysql.jdbc.Driver 

SampleDataAdmin/url=jdbc:mysql://localhost:3306/sampledata

SampleDataAdmin/user=root 

SampleDataAdmin/password=password 

FoodMart/type=javax.sql.DataSource

FoodMart/driver=com.mysql.jdbc.Driver

FoodMart/url=jdbc:mysql://localhost:3306/foodmart

FoodMart/user=root

FoodMart/password=password 

• You can delete the five line shark statements from the file as this workflow is no longer used.

• Check mysql drivers are placed inside the ../Pentaho/biserver-ce/tomcat/lib folder, if not download the latest drivers and place inside it.

• To stop HSQLDB from starting, edit web.xml file in ../Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF folder. This is a very important file, so it has a lot of parameters. Move to line 87 (or search for HSQLDB) and comment the hsqldb startup lines or delete them like this:

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

• and in line 215 (or next find):

<!-- [BEGIN HSQLDB STARTER] --> 

<!-- <listener>

      <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

    </listener>

--> 

<!-- [END HSQLDB STARTER] -->

• Edit pentaho.xml file in ../Pentaho/biserver-ce/tomcat/conf/Catalina/localhost directory same as we did earlier in context.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/pentaho" docbase="webapps/pentaho/">
    <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
        maxWait="10000" username="root" 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="root" password="password"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>
</Context>

• After doing these configurations, Pentaho Business Intelligence Server can be started by executing  'start-pentaho.sh' (default username 'joe' and password 'password')

:~/Pentaho/biserver-ce$./start-pentaho.sh

• It can be stopped by executing 'stop-pentaho.sh'

:~/Pentaho/biserver-ce$./stop-pentaho.sh

So that's it. See you with the next post. :)