“Tableau Data Model to Build Reliable Dashboards”

What is a Data Model?

To build compelling visualizations in Tableau, first, understanding tables is essential to connect correctly, adjust, and create relationships. For example, in a SaaS business, how do analysts connect all separated data, such as customer, subscription, revenue, and product-usage data, which are stored across multiple tables? If these tables are connected incorrectly, the dashboard may show duplicated revenue, inaccurate customer counts, or misleading KPIs.

Data modelling is a critical step to establish a reliable foundation for combining tables, defining relationships, and building visualizations. It structures related data sources so that data can be analyzed consistently and accurately. It helps deliver meaningful insights, calculate accurate business KPIs, and make business decisions.

Physical vs. Logical Layer in Data Model

The Tableau data model has two layers, which are the physical layer and the logical layer.

Here is a visualized diagram that represents what the logical layer and physical layer look like in the Tableau data model. It shows a comprehensive overview of their concept in an easy, simple layout.

The image illustrates that each logical layer contains physical tables in a physical layer. And then, let's go a little deeper into how they work in the data model.

Logical Layer

The logical layer makes relationships with noodles between tables without physically combining their columns or rows. Tableau uses these relationships to query tables based on the fields used in a visualization while preserving each table's level of detail. It means that the logical layer maintains each table independently in its row level of detail without joining or merging tables.

In the logical layer, users do not need to specify join types because Tableau automatically selects the appropriate join types based on the fields and context of analysis. So, the logical layer can also contain one or multiple physical tables merged through joins or stacked through unions. It is ideal to use when analyzing multiple tables with different grains, such as customer-level and transaction-level data.

Physical Layer

The physical layer combines tables at the row level using joins and unions. A join combines columns from related tables based on matching fields, while a union appends rows from tables with compatible structures.

Use the physical layer when you need precise control over how records are combined and how the resulting table is structured. The main difference is that joins and unions physically combine data, whereas relationships connect tables while preserving their individual levels of detail.

Core Concepts of Data Model

The most important concepts in the data model are tables, relationships, joins, unions, cardinality, referential integrity, and granularity. A reliable data model depends on several core concepts that determine how data is structured, connected, and analyzed. Understanding these concepts helps analysts prevent inaccurate aggregations and build trustworthy visualizations.

• Tables: Structured collections of rows and columns representing entities or business processes such as customers, subscriptions, or transactions. They provide the fundamental structure of the data model.
• Relationships: Connections between tables based on related fields. They allow Tableau to work with tables while preserving their individual levels of detail. The connection line between tables is called a "noodle" due to the curved line.
• Joins: A form that combines columns from two or multiple tables into one table using matching fields. They determine which records are included and can affect the row accounts and aggregations. Types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. The pitfalls of joining are the potential risk of losing data and duplication if tables are joined at different levels of detail.
• Unions: A form that appends vertically rows from one table on the top of other tables in compatible column structures. They are useful for combining datasets with the same business structure because two or three tables have to stack each other. To create a union, simply drag a table into the data source pane and hover below an existing table, and then a union box will appear.
• Cardinality: Describes how records in one table correspond to records in another table, such as one to one or one to many, many to many. It helps define appropriate table relationships.
• Referential integrity: Ensures that a value in one table always has a corresponding value in another table, based on their shared fields. Investigate missing foreign keys, orphan records, duplicate primary keys, null relationship keys, and inconsistent key formats.
• Granularity: Defines the level of detail represented by each record, such as one row per customer, subscription, or transaction. Maintaining appropriate granularity is essential for accurate calculations and business insights.

Before creating a chart or calculating a KPI, the first step is to understand the key concepts of data modeling: understand the table grain, identify the relationship keys, determine the cardinality, validate referential integrity, and choose between relationships, joins, or unions based on the analytical requirement. Then choose an appropriate data model to maintain data integrity and ensure that business insights are calculated at the intended level of detail.

Best Practices for Data Model

• Define granularity: Establish the level of detail for each table before connecting data.
• Use relationships appropriately: Prefer relationships when tables have different grains.
• Validate joins: Check row counts and duplicates to prevent data multiplication.
• Maintain referential integrity: Verify that key fields correctly connect related records.
• Validate calculations: Test KPIs against trusted source data before building dashboards.

Summary

In summary, data modeling is a foundational process for deriving meaningful business insights and presenting accurate KPI calculations that reflect business performance. We explored the core concepts of data modeling to better understand how to build compelling visualization. With a solid understanding of the data model, you can design reliable dashboards for stakeholders and business decision-making.