loading many-to-many?

[kvogelan]

i am in need of some general guidance on how to load a bridge table in a dwh. if i understand all the literature around this (and i think! i do), we want to avoid an actual bridge table in the oltp sense whereby we end up having a unique bridge key on the fact table. this gives the appearance of a 1:M and is helpful for BI tools. okay - so i'm on board, but totally at a loss for how to load such a thing.

here is my problem statement:

this is a dwh of real estate properties where one sales agent has many properties and a property may be associated to one or more sales agents. (see my (generalized) model attached.)

what just bends my brain is how do i formulate a unique key for each unique group of agents coming in once we disassociate from that which defines that group (the property). what would be the order of operations? ugh. in the kimball etl tool kit the recommendation is

lookup the group
if a match is found, insert group key in the fact table
if no match is found create a new group and add the existing group table, then insert the group key into the fact table.

i'm sure there are combination lookup/updates and db look ups here - but what is the order of operations and what would be the steps.

any guidance is helpful, including alternate model proposals. thank you in advance