Modelling Loan Fact - Arrears

View previous topic View next topic Go down

Modelling Loan Fact - Arrears

Post  pauljames on Thu Jun 03, 2010 6:31 am

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

pauljames

Posts : 3
Join date : 2010-06-03

View user profile

Back to top Go down

Re: Modelling Loan Fact - Arrears

Post  LAndrews on Thu Jun 03, 2010 4:27 pm

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

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Modelling Loan Fact - Arrears

Post  Karachi123 on Thu Jun 24, 2010 1:32 am

Thanks for the information provided.

Karachi123

Posts : 1
Join date : 2010-06-24
Age : 37

View user profile http://www.starlitt.com

Back to top Go down

Re: Modelling Loan Fact - Arrears

Post  BoxesAndLines on Thu Jun 24, 2010 9:05 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modelling Loan Fact - Arrears

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum