Issue
Please help me with the below issue that I have in Tableau while trying to construct the relationship data model between 4 separate tables(source mySQL):
Dim_Partners,
Fact_General_Contracts,
Fact_Additional_Contracts,
Dim_ThirdParty
I have a main dim table with the names of several partners, primary key Partner_ID. The tables General_Contracts and Additional_Contracts contain this field, so I can do a relationship between all 3 using this key. The problem is that I need to link a fourth table, Dim_ThirdParty, which is another dimension table, that contains the primary key ThirdParty_ID, and I need to link it to the two fact tables, General_Contracts, and Additional_Contracts.
How can I do this efficiently in Tableau? In Power BI the solution is very simple, as you are able to create multiple links between tables. But in Tableau?
I tried connecting the main dimension table Partners to the two Dim tables (see print screen attached), but how can I link the second dim Table, without having to link it to each table separately, resulting in a weird-looking data model? Would it be better to join each fact table in the logical layer?
Solution
Tableau doesn’t support any kind of relationship arrangement where you can trace a circle around the tables involved (as you would have if Dim_ThirdParty were a single point in your diagram).
You have two options to resolve this:
- Union Fact_General_Contracts and Fact_Additional_Contracts into a single table with an additional column to allow measures to distinguish between the two (or suitable count columns with 0\null for the wrong row types).
- Use a cross join to merge Dim_Partners and Dim_ThirdParty leaving you with a single dimension holding every valid combination of the two and then join the two on the pair ok keys.
Answered By – BarneyL
Answer Checked By – Mary Flores (BugsFixing Volunteer)