I have a current report with a couple of fact tables and a couple for dimension tables. I've used a link table to connect these together. I now want to add another fact table but it joins to one of the dimension tables using a different field from the others eg.
Currently I have:
factOrders - userid, dateid, ordercount
factQuotes - userid, dateid, quotecount
dimDate - dateid
dimUser - userid, region
This is all fine, i created a linktable based on userid + dateid
Now I want to add a factTemperature table with columns dateid, Region, Temperature.
Question is how do i join this in as a fact by connecting to dimDate and avoid a loop table to dimUser because of the Region fields (which are different granularity)?
I thought about having a separate region dimension and join factTemperature and dimUser, would this mean i have to have 2 separate link tables?
