Pentaho BI Server 3 5

8/10/2019 Pentaho BI Server 3 5

1/20

Pentaho Business Intelligence Server 3.5PostgreSQL 8.x.x and Windows Installation Tutorial

Version 1.0, published 7 January 2010 by Prashant Raju

Pentaho Business Intelligence Server 3.5 with MySQL and Windows Installation tutorial, Version 2.0, 7 Jan 2010 by Prashant Raju
http://www.prashantraju.com/http://www.prashantraju.com/http://www.prashantraju.com/http://www.prashantraju.com/


2/20

1. Table of Contents

1. Table of Contents.......................................................................................................... 0

2. Introduction ................................................................................................................... 0

About ............................................................................................................................... 3Donate ............................................................................................................................. 3The Community ............................................................................................................... 3Thanks............................................................................................................................. 4

2. Downloads ..................................................................................................................... 5

Java JDK ......................................................................................................................... 5SQL Script Packs............................................................................................................. 5Pentaho Business Intelligence Server 3.5 Stable............................................................ 5

3. Setting up the Database ............................................................................................... 7

Extract SQL Script Pack .................................................................................................. 7Load SQL Scripts ............................................................................................................ 7

4. Setting up the Business Intelligence Server ..............................................................9

Overview.......................................................................................................................... 9pentaho-solutions\ ........................................................................................................... 9

applicationContext-spring-security-jdbc.xml ................................................................ 9applicationContext-spring-security-hibernate.properties............................................10hibernate-settings.xml ................................................................................................ 10postgresql.hibernate.cfg.xml (optional) ......................................................................10

tomcat\........................................................................................................................... 10web.xml (optional)...................................................................................................... 11context.xml................................................................................................................. 11

5. Setting up the Administration Console..................................................................... 13

6. Starting the Business Intelligence Server ................................................................ 14

7. Starting the Administration Console.........................................................................18

8. Success! ...................................................................................................................... 20

http://www.prashantraju.com/http://www.prashantraju.com/


3/20

2. Introduction

About

This tutorial is an update to the its original "Pentaho Business Intelligence Server 2.0 with MySQL andLinux". Although this tutorial isn't as detailed as it's predecessor it assumes that readers haveintermediate to advancedknowledge in their setup of choice. With what this tutorial lacks in detail itdefinitely makes up to accommodates a lot more of different setups:

Windows MySQL 5.x PostgreSQL 8.x.x Oracle 10g & 11g

Linux MySQL 5.x PostgreSQL 8.x.x

Oracle 10g & 11g

This tutorial is for a Windows and PostgreSQL 8.x.x setup.

Here is a description of the different colour-coded boxes seen throughout the tutorial:

Blue boxes represent help

Grey boxes represent tips

Green boxes represent file description

Donate

My last tutorial had over 6,000 unique downloads and I believe from most of the feedback and numerouspeople adding me to Gmail that it was extremely helpful! But contrary to belief I do not work for Pentahoand all of this work is voluntary. So even $1 can help me with producing better and bigger tutorials!

You can donate to my PayPal account by clicking here.

The Community

Don't forget about other projects which are part of the Pentaho community:

PAT (Pentaho Analysis Tool)

An alternative to Pentaho's current OLAP analyser tool, JPivot.

CDF (Community Dashboard Framework)

A great framework for building dashboards within Pentaho's Business Intelligence Server User Console.

https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=10967601http://code.google.com/p/pentahoanalysistool/http://code.google.com/p/pentaho-cdf/http://www.prashantraju.com/http://www.prashantraju.com/http://code.google.com/p/pentaho-cdf/http://code.google.com/p/pentahoanalysistool/https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=10967601


4/20

Thanks

Thanks to the following blogs, people are groups:

Open Source Business Intelligence

Provided a working copy of the sample database for MySQL.

Bizcubed

Provided a working copy of the sample database for PostgreSQL.

Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL

A book by Roland and Jos van Dongen.

