Installing Mifos BI 1.0

Owned by UdaiU (Unlicensed)
Last updated: Sept 11, 2011 by Sky Purdin (Unlicensed)Version comment
2 min read

Software Requirements

In order to run Mifos BI, you need 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.0
  • 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.6.0-stable.tar.gz
tar xzf biserver-ce-3.6.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 /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)

unzip -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','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

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