...
This document will show you how to first setup and configure Pentaho Data Integration to run ETL scripts necessary for some BI and reporting (PPI data export and reports which show a "Data for this report last updated on 2011-05-17 at 18:58:04" type message when entering parameters) . It also continues to show you how to setup the Pentaho business intelligence J2EE web application in an existing tomcat instance.
...
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
...
To verify CDA is working use sample CDA (download here) which need to be extracted in biserver-ce-3.7.0-stable/biserver-ce/pentaho-solutions.
John W : I think the reference above to biserver-ce-3.7.0 is wrong and should be /etc/pentaho ???
Deploy Mifos BI files
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
...