Connecting to on-premises databases

In this recipe, we'll connect to a Microsoft SQL Server database. The many connectors provided out of the box by Tableau Prep allow you to connect to databases almost as easily as to file connections, allowing you to quickly start an advanced flow.

Getting ready

In order to follow along with this recipe, download the Sample Files 2.4 folder from the book's GitHub repository. This folder contains the Wide World Importers sample database backup (.BAK) file, which you can restore to your SQL Server instance.

Note that the provided BAK file is suitable for SQL Server 2016 SP1 or later. If you're running an earlier version or need instructions on installation, please consult Microsoft's support page at https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-oltp-install-configure.

How to do it…

To get started, ensure you have Tableau Prep Builder open, then follow these steps:

1. From the home screen, click the Connect to Data button to bring up the Connect pane. From here, select the search field and type in SQL to instantly filter the available connections.
2. From the filtered selection of connections, select Microsoft SQL Server. This will bring up the Connection dialog.
3. In this dialog, enter your connection details. Depending on your server, these details will vary. If you're not sure about these details, please contact your database administrator. Click Sign In to continue.

   Important note

   If you are using a macOS computer, the Sign In button may remain disabled even though you have populated the appropriate connection details. This could be the result of a driver missing on your device. To find the drivers needed, go to the Tableau Driver Download web page at https://www.tableau.com/en-us/support/drivers?edition=pro&lang=en-us&platform=mac.

4. Once the connection has been established, Tableau Prep will show a dropdown in the Connections pane of all databases on the server you've selected. Select your database to reveal the available tables:
   

   Figure 2.20 – Database selection

5. From the list of available tables, we need to select the table we'd like to ingest into our flow and drag it onto the canvas. Let's drag in the table named Order:

Figure 2.21 – Drag a table onto the canvas

With the table on the canvas, we can now continue building out this flow as with any other data connection type.

How it works…

Tableau Prep has a number of built-in database connections that remove the complexity of connecting by configuring connections such as ODBC manually. The number of supported data connection types is continually expanding, too. If you do not see your database listed, you can always opt to use an ODBC connection instead. See the recipe titled Connecting to JDBC or ODBC data sources in this chapter for more information.

There's more…

Database tables can relate to each other and be joined to each other to create insightful datasets. We'll cover joins in detail in Chapter 5, Combining Data. For now, I want to highlight a great feature in Tableau Prep that indicates the table primary key and foreign keys in the bottom pane. You can find this information in the Linked Keys section as shown in the following screenshot:

Figure 2.22 – Linked Keys for database connections

There are three types of keys: primary, foreign, and keys representing both primary and foreign. Tableau Prep shows an icon indicating the key type accordingly:

Figure 2.23 – Database key types

By completing the steps in this recipe, you have connected Tableau Prep to an on-premises database.