##pentaho & ##pentaho.patIRC channels found on Freenode (Pentaho and Pentaho PAT).

Pentaho Wiki & Pentaho Forums

The first place any new user to Pentaho should look.

http://osbi.nl/http://www.bizcubed.com.au/http://www.amazon.com/Pentaho-Solutions-Business-Intelligence-Warehousing/dp/0470484322http://freenode.net/http://wiki.pentaho.com/http://www.pentaho.com/forumshttp://www.prashantraju.com/http://www.prashantraju.com/http://www.pentaho.com/forumshttp://wiki.pentaho.com/http://freenode.net/http://www.amazon.com/Pentaho-Solutions-Business-Intelligence-Warehousing/dp/0470484322http://www.bizcubed.com.au/http://osbi.nl/


5/20

2. Downloads

Java JDK

The first thing is to check if you have a version of Java JDK (1.5 or greater) installed on your computer/server. You can easily check this by opening up a command prompt window and typing in the text foundin bold below:

C:\Users\Prashant>java -version

java version "1.6.0_17"

Java(TM) SE Runtime Environment (build 1.6.0_17-b04)

Java HotSpot(TM) Client VM (build 14.3-b01, mixed mode, sharing)

If you get a similar output to mine above you already have a version of the Java JDK installed.

If you don't get the above output here is an in depth tutorial on how to install and configure the Java JDK onWindows (only worry about steps 1 - 24).

The next step is to check if you have setup yourJAVA_HOME environment variable setup correctly. You

can also check this by opening up a command prompt window and typing in the text found in bold below:

C:\Users\Prashant>echo %JAVA_HOME%

C:\Program Files\Java\jdk1.6.0_13

If you get a similar output to mine above you already have setup yourJAVA_HOME environment variable,

if you don't make sure you completed all steps in the tutorial.

SQL Script Packs

Since Pentaho's Business Intelligence Server 2.0 release there has been incomplete/broken SQL scriptsprovided with all releases. I have decided to go through and create these "SQL Script Packs" for eachdatabase seen in this tutorial - these packs contain everything you need to setup your database of choice.

To download a SQL Script Pack click on the database which you are setting up Pentaho on and keep ithandy as you will need to use this in the next step.

MySQL 5.x PostgreSQL 8.x.x - you will need to download this one Oracle 10g & 11g

Pentaho Business Intelligence Server 3.5 Stable

The most important file you will need to download is thepentaho-ce-3.5.0.stable.zip file from the

Pentaho Sourceforge page. After you have downloaded this file extract its contents into a directory youwould like to have Pentaho running from - in this example I have chosenc:\pentaho.

http://www.strongkey.org/resources/documentation/installation/howto-install-jdk1.5.0-windows.htmlhttp://www.strongkey.org/resources/documentation/installation/howto-install-jdk1.5.0-windows.htmlhttp://www.prashantraju.com/wp-content/uploads/2010/01/pentaho-3.5.0.stable-postgresql-sql-pack-final.ziphttp://sourceforge.net/projects/pentaho/http://www.prashantraju.com/http://www.prashantraju.com/http://sourceforge.net/projects/pentaho/http://www.prashantraju.com/wp-content/uploads/2010/01/pentaho-3.5.0.stable-postgresql-sql-pack-final.ziphttp://www.strongkey.org/resources/documentation/installation/howto-install-jdk1.5.0-windows.htmlhttp://www.strongkey.org/resources/documentation/installation/howto-install-jdk1.5.0-windows.html


6/20

If you have successfully extracted the ZIP file you should have the following directories:

c:\pentaho\

adminstration-console\

biserver-ce\

We will need to make the majority of our changes inside thebiserver-ce directory.



7/20

3. Setting up the Database

Extract SQL Script Pack

In the previous step I told you to download the SQL Script Pack for PostgreSQL 8.x.x now you need toextract this file into a temporary location. Once you have done that you should have 5 SQL scripts:

1. create_repository_postgresql.sql2. create_quartz_postgresql.sql3. create_sample_datasource_postgresql.sql

