Datawarehouse Fact table question

Question

I have a fact table called Loans. In this table in a loan identifier, the date the loan was made, and the amount of the loan.

The business requirement I don't quite know how to do in the datawarehouse is the following. The loan amount can be adjusted in the future. So lets say August 1st we make a loan with an id of 1 and a amount of 20,000. October 1st this loan is adjusted to 19,000. Do I put two entries in the fact table with the same identifier, and different dates and amounts?

Do I create a new table (dimension table) with loan amount and date in it? What is the best way to do this scenario?

In the production database we have a table for total loan amount, and then a table off of that for loanAmounts so a combination of loanAmounts equals totalLoanAmount.

Answer 1

Whether you actually need multiple versions depends on requirements. Do you only need to report on the loan amounts as-is or do you need to know the as-was position as well? If you aren't sure then it probably makes sense to keep the history (multiple versions). My assumption would be to keep the history.

I would suggest creating new rows for each new version and date column(s) to represent the date(s) the version applies.

Answer 2

Treat the LoanID as a degenerate dimension and enter the correction separately. You may also use a transaction type dimension to describe: loans, payments, corrections etc.

DateKey CustomerKey TransacionTypeKey LoanID    Amount
---------------------------------------------------------
20100801    175          1               1     20000.00
20101001    175          2               1    - 1000.00

Show all loans by customer, by loan

select
      CustomerKey
    , LoanID
    , sum(Amount) as Amt
from factLoan
group by CustomerKey, LoanID
order by CustomerKey, LoanID ;

Answer 3

I would always recommend treating such fact tables as fully-summable, having what are effectively movement transactions for any uplift, payment, interest etc - if you do this you can have absolute flexibility to report over time, filter by transaction type etc - storing multiple version rows in the same fact table or updating a snapshot table maybe other options for reporting when you are amalgamating different metrics across dimensions but at the base level it is usual to have these line by line events itemised.

M