Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux family)

Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux )

Parts

1. Creating databases
2. Modifying configuration files
3. Stopping HSQL db start up

Creating databases

Command to execute the scripting files

mysql>source  D:\ biserver-ce\data\mysql5\create_jcr_mysql.sql

Similarly execute the remaining .sql files(i.e, execute create_quartz_mysql.sql and create_repository_mysql.sql)

1. Check the databases created using show databases command on MySQL command prompt.

 

Modifying configuration files

1. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original 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

Modified code

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

  2. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

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

Modified code

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

 

3. mysql5.hibernate.cfg.xml

Location of the file: pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml

Do need to change any code in this file.. Just need to check everything is perfect or not

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

Location of the file: pentaho-solutions\system\quartz\quartz.properties

Original Code

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

Modified Code

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

5. context.xml

Location of the file: tomcat\webapps\pentaho\META-INF\context.xml

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

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

 Imp Note:

Delete pentaho.xml file in below location

tomcat\conf\Catalina\localhost\pentaho.xml

Reason:

Pentaho creates  on startup pentaho.xml as a copy of context.xml

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code

<FileSystem>

<param name=”path” value=”${rep.home}/repository”/>

</FileSystem>

Active this code

<FileSystem>

<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)                  DataStore part

Comment this code

<DataStore/>

Active 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 part in the FileSystem Workspace part

Comment this code

<FileSystem>

<param name=”path” value=”${wsp.home}”/>

</FileSystem>

   Active this code

<FileSystem>

<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)                PersistenceManager part

Comment this code

<PersistenceManager>

<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_”/>

</PersistenceManager>

Active this code

<PersistenceManager>

<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)                  FileSystem Versioning part

Comment this code

<FileSystem>

<param name=”path” value=”${rep.home}/version” />

</FileSystem>

 

Active this code

<FileSystem>

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

<param name=”url” value=”jdbc:h2:${rep.home}/version/db”/>

<param name=”schemaObjectPrefix” value=”version_”/>

</PersistenceManager>

Active this code:

<PersistenceManager>

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

Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)

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

 

Also comment this 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 CE with Mysql 5.5

Now login to the Pentaho server .

URL:   localhost:8080/pentaho

Username/Password : Admin/password

NOTE:

• You will not find any sample working. B’z you have not installed sample data.
• Example available in pentaho are developed on sample data so you need to execute and give the new sample data base connections(for this you need to execute .sql file of sample data).

Helical IT Solutions Pvt Ltd

Business Intelligence C-Tools CDA cde CDF dashboard data visualization data warehouse ETL ireport jaspersoft jaspersoft consultant kettle OLAP open source BI pentaho talend