A data warehouse model for loans

View previous topic View next topic Go down

A data warehouse model for loans

Post  veskojl on Fri Nov 14, 2014 4:02 am

Hi guys,

Anyone have experience with loan or mortgage DWH models?
I'm trying to design a DWH model for a money lending company and few things a are bugging me.

1. I need to track the scheduled payment plan - this sounds like a transaction fact table, but not I'm 100% sure
2. I need to track the real payments/transaction - sounds like another regular fact table
3. The biggest problem is how to design the "Loan" data? As a dimension or as a fact table?
I think to design the process of loan approval like accumulating snapshot, but I also need to drill across 1) and 2), to compare scheduled vs. expected amount and track the loan arrears. So the "Loan" looks like an "Account" dimension described in the "The Data Warehouse Toolkit" book...

Any help or opinion is appreciated!

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

A data warehouse model for loans

Post  zoom on Fri Nov 14, 2014 2:21 pm

1. yes create a payment fact table with columns like expected payment and actual paymentů these columns help you to calculate arrear.
2. Yes create daily Trans table.
3. Create a Loan dim. Loan has attributes like rate, loan type (primary home loan, Home equity loan) loan investor (fannie mae or fredi mac)and other attributes which belong in a dim table.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: A data warehouse model for loans

Post  veskojl on Thu Nov 20, 2014 10:04 am

Thanks, zoom. I'm also leaning to this approach, but few things worry about it:
1. The size of the DimLoan - it's 1:1 to FactLoan
2. The SCD type - Shall I make it SCD1 or SCD2?
SCD1 sounds better, but what to do with the changing attributes like loan status? May be I should move them to a separate dimension of Type 2

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: A data warehouse model for loans

Post  BoxesAndLines on Fri Nov 21, 2014 1:46 pm

I would not create a loan dimension for the very reason you mentioned. Break up the loan attributes into smaller dimensions. Your fact table will be (or should be) a snapshot fact table. This can alleviate the need for type 2 dimensions as each snapshot will have the loan status. What you won't have is if the status description changes.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: A data warehouse model for loans

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