Apps Tutorial

This post teach you how to install the pentaho-bi-server.4.8.0 stable version in tomcat server with postgreSQL database.

Ive gone through many posts but could not find all the stuff in a single place. I just worked out and sharing the experience with it.
If you find any difficulty in the below steps feel free to drop a mail @ sadakar.1988@gmail.com for help.

Prerequisites :
1. Pentaho BI server CE 4.8.0 stable
2. tomat 6 server
3. PostgreSQL
4.PuTTY/WinScp


1)  Download the biserver-stable-4.8.0 using the following command in some folder.
Syntax :
 wget URLOfTheDownloadLocation
Example: 
wget http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable  /biserver-ce-4.8.0-stable.zip

2) After downloading completed unzip it using uznip command.
Syntaz : 
unzip .zipfileName
Example: 
unzip  biserver-ce-4.8.0-stable.zip 
 After unzipping you can find two folders .. They are i)administration-console & ii) biserver-ce

3) Install tomcat server externally(archive based installation) in your favorite location.
Example: 
Im taking jasperserver tomcat to install the pentaho server.
 root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/apache-tomcat#

4) Executing .sql files in postgresSQL
 * You need to build the two databases they are i) hibernate & quartz
*  Reason: You are going to install the pentaho bi server with postgreSQL(not with the hsql which directly comes with the download to interact with the server),hence you need to build the two databases for pentaho server to work properly. 
* Where you can find the .sql scripting files ?
Check in the location : 

/biserver-ce/data/postgresql
( biserver-ce is the folder where you unzipped in step-2)


Scripting file names: 

create_quartz_postgresql.sql 
create_repository_postgresql.sql  
create_sample_datasource_postgresql.sql 
migrate_quartz_postgresql.sql 
migration.sql
Commands to run the .sql files from putty :

root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/postgresql/bin# ./psql -U postgres -p 5432 -a -f /home/sadakar/softwares/pentaho/biserver-ce/data/postgresql/
create_quartz_postgresql.sql 

In the similar way execute the remaining scripting files.. you just need to change the file name in the above command.

Imp points to NOTE when you run the script files * You need to go to the "bin" folder of postgres installed and run the above command.
* In my case Im using the the postgresql that installed with jasper server.
* In the above command -U user name -p Port number of the postgreSQL
* Must specify   -a -f  in the command otherwise the script will not run.
* When you run the script itll ask you for postgreSQL password : give password as "password".
  If you use any other password for postgres give that password
* When you run the script itll ask for database user names :
  Open the script files in your fav editor and find this line

 CREATE USER pentaho_user PASSWORD password;
This means for the quartz database password is "password" and for the same follows to other scripting files while executing.

 NOTE:
* Once you execute all the scripting files check the postgreSQL databases whether the "hibernate" and "quartz" databases created or not.
* If you do not find the databases you might done wrong some where , cross check again the steps.
* And find 12 tables in "quartz" database and 1 table in "hiberante"database.

Hmmm... You are not done with the databases actually... bz you do not have all the tables in "hibernate" database. Bz the scripting files do not have all the data & tables.

Ill give you the links here to run the scripting files to get the tables. 
At present do not think of it. Find this in following steps...!!

5. Changes in config.xml file of tomcat server
* You need to add the following code to the config.xml file 
* location of the file : tomcat/confg/context.xml
* In my case the location is :
root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/conf#
<contex>
  <WatchedResource>WEB-INF/web.xml</WatchedResource>

  <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.postgresql.Driver" url="jdbc:postgresql://localhost:5432/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="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
                validationQuery="select 1"/></Context> 
6. Adding postgresql-driver in the lib folder of tomcat
* You need to copy the postgresql-driver in the lib folder of tomcat
* location of the lib folder for tomcat is :  tomcat/lib
* In my example it is there at
root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/lib# 
* You can directly download the postgresql driver using the following command or copy and paste it in lib folder if you are already using in some other place in your machine.
* Command is :
 wget jdbc.postgresql.org/download/postgresql-9.2-1003.jdbc4.jar
 
