Datawarehouse Data Table Question

Question

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

The business requirement that I do not quite know how to do in the data warehouse is as follows. The loan amount may be adjusted in the future. So, let's say, on August 1, we make a loan with identifier 1 and the amount of 20,000. On October 1, this loan is adjusted to 19,000. Do I put two records in the fact table with the same identifier and different dates and amounts?

Should I create a new table (size table) with the loan amount and date? What is the best way to make this scenario?

In the production database, we have a table for the total loan amount, and then a table with that for loanAmounts, so the combination of loan assets is equal to totalLoanAmount.

Answer 1

, , , .. - , , , .. - , , , , .

M

Answer 2

LoanID . : , , ..

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

,

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

Answer 3

, -, , , , . 3 .

1 , . , .

Type 2 Slowly Changing Dimension . , . .

3 , , , , . , , .

kimball .

Answer 4

, . " "? , , ( ). , .

, (), .

Answer 5

, - . , , . :

• . . , ID . (, ) , ( ). , . TransactionTypeKey TransactionTypeCode, .

• . , . - LoanID; date .

Most other applications, such as order processing, will use the first solution that I think you need. The advantage of solution 1 also lies in the fact that it is completely additive.

Regarding the comment from Saad El Oualji, this is not an SCD case at all, but a detailed design of the facts. His description of SCD is correct, but SCD describes dimensional changes, not changes in facts.