In this tutorial, you will learn-
- Connecting to various data sources
- Connection to Text File
- Connecting to Excel File
- Connection to Database
- Connection to Websites
- Connecting to Tableau Server
- Data Relationship
- Data Sorting
- Replacing Data Source
Connecting to various data sources
Tableau can connect to various types of data sources. It can connect to text files, excel files, PDF files, etc. It can also connect to various databases using its ODBC connector. Tableau has the capability to connect to servers and web connectors. In this tutorial, we will learn about the connection procedures from various data sources.
Following screenshot gives the exhaustive connection types Tableau supports
Connection to Text File
Step 1) Click on the "Text File" option given in the data tab.
Step 2) In the next screen,
1. Select the SuperStore.csv
2. Click on "Open" Option. This will connect the text file into Tableau.
In the left pane you will see the CSV file.
Connecting to Excel File
Step 1) Click on the "Microsoft Excel" option given in the data tab.
Step 2) In the next screen,
- Select the SuperStore.xlxs
- Click on "Open" Option.
Step 3) It connects the excel file into Tableau. The sheets present in the excel file is shown on the left side of the window.
Connection to Database
Step 1) Click on the required database connection given in the data tab. For example, if you want to connect to MySQL database, click on the "MySQL" Option.
Step 2) This will open MySQL connection window.
- You can enter the MySQL server name and edit the "Port" if needed.
- Enter the username and password used to connect the database.
- Click on the "Sign in" button to connect to the database.
This procedure connects the database into the Tableau. You can select the tables from the database and import it into Tableau. You can join multiple tables by creating a relationship between them.
Connection to Websites
Tableau can connect to websites using web connector. The connection procedure is given as follows.
Step 1) In this window,
- Click on "More" option in the data tab.
- Select "Web Data Connector" from the list.
Step 2) This will open a Web Data Connector window where you need to enter the web connector details.
Connecting to Tableau Server
Tableau Server can store extracted data sources and database connections. In enterprise level, it is important to keep all the required data connections in Tableau Server. It helps all the users of the enterprise to connect to the data source easily. The data sets in the Tableau Server can connect to Tableau Desktop by following the given procedures.
Step 1) Select the "Tableau Server" option given in the data tab.
Step 2) This opens the Tableau Server Sign in window. Click on 'Tableau Online' option.
Step 3) It opens Tableau Online Sign In Window.
- Enter the registered email id and password.
- Click on 'Sign In' button.
Step 4) This will connect Tableau Server into Tableau Desktop.
- Select the data set as shown in the figure.
- This will import the dataset into Tableau.
Data Relationship
A relational Database/Excel file consists of multiple Tables/sheets. These multiple tables/sheets can be connected to each other in Tableau. This connection is established by 'Join' or 'Union' feature present in Tableau. The relationship between data in two or more tables needs to be specified while joining tables.
Joins:
Tableau can "join" tables. It can join up to 32 tables in a data source. While joining, the relationship between two or more tables can be specified. The tables present in the data source can be related to each other using the joins such as inner join, left join, right join and outer join. The functionalities of Join types are explained in the table below,
Inner Join | Left Join | Right Join | Outer Join |
It joins all the common records between two tables or sheets. The joining condition can be given based on the primary key. One or more joining conditions can be specified to join the tables. Many tables can be joined together in Tableau for visualization. | A left join is used to join all the records from a left table and common records from the right table. One or more join conditions can be specified to left join two different tables. | A right join is used to join all the records from a right table and common records from left table. Based on the requirements, one or more joining condition can be set. | An Outer join is used to join all the records from both left and right table. Joining conditions can be set to join common records. |
Let's see an example of Join
Step 1) Once an Excel is connected, Drag the required sheet into data window as shown in the figure.
Step 2) You can connect multiple sheets by dragging the sheets into the data window. The relationship between the sheets can be given by connecting the data fields.
Step 3) In the data sheet
- Click on the "Join" option as shown in the figure. By default, Tableau joins two tables by connecting a relationship between common column present in it.
- You can edit the relationship by selecting the required joining Condition.
- You can add one or more data relationship between the sheets or tables.
Union
Union is used to append the data from two or more tables. In most of the cases, tables with same headers are appending together using union function. Joining conditions are not required while union two or more tables. The procedure to union tables are given as follows.
Step 1) Drag any of the sheet into data window as shown in the figure.
Step 2) Right click on the sheet and select "Covert to Union" option.
Step 3) In the data union window.
- Drag other sheets which need to be union.
- Click on 'OK' to append or union all the sheets present in the window.
A data relationship is not needed to union sheets present in the data source.
Data Sorting
Data present in the data source can be sorted based on the user requirement. It can be sorted using data source order, ascending, descending, ascending per table and descending per table.
Once the data is connected to Tableau, data sorting can be done using the "Sort Fields" option. The option is present in the "Data Source" tab.
Replacing Data Source
Tableau can connect multiple data sources in a single workbook. The different data sources can be used to build various sheets and dashboards in Tableau. In some cases, data sources need to be replaced with updated file. Tableau has a data source replacing feature which can replace data sources. This feature does not affect the already built visualizations using the old data source. It is important to keep or replace all the used dimensions and measures while replacing the data source.
The data source connected in Tableau can be replaced with another data source. The procedure for replacing data source is given as follows.
Step 2)
- Select the Data Source to be replaced.
- Right-click on the data source and select "Replace Data Source" option.
Step 3) It opens the data source replacement window.
1. Select the current
2. Select replacement data source.
3. Click on "OK" to replace the data source.
Summary
- Tableau can connect to various data sources like Excel, databases, and websites.
- It can join various sheets or tables present in the data source.
- The joining conditions used in Tableau are an inner join, right join, left join and outer join.
- Tableau can connect and join various sheets present in an excel file.
- The database can be connected to Tableau by connecting the database server.
- Tableau can connect websites by using web connector.
- Data sorting is available in the Tableau's data source Tab.
- Tableau Server can be connected to Tableau Desktop.
