Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 14 Next »

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 configurtation 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:

  1. Latest database structure (tables, relationships, constrainst etc)
  2. Reference Data that comes out of box and must exist for mifos to startup
  3. 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');

Roles and Permissions

  • Relaunched application with all the reference data as shown above.
  • Manually created five roles (Branch Accountant, Branch Accountant Assistant, Branch Manager, Program Officer, Program Unit Supervisor)

Model Organisation

After ensuring configuration, reference data is setup correctly for application, its time to add organizational items that are central to how mifos application works.

Model Office Hierarchy and offices

  • There are ~100 offices of which ~70 are branches so there are other types of offices. An office hierarchy of Head Office -> Regional Office -> Area Office -> Branch Office is used (dropped divisional office)
  • Manually created the first 11 offices: Customer Head Office, Luzon, North Luzon, Central Luzon, Capas, NCR, NCR North, Tandang Sora, Bulacan, Meycauayan, Valenzuela of which 4 were branch offices Capas, Tandang Sora, Meycauayan, Valenzuela.

NOTE: Only created first 11 up as far as Valenzuela as I want to migrate data for this branch into mifos.

Model Personnel/Staff/Application Users

  • The first data to be migrated is for a specific branch-loan-officer-group so I manually create a Personnel as a loan officer and associate with Valenzuela branch.

Model Financial Products

  • There is one general loan product available so manually created loan product.
  • Didn't have much info on savings product so manually created volunatry savings product.

Migrate group information

Migrate client members of group information

Migrate Loans using Opening Balance Approach

Migrate Savings using Opening Balance Approach

  • No labels