This chapter is from the book
This chapter is from the book
This chapter is from the book
Connecting to Data
When you first open Tableau Desktop, the Connect to Data screen appears (Figure 3.3). This “starting screen” is the first thing users see after launching the Tableau software.
)
Figure 3.3 The Tableau Connect to Data screen.
There are several important elements to know on this screen:
Connect: All possible data connections can be accessed here to connect to your data, including those stored locally or on servers. Additionally, two saved data sources—Sample-Superstore and World Indicators—are training data sets included natively within Tableau.
Open: As you create your own workbooks, recently opened workbooks appear here for quick access.
Sample Workbooks: Similarly to the saved data sources, these are default workbook samples provided by Tableau that can be used as simulations for training or other skills development. (Note: Beginning in Tableau 2022.4, these sample workbooks are now termed Accelerators.)
Discover: This pane connects you to various Tableau training, visualization, and other resources. Announcements for upcoming Tableau events, Viz of the Day, and upgrade reminders also appear at the bottom of this pane.
Connecting to Tables
Selecting a saved data source from the Connect pane will immediately take you to the Tableau worksheet canvas, bypassing the data preparation stage. We’ll review this interface later in this chapter, but first let’s take a look at how to prepare your data for analysis in Tableau.
For our purposes, we’ll connect to a very common file format—an Excel spreadsheet. You can connect to any Excel spreadsheet by clicking the Excel option under the Connect menu and navigating to the file’s location on your machine. Once connected to your data file (or any other file or database connection), Tableau opens the Data Source page (Figure 3.4).
)
Figure 3.4 The Data Source page.
Before we look at options to make changes to the data at this point, it’s important to note that whatever changes are made create metadata and have no impact on the underlying data source. This means you can make specific changes and prepare data directly in Tableau without affecting the data’s existing infrastructure.
The Data Source page provides several options to help you prepare this file for analysis in Tableau.
Connections: You can add additional data sources by clicking Add. You can also edit the name of the connection or remove it as desired by clicking the drop-down arrow to the right of the filename. (You can also rename the connection by clicking its title on the canvas to the right.)
Sheets: This pane displays all the sheets in the Excel file, corresponding to the names of individual worksheet tabs. Sheets in Excel are treated the same as tables in a database, and you can choose to connect to a single table or join multiple tables. To connect to a sheet, simply drag and drop it into the data connection canvas to the right (you will notice a “Drag tables here” prompt) or by double-clicking the sheet desired. After you connect to a sheet, three things happen (Figure 3.5), which allow you to further explore the capabilities of this screen:
The sheet name appears in the data connection canvas.
The data is displayed in the preview pane below the data connection canvas.
A Go To Worksheet icon is displayed.
)
Figure 3.5 You have connected to the Orders table of the Excel file, populating the data preview pane. Tableau also provides the prompt to Go To Worksheet, which allows you to begin visually exploring the data if you are ready.
Before moving on, there are a few more things to take note of on this screen.
First, if you aren’t satisfied with any individual column name, you can click the drop-down arrow to the right of the name and select Rename. Additionally, clicking the data type icon allows you to change the default data type for that column (Figure 3.6).
)
Figure 3.6 Clicking the data type icon allows you to change the default data type for that column. This determines how the fields are displayed on your worksheet in the next step.
If you would like to further refine your columnar data, you can find a few more options to prepare this data by clicking the drop-down arrow on the column (Figure 3.7):
Rename the field.
Copy values.
Hide (or show) fields.
Assign new names to individual dimension members via an Alias.
Create calculated fields to create a new field before populating your Tableau worksheet.
Create groups to combine different dimension members.
Split fields by delimiter using an automatic or custom split. (Note: This option appears only if the data can be split.)
Pivot data fields as necessary. (Note: You can do only one data pivot per data source.)
The Describe option gives you additional information about the field.
)
Figure 3.7 Clicking the drop-down arrow provides more options to prepare data.
Depending on the data type contained within the field, some options may not be available. Likewise, for quantitative fields, the string functions are not available, but an additional option, Create Bins, allows you to create equally sized bins—an ability useful for making histograms.
Live Versus Extract
You might have noticed the option for a Live or Extract connection on the sheet canvas (Figure 3.8).
)
Figure 3.8 Options for connecting live or extracting data.
By default, most data sources will connect live with no filters. However, before you begin analyzing data, this decision might be something you want to consider. Be sure to understand the benefits and drawbacks of Live versus Extract connection options as described in Table 3.1.
Table 3.1 Pros and cons of Live versus Extract connection options
Connection |
Pros |
Cons |
|---|---|---|
Live |
|
|
Extract |
|
|
Finally, you have the ability to filter the entire data source before working with it in Tableau. These filters can be created with any combination of fields by clicking the blue Add text under Filters. Filtering may help eliminate clutter or extraneous fields by streamlining your view of the data and removing data that is not needed for analysis.
Connecting to Multiple Tables with Relationships and Joins
As mentioned earlier, you can connect to multiple data sources in Tableau. You can also connect to multiple tables in the same data source. Previously, connecting multiple tables, or data sources, in Tableau was achieved through joins. However, starting in Tableau 2020.2, a new logical layer was added to Tableau’s data model, improving this functionality. While joins are still an option to connect multiple tables, this new logical layer uses “relationships” to normalize data sources with multiple tables at a different level of detail.
Relationships are a dynamic, flexible way to combine data for analysis, and generally a simpler process than working with joins. Therefore, Tableau recommends using relationships as a first approach to combining data.
To create a relationship, drag and drop or double-click the second sheet to which you want to connect. A “noodle” appears (Figure 3.9), which represents the relationship between the selected tables. Tableau automatically attempts to choose related fields, but users can manually adjust these—or choose another set of related fields.
)
Figure 3.9 The relationship “noodle.”
The data grid displays columns for each table, depending on which is selected on the upper pane. Although relationships do not merge tables together, Tableau automatically configures the join type, the right aggregation, and the handling of null values.
Relationships aside, there may be times when you still opt to create a join. To create a join, double-click on the first table, then drag out the second table. This will generate the same join experience as in previous versions of Tableau via the join canvas (Figure 3.10).
)
Figure 3.10 The join canvas.
The join icon with the blue center indicates that Tableau has automatically joined these tables as an inner join, making it the default join clause. Clicking the join icon will display the details and give you the option to edit the join clause, or even create a new one.
While Tableau will automatically join your tables, it does so by guessing your matching ID. You can change this default by clicking on the fields, which shows a drop-down menu of all data fields available to join.
Overview of Join Types
Tableau provides four types of joins that you can use to combine your data: inner, left, right, and outer. Inner and left joins are the most common types of joins.
Inner join: Joins records where there is a matching field in both datasets. Using an inner join to combine tables produces a new virtual table that contains values that have matches in both tables.
Left join: Joins records from the left and right sides of your equation when there is a match. Using a left join to combine tables produces a new virtual table that contains all values from the left table and corresponding matches from the right table. When there is no corresponding match from left to right, you will see a null value.
Right join: Joins all the records from the data on the right side of your equation and any matching records from the left side. The opposite of a left join, using a right join to combine tables produces a table that contains all values from the right table and corresponding matches from the left. Likewise, when a value in the right table doesn’t have a corresponding match in the left table, you will see a null value.
Outer join: Joins all the records from each dataset together, even when there is no join (this option is rarely used). Using a full outer join to combine tables produces a table that contains all values from both tables. If a value from either table doesn’t have a match with the other table, you will see a null value.