4. load_sample_users_postgresql.sql5. sample_data_postgresql.sql

You must load all these scripts in the order shown above. Load the above SQL scripts using thePostgreSQL console.

Load SQL Scripts

Before we start make sure that you place all your SQL scripts in the directory which you will be logginginto the PostgreSQL console, in this example that isc:\.

The next step is to type in the following commands found in bold - one after the other:

c:\psql --username=postgres -f create_repository_postgresql.sql

Password for user postgres:

...outputPassword for user hibuser: [enter "password"]

c:\psql --username=postgres -f create_quartz_postgresql.sql

Password for user postgres:

...outputPassword for user pentaho_user: [enter "password"]c:\psql --username=postgres -f create_sample_datasource_postgresql.sql

Password for user postgres:Password for user hibuser: [enter "password"]

...outputc:\psql --username=postgres -f load_sample_users_postgresqlsql

Password for user postgres:Password for user hibuser: [enter "password"]...output

c:\psql --username=postgres -f sample_data_postgresql.sql

Password for user postgres:...output

Now run the following command (in bold) to see if you have successfully created ahibernate,quartz

andsampledata database:

c:\psql --username=postgres

Enter password:



8/20

psql>show databases;

Just for reference here are the databases and tables which should of been created after loading the SQLScript Pack:

hibernate

authorities datasource granted_authorities

users

quartz

qrtz_blob_triggers

qrtz_calendars

qrtz_cron_triggers

qrtz_fired_triggers

qrtz_job_details

qrtz_job_listeners

qrtz_locks

qrtz_paused_trigger_grpsqrtz_scheduler_state

qrtz_simple_triggers

qrtz_trigger_listeners

qrtz_triggers

sampledata

customer_w_ter

customers

department_managers

dim_time

employees

offices

orderdetailsorderfact

orders

payments

products

quadrant_actuals

trial_balance



9/20

4. Setting up the Business Intelligence Server

Overview

The following steps will require you to use a text editor i.e. Notepad, Wordpad etc. The next couple ofsections within this chapter are defined by the parent directory the files that need changing belong to.

pentaho-solutions\

You will need to change the configurations of the following files within thepentaho-solutions/

directory:

pentaho-solutions\

system\

applicationContext-spring-security-jdbc.xml

applicationContext-spring-security-hibernate.properties

hibernate\

hibernate-settings.xml

postgresql.hibernate.cfg.xml (optional)

applicationContext-spring-security-jdbc.xml

Once you have opened this file locate this section of code:

Make changes to the highlighted section so that your section of code looks similar to this:



10/20

applicationContext-spring-security-hibernate.properties

Once you opened this file you should see the following code:

jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate

jdbc.username=hibuserjdbc.password=password

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Make changes to the highlighted sections so that your code looks similar to this:

jdbc.driver=org.postgresql.Driver

jdbc.url=jdbc:postgresql://localhost:5432/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

hibernate-settings.xml

Once you have opened this file locate the following line of code:

system/hibernate/hsql.hibernate.cfg.xml


system/hibernate/postgresql.hibernate.cfg.xml

postgresql.hibernate.cfg.xml (optional)

You do not need to make any changes to this file if you would like to use the default User hibuser - whichwas created with theload_sample_users_postgresql.sql file. However, if you would like to specify

your own user find and change the following two lines of code:

hibuser

password

Make changes to the highlighted sections to a username and password of your choice.

If you would like to use a User which you have created yourself you will also need to make changes to the

other files which have hibuser and password specified as the default Username and Password.

tomcat\

You will need to change the configurations of the following files within thetomcat\ directory:

tomcat\

webapps\ pentaho\



11/20

WEB-INF\ web.xml (optional)

META-INF\ context.xml

web.xml (optional)

You do not need to make any changes to this file if you are happy with visiting the URL

http://localhost:8080/pentaho to access Pentaho Business Intelligence Server however, if you would like

others to access the site (remotely or on a network) you will need to make changes to this file.

