Hello, I have 3 tables and not sure which way to build up the data model:
FACT table: mortgage loan data for 8 months is concatenated here:
• Contract ID,
• Person ID,
• Period (Jan, Feb. March...), - the same contracts are listed multiple times for diff months
• Monthly installment – the amount payed by the debtor that month
• Remining capital that month
• Interest rate
Contract DIM table about the detailes of the loan contracts:
• Contract ID, • Person ID, • Period (Jan, Feb. March...),
• Start of contract
• End of contract
• Currency of contract
• Total Amount of the contract
• Type of contract, .. other descriptive value
Fact 2. table about the non-performing contracts:
• Contract ID, • Person ID,
• Start of non-payment
• End of non-payment
• Not paid amount
Please note that between Fact table and Fact 2. table there is a many-to-many relationship. The same contract is listed for each month in Fact, and the same contract may have multiple entries in Fact 2 with different period and different not paid amount.
I have to calculate avarage interest rate per month separately for performing and non-performing contracts. I tried interval match between Fact and Fact2 (it works) but I do not know how to differentiate between the performing and non – performing contracts in charts.
I am not sure how to connect Fact and Fact 2.
Please advise, thanks a lot!!! Enikö
