Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Modelling Loan Fact - Arrears

4 posters

Go down

Modelling Loan Fact - Arrears Empty Modelling Loan Fact - Arrears

Post  pauljames 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

Back to top Go down

Modelling Loan Fact - Arrears Empty Re: Modelling Loan Fact - Arrears

Post  LAndrews 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

Back to top Go down

Modelling Loan Fact - Arrears Empty Re: Modelling Loan Fact - Arrears

Post  Karachi123 Thu Jun 24, 2010 1:32 am

Thanks for the information provided.

Karachi123

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

http://www.starlitt.com

Back to top Go down

Modelling Loan Fact - Arrears Empty Re: Modelling Loan Fact - Arrears

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

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

Back to top Go down

Modelling Loan Fact - Arrears Empty Re: Modelling Loan Fact - Arrears

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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