Scenario: Our app/database holds bank loans, and many lenders can be assigned to one loan, and many loans can be assigned to one lender. A pretty classic many-to-many set-up I think. What we need our model to do is:
Track how much each lender is allocated to each loan. We have the loan data, and the allocation for each loan by lender. We are pulling the data from our transactional DB, passing it through ETL and staging in Snowflake, so we have some flexibility in how we model it.
In the attached .pbix I have modeled two scenarios:
Scenario 1: store the notional for each loan at the allocation grain. This seems to work ok, but not sure I love have multiple records for each loan, when its really just one loan allocated out.
Scenario 2: using a bridge table, and creating a lender group, which is an ID assigned to every unique group of lenders. I stole this design from here (https://www.nuwavesolutions.com/bridge-tables-deep-dive/), but then not sure how to write measures to show loans allocated, it seems like the joins might have to be bi-directional, or use crossfilter in the measure?
The last wrinkle: the lender allocation mix can change over time, where some lenders might drop or others be added, or the lenders say the same and the percentage changes, so also trying to figure out where to best to incorporate the slowly changing dimension as well.
Any guidance would be appreciated!
https://drive.google.com/file/d/1DWsBDk_U2nHQOUB8pbxZ9KF6JHvc86lf/view?usp=sharing