Open up the file and locate this line off code:

http://localhost:8080/pentaho/

Make changes to the highlighted section to your desired domain or IP address so it looks similar to this:

http://www.prashantraju.com:8080/pentaho/or

http://100.100.10.1:8080/pentaho/

context.xml

Once you have opened up the file you should see the following code:


http://localhost:8080/pentahohttp://www.prashantraju.com/http://www.prashantraju.com/http://localhost:8080/pentaho


12/20



13/20

5. Setting up the Administration Console

To get the Administration Console working with PostgreSQL you will need to download the JDBC driverfor PostgreSQL. To do so, visit the PostgreSQL JDBC Driver downloads page and download a driver

which suits your Java version.

After downloading thepostgresql-8.4-701.jdbcx.jar file move it to thec:/pentaho/

administration-console/jdbc/ folder.

http://jdbc.postgresql.org/download.htmlhttp://www.prashantraju.com/http://www.prashantraju.com/http://jdbc.postgresql.org/download.html


14/20

6. Starting the Business Intelligence Server

For some reason when testing the tutorial with Windows setup I wasn't able to just run thestartup.bat

file located in thec:/biserver-ce/tomcat/bin directory, however I was able to get Tomcat running

by installing as a Windows service.

To get started you will need to first register tomcat as a service. To do this type the text found in boldbelow insidec:/biserver-ce/tomcat/bin directory:

C:\pentaho\biserver-ce\tomcat\bin>service.bat install tomcat5

Installing the service 'tomcat5' ...

Using CATALINA_HOME: D:\pentaho\biserver-ce\tomcat

Using CATALINA_BASE: D:\pentaho\biserver-ce\tomcat

Using JAVA_HOME: C:\Program Files\Java\jdk1.6.0_13

Using JVM: C:\Program

Files\Java\jdk1.6.0_13\jre\bin\server\jvm.dll

The service 'tomcat5' has been installed.

Once you have received the above output the next step is to start the Tomcat service. To dot his firstlyclick on theStart button thenRun and type inservices.msc and clickOK. AServices dialog box

should appear and it will list all available services, locate theApache Tomcat tomcat5 service and

double click on it to open up theProperties dialog box:



15/20


16/20

After logging in try and run a sample report from theSteel Wheels solution folder:



17/20



18/20

7. Starting the Administration Console

To start the Administration Console double click on the thestart-pac.bat file which is located under

thec:\pentaho\adminstration-console\ directory. It will pop up a new command prompt window

and should display something similar to the output below:

DEBUG: Using JAVA_HOME

DEBUG: _PENTAHO_JAVA_HOME=C:\Program Files\Java\jdk1.6.0_13

DEBUG: _PENTAHO_JAVA=C:\Program Files\Java\jdk1.6.0_13\bin\java.exe

2010-01-05 16:27:17.824::INFO: Logging to STDERR via

org.mortbay.log.StdErrLog

05/01/2010 4:27:18 PM org.pentaho.pac.server.JettyServer startServer

INFO: Console is starting

2010-01-05 16:27:18.118::INFO: jetty-6.1.2

2010-01-05 16:27:38.672::INFO: Started SocketConnector @ 0.0.0.0:8099

05/01/2010 4:27:38 PM org.pentaho.pac.server.JettyServer startServer

INFO: Console is now started. It can be accessed usinghttp://D119940:8099 or http://161.117.117.40:8099

Now you should be able to visit http://localhost:8099/ or the other two address's specified in your output(highlighted). You will be prompted for a Username and Password which are "admin" and "password". Ifyou have successfully started and logged into the administration console you should see the followingwelcome screen:

http://localhost:8099/http://www.prashantraju.com/http://www.prashantraju.com/http://localhost:8099/


19/20



20/20

8. Success!

You have correctly setup Pentaho Business Intelligence Server 3.5 on Windows with PostgreSQL 8.x.x.

Pentaho BI Server 3 5

Documents

Transcript of Pentaho BI Server 3 5