Table of Contents |
---|
Introduction and Scope
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)http://mifosforge.jira.com/browse/MIFOSBI-218 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.
Code Block |
---|
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,
Code Block |
---|
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
Code Block |
---|
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.