Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Saturday, 24 August 2013

Pentaho BI Server Installation in existing tomcat server with PostgreSQL in Windows7 64bit

Need to update with images and few more imp stuff... Need a review on this artical. 


Pre-requisites:
·         Web App Server : apache-tomcat-6.0.36
·         BI Server Version : biserver-ce-4.8.0-stable
·         Database: postgreSQL, the below version or later
      Operating System :  Windows 7  -64 bi OS (OR) LINUX(Amazon cloud instance)
·         Other Tools used : PuTTy, WinScp
Download locations:
Apache-tomcat
Pentaho BIserver-ce-4.8
PostgreSQL
PuTTY
WinScp
http://winscp.net/eng/download.php

Steps for BI server installation in existing tomcat

1.     Apache tomcat installation (zip file installation)
a.       Place the apache-tomcat-6.0.36.tar.gz in your favourite place and unzip it.
b.      For example: C:\Apache2\apache-tomcat-6.0.36.tar.gz

c.       Run the server (i.e., Test whether the server is running properly or not)
d.      Once you confirm by seeing log of server, you need to work up on installation.

2.     Copy folders from biserver-ce-4.8.0-stable to the tomcat web apps
a.       After downloading biserver-ce-4.8.0-stable from source forge site unzip in your favourite location.
b.      Copy “pentaho”and “pentaho-styles” folders to webapps folder of tomcat server.
c.       See the below image
d.      Copy the “pentaho-solutions” folder in Similar location of tomcat installed folder.
NOTE: you can place it anywhere (i.e., you can place “pentaho-solutions” folder in any location of your machine).
e.      Find the image below for point  “d”.





3.     Executing Scripting files to create hibernate and quartz databases
Go to this location and down load all the 5 scripting files

Scripting files are:
1_create_repository_postgresql.sql
2_create_quartz_postgresql.sql
3_create_sample_datasource_postgresql.sql
4_load_sample_users_postgresql.sql
5_sample_data_postgresql.sql

Run all the files in postgreSQL which will create hibernate and quartz databases in postgreSQL.

NOTE:
·         Execute the scripts from command prompt.(Graphical execution may not work).
·         You may need to give the permissions to execute these scripting files.
·         You can also find all the five scripting files in the following location of pentaho unzipped.
·         D:\Installation Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\data
·         But these files may not have all the data to be used by the pentaho server.
·         So make sure that you have all the sample data for postgreSQL which will be used by Pentaho Server(for example: user name & password stores in user table & this we have to get from the database – this columns(data) may not present in the downloaded unzip)—so it’s better you go the link specified above and run the scripting files.
4.     Configuring the context.xml file in tomcat server
a.       Open the context.xml from “Config” folder C:\Apache2\apache-tomcat-6.0.36\conf)
b.      Code before modified
<Context>

    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
               
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <!-- Uncomment this to enable Comet connection tacking (provides events
         on session expiration as well as webapp lifecycle) -->
    <!--
    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
    -->

</Context>

c.       Code after modified( Copy and paste this code in context.xml file)
<Context>

    <!-- Default set of monitored resources -->
    <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:6062/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:6062/quartz"
                                validationQuery="select 1"/>

</Context>

NOTE: do not touch any other configuration files
5.     Changes in META-INF & WEB-INF folders which resides in Pentaho folder of web-apps of tomcat installation folder
Find the image below shown
I)                    Changes in META-INF folder
a.       Open the META-INF folder, you can find only one file. i.e., MANIFEST.MF
b.      You need not to work with this file.
c.       Now, create a new file.
                                                               i.      File should be : context.xml
                                                             ii.      Copy the following content in the file and save it.
<?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="org.postgresql.Driver" url="jdbc:postgresql://localhost:6062/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:6062/quartz"
                                validationQuery="select 1"/>
</Context>

    NOTE: You can copy paste the “context.xml” which we modified in Step 3.
                  The same thing you need to do here.
II)                  Changes in WEB-INF folder
a.       Open web.xml file from this folder(C:\Apache2\apache-tomcat-6.0.36\webapps\pentaho\WEB-INF)
b.      Change the following code( lines from 8 to 11)
Code after modified
<context-param>
                                <param-name>solution-path</param-name>
                                <param-value>locaton/path of pentaho-solutions</param-value>
</context-param>

NOTE: 
*   You need not to give path for pentaho-solutions if you copy the pentaho-solutions folder        similar to tomcat installation
      Find image-3
·         In case if you copy-the pentaho-solutions folder somewhere else you must have to give the path between <param_value> and </param-value> tags.
*      For example :
<context-param>
                                <param-name>solution-path</param-name>
                                <param-value>D:\Installation Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions</param-value>
                </context-param>

·         If the server doesn’t find path change the \ to / or give \\ or //


6.     Changes in pentaho-solutions folder

Go to the location D:\Installation Softwares\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system and find the following files and configure settings .

1.       applicationContext-spring-security-jdbc.xml
2.       applicationContext-spring-security-hibernate.properties

1.       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:6062/hibernate" />
                                <property name="username" value="hibuser" />
                                <property name="password" value="password" />
                </bean>
2.       applicationContext-spring-security-hibernate.properties
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:6062/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
Now, go to this location C:\Apache2\pentaho-solutions\system\hibernate  and find the files and configure the settings
1)      hibernate-settings.xml
2)      postgresql.hibernate.cfg.xml

1.       hibernate-settings.xml
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

2.       postgresql.hibernate.cfg.xml
<hibernate-configuration>
  <session-factory>
 
                <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>
    <!--  Postgres 8 Configuration -->
    <property name="connection.driver_class">org.postgresql.Driver</property>
    <property name="connection.url">jdbc:postgresql://localhost:6062/hibernate</property>
    <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</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>
    <!-- replaces DefinitionVersionManager -->
    <property name="hibernate.hbm2ddl.auto">update</property>
    <!-- load resource from classpath -->
    <mapping resource="hibernate/postgresql.hbm.xml" />
    <!--  This is only used by Pentaho Administration Console. Spring Security will not use these mapping files -->             
    <mapping resource="PentahoUser.hbm.xml" />
    <mapping resource="PentahoRole.hbm.xml" />
    <mapping resource="PentahoUserRoleMapping.hbm.xml" />
   
  </session-factory>
</hibernate-configuration>




7.     Refrences