Versions Compared

Old Version 28

changes.mady.by.user johnwoodlock

Saved on

compared with

New Version Current

changes.mady.by.user johnwoodlock

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3
Table of Contents

Listing of Data Warehouse Tables

DIM

 

FACT

 

HIST

 

DW

 

 

PPI

 

 

STG

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

dim_date

 

fact_loan_disbursals

 

hist_loan_balances

 

dw_account_action

 

 

dw_ppi_survey

 

 

stg_personnel_names_and_name_changes

dim_product

 

fact_loan_repayments

 

hist_loan_arrears

 

dw_currency

 

 

ppi_category_likelihood_bands

 

 

stg_customer_and_account_updates

dim_office

 

fact_loan_fees_and_charges

 

hist_savings_balances

 

dw_fee

 

 

fact_ppi_survey_results

 

 

stg_customer_type1_columns

dim_personnel

 

fact_customer_fees_and_charges

 

 

 

dw_fund

 

 

 

 

 

stg_loan_type1_columns

dim_customer

 

fact_savings_transactions

 

 

 

dw_mfi_configuration

 

 

 

 

 

 

dim_loan

 

fact_client_attendances

 

 

 

Lookup tables for Data Warehouse convenience

 

 

 

 

 

stg_etl_run_history

dim_savings

 

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

 

 

 

dw_account_charge_type

 

 

 

 

 

 

 

 

dw_loan_schedules

 

 

 

dw_arrears_band_monthly

 

 

 

 

dw_arrears_band_monthly

 

 

 

 

 

 

 

 

dw_loan_fee_schedules

 

 

 

 

dw_arrears_band_weekly

 

 

 

 

 

 

 

 

 

 

dw_customer_schedules

 

 

 

 

 

 

 

 

 

 

 

 

dw_customer_fee_schedules

 

 

 

 

 

 

 

 

 

 

WIP: Items below to be updated to match the table listing above

DIM Tables

FACT Tables

HIST Tables

The following 'hist' tables hold daily balances for 'active' accounts and are derived from other data warehouse tables.  These tables do increase the size of the data warehouse database substantially but offer improved performance and simpler querying.

...

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:

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

DW Tables

PPI Tables

STG Tables

DW Picture

Overview - Dimension tables surrounding fact tables

A representation (slightly out of date) of how DW 1.0 solves the gnarly aspect of reporting on transaction totals and arrears aging information at MFI/Branch/Loan Officer/Center levels on current and historical date ranges is ...

Star Schema Involving fact_loan_repayments

A representation of how reporting on loan repayments at MFI/Branch/Loan Officer/Center levels on current and historical date ranges is generally is solved by DW 1.0 is ...

Metrics Reporting

A representation of how DW 1.0 solves reporting on 'metrics' at MFI/Branch/Loan Officer/Center levels on current and historical date ranges is ...

(put dim hierarchy piccy here)

High level DW table Info

These tables are currently deployed and being road tested during report development on the development reporting sandbox.  Access to this box is restricted but for those with access... the database is called mifos_secdep_dwh and early (poorly presented by me) reports are running from sandbox-reporting.mifos.org/pentaho/Home under the solutions directory JPWWIP/DW standard reports.

...