Data Warehouse Database Design

Dimension modelling reference tables

Dimension modelling fact tables

Daily balance tables derived from Data Warehouse fact tables.

 Lookup tables copied from mifos OLTP data.

Staging tables.  Not used for reporting.

Lookup tables for Data Warehouse convenience

Schedule tables cut down and copied to Data Warehouse to allow some processing of Due Vs Collected type functionality

hist_loan_balances

Contains daily balances for 'active' loan accounts (good and bad standing status's).  Doesn't included reversed transactions as they affect the 'as of' situation.  
For example, a loan might be active on Jan 1 (so should appear on a report run for Jan 1).  On Jan 2, the loan may be reversed which creates backdated transactions to contra all repayment and disbursal transactions created.
Example of listing all loans as of 1st Aug 2010:

SELECT cust.display_name, l.loan_account_id, l.loan_amount, l.loan_status, l.disbursement_date,
       ifnull(lb.principal_outstanding, 0.000) balance
FROM dim_loan l
LEFT JOIN dim_customer cust ON cust.customer_key = l.customer_key
LEFT JOIN hist_loan_balances lb ON lb.loan_account_id = l.loan_account_id and lb.as_of_date = '2010-08-01'
WHERE  (l.valid_from <= '2010-08-01' and l.valid_to > '2010-08-01')
ORDER BY cust.display_name, l.loan_account_id

hist_loan_arrears

Contains daily balances for over due loans (hist_loans_overdue is a more correct table name).

Overdue: loan is not fully up to date with repayment schedule
In Arrears: loan is overdue by more than 'lateness' days (where lateness is defined within mifos Admin functionality and copied to DW table dw_mfi_configuration).  

hist_savings_balances

Contains daily balances for 'active' savings accounts (active and inactive status's).

dim_date

Very typical date dimension table used in star schema for handy date processing e.g. processing by quarter or month or 2nd day of the week.

Copied from what Jos had in his and Roland's book :) but can be added to (by hand or otherwise) to give whatever data categorisation is relevant.

dim_account_action

Just a list of account actions that appear on mifos account_trxn table.  
Really used to pickup things like write-offs when processing repayments

Very simple. Just copied from mifos values.  No need to manage 'type 2' changes.

dim_currency

list of currencies.  Currency doesn't feature much in current reports but probably will.

Very simple. Just copied from mifos values. No need to manage 'type 2' changes.  
A currency key is related to a dim_loan or dim_savings entry.  Its really related to a
dim_product but current mifos  implementation allows null currency at product level
if just a default currency is used... so decided to just make sure accounts have their currency.

dim_product

Just a list of products from mifos table prd_offering with a couple of extra columns to cater for savings product specifics.

Very simple. Just copied from mifos values. No need to manage 'type 2' changes.

dim_office

Just a list of offices from the mifos table office.  Does include the search_id column that does the 'hierarchy' magic... to help with hierarchy processing in reports (i.e. easy to pick up all branches under a head office.

The was one I wanted to capture movement of, for example, branches to other regional offices.  However, that change is not record in mifos.  So, for now, this is treated as a simple copy of current information.

dim_personnel

Just a list of MFI people from the mifos table personnel.  Most relevant because it holds the loan officers... didn't call it dim_loan_officer because wanted to take all MFI people especially as people can 'turn into' and 'out of' loan officers over time.

Apply 'type 2' when loan officers move branch.  This means we keep a history of where the loan officer has worked.

dim_customer

Very key dimension table which holds center, group and client details

Apply 'type 2' on hierarchy and status changes.  The ETL for this is explained elsewhere but is probably the only 'difficult' bit of the ETL.

dim_loan

Very key dimension table for loans.

Apply 'type 2' on hierarchy and status changes.  The ETL for this is explained elsewhere but is probably the only 'difficult' bit of the ETL.

dim_savings

Very key dimension table for savings.

Apply 'type 2' on hierarchy and status changes.  The ETL for this is explained elsewhere but is probably the only 'difficult' bit of the ETL.

fact_savings_transactions

Key transaction table holding details of savings deposits, withdrawals and interest payments (also adjustments for deposits and withdrawals)

Transactions simply extracted from mifos table account_trxn and the correct dimension entries applied.

fact_loan_repayments

Key transaction table holding details of loan repayments (and reversals), write-offs, reschedules

Transactions simply extracted from mifos table account_trxn and the correct dimension entries applied.

fact_loan_disbursals

Useful transaction table holding disbursal transactions (and reversals)

Transactions simply extracted from mifos table account_trxn and the correct dimension entries applied.

fact_client_attendances

Simple transaction table hold client meeting attendance info.  Not used as much as other fact tables but can report on attendance, absenteeism etc. by all levels.

Transactions simply extracted from mifos equivalent and the correct dimension entries applied.  Can be updated.

hist_loan_arrears

Snapshot table which contains loan arrears information on a particular date.    Has been difficult and expensive to report on 
this type of data in the past so needed extra effort over and above the loan_repayments and loan_schedules fact tables to
make it very easy to report on.

Scheduled as necessary (expected daily) and derived from data already in DW database.

dim_arrears_band_monthly

Convenience table. A pre-done monthly banding for arrears aging.  Can be edited to meet specific customer needs.

DW Static data (but can be altered).  ETL job uses the data 
in this table when creating hist_loan_arrears entries.

dim_arrears_band_weekly

Convenience table. A pre-done weekly banding for arrears aging.  Can be edited to meet specific customer needs.

DW Static data (but can be altered).  ETL job uses the data 
in this table when creating hist_loan_arrears entries.

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

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).

dw_account_charge_type

Indicates the type of fee...charge/fee/penalty - use on the fee transaction tables
Values are
1 Periodic and Upfront Fees (get fee frequency info from dim_fee)
2. Misc. Fees/Charges
3. Misc. Penalties

preloaded with DW.