mifos_dw_etl_test_dataset.zip

The Requirements/Test Summary

This is (probably) a temporary wiki page showing details of manual testing of Data Warehouse 1.0.  Attached is the mifos 1.6.1 database backup (mifos_dw_etl_test_dataset.zip) which has the test data used during ETL testing.  This page gives some idea of what's involved in the ETL job that moves data from mifos to the data warehouse.  The actual Pentaho Data Integration job (CreateDataWarehouse.kjb) is in the business intelligence git repository (git://mifos.git.sourceforge.net / mifos/bi).  Manual testing was by query inspection and running against some standard reports (Loan Officer Detailed Report, MFI Progress Report, Due vs Collect Branch Report and Due vs Collected Loan Officer Report).

The Data Warehouse ETL job should...

• copy over the simple 'dimensions' such as products, currencies, offices, personnels correctly
• handle being run against an initialised mifos database
• handle simple hierarchy (loan officer, center, group, client, loan/savings account) creation.
  Involves handling new entries and catering for status changes when making groups, clients and accounts active.  
• handle when a client without a group membership is created and has its status changed and has a loan and a savings account. 
• handle when a group without center hierarchy is created and has its status changed and has a client with a loan and a savings account and has its own savings and loan account as well. 
• handle when a center has a savings account.
•  
• handle moving a client from one group to another in the same center. 
• handle moving a client from one group to another in the same branch 
• handle moving a client from one group to another in a different branch
• handle removing group membership from a client. 
• handle moving client with no group membership to another branch. 
• handle a client with no group membership becoming a group member. 
• handle a Loan Officer change for a client with no group membership. 
• handle a Loan Officer change for a group with no hierarchy.
• handle moving group with no hierarchy from one branch to anotherhandle removing group membership from a client.
• handle center change for a group to center in same branch
• handle center change for a group to center in different branch
• handle adding a group hierarchy back to a center
• should handle a Loan Officer change for a center
•  
• All savings transactions should link to the latest savings account entry that is 'open' (active or inactive but not closed) for the date matching the transaction action_date. 
• All repayment transactions should link to the latest loan account entry that is 'active' (good or bad standing)for the date matching the transaction action_date 
• All write off transactions should link to the loan account entry having a loan writeoff status.  
  Write-off account_action_id is 15 - 'AccountAction-LoanWrittenOff' 
• All disbursal transactions should link to the latest loan account entry that is pre-active -  'AccountState-ApplicationApproved'standing or 'AccountState-DisbursedToLo
  (which is probably not used and deprecated) for the date matching the transaction action_date

Basic Copy Data and Status Tests

Customer Hierarchy Change Tests

Note: There are 3 types of change.  New, Hierarchy and Status and if they occur on the same day they are processed in the order New, Hierarchy then Status.  This means it can be technically wrong.  For example,  if you create a client, make her active then move the client to another group all on the same day it will show as new client, move to new group, make active.  Couldn't distinguish which event was first because auditing in mifos is at a day level.

Transaction Tests