...
sudo add-apt-repository "deb http://archive.canonical.com/ lucid partner"
sudo apt-get update
sudo apt-get install sun-java6-jdk libmysql-java
Install Data Integration/ETL
Download [pdi-ce-4.0.0-stable.tar.gz|http://downloads.sourceforge.net/project/pentaho/Data%20Integration/4.0.0-stable/pdi-ce-4.0.0-stable.tar.gz?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fpentaho%2Ffiles%2FData%2520Integration%2F4.0.0-stable%2F&ts=1295426768&use_mirror=voxel]
Extract it to a location we will call $PDI_HOME (the root of the Pentaho Data Integration)
Get Mifos BI 1.1
Get the Latest Mifos BI from sourceforge.org. [(download here)|https://ci.mifos.org/hudson/job/bi-1.1.x-archive/lastSuccessfulBuild/artifact/]
Extract the ETL folder to $PDI_HOME :
unzip -d $PDI_HOME mifos_bi-1.1.0.zip ETL/*
Extract the files etl_build.sh, etl_build_prod.sh, ppi_build.sh to $PDI_HOME:
unzip -d $PDI_HOME mifos_bi-1.1.0.zip etl_build.sh etl_build_prod.sh ppi_build.sh
Configure Pentaho database settings and install ETL
You will need to configure two databases to get pentaho up and running a "Hibernate" database and a "Quartz" database
echo "create database hibernate;" | mysql <root mysql credentials>
echo "create database quartz;" | mysql <root mysql credentials>
echo "grant all on hibernate.* to '<hibernate user>'@'localhost';" | mysql <root mysql credentials>
echo "grant all on quartz.* to '<quartz user>'@'localhost';" | mysql <root mysql credentials>
echo "set password for '<hibernate user id>'@'localhost' = password('<hibernate user password>');" | mysql <root mysql credentials>
echo "set password for '<quartz user id>'@'localhost' = password('<quartz user password>');" | mysql <root mysql credentials>
Note: hibernate user and quartz user can be the same.
You will need to configure two databases SourceDB and DestinationDB. Edit the file with SourceDB and DestinationDB login credentials
$PDI_HOME/simple-jndi/jdbc.properties (DestinationDB entries)
Also you will need to add these credentials to the "hibernate" database:
echo "CREATE TABLE DATASOURCE(NAME VARCHAR(50) NOT NULL PRIMARY KEY,MAXACTCONN INTEGER NOT NULL,DRIVERCLASS VARCHAR(50) NOT NULL,IDLECONN INTEGER NOT NULL,USERNAME VARCHAR(50) NULL,PASSWORD VARCHAR(150) NULL,URL VARCHAR(512) NOT NULL,QUERY VARCHAR(100) NULL,WAIT INTEGER NOT NULL);" | mysql <hibernate login creds>
echo "INSERT INTO DATASOURCE VALUES('SourceDB',20,'com.mysql.jdbc.Driver',5,'<SourceDB user>','<base64 encoded SourceDB password>','jdbc:mysql://<SourceDB host/port>/<SourceDB name>?useUnicode=true&characterEncoding=UTF-8','select 1',1000);" | mysql <hibernate login creds>
echo "INSERT INTO DATASOURCE VALUES('DestinationDB',20,'com.mysql.jdbc.Driver',5,'<DestinationDB user>','<base64 encoded DestinationDB password>','jdbc:mysql://<DestinationDB host/port>/<DestinationDB name>_dw?useUnicode=true&characterEncoding=UTF-8','select 1',1000);" | mysql <hibernate login creds>
Initialize Quartz DB
mysql <quartzdb credentials> < [create_quartz_mysql.sql|http://source.pentaho.org/svnroot/bi-platform-v2/tags/3.6.0-GA/bi-platform-sample-data/mysql5/create_quartz_mysql.sql]
Run ETL job
Set up a cron job to run the etl job. You may also run it manually:
$PDI_HOME/etl_build_prod.sh <destination db name> /usr/local/data-integration "<destination db mysql args>" > /tmp/data_integration.sh.out 2> /tmp/data_integration.sh.err
Install Pentaho BI Server
...
Configure Pentaho BI database parameters
Edit:
/etc/pentaho/solutions/applicationContext-spring-security-hibernate.properties
/etc/pentaho/system/hibernate/mysql5.hibernate.cfg.xml
$TOMCAT_HOME/webapps/pentaho/META-INF/context.xml (jdbc/Hibernate entry)
with the Hibernate login credentials.
and
$TOMCAT_HOME/webapps/pentaho/META-INF/context.xml (jdbc/Quartz entry)
with the Quartz login credentials.
Edit /etc/pentaho/system/hibernate/hibernate-settings.xml , so that the config-file line reads
<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
Edit the following file with the destination DB entries
/etc/pentaho/system/simple-jndi/jdbc.properties
...