...
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
Extract it to a location we will call $PDI_HOME (the root of the Pentaho Data Integration)
Get Mifos BI 1.2
Get the Latest Mifos BI from sourceforge.org. download here
Extract the ETL folder to $PDI_HOME :
unzip -d $PDI_HOME mifos_bi-1.2.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.2.0.zip etl_build.sh etl_build_prod.sh ppi_build.sh
John W: These 3 sh files aren't in the 'download' zip (they are under the git bi directory however). They probably aren't needed as running full_reload.sh (with proper arguments works for me). However, Sumit has set up the hosted MFI to use etl_build_prod.sh with cron. So the download is okay as long the instructions to 'Run ETL Job" below is written for full_reload.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 >
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
...
unzip -j -d /etc/pentaho/system/pentaho-cdf mifos_bi-1.2.0.zip mifos_bi-1.2.0/reports/CDFReportingTemplate/template-dashboard-mifosreports.html
unzip -j -d /etc/pentaho/system/pentaho-cdf mifos_bi-1.2.0.zip mifos_bi-1.2.0/DashboardsTemplate/template-dashboard-dashboards.html
unzip -j -d /etc/pentaho/system/pentaho-cdf/resources/style/images mifos_bi-1.2.0.zip mifos_bi-1.2.0/reports/CDFReportingTemplate/*.jpg
unzip -d /etc/pentaho mifos_bi-1.2.0.zip mifos_bi-1.2.0/reports/*.*
mv /etc/pentaho/mifos_bi-1.2.0/reports/Dashboards /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/DashboardsMenu /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/standardReports /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/commonActions /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/mpesa /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/dataAccess /etc/pentaho
mv /etc/pentaho/mifos_bi-1.2.0/reports/i18n/org $TOMCAT_HOME/webapps/pentaho/WEB-INF/classes
rm -rf r /etc/pentaho/reports/CDFReportingTemplate
rmdir /etc/pentaho/reports/i18n
rmdir /etc/pentaho/reportsmifos_bi-1.2.0
chown -R tomcat6 $TOMCAT_HOME/webapps
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
...