Home Loan Application

Hi


I am creating a dimensional model for Home Loan Applications where each home loan application that can have multiple loans associated with it and also multiple applicants

There will be lots of dates like ApplicationReceivedDate and ApplicationFirstApprovalDate plus id's like LoanID and ApplicationID as well as LoanPurpose, LoanAmount, LoanApplicationStatus as well as

Do I just create a accumulating fact table at the loan grain level which will for each loan include the application dates and status as well as the loan amount or do I need to create a loan fact table which is linked to an application dimension ?

Thanks

Tim

Yes, a accumulating fact table with start and end date on the fact table would work. It is called SCD type 2 Fact table. A start and end date on the fact table allow you to find out when do you get information on a specific measures and it allows you to get the latency time/day among measures. You can search Kimball Tips library to get more information on creating a start and end data on a fact table.

What metrics are important to your users? Pipeline reporting is best done using accumulating snapshots. Daily snapshots are also good for other types of reporting.

Hi

Also a home loan application can have more than one customer how do I model that ?, a bridge table ?

Regards

Tim

It depends on your business users need if you want to create a bridge table.... if business user wants to see all the borrowers on a report.  Usually primary borrower is used in a report. Lets assume your business user wants to see only primary and secondary borrowers on the report. In that case the other option you have is to use your customer dim as role playing dim.... have 2 foreign keys ( 1 key for primary and other key for secondary borrower)  from your customer dim to your fact table.