• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Install Pentaho BI Server 4.8 Community Edition with PostgreSQL Repository

    Selvakumar Arumugam

    By Selvakumar Arumugam
    November 7, 2013

    Pentaho BI server community edition can be installed through an archive file available from SourceForge.

    Prerequisites

    • Java 6
    • PostgreSQL

    Download Pentaho BI Server installation file (biserver-ce-4.8.0-stable.zip) from SourceForge: http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable

    Unzip the archive file and navigate inside biserver-ce to set sh files to executable mode:

    $ unzip biserver-ce-4.8.0-stable.zip
    $ cd biserver-ce
    $ find . -type f -iname '*.sh' -exec chmod a+x {} \;
    

    Pentaho community edition uses hsql database as default. Need to create two databases in Postgres for Pentaho. Find the SQL files to create databases under biserver-ce/data/postgresql. database_name, user_name and password are configurable through SQL files. Fix two errors before creating database using SQL files. Comment two lines in below files tables as commented.

    • create_quartz_postgresql.sql
    ALTER TABLE qrtz_fired_triggers
        ALTER TRIGGER_NAME  TYPE VARCHAR(200),
        ALTER TRIGGER_GROUP TYPE VARCHAR(200),
        ALTER INSTANCE_NAME TYPE VARCHAR(200),
        ALTER JOB_NAME      TYPE VARCHAR(200),
        ALTER JOB_GROUP     TYPE VARCHAR(200),
        ADD COLUMN PRIORITY INTEGER NULL;
    --    ADD COLUMN PRIORITY INTEGER NOT NULL;
    
    • migrate_quartz_postgresql.sql
    ALTER TABLE qrtz_fired_triggers
        ALTER TRIGGER_NAME  TYPE VARCHAR(200),
        ALTER TRIGGER_GROUP TYPE VARCHAR(200),
        ALTER INSTANCE_NAME TYPE VARCHAR(200),
        ALTER JOB_NAME      TYPE VARCHAR(200),
        ALTER JOB_GROUP     TYPE VARCHAR(200);
    --    ADD COLUMN PRIORITY INTEGER NOT NULL;
    --    ALTER COLUMN PRIORITY SET NULL;
    

    Modify database name, username & password if necessary and create databases for configuration and scheduling by running below commands.

    $ psql -U postgres -a -f create_quartz_postgresql.sql
    $ psql -U postgres -a -f create_repository_postgresql.sql
    $ psql -U postgres -a -f create_sample_datasource_postgresql.sql
    $ psql -U postgres -a -f migrate_quartz_postgresql.sql
    $ psql -U postgres -a -f migration.sql
    

    Verify databases quartz (scheduling) and hibernate (configuration) and their tables.

    Now database name, username, password and driver should be configured in following places in files. By default hsql drivers, settings enabled in config files so comment hsql configurations and enable Postgres settings.

    • biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
    <?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: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>
    
    • biserver-ce/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>
    
    • biserver-ce/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
    
    • biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
    <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
    
    • biserver-ce/pentaho-solutions/system/hibernate/postgresql.hibernate.cfg.xml
    <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="connection.username">hibuser</property>
        <property name="connection.password">password</property>
    
    • biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
    Hibernate/type=javax.sql.DataSource
    Hibernate/driver=org.postgresql.Driver
    Hibernate/url=jdbc:postgresql://localhost:5432/hibernate
    Hibernate/user=hibuser
    Hibernate/password=password
    Quartz/type=javax.sql.DataSource
    Quartz/driver=org.postgresql.Driver
    Quartz/url=jdbc:postgresql://localhost:5432/quartz
    Quartz/user=pentaho_user
    Quartz/password=password
    

    Specify the pentaho solutions path, server url and port in web.xml of tomcat webapp

    • biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
            <context-param>
                    <param-name>solution-path</param-name>
                    <param-value>/opt/avr-new/biserver-ce/pentaho-solutions</param-value>
            </context-param>
    
            <context-param>
                    <param-name>fully-qualified-server-url</param-name>
                    <param-value>http://localhost:8080/pentaho/</param-value>
            </context-param>
    

    Pentaho can be run tomcat custom ports by modifying the ports in server.xml and web.xml

    • biserver-ce/tomcat/conf/server.xml
        <Connector URIEncoding="UTF-8" port="9090" protocol="HTTP/1.1"
                   connectionTimeout="20000"
                   redirectPort="8443" />
    
    • biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml 
             <context-param>
                    <param-name>fully-qualified-server-url</param-name>
                    <param-value>http://localhost:8080/pentaho/</param-value>
            </context-param>
    

    Let’s start the Pentaho BI server and try out its great features. Commands to start and stop the BI server:

    biserver-ce$ ./start-pentaho.sh
    biserver-ce$ ./stop-pentaho.sh
    

    Troubleshooting:

    biserver-ce$ tail -f tomcat/logs/catalina.out
    biserver-ce$ tail -f tomcat/logs/pentaho.out
    

    analytics pentaho postgres casepointer


    Comments