A data warehouse model for loans
3 posters
Page 1 of 1
A data warehouse model for loans
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!
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
A data warehouse model for loans
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.
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
Re: A data warehouse model for loans
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
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
Re: A data warehouse model for loans
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Canonical Data Model for Data warehouse
» How to model Customer in data warehouse
» Model available time per day in timesheet data warehouse: fact or dimension or neither?
» Ebook The Data Warehouse Lifecycle Toolkit, 2nd Edition: Practical Techniques for Building Data Warehouse and Business Intelligence Systems
» data warehouse or not ? when is it okay to use OLAP without a data warehouse database
» How to model Customer in data warehouse
» Model available time per day in timesheet data warehouse: fact or dimension or neither?
» Ebook The Data Warehouse Lifecycle Toolkit, 2nd Edition: Practical Techniques for Building Data Warehouse and Business Intelligence Systems
» data warehouse or not ? when is it okay to use OLAP without a data warehouse database
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|