Tableau's New Data Model & Relationships

The latest version of Tableau, 2020.2, introduces a game-changing new data model, which is significantly different from the way the data model has worked in the past. In this blog, I’m going to dive a bit into how this new data model works compared to the previous model, as well as some of the problems it solves.

A few notes before we begin. First, if you’re not yet familiar with the new data model, I’d suggest that you start by watching the short video on How Relationships Differ from Joins. It will give you an overview of the new concept of “relationships” and generally how they differ from joins. If you’re interested in digging a bit deeper, you can also read The Tableau Data Model and Relationships, Part 1: Introducing New Data Modeling in Tableau. Second, in order to understand exactly what the data model is doing, we’re going to be looking at the SQL generated by Tableau. While I’ll be simplifying this SQL a bit for readability, I am assuming some previous understanding of SQL. If you do not have a SQL background and want to learn, feel free to check out my series on SQL for Tableau Users. Finally, these tests will be performed using my publicly-available SQL Server Superstore database. If you’d like to replicate these tests yourself, the connection information is available on the SQL for Tableau Users blog shared above.

The Old Data Model

Using the old data model (pre-2020.2), you had to specify each table and tell Tableau exactly how to join these tables together. For instance, here’s a simple data model joining Orders to People based on the Region field.

In order to demonstrate how this is communicating with the back-end database, let’s look at the SQL that is executed:

SELECT *

FROM [Orders]

INNER JOIN [People]

ON [Orders].[Region] = [People].[Region]

Any visual we create in Tableau will always use this base SQL, joining Orders to People, even if we don’t show any of the fields from People. For instance, let’s create a very simple bar chart showing Sales by Customer. This will generate the following SQL:

SELECT [Customer Name], SUM([Sales])

FROM [Orders]

INNER JOIN [People]

ON [Orders].[Region] = [People].[Region]

GROUP BY [Customer Name]

The only two fields used in our view are the dimension, Customer Name and the measure, Sales, both which come from the Orders table—we are not using any fields from People. Yet, the SQL generated by Tableau still includes the join to the People table.

This approach has many drawbacks. For one, it’s unnecessarily joining tables, which will be less performant than simply selecting data from the one table that is actually required. A second drawback is that it could, potentially retrieve more records than it actually needs. To demonstrate this, let’s look at a scenario where we have a one-to-many relationship between our tables. In my database, I have an alternate Person table called Person_Multiple:

As you can see, this table has two people for each region. When we use this in our data model, it executes a similar SQL statement as shown above. But, because there are two people for each region, each record in Orders is duplicated. The result set is 19,988 records, as opposed to the 9,994 records in Orders. If you’re dealing with millions of records, this duplication could prove to be a huge constraint. But this size difference is only part of the problem—aggregations are also impacted by this data duplication. For example, let’s create that bar chart showing Sales by Customer again. It executes pretty much the same SQL (except using People_Multiple instead of People), but because of the record duplication, our resulting Sales aggregates are doubled. The typical solution to this problem is the use of a Level-of-Detail calculation (see use case # 1 in 20 Uses for Level-of-Detail Calculations). Unfortunately, LODs are a bit tricky and not readily accessible to new users of Tableau. Additionally, some people may not even realize that their join has led to data duplication and, thus, don’t know that they need to address the problem.

So, even with this very simple data model, we’re left with three key problems:

1) Over-complicated SQL – Unnecessary over-complication of our SQL, which will have a performance impact.

2) Duplicated Records – More records than we actually need or want.

3) LOD Requirements – The need to use LOD calculations to address data duplication in aggregates.

The key point here is that the old data model is “set-in-and-forget-it”. You create the basic structures, then all of the SQL executed will pull from that same structure. The SQL may retrieve different fields and may perform different types of aggregations, but the FROM clause will always include all the tables in your model, joined exactly how you’ve instructed it to do so.

Note: As Zen Master, Tamas Foldi, pointed out to me, my above statement is not exactly true (I should know better than to use the word "always" when discussing anything related to Tableau as it seems there is "always" another option 😉). If you use the "Assume Referential Integrity" option, Tableau will eliminate (cull) the join when that table is not required on the view. This will, of course, result in a more efficient SQL statement. But, you must also be careful—if you select this option and your database does not have referential integrity, it could produce inaccurate results. For more information on this feature, see Assuming Referential Integrity for Joins.

The New Data Model

