Many to Many Look alike Dimensions

Hi all,

I have this design scenario that I need to validate if this is the best approach and that there are no other alternatives that I have not looked at.

OLTP 3 Tables:

Loans (PK-->LoanID, LoanSequence)

Borrower (PK-->BorrowerID, columns LoanID, LoanSequence are also there)

BorrowerAddress (PK--> BorrowerAddressID, columns BorrowerID, LoanID, LoanSequence are also there)

1. The OLTP system does not do update, every update is inserted as a new record. So we practically have type 2 change on OLTP system side. This is why Primary Key in Loan table is a com

2. One Loan record can have many borrowers (Primary, Co-borrower, Other Borrrower)

3. One Borrower can change his/her address many time during the course of the application process. So this implies many BorrowerAddresses for Each Borrower.

OLAP Tables:

table FactLoanBorrower and FactBorrowerAddress are the middle table for the many-to-many relationship.

FactLoans (TimeKey,FactLoanBorrowerKey)

FactLoanBorrower (FactLoanBorrowerKey, LoanID, LoanSequence, BorrowerID, BorrowerTypeKey)

FactBorrowerAddress (FactLoanAddressKey, BorrowerAddressID, BorrowerID, LoanID, LoanSequence)

DimBorrowerType (BorrowerTypeKey)

DimBorrower (BorrowerKey with Natural Keys (BorrowerID, LoanID, LoanSequence))

DimBorrowerAddress (BorrowerAddressKey, BorrowerID, LoanID, LoanSequence)

Basically the relationships look like this:

FactLoans<--FactLoanBorrower-->DimBorrower<--FactBorrowerAddress-->DimBorrowerAddress.

Is this design valid?

thank ahead.

Yes, this may work, however please measure performance on the predicted data volumes you're likelt to have and for a representative query workload.

Thank you