Tableau Data Connection with Data Sources

Tableau can connect with various sources available such as Excel Files, Text Files, PDF Files, Databases (with ODBC Connector), Websites, Servers, etc.

Following types of data sources can be connected to the Tableau:

• File System: For example, Microsoft Excel, CSV, etc.
• Cloud System: For example, Google Big Query, Microsoft Azure, etc.
• Relational System: For example, Microsoft SQL Server, DB2, Oracle, etc.
• Other Sources: For example, ODBC.

The below picture shows the various types of data sources available in Tableau:

How to make a connection with the Text File?

Tableau can connect to the following text files: 

• .csv
• .tsv
• .txt
• .tab

Step 1: Open the Tableau Desktop.

Step 2: Click on the Text File option available below the Connect.

Step 3: Select the file to connect and click on the Open button.

Step 4: Now, you will see the CSV file on the data source’s left side.

The worksheet will look like this:

The below image shows the list of currently connected data sources:

We have only one data source connected at this time. Columns with string data can be placed under the Dimensions section, and columns with numeric data can be placed under the Measures section.

How to make a connection with the Excel File?

Step 1: Open the Tableau Desktop.Step 2: Click on the Microsoft Excel option available below the Connect.

Step 3: Select the file to connect and click on the Open button.

Step 4: Now, you will see the XLS file on the left side of the data source.

Step 5: More than one sheet can be dragged from the sheets tab.

The worksheet looks like:

Similarly, the below image shows the list of currently connected data sources where columns with string data can be placed under the Dimensions section and columns with numeric data can be placed under Measures section:

How to make a connection with the Database?

Step 1: Open the Tableau Desktop.

Step 2: Click on the required database, such as MySQL option available below the Connect.

Step 3: A dialog box will appear. Enter the name of the server and port. The default value of the port is 3306. Enter the required fields Username and Password and click on the Sign-in button. If you want to connect to the SSL server, click on the Require SSL checkbox. If you want to specify a SQL command that should run at the beginning of every connection, click on Initial SQL.

Step 4: Select the required database.

Multiple tables can also be joined by creating a relationship between them.

How to make a connection with the Website?

Step 1: Open the Tableau Desktop.

Step 2: Click on the option Web Data Connector available under option More.

Step 3: A web data connector window will open. Enter the URL and press enter.

If the web page is displayed, enter the information you’re prompted for and submit it. Wait while the connector is trying to retrieve the data and then import it as an extract in Tableau. 

Select a new worksheet and start your analysis.

How to make a connection with the Tableau Server?

Step 1: Open the Tableau Desktop.

Step 2: Click on the option Tableau Server available under option To a Server.

Step 3: Tableau Server Sign-in window will open. 

Step 4: Enter the required fields email id and password, and click on Sign in button.

Step 5: Select the required dataset and import it in Tableau.

Data Relationship:

A relational database/excel consists of tables/sheets connected by Union and Join to each other.

• Joins: Tableau can join up to 32 tables. There are different joins available in Tableau software such as Inner Join, Left Join, Right Join, and Outer Join.

First, we will load the tables in the Tableau. The first table is the Employees table containing Employee ID, Birth Date, First Name, Last Name, Gender, and Hire Date. The second table is the Salary table containing Employee ID, Salary, and From Date. Employee ID is the common field in both the tables and will act as a primary key. Hire Date and From Date are the same fields but are having different names. We will drag both the tables. 

When both the tables are dragged, an inner join will automatically be created. It is represented by intersecting circles in the picture below:

1. Inner Join: It is used to join all the common records between two tables/sheets. The joining condition is based on the primary key, and there can be more than one joining condition. 

When you click on the intersecting circle, a box will pop-up showing the different types of joins, and the currently applied join is highlighted.

2. Left Join: It is used to join all the records from the left table and only the common records from the right table.

3. Right Join: It is used to join all the records from the right table and only the common records from the left table.

4. Outer Join: It is used to join all the records from both the right and the left table.

• Union: Unions are used to append data from two or more tables. Joining condition is not required in Unions. For using Union, tables should have the same header.

First, we will drag the tables Air, Land, and Sea in the data window. Then right-click on the table and select Convert to Union option.

Tableau will then automatically append the rows by their common headers.

Before Union:

After Union:

Data Sorting: Tableau allows the sorting of data based on the user requirement. There are two ways of sorting:

1. Computed Sorting: Sort is directly applied on the axis using the Sort Dialog option.
2. Manual Sorting: Rearranging the order of dimension fields by dragging them adjacent to each other in an ad-hoc manner.

It can be sorted using data sources order like A to Z ascending, Z to A descending, A to Z ascending per table, and Z to A descending per table.

Sorting can be done using the option Sort Fields, which is present under the Data Source tab.

Replacing Data Source: Multiple data sources can be connected to a single workbook in Tableau. The different sources can be used to create dashboards and sheets in Tableau, but in some cases, we may need to replace the data source with the updated file.

Tableau has a feature named Replace data source to replace the data source.

Step 1: First, go to the connected data sources.

Step 2: Select the data source that needs to be replaced. Right-click on that source and click on the option Replace Data Source.

Step 3: A Data Source Replacement window will open. Select the Current and Replacement option and click on the button, OK.

It will now replace the data source.