but if you built a bridge table between the Address and Name that just contained the Surrogate keys from either side, effectively creating a Tuple wouldnt that work?

EG

CREATE TABLE Dim.Name (

NameSurrogateKey int

,NameBusinessKey

,Name varchar(100)

)

CREATE TABLE Dim.Address(

AddressSurrogateKey int

,AddressBusinessKey

,Address1 varchar(1000)

,Phone1 varchar(100)

)

CREATE TABLE Bridge.AddressName(

NameSurrogateKey int

,AddressSurrogateKey Int

)

That way you've created a Tuple between the two so part of the ETL simply looks up the new key combinations and inserts them as you must have some way to link them already.

In SQL you would do something like

SELECT

*

From

Dim.Name n

JOIN Bridge.AddressName an on n.NameSurrogateKey=an.NameSurrogateKey

JOIN Address a on n.AddressSurrogateKey=a.AddressSurrogateKey

Where

Address1='somewhere'

AND/OR Name='Someone'

what are you using for your reporting tool?