...
In order to run Mifos BI, you need to install a server running ubuntu 10.04 LTS with the following software installed:
- Sun Microsystems Java - JDK/JRE 1.6
- MySQL - Community Edition 5.1.45
- Pentaho BI Server 3.6.70
- Pentaho CDA 1.0.0
- Pentaho Data Integration Community Edition 4.0.0
Note: these tools can also be run on Windows, but instructions and scripts are currently provided only for Ubuntu.
Mifos BI 1.0 works with Mifos 2.0 and above.
Install base os dependencies
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 Pentaho BI Server
Download Pentaho BI Server biserver-ce-3.76.0-stable.zip
Install Pentaho with MySQL 5.1 using this document (Google doc)
After installing Pentaho BI server correctly and testing it's working as expected.
Install CDA in in Bi Server
Download CDA for Pentaho Server 3.7 (download here)
Extract the "cda folder in tar.gz
tar xzf biserver-ce-3.76.0-stable.tar.gz -C /tmp/bi
cp -r /tmp/bi/biserver-ce/tomcat/webapps/pentaho /var/lib/tomcat6/webapps
cp -r /tmp/bi/biserver-ce/tomcat/webapps/pentaho-style /var/lib/tomcat6/webapps
cp -r /tmp/bi/biserver-ce/tomcat/webapps/sw-style /var/lib/tomcat6/webapps
mkdir -p /etc/pentaho
cp -r /tmp/bi/biserver-ce/pentaho-solutions/system /
Restart the server.
etc/pentaho
cp /usr/share/java/mysql.jar /var/lib/tomcat6/webapps/pentaho/WEB-INF/lib/mysql.jar
Install CDA in BI Server
download cda-installer-v1.0.jar
java -jar cda-installer-v1.0.jar
select 3.6 version of pentaho
point to /etc/pentaho/solutions when asked for pentaho solutions directory
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.
Install Data Integration/ETL
Download pdi-ce-4.0.0-stable.tar.gz
tar xzf pdi-ce-4.0.0-stable.tar.gz -C $imagedir/usr/local
Get Mifos BI 1.0
Get the Latest Mifos BI from sourceforge.org. (download here)
Install ETL + Data Warehouse database
Let's call existing Mifos application database (mifos) as SourceDB
Let's call Mifos Data Warehouse database (mifos_dwh) as DestinationDB
Set up JNDI entries for SourceDB and DestinationDB in the administration console. Only DestinationDB is needed for the datawarehouse, the other (mifos application database) is needed if running ETL or running non-DW reports so you may as well set them both up at the same time.
1st go into the administration-console directory and run start-pac.bat
then go to your browser and put in "localhost:8099" log in as admin, click Administration, then 'database connections', then the + to add a new one and fill in
name: SourceDB
driver class: com.mysql.jdbc.Driver
user name: root (or whatever mysql user you want)
password: *****
Url: jdbc:mysql://localhost:3306/mifos
the bit at the end is the database name to point to. Then click test and assuming you get a clean test, save it. Do same for DestinationDB
Install Pentaho Data Integration Tool 4.0.0-stable
the ETL source is in bi/ETL/MifosDataWarehouseETL
go to where you installed pdi-ce-4.0.0-stable and then to the the file data-integration\simple-jndi\jdbc.properties and add:
SourceDB/type=javax.sql.DataSource
SourceDB/driver=org.gjt.mm.mysql.Driver
SourceDB/url=jdbc:mysql://localhost:3306/mifosunzip -j -d /etc/pentaho/system/pentaho-cdf mifos_bi-1.0.0.zip reports/CDFReportingTemplate/template-dashboard-mifosreports.html
unzip -j -d /etc/pentaho/system/pentaho-cdf/resources/style/images mifos_bi-1.0.0.zip reports/CDFReportingTemplate/*.jpg
unzip -d /usr/local/data-integration mifos_bi-1.0.0.zip ETL/*
unzip -d /etc/pentaho mifos_bi-1.0.0.zip reports/*
mv /etc/pentaho/reports/standardReports /etc/pentaho
mv /etc/pentaho/reports/commonActions /etc/pentaho
mv /etc/pentaho/reports/dataAccess /etc/pentaho
mv /etc/pentaho/reports/i18n/org /var/lib/tomcat6/webapps/pentaho/WEB-INF/classes
rm -rf /etc/pentaho/reports/CDFReportingTemplate
rmdir /etc/pentaho/reports/i18n
rmdir /etc/pentaho/reports
chown -R tomcat6 /var/lib/tomcat6/webapps
Configure pentaho and databases, 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.
Edit:
/etc/pentaho/solutions/applicationContext-spring-security-hibernate.properties
/etc/pentaho/system/hibernate/mysql5.hibernate.cfg.xml
/var/lib/tomcat6/webapps/pentaho/META-INF/context.xml (jdbc/Hibernate entry)
with the Hibernate login credentials.
and
/var/lib/tomcat6/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>
You will need to configure two databases SourceDB and DestinationDB
The existing Mifos application database (mifos) will be the SourceDB
The Mifos Data Warehouse database (mifos_dw) will be the DestinationDB
/usr/local/data-integration/simple-jndi/jdbc.properties (DestinationDB entries)
/etc/pentaho/system/simple-jndi/jdbc.properties (DestinationDB entries)
with SourceDB and DestinationDB login credentials
Also you will need to add these credentials to the "hibernate" database:
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
SourceDB/user=root
SourceDB/password=****
DestinationDB/type=javax.sql.DataSource
DestinationDB/driver=org.gjt.mm.mysql.Driver
DestinationDB/url=','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://localhost:3306/mifos_dwh<DestinationDB host/port>/<DestinationDB name>_dw?useUnicode=true&characterEncoding=UTF-8
DestinationDB/user=root
DestinationDB/password=****
run bi/ETL/MifosDataWarehouseETL/full_reload_sh (after editing MYSQL_ARGS to correct values)
./full_reload.sh mifos_dwh PDI/data-integration','select 1',1000);" | mysql <hibernate login creds>
initialize quartz db:
mysql <quartzdb credentials> < create_quartz_mysql.sql
install CDA "hack":
edit line 24 in:
/etc/pentaho/solutions/system/pentaho-cdf/js/CoreComponents.js
to
line 24> default: myArray.push([this.resultp0,this.resultp1]);break;
See http://thread.gmane.org/gmane.comp.finance.mifos.devel/10466
setup ETL job
set up a cron job to run the etl job:
/usr/local/data-integration/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