I have two dimension tables: Calendar & Region
I have two fact tables: Sales & Finance
Now, the relationships look like those in the diagram. The one in purple is the one I cannot create because we have ambiguous paths. I had to create many-many relationships because of difference in granularity. I was wondering if replacing with bridge tables everywhere would make any difference? If not, then how can I resolve this situation?
Hi @afaro
Thanks for the reply from AbhinavJoshi and ahadkarimi .
@ahadkarimi , did ahadkarimi 's method solve your problem? If yes, could you please mark it as a solution? This will be of great help to other users experiencing similar problems. Thank you!
If you still have questions, please feel free to contact me.
Best Regards,
Yulia Xu
Hi @afaro, try and let me know if there is any problem.
Bridge Table for Region: (Region -> BridgeRegion -> Sales and Finance)
| BridgeRegionKey | Country | Continent | SalesRegionID | FinanceRegionID |
| 1 | USA | North America | 101 | 201 |
| 2 | Germany | Europe | 102 | 202 |
Bridge Table for Calendar: (Calendar -> BridgeCalendar -> Sales and Finance)
| BridgeDateKey | Date | Year | SalesYearID | FinanceYearID |
| 1 | 01/01/2024 | 2024 | 2024 | 2024 |
| 2 | 01/01/2023 | 2023 | 2023 | 2023 |
Yes I am suggesting a bridge table for region. Please give it a try.
I would leave the date table based on your scenario then
Well, bridge tables do not resolve ambiguous paths as the paths still remain. I tried it out but it didn't make a difference.
So the only many to many relationship should be between date table and finance/sales. Is it possible for you to bring in the individual "date" value for finance/sales table?
Unfortunately not. The thing is my calendar table is used to connect many other tables who granularity varies. So, I am bound to have to deal with many-many relationships. I can create bridge tables but I don't know if they would help solve the problem for ambiguous paths.
I can't say if any bridge tables would solve your problem with many to many relationship against date table as they are connected using year field.
Hi @afaro I would say split the region table into country and continent so that you can have unique continents and countries. Then, link these tables using 1:Many relationship.
The date table you have, is it a custom date table or you got it from somewhere?
To link the country and continent table, I would need a bridge table. So, I would have 1-many (country->bridge) and many-1(bridge-continent). Is that what you are suggesting?
Date is imported from somewhere else and I cannot make modifications to it. @AbhinavJoshi
Hi @afaro,
Why do you have duplicate values in a region table? I would recommend having unique keys in region table, you could concatinate values and the connect it to the fact tables.
Also, you could create a custom date table to make your other relaitonship 1:Many instead of many to many.
Thanks,
Abhinav
I don't quite get you. The granularity is country so we have unique country names but continent is duplicate.
Custom date table, as in duplicate date table?
