...
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 |
| dw_loan_schedules |
|
|
| dw_fund |
|
|
|
|
| stg_loan_type1_columns |
dim_customer |
|
|
|
|
| dw_mfi_configuration |
|
|
|
|
|
|
dim_loan |
| fact_savings_transactions |
|
|
| Lookup tables for Data Warehouse convenience |
|
|
|
|
| stg_etl_run_history |
dim_savings |
|
|
|
|
| dw_account_charge_type |
|
|
|
|
|
|
|
| fact_customer_fees_and_charges |
|
|
| dw_arrears_band_monthly |
|
|
|
|
|
|
|
| fact_client_attendances |
|
|
| dw_arrears_band_weekly |
|
|
|
|
|
|
WIP: Items below to be updated to match the table listing above
DW Picture
Overview - Dimension tables surrounding fact tables
...
Table Name | Description | ETL Approach |
|
|---|---|---|---|
dw_mfi_configuration | currently contains: | 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 | preloaded with DW. |
|
...