Connecting to Databases and Cloud Services
So far, this chapter has covered files and relational databases that Tableau can connect to. However, large corporations with complex and abundant amounts of data are likely to use databases and cloud services that hold their data. This section will go through the different types of databases and cloud connectors and discuss how to connect to the data.
Default Server Connectors
Tableau has a vast number of default servers readily available for use. Go to the Connect pane and, under where it says To a Server, hover over More… to see all the types of server connections that can be made.
Figure 1.14: Tableau Server connection options
Servers is a broad term, but in the context of Tableau, largely refers to the host of relational databases (or other technically different but functionally similar services, such as Microsoft OneDrive, which allows business users to share and collaborate on the web).
As visible in Figure 1.14, the list of connectors is comprehensive, even for Tableau version 2022.3. You are not expected to memorize the full list of connectors, and Tableau is continually adding new connectors to this list. However, it is useful to be aware of their general types.
Relational databases (introduced earlier in this chapter) are communicated with and queried using SQL, which is compiled by Tableau behind the scenes. These queries are written to be highly efficient, so queries can return results as quickly as possible.
When a server is selected, the resulting window will ask the user for connection and authentication details. (Though the exam is unlikely to pose questions about this window specifically, it is important to understand what is required to establish a connection that works reliably.)
Figure 1.15: Credentials section to SQL Server
Note that for certain services, such as Microsoft OneDrive and SharePoint Online, single sign-on (SSO) may be the authentication type configured by the network administrator. This will be someone who has access to permissions for others in the organization.
When this is the case, users are usually directed to their default browser to sign in with their organizational account.
Figure 1.16: SQL query builder
Initial SQL is an advanced option in Tableau Desktop that developers can use if they wish to recall a specific subset of data (for example, population data for a particular region only). It runs when the workbook is first opened and generates a cache (or temporary table) for the duration of the session. Any changes to the data will not be reflected until the workbook is reopened.
Initial SQL can result in noticeable performance improvements when used alongside custom SQL. See the Custom SQL Connections section later in the chapter.
Once connected, the database tables available to the user will appear on the left-hand side, in the Data pane. At this stage, a connection has been established, but Tableau cannot predict which data the user is interested in. The table(s) of interest must be dragged onto the relationship canvas, as shown here:
Figure 1.17: Connection pane and tables to be connected
To preview the data for the given table in the Results pane, select Update Now:
Figure 1.18: The Update Now button to preview the data
Custom SQL Queries
A more technically advanced but powerful option for connecting to database tables is Custom SQL. This allows a developer to specify the exact fields and even filter the data from the source to create an efficient dataset to work with in Tableau. The following section will look into SQL queries and how to connect to them in Tableau.
Figure 1.19: Example of an Employee Lookup Custom SQL