7. Changes need to do inside pentaho-solutions folder

* This is quite interesting thing to work here.
* Before you do modifications in pentaho-solutions folder, you need to copy this folder similar to tomcat installation location( You can keep this folder any where you want).
* For example : I have copied this folder from bi-server folder to similar location where the tomcat is installed .(from step 2 of this artical)
i.e.,  At root@sadakar-server:/opt/jasperreports-server-cp-5.0.0# ls

apache-ant     common        installation.log  license.txt        properties.ini    scripts                  uninstall.dat
apache-tomcat  ctlscript.sh  java              pentaho-solutions  releaseNotes.txt  Third-Party-Notices.pdf
buildomatic    docs          licenses          postgresql         samples           uninstall
 
       
* You need to configure the the settings for postgresql in applicationContext-spring-security-jdbc.xml file
* location of this file is : pentaho-solutions/system/applicationContext-spring-security-jdbc.xml

       
 <bean id="dataSource"
                class="org.springframework.jdbc.datasource.DriverManagerDataSource">
                <property name="driverClassName" value="org.postgresql.Driver" />
                <property name="url"
                        value="jdbc:postgresql://localhost:5432/hibernate" />
                <property name="username" value="hibuser" />
                <property name="password" value="password" />
 </bean>

* Next, you need to configure setting in : applicationContext-spring-security-hibernate.properties
* location of this file is : pentaho-solutions/system/applicationContext-spring-security-hibernate.properties

jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

8. Changes need to do in hibernate folder
*  Navigate to "hibernate" folder from "system" folder of same "pentaho-solutios" folder.
* Youll find different .xml files for different databases.
* You need to touch
       i) hibernate-settings.xml and
       ii) postgresql.hibernate.cfg.xml          files.. i.e., you need to do some modifications in these two files.
Changes in :
i) hibernate-settings.xml file
Comment this line 
<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Enable this line 
 <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

ii) postgresql.hibernate.cfg.xml

* You need not to do any modifications in this but you need to have an eye in this file.
 if your postgresql port number is different than 5432 , just give your one and if you give the appropriate host if you use any host number .


9. Changes in context.xml file of META-INF folder of tomcat
* You need to modify the "context.xml" file located in the tomcat/webapps/pentaho/META-INF folder.
* In my example: It is located at 

root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/webapps/pentaho/META-INF#

<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="org.postgresql.Driver" url="jdbc:postgresql//localhost:5432/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.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
                validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
</Context>
 
NOTE: We deployed "pentaho" and "pentaho-style" folders in weapps folder of tomcat server.

10. Changes in web.xml file of WEB-INF folder of tomcat
*  You need to modify web.xml of WEB-INF folder of tomcat server. i.e,. tomcat/webapps/pentaho/WEB-INF/web.xml
 * In my example the location of the file is : 
root@sadakar-server:/opt/jasperreports-server-cp-5.0.0/apache-tomcat/webapps/pentaho/WEB-INF#
 <context-param>
                <param-name>solution-path</param-name>
                <param-value>/opt/jasperreports-server-cp-5.0.0/pentaho-solutions</param-value>
  </context-param>
 
 NOTE: give the path for the "pentaho-solutios" b/w <param-value> and </param-value> tags


* You also need to check the port number & URL for the pentaho server in the same web.xml file.
 <context-param>
                <param-name>fully-qualified-server-url</param-name>
                <param-value>http://localhost:9090/pentaho/</param-value>
  </context-param>
 
NOTE: if you use some other port number for tomcat other than 8080 , you must specify the port number as shown above.

11.Tomcat server shutdown & startup
*  Go to the bin folder of tomcat server and shutdown the server if it already runs.
* Start the tomcat server.
* Commands :
Shutdown: ./shutdown.sh
Startup :   ./startup.sh

12. Type the pentaho server URL in any browser
* Go to the URL of any browser( Mozilla firefox is preferable as it is having firebug facility to track the errors if you get any)
* URL : 

 
References
1. http://blog.endpoint.com/2013/11/install-pentaho-bi-server-48-community.html