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 another.
- 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
Issues Outstanding
MIFOS-3871 - Data Warehouse doesnt handle removing group membership from a client.
MIFOS-3872 - Data Warehouse - mifos doesnt currently audit group transfer to another branch.
Basic Copy Data and Status Tests
Requirement |
Test |
Result |
---|---|---|
Copy over the simple 'dimensions' such as products, currencies, offices, personnels correctly |
Setup up an office hierarchy, some loan and savings products, some system users (loan officer and non loan officers) and run ETL |
Pass. |
Handle being run against an initialised mifos database |
run ETL against an initialised mifos 1.6.1 database |
Pass |
Handle simple hierarchy (loan officer, center, group, client, loan/savings account) creation. |
Setup a center hierarchy down to clients having loan and savings accounts |
Pass. All new customers and accounts are added to the datawarehouse as expected |
Handle when a client without a group membership is created and has its status changed and has a loan and a savings account. |
Client: BO 1 / client w/o group mem xxx 0003-000000028 |
Pass. New client and accounts are added to the datawarehouse as expected |
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. |
Group: group w/o center hierarchy 0004-000000029 |
Pass. New group, client and all accounts are added to the datawarehouse as expected |
Handle when a center has a savings account. |
Center: center with savings a/c 0004-000000031 |
Pass. Center and savings account added as expected. |
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.
Moving clients/groups can only be done if no active accounts exists for that client (all accounts must be closed). Except for clients with no group membership... moving to another branch puts them on-hold and the loan officer is removed and has to be reassigned.
Requirement |
Test |
Result |
---|---|---|
Handle moving a client from one group to another in the same center. |
Group: group dw1 1 - 0003-000000002 |
Pass. Client entry added to reflect new group. and savings entry added to link to new client entry. |
Handle moving a client from one group to another in the same branch |
Group: group dw1 1 - 0003-000000002 |
Pass. Client entry added to reflect new group. and loan entry added to link to new client entry. |
Handle moving a client from one group to another in a different branch |
Group: group dw1 1 - 0003-000000002 |
Pass. Client entry added to reflect new group. and savings entry added to link to new client entry. |
Handle removing group membership from a client. |
client: client remove group membership xxx 0003-000000050 |
Fail. The data is ETL's but the outcome looks as if the client had not group membership from the start. |
Handle moving client with no group membership to another branch. |
Client: client w/o gr memb xfer to branch xxx 0003-000000045 |
Pass. Client and savings account show application of 'Loan Officer Assigned', 'Branch membership' |
Handle a client with no group membership becoming a group member. |
Client: client add back to group hierarchy after xxx 0003-000000051 |
Pass. |
Handle a Loan Officer change for a client with no group membership. |
Client: client w/o grp memb hier LO change xxx 0003-000000044 |
Pass. Client and Savings entries as expected. As LO change was done on same day as client and |
Handle a Loan Officer change for a group with no hierarchy. |
Group: group w/o center hier LO change 0003-000000042 |
Pass. Group, client and account entries as expected. As LO change was done on same day as |
Handle moving group with no hierarchy from one branch to another. |
Group: group w/o center hier xfer branch 0003-000000046 |
Fail. Mifos audit bug - should audit like moving a client to another branch but doesnt. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Requirement
The ETL should handle moving group with no hierarchy from one branch to another
Group: group w/o center hier xfer branch 0003-000000046
Group savings: group savings product # 000100000000084
Client: client of group w/o center hier xfer branch xxx 0003-000000047
Client Loan: loan prod 1 # 000100000000086
field_name old_value new_value
'Status'
'Loan Officer Assigned' old lo -
'Branch membership' old branch new branch
so can't pick up these changes
but is handled in the ETL code if its fixed.
Requirement
The ETL should handle center change for a group to center in same branch
Group: group for change to center in same branch 0003-000000056
Savings: group savings product # 000100000000100
Result
Pass.
Requirement
The ETL should handle center change for a group to center in different branch
Group: group for change to center in diff branch 0003-000000057
Savings: group savings product # 000100000000102
Result
Pass. but mifos BUG changing of status to on-hold not audited.
Requirement - to do
The ETL should handle adding a group hierarchy back to a center
This looks to be covered. do one more test on diff days.
Test
Group: group going back to a center hier 0003-000000052
Savings: group savings product # 000100000000095
fail. it thinks it was part of the hierarchy all the time...
Centers
Requirement
The ETL should handle a Loan Officer change for a center
Test
Center: center with a LO change 0003-000000032
Center Savings: center savings # 000100000000067
Group: group for center LO change 0003-000000033
Group Savings: group savings product # 000100000000064
Client: client for center LO change xxx 0003-000000034
Client Loan: loan prod 1 # 000100000000066
Result
Pass. center, group & client customer entries good and associated accounts good.
Transaction Tests
Requirement |
Test |
Result |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|