Versions Compared

Old Version 2

changes.mady.by.user johnwoodlock

Saved on

compared with

New Version 3

changes.mady.by.user johnwoodlock

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table Name

Description

ETL Approach

 

stg_customer_and_account_updates

Staging table - into which are put all new customers and account and all changes 
to hierarchy and status's.  The entries are then processed 'in order' to load the dim_customer
and dim_loan and dim_savings tables.

Loaded from empty when ETL Run

 

stg_personnel_names_and_name_changes

Staging table - when a center, group or client changes loan officer... the old and new loan officer
name is recorded on the mifos change_log table.  Can run into difficulties if the loan office name
is not unique either on the personnel table or within the list of whatever name changes have
occured.  This table's main purpose is to ensure that duplicate names are picked up (is a unique
key on name and all names and name changes are loaded) and the secondary reason is to simplify
the ETL coding for picking up the correct personnel_id for the loan officer.

Loaded from empty when ETL Run

 

stg_customer_type1_columns

Staging table - latest value of customer columns not involved in type 2 updates.  Allows columns to
be added to dim_customer without touching current stored procs (use to manage hierarchy and status
changes for customers and accounts).

Loaded from empty when ETL Run

stg_loan_type1_columns

Staging table - latest value of loan account columns not involved in type 2 updates.  Allows columns to
be added to dim_loan without touching current stored procs (use to manage hierarchy and status 
changes for customers and accounts).

Loaded from empty when ETL Run

Info

Other

Table Name

Description

ETL Approach

 

dw_mfi_configuration

currently contains:
loan_lateness_days - no. of days before a loan is considered in bad standing (is used in arrears reporting)
savings_dormancy_days - no. of days before a savings active is dormant before it is considered inactive (not currently used in mifos or DW)

Loaded from prd_type mifos table

 

dw_loan_schedules

loan schedule details (was a 'fact' table but decided against that as found it very hard to keep schedule entries tracked to the dim_loan entries.

Transactions simply extracted from mifos equivalent.  Only links to the loan_account_id not a specific entry in dim_loan. Need to be updated if the schedule dates change (if disbursed on a date different to the expected date).

 

...