Install Guide for Pentaho BI Server 4.8 (Community Edition) with MySQL on Windows

File Descriptions

Under http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable/ you will find the following files -

• biserver-manual-ce-4.8.0-stable.zip - For Windows 32-bit and 64-bit. This is for a custom install. It allows you to customize and build your own WAR file. It also containst the SQL scripts for creating the Hibernate and Quartz databases for MySQL, hsqldb (previously Hypersonic), Oracle and postgresql. It doesn't contain the Admin console nor Tomcat.
• biserver-ce-4.8.0-stable.zip - For Windows 32-bit and 64-bit. This is the file you want to get you started quickly on Windows. It comes with Tomcat and you have to do very little to make it work
• biserver-ce-4.8.0-stable.tar.gz - the Linux install I'm guessing. I know nothing about this and I haven't tried it. I would prefer to run my prod version on Linux so I might provide more details in the future
• bi-platform-4.8.0-stable-sources.zip - Source code
• biserver-ce-4.8.0-stable-javadoc.zip - Javadocs for the API in case you want to develop your own application

Embedded Tomcat Install

• My install root is C:\pentaho\ but you can put it wherever you want
• Download biserver-ce-4.8.0-stable.zip
• Unzip the contents to C:\pentaho so that you have the following subfolders -
• \administration-console\
• \biserver-ce\

Set up MySQL

• Download and install MySQL for Windows 
• Comes with an install wizard - fairly straightforward 
• Do a full install including admin tools
• Download and unzip biserver-manual-ce-4.8.0-stable.zip in a temp directory
• Launch MySQL Workbench and open a SQL Editor by selecting 'Open Connection to Start Querying' and Select 'Scripting -> Open Script...'
• From C:\temp\biserver-manual-ce-4.8.0-stable\biserver-manual-ce\pentaho-data\mysql5\ run the following scripts to create the system databases - 
• create_quartz_mysql.sql
• create_repository_mysql.sql
• create_sample_datasource_mysql.sql
• Download the Sample data dump from http://source.pentaho.org/svnroot/legacy/pentaho-data/trunk/mysql5/ and save it as SampleDataDump_MySql.sql
• In SQL Editor run SampleDataDump_MySql.sql to load the Sample Data

Configure the BI Server

• The following steps do the following -
• Set up MySQL COnnectivity
• Configure the Pentaho BI Server to use the Hibernate DB on MySQL
• Configure the Pentaho BI Server to use the system repository on MySQL
• Disable the default HyperSQL Database
• Download JDBC MySQL Connector from http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.22.zip/from/http://cdn.mysql.com/ 
• Unzip the files
• Update /pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties so that it reads -

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

• Update  /pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml to read -      


<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="hibuser" />
    <property name="password" value="password" />
</bean>

• Update  /pentaho/biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml to read - 

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

• Update  /pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml to read -

<?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="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"/>
</Context>

• Update  /pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml to read -


• The HSQLD sections are commented out (you can delete them if you prefer)


<context-param>
   <param-value>C:/pentaho/biserver-ce/pentaho-solutions</param-value>  
</context-param>

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

• Check the file  /pentaho/biserver-ce/tomcat/conf/catalina/localhost/pentaho.xml to make sure it has the same values as the context.xml and if not, update it to read -

<?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="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"/>
</Context>

• Set the publisher pasword by editing c:\pentaho-solutions\system\publisher_config.xml

<publisher-config>
        <publisher-password>password</publisher-password>
</publisher-config>

Configure the Administration Console

• Update pentaho/administration-console/resource/config/console.xml  to read -

<?xml version="1.0" encoding="UTF-8"?>
<console>
  <solution-path>../biserver-ce/pentaho-solutions</solution-path>
  <war-path>../biserver-ce/tomcat/webapps/pentaho</war-path>
  <platform-username>joe</platform-username>
  <biserver-status-check-period-millis>30000</biserver-status-check-period-millis>
  <homepage-url>http://www.pentaho.com/console_home</homepage-url>
  <homepage-timeout-millis>15000</homepage-timeout-millis>
  <!-- comma separated list of roles (no spaces) -->
  <default-roles>Authenticated</default-roles>
  <default-server-dir>biserver-ce</default-server-dir>
</console>

Fix the MySQL 8-hour Connection Timeout Issue

• Download c3p0-0.9.2-pre8.bin.zip (or the latest available release) from http://sourceforge.net/projects/c3p0/files/c3p0-bin/
• Unzip the archive and copy c3p0-0.9.2-pre8.jar to the following directories -
• c:\pentaho\biserver-ce\tomcat\webapps\pentaho\WEB-INF\lib\
• c:\pentaho\administration-console\lib\
• Edit /pentaho/pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml and add the following lines  immediately after the openeing tag for <session-factory> -

<!-- c3p0 start -->
  <!-- hibernate c3p0 settings -->

  <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
  <property name="hibernate.c3p0.acquire_increment">3</property>
  <property name="hibernate.c3p0.idle_test_period">10</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">true</property>

    <!-- hibernate cache settings -->
  <!-- End of patch added Friday April 3, 2009 to address issues of the database connection going dead -->

  <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>
<!-- c3p0 end -->