Thursday, December 1, 2011

Installing Pentaho BI on Tomcat/JBoss with MySQL

This posts explain how to install Pentaho-BI(Business Intelligence) Community Edition(CE)  server on Tomcat(7.0.22)/JBoss(5.1) on Windows platform.This posts will not cover any basics regarding Pentaho-BI and  please refer to pentaho-bi platform for more information.


You can either use Tomcat or JBoss for the installation but the steps outlined below will cover both
  1. Download pentaho-bi server-CE build for manual deployment 
    • Download  pentaho-biserver CE  and save it in your file system .I have saved it in C:\pentaho-manual-deployment and henceforth will be referring to the same location as "PENTAHO_MANUAL_BI_HOME" 
    • Choose the version .For this example i have chosen 3.8.0-stable and same can be downloaded 
    • NOTE: The naming convention for the download was confusing due to "manual" in the artifact but actually it was referring to manual-deployment
  2. Create pentaho.war for Installation - Use ANT  scripts (provided along with bi-server build) NOTE: Before proceeding with this step please make sure you have ANT installed in your system 
      • Navigate to <PENTAHO_MANUAL_BI_HOME>\biserver-manual-ce\
      • Configuration of Solution Repository - This is to inform pentaho web-application where it's solution repository resides.This will avoid server restarts for any reports/dashboards whenever it should be published to  pentaho-BI Server.The changes are follows
        • Open  <PENTAHO_MANUAL_BI_HOME>\biserver-manual-ce\pentaho-webapp\WEB-INF\web.xml in your favorite editor and add the <param-value> to solution-path parameter and it should look like follows .Please modify the path according to your environment where you installed 
          <context-param>
                                          <param-name>solution-path</param-name>
                                          <param-value>C:\\pentaho-manual-deployment\\biserver-manual-ce\\pentaho-solutions</param-value>
                          </context-param>
      • Execute ANT script as follows  -  This will build ANT with updated web.xml so that you don't want to change once deployed to the containerant build-all This will build WAR(Web Application Archives) for Tomcat & JBoss containers respectively .If you want to build only to specific container please do it as follows
          Tomcat: ant war-pentaho-tomcat
           JBoss: ant war-pentaho-jboss
  3. Installation of containers & databases .In our case it would be Tomcat/JBoss and MySQL.The installers for them are listed below - 
      • Download  Tomcat7.0 and  JBoss5.1.0GA and extract to your file system .The extracted location of each of them will be now referred as "TOMCAT_HOME" and "JBOSS_HOME" .In my case TOMCAT_HOME=C:\apache-tomcat-7.0.22 and JBOSS_HOME=C:\jboss-5.1.0.GA
      • Download MySQL5.1 and follow Installation instructions mentioned
  4. MySQL configuration for Pentaho 
      • Run the   scripts(create_repository_mysql.sql,create_quartz_mysql.sql,create_sample_datasource_mysql.sql) located in <PENTAHO_MANUAL_BI_HOME>\biserver-manual-ce\pentaho-data\mysql5
      • In addition please run SampleData.sql which will install "sampledata" solution repository needed data
  5. Configuration of data sources in Tomcat/JBoss
      • Tomcat
        • We need to setup datasources in order to enable pentaho-bi server communicate with database .Navigate to <TOMCAT_HOME>\conf\context.xml and configure it as below .Please change the mysql server url according to your environment 
                     <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://192.168.101.55: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://192.168.101.55:3306/quartz"
                                          validationQuery="select 1"/>
                                         
                          <Resource name="jdbc/SampleData" 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://192.168.101.55:3306/sampledata"
                                          validationQuery="select 1"/>
        • Download MySQL Jdbc Driver and place it in <TOMCAT_HOME>\lib folder.This will enable  pentaho-biserver to communicate with MySQL
      • JBoss
        • Navigate to <JBOSS_HOME>\server\default\deploy and you have to create datasources configuration files (should suffixed with -ds.xml) so that jboss  can recognize and make the datasource instances available for the application .In my case i have created two files
          • pentaho-ds.xml - contains datasources for Hibernate & Quartz.Now the pentaho-ds.xml should look like below .Please modify the MySQL server related configuration according to your environment 
            <?xml version="1.0" encoding="UTF-8"?>

            <!-- Hibernate Datasource definition file-->

            <datasources>
                            <local-tx-datasource>
                                            <jndi-name>jdbc/Hibernate</jndi-name>
                                            <connection-url>jdbc:mysql://192.168.101.55:3306/hibernate</connection-url>
                                            <driver-class>com.mysql.jdbc.Driver</driver-class>
                                            <user-name>hibuser</user-name>
                                            <password>password</password>
                                            <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                                            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                                            <connection-property name="autoReconnect">true</connection-property>
                                            <min-pool-size>10</min-pool-size>  
                                            <max-pool-size>20</max-pool-size>
                                            <query-timeout>60</query-timeout>
                                            <idle-timeout-minutes>5</idle-timeout-minutes> 
                                            <prepared-statement-cache-size>32</prepared-statement-cache-size>
                                            <metadata>
                                                            <type-mapping>mySQL</type-mapping>
                                            </metadata>
                            </local-tx-datasource> 
                            <!-- Quartz Datasource definition --> 
                            <local-tx-datasource>
                                            <jndi-name>Quartz</jndi-name>
                                            <connection-url>jdbc:mysql://192.168.101.55:3306/quartz</connection-url>
                                            <driver-class>com.mysql.jdbc.Driver</driver-class>
                                            <user-name>pentaho_user</user-name>
                                            <password>password</password>
                                            <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                                            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                                            <connection-property name="autoReconnect">true</connection-property>
                                            <min-pool-size>10</min-pool-size>  
                                            <max-pool-size>20</max-pool-size>
                                            <query-timeout>60</query-timeout>
                                            <idle-timeout-minutes>5</idle-timeout-minutes> 
                                            <prepared-statement-cache-size>32</prepared-statement-cache-size>
                                            <metadata>
                                                            <type-mapping>mySQL</type-mapping>
                                            </metadata>
                            </local-tx-datasource> 
            </datasources>
          • sampledata-ds.xml - contains datasource for "sampledata" solution repository.Now the XML should look like below and please modify the MySQL server related configuration according to your environment
            <?xml version="1.0" encoding="UTF-8"?>
            <!-- Hibernate Datasource definition file-->
            <datasources>
                            <local-tx-datasource>
                                            <jndi-name>jdbc/SampleData</jndi-name>
                                            <connection-url>jdbc:mysql://192.168.101.55:3306/sampledata</connection-url>
                                            <driver-class>com.mysql.jdbc.Driver</driver-class>
                                            <user-name>pentaho_user</user-name>
                                            <password>password</password>
                                            <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                                            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                                            <connection-property name="autoReconnect">true</connection-property>
                                            <min-pool-size>10</min-pool-size>  
                                            <max-pool-size>20</max-pool-size>
                                            <query-timeout>60</query-timeout>
                                            <idle-timeout-minutes>5</idle-timeout-minutes> 
                                            <prepared-statement-cache-size>32</prepared-statement-cache-size>
                                            <metadata>
                                                            <type-mapping>mySQL</type-mapping>
                                            </metadata>
                            </local-tx-datasource> 
            </datasources>
          • Download MySQL Jdbc Driver and place it in <JBOSS_HOME>\server\default\lib folder.This will enable  pentaho-biserver to communicate with MySQL
        • Download MySQL Jdbc Driver and place it in <TOMCAT_HOME>\lib folder.This will enable  pentaho-biserver to communicate with MySQL
  6. Configuration of pentaho-solutions for MySQL
      • Navigate to <PENTAHO_MANUAL_BI_HOME>\biserver-manual-ce\pentaho-solutions\system\hibernate and modify the following files for mysql
        • hibernate-settings.xml - Modify the <config-file> value to mysql as -   <config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
        • mysql5.hibernate.cfg.xml - Modify the mysql settings according to your environment and also added connection pooling related configuration as below  for  effective connection management and it should look like below.This would need  c3p0 library in our container classpath  so that traditional  JDBC drivers with JNDI-bindable DataSources, including DataSources that implement Connection and Statement Pooling, as described by the jdbc3 spec and jdbc2 std extension
          <hibernate-configuration>
            <session-factory>  
            <!-- Connection Pooling Configuration Start -->  
                          <property name="hibernate.c3p0.acquire_increment">3</property>
                          <property name="hibernate.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">3</property>  
                          <!-- Connection Pooling Configuration End  -->  
                          <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
              <property name="hibernate.generate_statistics">true</property>
              <property name="hibernate.cache.use_query_cache">true</property>  
              <!--  MySQL Configuration -->
              <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
              <property name="connection.url">jdbc:mysql://192.168.101.55:3306/hibernate</property>
              <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
              <property name="connection.username">hibuser</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>
              <property name="hibernate.hbm2ddl.auto">update</property>
              <mapping resource="hibernate/mysql5innodb.hbm.xml" />
              <mapping resource="PentahoUser.hbm.xml" />
              <mapping resource="PentahoRole.hbm.xml" />    
            </session-factory>
          </hibernate-configuration>
        • Extract the c3p0 and place the jar in tomcat - <TOMCAT_HOME>\lib and jboss - <JBOSS_HOME>\server\default\lib
      • Navigate to <PENTAHO_MANUAL_BI_HOME>\biserver-manual-ce\pentaho-solutions\system and modify file - applicationContext-spring-security-hibernate.properties  for mysql
        jdbc.driver=com.mysql.jdbc.Driver
        jdbc.url=jdbc:mysql://192.168.101.55:3306/hibernate
        jdbc.username=hibuser
        jdbc.password=password
        hibernate.dialect=org.hibernate.dialect.MySQLDialect
  7. Deployment of pentaho.war  web-app to Tomcat/JBoss 
    1. Use the build artifact as result of  Step-2  and copy the same to respective container as below
      • Tomcat - place it in <TOMCAT_HOME>\webapps
      • JBoss - place it in <JBOSS_HOME>\server\default\deploy
    2. start the respective container as follow 
      1. Tomcat - Navigate to <TOMCAT_HOME>\bin\catalina run
      2. JBoss - Navigate to <JBOSS_HOME>\bin\run.bat  - This will start the default server instance of JBoss
Once container started successfully you  should see the following for tomcat(In my case tomcat instance was running in port-8080)


For Jboss you should see the below (In my case Jboss instance was running on port-8180)


As part of above  installation i have faced few errors and also mentioned the steps that have been taken to overcome the same .During jboss installation 
  •  "Multiple bindings for SLF4J" .Hence removed SLF4J-logging.jar from <JBOSS_HOME>\common\lib and i believe there is a fix from SLF4J as well which may solve in latest versions of jboss
  • "ClassCastException as part of parsing CDF" - Removed the following jars from <JBOSS_HOME>\lib\endorsed since these libraries are conflicting with pentaho-web app
    • xercesImpl.jar
    • serializer.jar
    • xalan.jar
      Happy learning!!!!!

    2 comments:

    1. hi raghavan,very interesting blog about caching.. but how about if we use AtomicReference for caching? currently im using this concept of AtomicReference. Which is the best one to use for caching?

      ReplyDelete
    2. Raghavan,
      Do you know if it possible to install version 4.5 or 4.8 of Pentaho on JBoss Server 6 or 7?

      ReplyDelete