This is where the new data model is different. The use of “relationships” means that Tableau will be generating a unique SQL statement for every view you create. It will no longer include all the tables in your model. Instead, it analyzes the fields used in your view and creates a SQL statement that is tailored to only retrieve the data you actually need. Let’s take a look.

Here’s the data model using relationships. Notice that there is no Venn diagram showing the join type. That’s because we no longer have to specify the join type. We simply define the relationships between the two tables—in this case, the Region fields. But, at this point, these tables remain independent. Tableau is not building a base SQL statement to use throughout the workbook as with the old data model. We’re just establishing a loose tie between the table that will be used when Tableau generates the view-specific SQL.

Let’s, once again, build our Sales by Customer bar chart. Using the new data model, our SQL will look like this:

SELECT [Customer Name], SUM([Sales])

FROM [Orders]

GROUP BY [Customer Name]

Tableau has identified that none of the fields from People are used in this view and has, therefore, excluded it from the SQL. This is a concept that Tableau refers to as “join culling”—basically a process by which the data engine removes unnecessary joins.

But what happens if we use a field from People in our view? For example, what if we only keep records for the Person, Anna Andreadi? That will generate the following SQL:

SELECT [Customer Name], SUM([Sales])

FROM [Orders]

INNER JOIN [People]

ON [Orders].[Region] = [People].[Region]

WHERE [Person] = 'Anna Andreadi'

GROUP BY [Customer Name]

Because we’ve used a field from the People table, Tableau has generated a join using the relationship we established in the data model.

We can immediately see the benefit of this on-the-fly generation of SQL as it ensures that we’re only asking the database for the data we actually need. This will, inevitably, lead to better query performance, addressing the first problem with the old data model.

But how does the new data model impact our other two problems—duplicated records and the need for LODs—which are caused by one-to-many relationships? The new data model stores each table independently, rather than joining them together then storing the data. Thus, data duplication is not really a problem from a data size standpoint. But it could be a problem when it comes to aggregation. To understand this, let’s change our data model to use the People_Multiple table instead of People:

Now let’s build our simple Sales by Customer bar chart again. When we do this, it executes the following SQL:

SELECT [Customer Name], SUM([Sales])

FROM [Orders]

GROUP BY [Customer Name]

That looks familiar! Again, because the view does not include any fields from People_Multiple, Tableau culls the join. And the beauty of this simple SQL statement is that SUM(Sales) will not be duplicated—this will give us the actual value, without the need for an LOD calculation.

But what happens when we do use fields from People_Multiple? Will that break everything and force us to use an LOD? Let’s add a filter on Person =  ‘Central Person 1’. That executes the following SQL:

SELECT [Customer Name], SUM([Sales])

FROM [Orders]

INNER JOIN (

    SELECT [Orders].[Region]

    FROM [Orders]

    INNER JOIN [People_Multiple]

    ON [Orders].[Region] = [People_Multiple].[Region]

    WHERE [Person] = 'Central Person 1'

    GROUP BY [Orders].[Region]

) AS [Table 1]

ON [Orders].[Region] = [Table 1].[Region]

GROUP BY [Customer Name]

As you can see, this query is a bit more complex than any we’ve seen before. But, that’s not necessarily a bad thing. Tableau has realized that we have a one-to-many relationship in our table, so it avoids a direct join between the Orders and People table because that would cause duplication. Instead, Tableau generates the following sub-query:

SELECT [Orders].[Region]

FROM [Orders]

INNER JOIN [People_Multiple]

ON [Orders].[Region] = [People_Multiple].[Region]

WHERE [Person] = 'Central Person 1'

GROUP BY [Orders].[Region]

This query joins Orders and People returning only the Region—the field upon which our table relationship is built. The result of this specific SQL is a single column and row:

It then joins that back to the Orders table and sums up the sales. Because the SQL avoids directly joining the two tables, we do not experience any data duplication and the aggregation returns the accurate value, without the need to perform complex LOD calculations. Pretty cool, eh!!

Summary

We’ve only just scratched the surface of the new data model, but I hope that you now have a better understanding of how it works and, particularly, how it’s different from the old data model. Make no mistake about it—this is a game-changing feature. It introduces a level of intelligence into the data model that simply did not exist before; it eliminates a number of common problems—specifically when dealing with one-to-many relationships; and it will make Tableau easier than ever for new users. I’m really excited about this feature and can’t wait to start using it.

Note: I was recently invited to do a presentation on the New Data Model for VizConnect. This was basically a video version of the blog, so if you prefer video, you can find it here:

Ken Flerlage, May 6, 2020

Twitter | LinkedIn | GitHub | Tableau Public