How to resolve ambiguous relationships in this scenario?

[afaro]

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? 

[v-xuxinyi-msft]

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

[ahadkarimi]

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

[AbhinavJoshi]

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

[afaro]

Well, bridge tables do not resolve ambiguous paths as the paths still remain. I tried it out but it didn't make a difference. 

[AbhinavJoshi]

After creating the bridge table, did you still have 1:Many or Many:Many Relationship? 

[afaro]

1-many and many-1. There was many-many with date table but not with region anymore. 

[AbhinavJoshi]

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? 

[afaro]

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. 

[AbhinavJoshi]

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.

[AbhinavJoshi]

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?

[afaro]

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 

[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

[afaro]

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?