Tableau Tutorials

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:

Data Connection with Data Sources

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.

Tableau Data Connection with Text File

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

Tableau Data Connection with Text File

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

Tableau Data Connection with Text File

The worksheet will look like this:

Tableau Data Connection with Text File

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

Tableau Data Connection with Text File

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.

Tableau Data Connection with Microsoft Excel

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

Tableau Data Connection with Microsoft Excel

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

Tableau Data Connection with Microsoft Excel

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

Tableau Data Connection with Microsoft Excel

The worksheet looks like:

Tableau Data Connection with Microsoft Excel

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:

Tableau Data Connection with Microsoft Excel

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.

https://www.guru99.com/images/tableau/060818_0511_TableauConn8.jpg

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.

https://www.edureka.co/community/?qa=blob&qa_blobid=10425111308816553892

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.

https://www.guru99.com/images/tableau/060818_0511_TableauConn10.jpg

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

https://help.tableau.com/current/pro/desktop/en-us/Img/example_wdc_error.png

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.

https://www.guru99.com/images/tableau/060818_0511_TableauConn12.jpg

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

https://help.tableau.com/current/pro/desktop/en-us/Img/desktopserversignin.png

Step 4: Enter the required fields email id and password, and click on Sign in button.https://help.tableau.com/current/pro/desktop/en-us/Img/sign_in.png

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. 

drag the table in tableau join

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

drag salary table in tableau joins2
  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.

intersecting two side
  1. Left Join: It is used to join all the records from the left table and only the common records from the right table.
left joins
  1. Right Join: It is used to join all the records from the right table and only the common records from the left table.
right join
  1. Outer Join: It is used to join all the records from both the right and the left table.
use full outer join
  • 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.

https://www.thedataschool.co.uk/wp-content/uploads/2018/09/Blank-data-pane-1024x536.png

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

Before Union:

https://www.thedataschool.co.uk/wp-content/uploads/2018/09/Before-union.png

After Union:

https://www.thedataschool.co.uk/wp-content/uploads/2018/09/After-union.png

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.

Tableau Data Sorting

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.

Tableau Replacing Data Source

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

Tableau Replacing Data Source

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

Tableau Replacing Data Source

It will now replace the data source.

Facebook Comments
Tags

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top button
Close
Close