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
Issues Outstanding
http://mifosforge.jira.com/browse/MIFOS-3871 Data Warehouse doesnt handle removing group membership from a client.
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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Requirement - to do
The ETL should handle removing group membership from a client.
Test
client: client remove group membership xxx 0003-000000050
loan: loan prod 1 # 000100000000091
moved to 'sturty' loan officer
&
remove group membership xxx 0003-000000054
Test
Fail. Hard to pick up a client that was a group member and then has membership removed because it looks like a client that was w/o group membership from the start. Would be same from group except you can't remove a group from center membership???
Result
Fail. client and loan not versioned. Current value (no group) given to all. need to pick up on. all one change log.
field_name old_value new_value
'groupFlag' 1 0
'Group Name' the group name -
Requirement
The ETL should handle moving client with no group membership to another branch.
Test
Client: client w/o gr memb xfer to branch xxx 0003-000000045
Client Savings: savings product 1 # 000100000000082
Result
Pass. Client and savings account show application of 'Loan Officer Assigned', 'Branch membership' and 'Status' changes associated with a branch membership change. Lo is nulled out, client status is changed to 'on hold'.
Requirement
The ETL should handle a client with no group membership becoming a group member.
Client: client add back to group hierarchy after xxx 0003-000000051
Savings: savings product 1 # 000100000000093
Result
Pass.
Requirement
The ETL should handle a Loan Officer change for a client with no group membership.
Test
Client: client w/o grp memb hier LO change xxx 0003-000000044
Client Savings: savings product 2 # 000100000000080
changed 'smith' to 'xxx'
Result
Pass. Client and Savings entries as expected. As LO change was done on same day as client and savings creation... the LO change is treated as if it happened before the client active status and before the savings account was created (because changes on the same day are appled in order of new, hierarchy then status changes and centers are processed before groups before clients before accounts)
Groups
Requirement
The ETL should handle a Loan Officer change for a group with no hierarchy.
Test
Group: group w/o center hier LO change 0003-000000042
Group Savings: group savings product # 000100000000076
Client: client for group w/o center hier LO change xxx 0003-000000043
Client Loan: loan prod 1 # 000100000000078
changed 'smith' to 'xxx'
Result
Pass. Group, client and account entries as expected. As LO change was done on same day as group, client and account creation... the LO change is treated as if it happened before the group active status and before the client and accounts were created (because changes on the same day are appled in order of new, hierarchy then status changes and centers are processed before groups before clients before accounts)
Requirement
The ETL should handle moving group with no hierarchy from one branch to another
BUG should audit like moving a client to another branch but doesnt
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|