Loan (banking) Datawarehouse

Hi,

I'm starting with the modeling of a new DWH for a Loan application system. I have been pondering around how to model one of the tables which I describe below (::*note*=> the OLTP tables shown below are being thought to be modeled as Fact tables which will have FKs to Dimension tables such as "Customer", "Product", "Date", ....)

OLTP tables:
--------------

* Loan
- Identified as : FACT table
- Note(s) :
- this table basically contains data of an approved loan and contains data such as : Customer ID that made the Loan, Start Date for Loan, Loan Amount, .... and others

* Loan_Amortization
- Identified as : FACT table
- Note(s) :
- this is an amortization table, which basically contains a row for each scheduled payment for the lifecycle of the loan; say that the loan is for 60 months, therefore there are 60 rows for the particular Loan in
this table.
- apart from the scheduled payment amount for "each month", the rest of the columns are basically HOW much of the payment amount will go to : insurance payment, interest payment, capital payment and so
forth.

My main question here is:

- Should I model the "Loan_Amortization" as a SEPARATE Fact table? I sometimes do think that it should be modeled as a 'separate' Fact table it has measurements in itself (::such as how much of the monthly payment made by the Customer goes to: interest, capital, unemployment insurance and so forth). However, I have doubts when I think that this table really seems like a CHILD of the main "Loan" table and it might be modeled differently (::although a Dimension table seems off the table because is not really a description of the Loan Table).

I appreciate any feedback.

Thank you very much...

It seems like you have two fact tables.

"Loan" : fact grain is 1 row per loan.
"Loan_Amortization" : grain is 1 row per loan per month.

two different grains = two different fact tables.

I'm not clear that I'm following your remark about "..seems like a CHILD of the main ..."

How would something like this fit your needs?

Loan (PK is LoanNumber)
------
CustomerKey FK
[multiple Date keys, application date, payoff date, disbursement date, etc]
LoanProductKey FK
LoanNumber (Degenerate Dimension)
LoanAmount
Interest Rate
...

Loan Amortization (PK is PaymentDateKey and LoanNumber)
-----------------
Customerkey FK
PaymentDateKey FK
LoanProductKey FK
LoanNumber (DD)
PaymentAmount
PrincipalAmount
InterestAmount
InsuranceAmount

Two facts, but I don't understand why you would have an amortization fact. Amortization is a function of principal balance, rate, payment frequency, and time remaining. It also makes huge assumptions, primarily that the precise payment is made on the due date. Well... it doesn't happen like that and very often most amortization schedules are obsolete soon after they are generated.

Do you intend to continually update this fact table? Why could you not simply generate a schedule when someone needs one?

@jgranden:

Thanks for your response. Your model of the 2 tables is basically how I envisioned it. With regards to my comment around {...seems like a CHILD of the main "Loan" table }.. is because DWH users will eventually want a 'report' that has the following structure:


- Parent Row (record from the "Loan" main table, where it gives Balance of that Loan and such)
- Child Rows (here we have all "Loan_Amortization" rows for the particular "Loan"; these rows basically give detailed information of how the payments were applied to
the different components such as the ones I listed above [life insurance, vehicle insurance, loan interests for the particular monthly payment, ... and so
forth)

Having said this, how should I model this do that I can generate this report from the DWH?

Thank you in advance!!
-------------
@ngalemmo:

Thanks for your response!
You could say that the 'Loan_Amortization' table has 2 group of fields. The first "set" of fields/columns basically describe how the monthly payment should break-down into its components (say that the monthly payment is of $100, then you have in this fields that $10 goes to life insurance, $5 goes to normal interest and the rest goes to capital). Then the 2nd set of columns in this table, describes how much actually was paid (say that like you said, that the Customer did NOT pay on time, then there is late fee of $10, then that will be reflected in these 2nd set of columns; then the $90 that are left, is then 'distributed' to the rest of the components/"buckets" of what should be paid for that month (say $10 life insurance, $5 normal interest and $75 goes to capital). At the end of the day, you see that this fact table will be updated with every payment that comes in for that Loan and for when the 'Billing cycle' of this Loan comes up .... say on the 30th of every month.
I hope that I have given better picture of how things are. Do you have any suggestions?

Thanks!

P.S. If there are any business cases/examples of a Loan/banking Datawarehouses that you know of , I'll appreciate if you point me in that direction. This way I can get more ideas and refine the model that I have so far.

Payments against the loan should be its own fact table. You open yourself up to a world of hurt if you think you can somehow match payments up against a fixed schedule. Someone decides to send an extra check out of the blue to pay down some of the principle and you are stuck. Which row do you update? The correct answer is, you don't. Record payments independent of amortization.

@ngalemmo:

There is a separate 'Payment' fact table. However, this "Loan_Amortization" table contains the information of HOW that payment was applied (/"distributed") among the different "buckets"/components of what the monthly payment is conformed of. The OLTP Loan System uses this "Loan_Amortization" table (specifically using the 1st set of columns which describe 'theoretically' how much interest, insurance, payment of capital and such ... will be paid with the already set monthly payment). Of course, if the Customer does NOT pay on time, then some other fees will be charged... but the theoretical interest for that month will remain the same and what is left (say $90 like I stated on the previous post) will be used to 'zero-out' the next in line components/"buckets" for that monthly payment of the Loan.

From your response, I get that FACT tables should never be updated. However, how can I reflect the changes in the "Loan_Amortization" table? Like I tried to express earlier, whenever a Loan is opened, and say the Loan is for 60 months, the OLTP system not only inserts a New row in the "Loan" table (main table) but also 60 rows in the "Loan_Amortization" table (1 row per month). The Business Users for example, will want to get an answer from the DWH for this question: "How much interest will I theoretically get in the next 12 months from the currently-opened Loans?". As far as I have envisioned this, this information should come from the derived FACT table of the "Loan_Amortization" table.

Please advise, since I understand the principles that you are giving me but still have doubts as to how I should model this.

Thank you very much!
P.S. If you can give me your take on what I asked [jgranden] ... it will be very much appreciated too.

Fact tables can be updated, it is just that it is better if they are not. But I digress...

The payment fact should contain information about how the payment was applied. At issue is updating the amortization fact table, performing what essentially should be a reporting function.

I guess my concern is going back and updating the amortization fact. If performance is an issue (i.e. a query that combines the payment and amortization information runs too long), why not add the amortization data to the payment fact when the payment is recorded?

Speaking from experience, you don't need an amortization fact. What you track are payments (and how they are applied) and unpaid principal balances. You minimally need a loan snapshot fact and a payment transaction fact.

@ngalemmo:

The thing here is that the OLTP system records how the payments was processed (::amount applied to different components/"buckets") in the amortization table and NOT in the Payment table. I can't change this, since this is how the system was built.
Going back at one of my original questions, how should I model this if from the DWH they need the report with the structure:

- Parent Row (record from the "Loan" main table, where it gives Balance of that Loan and such)
- Child Rows (here we have all "Loan_Amortization" rows for the particular "Loan"; these rows basically give detailed information of how the payments were applied to
the different components such as the ones I listed above [life insurance, vehicle insurance, loan interests for the particular monthly payment, ... and so
forth)

This seems that I need to join 2 fact tables. How should I go forward from here?

Many thanks for your time and help on this!!