While looking for Paul Revere, I recently learned that you can define many-to-many database relationships in Tableau. I should not have been surprised, Tableau seems to do most things well. It should wear a cape all the time.
Anyways… before I forget, here how to to define a many-to-many database relationship in Tableau.
First, lets define the domain for this exercise. Lets say we have three tables, one for person (PERSON), one for organization (ORG), and an association table defining each persons membership into an organization (ORGPERSON). Each person can belong to none, one or many organizations and, of course, an organization can have any number of members as well.
Here is how this would look in a database:
1. Importing Data (text files) – From the Connect to Data Pane, select text file as the datasource to use. Make sure you have the three CSV files in the same directory. Select our association table ORGPERSON (file orgperson.csv). This is the file that defines our relationships. Please note that we are not relying on the schema’s foreign key definitions to do this.
2. In the Text File Connection popup menu that follows, on Step 3, Select Multiple Tables as shown. In this menu, we are going to add the ORG and PERSON tables.
3. Adding Org Table – Click Add Table, then select org#csv from popup.
4. Defining Our Join between ORGPERSON and ORG (on IDORG). Click Join on the Add Table popup.
Ensure or change join on the key in common. If IDORG is not defined as join clause, just delete whatever is there and add join clause desired from bottom. Multiple join criteria can be defined here as well. Press OK when satisfied.
Now, dialog box shows we have a join-ed table:
5. Adding Person Table – For the PERSON table, repeat step 3 and step 4 to arrive at this illustration:
Voila! I think Tableau is merging these tables for importing ONE entity. Very clever step to unifying our data as soon as possible. The only side effect I ran into is that, when I spent some time aliasing my columns, I had to use distinct labels for each.
Case: Table ORG and PERSON both have a NAME column. These two columns would need to be labeled uniquely since they are now effectively part of the same table. Neat.
Hit OK to import our, now one, table into Tableau.
This is how our asset looks like now. Note the aliases case worked thru as well.
And here is our simplest of visuals showing how many members does each organization have.
Doing a quick check on query tool gives us the same numbers.
1: select a.name organization, count (b.idperson) 2: from org a inner join orgperson b on a.idorg = b.idorg 3: group by a.name;
Check! Similarly, we should do exactly the same for persons and the organizations each belongs to.
1: select a.name person, count (b.idorg) 2: from person a inner join orgperson b on a.idperson = b.idperson 3: group by a.name;
This one is too large to paste but again, it seems Tableau can do no wrong.
No worries, the complete sheet is in the Tableau Worksheets provided.
Now we can just work in Tableau and come up with all those awesome visuals too difficult anywhere else.
That’s all it takes. This is new to me and figured it would be of use to some of you. Thanks for reading.