Introduction and Scope

http://mifosforge.jira.com/browse/MIFOSBI-218

Data Warehouse 1.0 is Stage One

The soon-to-be-released Mifos Technology Plan will probably have as one of its main goals "Transform Mifos into a best-of-breed business intelligence system for microfinance".  Part of this vision involves the development of a data warehouse that contains MFI data from the Mifos application as well as other sources.  The scope of this 'stage one' data warehouse initiative (DW 1.0) is to provide the ability to tackle the traditionally 'gnarly' mifos reports.

Focus on High Cost Gnarly Reports

Gnarly reports aren't the  'show me a list of clients and their addresses' type of report.  They are the operational and finance reports that show a raft of metrics, transaction totals and arrears aging information at MFI/Branch/Loan Officer/Center levels on current and historical date ranges.

Prior Mifos releases shipped with just a few reports... the Branch Progress report being typical of the gnarly type.  As a result, a number of clients have developed their own gnarly reports. (Ryan Whitney, Sam Birney, Sungard are some of the people who have been at the coalface) is the issue that this wiki page relates to.

Note: Further changes to the checking scripts won't be attached to this wiki page but are available from the bi git repository in directory ETL/scripts.

Before and After Checks on the Mifos OLTP and Data Warehouse Databases

Mifos OLTP Database Pre-ETL Check

Run the attached script mifosPrecheck.sql to see if any blocking and/or warning data quality issues are likely to come up when creating the Data Warehouse from the Mifos database.

mysql -uUSER -pPASSWORD OLTP_DATABASENAME < mifosPrecheck.sql > mifosPrecheck.out

For blocking issues (and further information on any warning issues) community members should contact the mifos developer list. 

Typical problems highlighted include creating accounts before the corresponding clients, lack of account status history, duplicate fees and loan officers... mostly due to initial migration into mifos needs or moving the system date backwards and forwards.

Data Warehouse Data Quality Check

The mifos data warehouse suite comes with a job that will check your data warehouse data quality.

In ubuntu,

bash bi/data_warehouse_health_check.sh <PDI_HOME> <PDI_JOB> <OUTPUT_FILE>

e.g. bash bi/data_warehouse_health_check.sh /opt/pentaho/data-integration /home/ubuntu/bi passfail.log

Trouble-Shooting if ETL Job Fails

If the job that brings mifos data into the data warehouse fails... run the attached script mifosUnsuccessfulETL.sql

mysql -uUSER -pPASSWORD DATAWAREHOUSE_DATABASENAME < mifosUnsuccessfulETL.sql > mifosUnsuccessfulETL.out

Typical problems highlighted include creating clients before groups.

Community members should contact the mifos developer list with the output and may need to supply a copy of the mifos database and incomplete data warehouse database for further trouble shooting.