<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">

Pentaho configuration for MySQL

Shailesh Kulkarni Dec 26, 2012

Pentaho Liferay Integration business intelligence MySQL pentaho Technology

Steps to change Pentaho preconfigured hypersonic database to MySQL

Recently I integrated Pentaho with Liferay and Mysql. The open source Pentaho Pre-Configured Install (PCI) uses hypersonic as its sample data source, I want to remove this hypersonic dependency and want Pentaho to point to mysql5 database. I have searched for exact steps on Pentaho’s official site (http://wiki.pentaho.com/) but did not find easy steps to follow. In this blog I have explained the exact steps to remove hypersonic dependency and use mysql5 database.

I have used the following steps to achieve this objective

  1. Extract pentaho-data.zip in [drive name]liferay-portal-5.2.3 folder.
  2. Extract pentaho-solutions.zip in [drive name]liferay-portal-5.2.3 folder.
  3. Execute the SQL Scripts from the "pentaho-data/mysql5" folder.
  4. create_quartz_mysql.sql
    create_repository_mysql.sql
    create_sample_datasource_mysql.sql
    migration.sql
    
  5. Copy Pentaho.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
  6. Copy pentaho-portal-layout.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
  7. Copy pentaho-style.war file in [drive name]liferay-portal-5.2.3tomcat-6.0.18webapps
  8. Open server.xml file from [drive name]liferay-portal-5.2.3tomcat-6.0.18conf folder and add following <context> entries after <Host> tag entry.
  9. <Context path="/pentaho" docbase="webapps/pentaho/">
    <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root" factory="org.apache.commons.dbcp.BasicDataSourceFactory"  driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/sampledata" />
    
    <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="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate" />
    
    <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="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz" />
    
    <Resource name="jdbc/Shark" auth="Container" type="javax.sql.DataSource" factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5" maxWait="10000" username="root" password="root"driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/shark" />
    

    Please provide the correct MySQL database URL, username and password in above tags. The fields are highlighted with a yellow background.

  10. Configure database username, password and driver information in mysql5.hibernate.cfg.xml, hibernate-settings.xml file from pentaho-solutionssystemhibernate folder.
  11. Edit file applicationContext-spring-security-hibernate.properties

    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
    

    Changed to

    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3309/hibernate
    jdbc.username=<<database user name>>
    jdbc.password=<
    <password>>
    hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
    

    Edit file applicationContext-spring-security-jdbc.xml

    <!--  This is only for Hypersonic. Please update this section for any other database you are using -->
        <bean id="dataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url"
    	    value="jdbc:hsqldb:hsql://localhost:9001/hibernate" />
    <property name="username" value="hibuser" />
    <property name="password" value="password" />
        </bean>
    

    Changed to

    <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/liferayreport" />
    <property name="username" value="<<database user name>>" />
    <property name="password" value="<<password>>" />
    </bean>
    

    Edit file mysql5.hibernate.cfg.xml

    change user/password

    <property name="connection.username"><<database user name>></property>
    <property name="connection.password"><
    <password>>/property>
    
  12. Restart the tomcat, now Pentaho will use mysql as its database

Similar Blog

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.