...
The following query shows how to get a breakdown of repayment transaction transactions for a branch for a quarter. This is a typical star schema type query. The 'fact' table is joined to the 'dimension' tables that are used for filtering and grouping.
Repayment transactions cover normal repayments, adjustments, reversals, reschedules and write-offs.
...
This simple query does quite a lot. Its also a trivial matter to change 'branch' for 'loan officer', 'center', 'group' and other built in ways to filter and group tables that are used for filtering and grouping data. There are other 'fact' tables in the data warehouse that cover such things as disbursals and savings. Same It's the same approach to querying for all of them (consistency of query pattern is a strength of this type of data warehouse).
Finally, here's a query to get a breakdown of the number and amount of disbursals per loan_officer by branch by month for 2009 (loan officers can move branch).
...
Because it's quite complicated, the data warehouse pre-calculates information about each loan that is in arrears each day so that you get a quick and easy look at arrears related data.
The . Data is snapshot by day. The query below gets arrears information that is past a mifos configure configured 'lateness' number of days as of end 2010-03-31. It does it for a loan officer and breaks the results down into pre-determined weekly bands. Replacing 'weekly' by 'monthly' give the same data in monthly bands.
| Panel |
|---|
select wb.past_due_description, from hist_loan_arrears hla where endperiod.date_value = date('2010-03-31') group by wb.past_due_description |
...
In the mifos standard reports being developed ... there's a little of the vanilla reporting above, quite a bit of the loan arrears reporting but mostly ite it's customer and account based. It's currently This type of reporting against the data warehouse is a little more involved than the vanilla but the there are 2 main positives are that this 1) .
- it's still better than querying the mifos application
...
- database
- most importantly,
...
- it caters for customer hierarchy changes (loan officer movements, group and client transfers etc) as well as customer and account status changes. Basically,historical reporting like comparing one time period to another is made possible.
The main tables (dim_customer, dim_loan and dim_savings) each have links link directly to a full mifos hierarchy... branch, loan officer, center, group etc... but the thing which is different from normal mifos tables is that one customer, for example a client, will have a number of entries in dim_customer. Each time its the hierarchy changes (e.g. client or group transfer, loan officer assignment) or it has there is a status change... a new entry is added. The entry are is valid for a period of time which is denoted by a pair of date fields called valid_from and valid_to. If you want to find the unique version of the client that is relevent on 2010-01-01 you need to add the following to a query...
| Panel |
|---|
and valid_from <= date('2010-01-01') |
It took me a little while to get used to this. Also some of the queries felt a little complicated but still better than working with the mifos application database structure. Below are a couple of examples from the standard mifos reports. If you have the Pentaho Report Designer you can get the .prpt files from git://mifos.git.sourceforge.net / mifos/bi and look at the queries.
...