Link table using another field from dimension

ce19 — Wed, 18 Dec 2019 14:15:25 GMT

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?

Thanks

topic Link table using another field from dimension in App Development

Link table using another field from dimension