What are Physical v Logical tables in Tableau ?? Explained.

When working with Tableau in the data canvas, you will most certainly have come across the term Physical tables and Logical tables. Here I explain what they are, the difference between the two, and when you should use each of them.

Every Data Source Has a Data Model

At its very core, every Tableau data source is built on a data model. This is basically a blueprint for how Tableau queries and processes data from your connected tables. Think of it as the backbone of your analysis.

The Two Layers of Tableau’s Data Model

Tableau’s data model is split into two key layers, each serving a different purpose:

1. The Logical Layer (Relationships Canvas): Clever Connections. Tableau uses its noodle.

The logical layer, or Relationships Canvas, is where you connect data tables using relationships instead of traditional joins. This makes data modelling more flexible and dynamic.

• Relationships, Not Joins: Instead of manually setting join types, you define relationships between tables. These are the little noodles you use to connect each table.
• Context-Aware Joins: Tableau automatically picks the correct join type based on your analysis.
• Better Performance: Since Tableau only queries the data it needs, relationships help reduce duplication and speed things up.
• Modular Structure: Makes it easier to scale and maintain your data model over time.

When to use them:

• Complex data sources: When you’re working with multiple related tables.
• Creating flexible relationships: When you need to define relationships between tables without worrying about data duplication or loss.
• Building data models: For creating robust and scalable data models that can support a variety of analyses.
• Simplified data blending: When you need to combine data from different sources without complex join logic.
• Maintaining data integrity: When you want to ensure that your analysis is accurate and consistent, even when dealing with complex data relationships.
• When you want Tableau to handle the aggregations correctly: The logical layer allows Tableau to aggregate data correctly.
• When you do not want to worry about the join type: The logical layer uses relationships, and Tableau will determine the appropriate join type based on the visualisation that is being produced.
• Relationships are used to connect logical tables, instead of joins. These are flexible and do not result in data duplication or loss.
• Tableau handles the join logic based on the context of the analysis.
• Logical tables operate at an aggregated level, based on the context of the view. Logical tables allow for a “star schema” like structure to be created within tableau.  Below is an example of logical tables and it noodles.

2. The Physical Layer (Join/Union Canvas): You are in control

The physical layer lets you determine how data is combined using joins, unions, and pivots.

• Traditional Joins & Unions: Uses standard join types like inner, left, right, and full outer joins.
• How It Used to Work: Before Tableau 2020.2, this was the only layer for data modeling.
• Detailed Data Structure: Gives you full control over how tables are joined. You decide.

When to use them:

• Simple data sources: When you’re working with a single table or when you need to perform direct, row-level operations on the data.
• Data preparation: For tasks like cleaning, filtering, and performing calculations at the row level before creating relationships.
• Legacy connections: If you’re working with older Tableau workbooks that predate the introduction of the logical layer.
• Performance optimisation of single tables: When you want to specifically tune the performance of a single table, such as by creating extracts or applying filters.
• When needing very specific joins: when you need to control the type of join very precisely, and you understand the data well enough to know the implications of the join.
• Characteristics: Physical tables represent the actual tables from your data source.
• Relationships between physical tables are defined through joins (inner, left, right, full).
• Joins can lead to data duplication or loss if not handled carefully.
• Physical tables operate at the row level.

Below is an example of physical tables. You decide on the joins.

There are suitable use cases for each, so be cautious when selecting which method to use. the logical method works well in most situations when you have more than 1 data source. However, if you want FULL control of the joins, then use the physical method. When I use the physical method and the datasource is connected to a database, I always test it out in an SQL client first, to ensure the right results.

If you need some help on this, let’s connect and discuss.

#tableau #data #datamodel #tables #physicaltables #logicaltables