An Example Data Migration
Analysis of MFI and their source data
MFI has ~100 offices (~70 are branchs) and wishes to migrate 5 of these branchs to mifos.
MFI has x amount of personnel, y of which are loan officers, z of which are other staff, such as data-entry, manager etc
MFI has 1 Loan Product (a individual loan applicable to clients, 1-24 installments, weekly installments)
No Centers as such.
Number of groups per branch?
Number of clients per group?
Group with many clients
Clients with individual loans and savings accounts
Custom Fields for client information
Decision is taken to manually enter Personnel, Offices, Reference Data (Loan Purposes),
Decision is taken to use an 'opening balance' approach. Basically for a specific branch and field-officer, if that field-officer is meeting with a praticular group on say Monday. Once the day is over, a spreadsheet with all the details of the loan for each client member of group is captured and the following details are tracked:
Original Loan Amount
no of installments
Disbursement Date
First Installment Date
Current Installment Date
Amount Paid to Date (Principla + Interest)
Loan Cycle
Loan Purpose
extraction, transformation and loading of data
This information is then extracted from template spreadsheet and (possibly transformed in some way) and loaded into mifos.
At this point this group is considered to be on mifos and from now on they should be using mifos application
Pros
Consolidation? ask ryan again?
Cons
Past history of loans is not captured.
Steps to getting setup
Create latest schema with lastest data
From a blank mifos schema:
- apply latest-schema.sql
- apply latest-data.sql
- update 'admin' users password to 'testmifos'
update personnel set password=0x22648ca42330a561964dbe32f54e6a04a49d03f203ca64b2515338d9 where personnel_id=1;
Use custom configutation files
After doing analysis on the mfi, it may be possible to put together application configuration files:
- edit applicationConfiguration.custom.properties and place in configuration directory e.g. MIFOS_CONF
- create mifosBeanConfig.custom.xml (think this must go in application server classpath (lib folder for instance))
- create mifosChartOfAccounts.custom.xml and place in configuration directory e.g. MIFOS_CONF
Launch Application
At this point you should be able to start mifos application against mifos schema. As a result the database at present has the following information:
- Latest database structure (tables, relationships, constrainst etc)
- Reference Data that comes out of box and must exist for mifos to startup
- Persistence of some configuration from applicationConfiguration.custom.properties which now cannot be changed.
Reference Data
Executing the following query will show you the existing reference data
SELECT l.lookup_id, le.entity_name, l.lookup_name, le.description FROM lookup_value l INNER JOIN lookup_entity le ON l.entity_id=le.entity_id order by l.entity_id, l.lookup_id;
Seed Reference Data
Reference data populated by applying latest-data.sql and which must exist for application to work.
Data Type |
entity_id |
Options |
|---|---|---|
ClientStatus |
1 |
Pending Approval, active, on hold, cancelled, closed |
GroupStatus |
2 |
Partial, Pending, active, on hold, cancelled, closed |
AccountStates |
5 |
partial, pending, approved, disbursed to LO, active-good-standing, active-bad-standing, closed-obligations-met, closed-written-off, closed-rescheduled, cancelled |
PovertyStatus |
10 |
poor, very poor, non poor |
Gender |
16 |
male, female |
PersonnelLevels |
30 |
loan officer, non loan officer |
SpouseFather |
52 |
spouse, father, mother, child |
CustomerStatus |
53 |
active, inactive |
PersonnelStatus |
56 |
active, inactive |
Language |
74 |
English, Spanish, French, etc |
LivingStatus |
92 |
Together, Not Together |
Custom Reference Data
Reference data that is optional and typically different on a customer by customer basis
Data Type |
entity_id |
Options |
|---|---|---|
Salutation |
15 |
Mr., Mrs. |
MaritalStatus |
17 |
single, married, seperated, widow |
Citizenship |
18 |
Roman Catholic,Protestant Baptist Pentecostal,Born Again Christian,Seventh Day Adventist,Iglesia Ni Kristo,Jehovahs Witness,Latter Day Saints,Islam,Others |
Ethinicity |
19 |
Yes, No |
EducationLevel |
20 |
Elementary, High School, College, Vocational, None |
BusinessActivities |
21 |
BuyAndSell, Vending, SariSariStore, Retailing, Dressmaking, Garments, Handicrafts, FoodProcessing, SoapMaking, PerfumeMaking, DishwashingDetergentMaking, BeadsAccessoriesMaking, Tailoring, Upholstery, Vulcanizing, DoormatMaking, TricycleOperator |
PersonnelTitles |
29 |
Program Officer, Program Unit Supervisor, Branch Accountant, Branch Accountant Assistant, Branch Manager |
NOTE: Citizenship is used to track religon
NOTE: Ethinicity is used to track ?
You should restart the application after inserting in custom reference data as lookup values are cached on start up.
SQL Script for inserting in customer specific reference data
-- Entity: Salutation insert into lookup_value(lookup_id,entity_id,lookup_name) values (2000, 15, 'Salutation-Mr'), (2001, 15, 'Salutation-Mrs'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2000,1, 2000,'Mr'), (2001,1, 2001,'Mrs'); -- Entity: MaritalStatus insert into lookup_value(lookup_id,entity_id,lookup_name) values (2002, 17, 'MaritalStatus-Single'), (2003, 17, 'MaritalStatus-Married'), (2004, 17, 'MaritalStatus-Seperated'), (2005, 17, 'MaritalStatus-Widow'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2002, 1, 2002, 'Single'), (2003, 1, 2003, 'Married'), (2004, 1, 2004, 'Seperated'), (2005, 1, 2005, 'Widow'); -- Entity: Citizenship insert into lookup_value(lookup_id,entity_id,lookup_name) values (2006, 18, 'Citizenship-RomanCatholic'), (2007, 18, 'Citizenship-ProtestantBaptistPentecostal'), (2008, 18, 'Citizenship-BornAgainChristian'), (2009, 18, 'Citizenship-SeventhDayAdventist'), (2010, 18, 'Citizenship-IglesiaNiKristo'), (2011, 18, 'Citizenship-JehovahsWitness'), (2012, 18, 'Citizenship-LatterDaySaints'), (2013, 18, 'Citizenship-Islam'), (2014, 18, 'Citizenship-Others'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2006, 1, 2006, 'Roman Catholic'), (2007, 1, 2007, 'Protestant Baptist Pentecostal'), (2008, 1, 2008, 'Born Again Christian'), (2009, 1, 2009, 'Seventh Day Adventist'), (2010, 1, 2010, 'Iglesia Ni Kristo'), (2011, 1, 2011, 'Jehovahs Witness'), (2012, 1, 2012, 'Latter Day Saints'), (2013, 1, 2013, 'Islam'), (2014, 1, 2014, 'Others'); -- Entity: Ethinicity insert into lookup_value(lookup_id,entity_id,lookup_name) values (2015, 19, 'Ethinicity-Yes'), (2016, 19, 'Ethinicity-No'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2015, 1, 2015, 'Yes'), (2016, 1, 2016, 'No'); -- Entity: EducationLevel insert into lookup_value(lookup_id,entity_id,lookup_name) values (2017, 20, 'EducationLevel-Elementary'), (2018, 20, 'EducationLevel-High School'), (2019, 20, 'EducationLevel-College'), (2020, 20, 'EducationLevel-Vocational'), (2021, 20, 'EducationLevel-None'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2017, 1, 2017, 'Elementary'), (2018, 1, 2018, 'High School'), (2019, 1, 2019, 'College'), (2020, 1, 2020, 'Vocational'), (2021, 1, 2021, 'None'); -- Entity: PersonnelTitles insert into lookup_value(lookup_id,entity_id,lookup_name) values (2022, 29, 'PersonnelTitles-ProgramOfficer'), (2023, 29, 'PersonnelTitles-ProgramUnitSupervisor'), (2024, 29, 'PersonnelTitles-BranchAccountant'), (2025, 29, 'PersonnelTitles-BranchAccountantAssistant'), (2026, 29, 'PersonnelTitles-BranchManager'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2022, 1, 2022, 'Program Officer'), (2023, 1, 2023, 'Program Unit Supervisor'), (2024, 1, 2024, 'Branch Accountant'), (2025, 1, 2025, 'Branch Accountant Assistant'), (2026, 1, 2026, 'Branch Manager'); -- Entity: BusinessActivities insert into lookup_value(lookup_id,entity_id,lookup_name) values (2027, 21, 'BusinessActivities-BuyAndSell'), (2028, 21, 'BusinessActivities-Vending'), (2029, 21, 'BusinessActivities-SariSariStore'), (2030, 21, 'BusinessActivities-Retailing'), (2031, 21, 'BusinessActivities-Dressmaking'), (2032, 21, 'BusinessActivities-Garments'), (2033, 21, 'BusinessActivities-Handicrafts'), (2034, 21, 'BusinessActivities-FoodProcessing'), (2035, 21, 'BusinessActivities-SoapMaking'), (2036, 21, 'BusinessActivities-PerfumeMaking'), (2037, 21, 'BusinessActivities-DishwashingDetergentMaking'), (2038, 21, 'BusinessActivities-BeadsAccessoriesMaking'), (2039, 21, 'BusinessActivities-Tailoring'), (2040, 21, 'BusinessActivities-Upholstery'), (2041, 21, 'BusinessActivities-Vulcanizing'), (2042, 21, 'BusinessActivities-DoormatMaking'), (2043, 21, 'BusinessActivities-TricycleOperator'); insert into lookup_value_locale(lookup_value_id,locale_id,lookup_id,lookup_value) values (2027, 1, 2027, 'Buy And Sell'), (2028, 1, 2028, 'Vending'), (2029, 1, 2029, 'Sari Sari Store'), (2030, 1, 2030, 'Retailing'), (2031, 1, 2031, 'Dressmaking'), (2032, 1, 2032, 'Garments'), (2033, 1, 2033, 'Handicrafts'), (2034, 1, 2034, 'Food Processing'), (2035, 1, 2035, 'Soap Making'), (2036, 1, 2036, 'Perfume Making'), (2037, 1, 2037, 'Dishwashing Detergent Making'), (2038, 1, 2038, 'Beads Accessories Making'), (2039, 1, 2039, 'Tailoring'), (2040, 1, 2040, 'Upholstery'), (2041, 1, 2041, 'Vulcanizing'), (2042, 1, 2042, 'Doormat Making'), (2043, 1, 2043, 'Tricycle Operator');