Understanding the Tableau Data Model, Joins, and Blends

In this chapter, we'll gain a deeper understanding of how to model and structure data with Tableau. We've seen the Data Source screen in previous chapters and briefly explored how to drag and drop tables to form relationships. Now, we'll explore some of Tableau's more complex features to gain a good understanding of how Tableau allows you to relate multiple tables together, either logically or physically.

We'll start with a broad overview of Tableau's new data model and then examine some details of different types of joins and blends. The data model and blending apply primarily to Tableau Desktop (and Server), but pay special attention to the discussion of joins, as a good understanding of join types will aid you greatly when we discuss Tableau preparation in Chapter 15, Taming Data with Tableau Prep.

The data model is only available in Tableau 2020.2 and later. If you are...

Explanation of the sample data used in this chapter

For this chapter, we'll use a sample dataset of patient visits to the hospital. The data itself is contained in the Excel file Hospital Visits.xlsx in the Learning Tableau\Chapter 13 directory. The tabs of the Excel file represent tables of data, just as you might find in any relational database as multiple files, or simply as literal tabs in an Excel file! The relationship between those tables is illustrated here:

Figure 13.1: The four tabs of the Excel file illustrated as four tables with relationships

Excel does not explicitly define the relationships, but they are shown here as they might exist in a relational database using foreign key lookups. Here is a brief explanation of the tables and their relationships:

• Hospital Visit: This is the primary table that records the admission and diagnosis of a single patient on a single visit to the hospital. It contains attributes, such as Admit Type and Location...

Exploring the Tableau data model

You'll find the data model as a new feature in Tableau 2020.2 and later. Every data source will use the data model. Data sources created in previous versions will be updated to the data model but will be contained in a single object, so, functionally, they will work in the same way as the previous version.

Previous versions of Tableau allow you to leverage joining tables and blending data sources together, and we'll consider those options at the end of this chapter. For now, we'll look at creating a data model and understanding the paradigm.

Creating a data model

We've briefly looked at the Data Source screen in Chapter 2, Connecting to Data in Tableau. Now, we'll take a deeper look at the concepts behind the interface. Feel free to follow along with the following example in the Chapter 13 Starter.twb workbook, or examine the end results in Chapter 13 Complete.twbx.

We'll start by creating a connection...

Using joins

A join at the physical level is a row-by-row matching of the data between tables. We'll look at some different types of joins and then consider how to leverage them in the physical layer of a data model.

Types of joins

In the physical layer, you may specify the following types of joins:

• Inner: Only records that match the join condition from both the table on the left and the table on the right will be kept. In the following example, only three matching rows are kept in the results:

  

  Figure 13.12: Inner join

• Left: All records from the table on the left will be kept. Matching records from the table on the right will have values in the resulting table, while unmatched records will contain NULL values for all fields from the table on the right. In the following example, the five rows from the left table are kept, with NULL results for any values in the right table that were not matched:

  

  Figure 13.13: Left join...

Using blends

Data blending allows you to use data from multiple data sources in the same view. Often, these sources may be of different types. For example, you can blend data from Oracle with data from Excel. You can blend Google Analytics data with a spatial file. Data blending also allows you to compare data at different levels of detail. Let's consider the basics and a simple example.

Data blending is done at an aggregate level and involves different queries sent to each data source, unlike joining, which is done at the row level and (conceptually) involves a single query to a single data source. A simple data blending process involves several steps, as shown in the following diagram:

Figure 13.26: How Tableau accomplishes blending

We can see the following from the preceding diagram:

1. Tableau issues a query to the primary data source.
2. The underlying data engine returns aggregate results.
3. Tableau issues another query to the secondary data...

When to use a data model, joins, or blends

In one sense, every data source you create using the latest versions of Tableau will use a data model. Even data sources using one physical table will have a corresponding object in the logical layer of a data model. But when should you relate tables using the data model, when should you join them together in the physical layer, and when should you employ blending?

Most of the time, there's no single right or wrong answer. However, here are some general guidelines to help you think through when it's appropriate to use a given approach.

In general, use a data model to relate tables:

• When joins would make correct aggregations impossible or require complex LOD expressions to get accurate results
• When joins would result in the duplication of data
• When you need flexibility in showing full domains of dimensions versus only values that match across relationships
• When you are uncertain of a data...

Summary

You now have several techniques to turn to when you need to relate tables of data together. The data model, a new feature in the latest versions of Tableau, gives a new paradigm for relating logical tables of data together. It introduces a few new behaviors when it comes to showing the full and partial domains of dimensional values, but it also greatly simplifies aggregations by taking into account the natural level of detail for the aggregation. In the physical layer, you have the option of joining together physical tables.

We covered the various types of joins and discussed possibilities for using join calculations and cross-database joins for ultimate flexibility. We briefly discussed how data blending works and saw a practical example. Finally, you examined a broad outline of when to turn to each approach. You now have a broad toolset to tackle data in different tables or even in different databases or files.

We'll expand that toolset quite a bit more in the...