Relationship Building with Fact Table

[Anonymous]

Hello

I have a star schema data model that I am attempting to build out. we have several excel reports from various locations that I'd like to connect to a FactTable, in order to be able to refresh them automatically instead of manually.

I am getting stuck with connecting one of the reports to the fact table. It is a variety of locations, which each have their own location name. However, on this particular DIM table (call it EFC), it has location names listed multiple times and if I remove duplicates I will remove some of the necessary data. 

Is there a way to create a unique "ID" for each row on the EFC DIM table, and then somehow have that connect to the Fact Table in some way?

let me know if I need to clarify any of the above, as I can't share the data otherwise I would screenshot.

[Greg_Deckler · Accepted solution]

So, typically the way you handle this is to copy your dimension table query to a new query. Remove all columns except your desired key column (location). Remove duplicates on this column. Connect your dimension table to this new "bridge" table and then connect the bridge table to your fact table. Poof! many-to-many.