Relationships are a dynamic, flexible way to combine data from multiple tables for analysis. A relationship describes how two tables relate to each other, based on common fields, but does not merge the tables together. When a relationship is created between tables, the tables remain separate, maintaining their individual level of detail and domains. Show
Think of a relationship as a contract between two tables. When you are building a viz with fields from these tables, Tableau brings in data from these tables using that contract to build a query with the appropriate joins. What are relationships?Relationships are the flexible, connecting lines created between the logical tables in your data source. Some people affectionately call relationships "noodles", but we usually refer to them as "relationships" in our help documentation. We recommend using relationships as your first approach to combining your data because it makes data preparation and analysis easier and more intuitive. Use joins only when you absolutely need to(Link opens in a new window). Relationships provide several advantages over using joins for multi-table data:
Requirements for relationships
Factors that limit the benefits of using related tables:
Most relational connection types are completely supported. Cubes, SAP HANA (with OLAP attribute), JSON, and Google Analytics are limited to a single logical table in Tableau 2020.2. Stored procedures can only be used within a single logical table. Published data sources can't be related to each other. Unsupported
Limited support
Create and define relationshipsAfter you drag the first table to the top-level canvas of the data source, each new table that you drag to the canvas must be related to an existing table. When you create relationships between tables in the logical layer, you are building the data model for your data source. Note: In Tableau 2020.3 and later, you can create relationships based on calculated fields, and compare fields used for relationships using operators in the relationship definition. Create a relationshipYou create relationships in the logical layer of the data source. This is the default view of the canvas that you see in the Data Source page. Note: The Salesforce connector doesn't support inequality operators. Google Big Query and MapR connectors support non-equal joins starting with version 2021.4. The MapR connector is deprecated as of version 2022.3.
After you have built your multi-table, related data source, you can dive into exploring that data. For more information, see How Analysis Works for Multi-table Data Sources that Use Relationships and Troubleshooting multi-table analysis. Move a table to create a different relationshipTo move a table, drag it next to a different table. Or, hover over a table, click the arrow, and then select Move.
Tip: Drag a table over the top of another table to replace it. Change the root table of the data modelTo swap the root table with another table: Right-click another logical table in the data model, and then select Swap with root to make the change. Remove a table from a relationshipTo move a table, hover over a table, click the arrow, and then select Remove.
Deleting a table in the canvas automatically deletes its related descendants as well. View a relationship
Edit a relationship
Tips on creating relationships
Validate relationships in your data sourceYou have several options for validating your data model for analysis. As you create the model for your data source, we recommend going to the sheet, selecting that data source, and then building a viz to explore record counts, unmatched values, nulls, or repeated measure values. Try working with fields across different tables to ensure everything looks how you expect it to. What to look for:
Options for validating relationships and the data model:
Tip: If you would like to see the queries that are being generated for relationships, you can use the Performance Recorder in Tableau Desktop.
Another more advanced option is to use the Tableau Log
Viewer(Link opens in a new window) on GitHub. You can filter on a specific keyword using Dimension-only visualizationsWhen using a multi-table data source with related tables: If you build a dimension-only viz, Tableau uses inner joins and you won't see the full unmatched domain. To see partial combinations of dimension values, you have can:
For more information, see How Analysis Works for Multi-table Data Sources that Use Relationships and Troubleshooting multi-table analysis. Relationships (logical tables) versus joins (physical tables)While similar, joins and relationships behave differently in Tableau, and are defined in different layers of the data model. You create relationships between logical tables at the top-level, logical layer of your data source. You create joins between physical tables in the physical layer of your data source. Joins merge data from two tables into a single table before your analysis begins. Merging the tables together can cause data to be duplicated or filtered from one or both tables; it can also cause NULL rows to be added to your data if you use a left, right, or full outer join. When doing analysis over joined data, you need to make sure that you correctly handle the effects of the join on your data. Note: When duplication or the filtering effects of a join might be desirable, use joins to merge tables together instead of relationships. Double-click a logical table to open the physical layer and add joined tables. A relationship describes how two independent tables relate to each other but does not merge the tables together. This avoids the data duplication and filtering issues that might occur in a join and can make working with your data easier.
Relationships versus blendsWhile both relationships and blends support analysis at different levels of detail, they have distinct differences. One reason you might use blends over relationships is to combine published data sources for your analysis.
Features of different options for combining data: Relationships, joins, and blendsThere are many ways to combine data tables, each with their own preferred scenarios and nuances.
Can we edit relationships between tables?Select the Home ribbon > Manage relationships, then select the relationship and then select Edit. Double-click any line between two tables. Right-click any line between two tables and then choose Properties. Select any line between two tables, then select Open relationship editor in the Properties pane.
When creating a relationship between tables What is required?Requirements for a table relationship. Each table must have a single column that uniquely identifies each row in that table. This column is often referred to as the primary key. The data values in the lookup column must be unique.
How would you resolve a manyTo avoid this problem, you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table. Each record in a join table includes a match field that contains the value of the primary keys of the two tables it joins.
Why is it important to maintain the integrity of relationships between tables?Referential integrity is a term used in database design to describe the relationship between two tables. It is important because it ensures that all data in a database remains consistent and up to date. It helps to prevent incorrect records from being added, deleted, or modified.
|