Modelling Loan Fact - Arrears

Hi,

I'm in the process of building a loan transaction fact table but was wondering how to handle the requirement to report on loan arrears amounts (could equally apply to other financial products like mortgages, credit cards etc).

This would be an absence of a transaction - so I'm not sure if I should add an Expected Payment Amount to the fact table or to have a separate fact table for expected loan transactions or something else?

Someone's probably solved this issue before, so any suggestions?

Thanks,
Paul

Interesting challenge. I haven't had to solve it yet myself, so I would be interested in what others come up with.

I think the answer lies in the 3 main types of fact tables : Transactional, Periodic Snapshot and Accumulating snapshot. (http://www.rkimball.com/html/articlesArchitecture/articlesAdvancedFact.html)

My first inclination would be to say that this requires an accumulating snapshot fact table, with the grain being a single loan at a single point in time.

As you said, there isn't really a transaction for "Arrears" - it really is more of a measure of the status of a loan.

Therefore, there probably is a business rule that defines the state of a loan being "In Arrears". Your ETL would look for transaction/events that trigger changes in "Arrears" state, and those changes would trigger a new calculation of amount_in_arrears.

For example:
Consider a business rule that gives 5 days grace for a loan payment. Payment is expected to be $100.

On due_date + 5, no payment was received -> the ETL would create a new snapshot fact for the load, with amount_in_arrears=$100
On due_date + 7, payment of $100 was received - > the ETL would create a new snapshot fact for the loan, with amount_in_arrears=$0.

Hope this points you in a successfull direction.....

Thanks for the information provided.

I would go with a separate fact table. The grain would be all expected payments for all loans. Your missing payments are all expect payments fact - payments received fact.