Common Data Connections in Tableau

In this article, we will discuss about the commonly used file based data connections  and SQL based database connections in Tableau.

Commonly used file based data connections:

1. Microsoft Excel

2. CSV (comma separated value)

3. PDF files

SQL based data connections:

1. SQL Server

1. Microsoft Excel File as a Data Source Connection in Tableau:

For example there is an Amazon website which people buy different products from, for every purchase transaction on Amazon there will be a record created/inserted into the excel file - example Sample SuperStore data on Tableau.

Connecting to the Microsoft Excel as a data source in Tableau:

Go to the “Data Source” tab and click on the “Microsoft Excel” and select the “Sample - Superstore.xls” file from your desktop and click on open.

Now, on the left-hand side, you will see the Excel file, Sample -Superstore. That's a data source and below there are sheet names, orders, people, and returns. That's nothing but named range in excel. 

Now let’s drag the order table/sheet to the canvas. Whenever a table (Orders) is dropped to the canvas,  the table metadata will be displayed on the canvas - left hand side all the field names on the table and right hand side 100 sample records.

This is sample data, there are around 10,000 rows in this order sheet. But only 100 records will be displayed by default for preview purposes. If required, the sample record display can be changed based on the need basis by entering the desired number and clicking on this arrow after entering, and a sample data set should be updated. So that's one method of adding an excel file into Tableau. 

Now go to the Sheet1 to start using the columns from the orders tables in the visualizations and here, all  the dimensions and measures will be displayed from the Orders table.

1. CSV (Comma separated Value) File as a Data Source Connection in Tableau:

Now Let's see the CSV file data source connection. Downloaded a diabetes.csv file from the online data source and stored it on the local desktop. So now, let's go back to Tableau and pick up this file. Click on New Data Source and select the text file because it's a CSV file. 

And go to that folder that contains the CSV file and click open to connect to that csv file as a data source. Go to Tableau Data Sources to see the CSV file as a data source. Since there will be only one sheet in the csv files, it will be automatically added into the canvas as a source table and list of columns and sample data will be displayed similar to excel file source.

Now click on Sheet1 and start using the source fields for creating the visualizations.

1. PDF File as a Data Source Connection in Tableau::

Downloaded the sample PDF file (nz_water.pdf) from ( https://public.tableau.com/app/sample-data/nz_water.pdf ) Tableau public data sources and stored it on the local desktop.

Once the PDF file is opened there are multiple pages and there are multiple tables in the majority of the pages. So now, it is required to analyze this data or perform advanced analysis. Go back to Tableau, click on New Data Source, click on PDF file, and locate the data source PDF file on the local desktop and select the file and click on open, Tableau gives an option if you want to scan data for all the pages that have a single page or for a range of pages for now. 

 Let us select all and click OK, All the pages where there is a table in it will be displayed on the left hand side. Drag page number 1 onto your canvas to see the data in that page table. Table fields and sample data will be displayed similar to other file sources, of course, some formatting and cleaning is required on these table fields or records. There is a data interpreter inbuilt feature in Tableau, which cleans and formats the files. After that, the sample data will be updated and looks clean like other file based sources.

Once the data is cleaned/formatted as required - the table can be used to create visualizations.

SQL Data source - SQL Server Database:

To connect SQL data source to Tableau, click on New Data Source and there will be a list of databases which can be connected to Tableau. Tableau keeps expanding this list as and when a new database is released into the market. Click on Microsoft SQL Server database option to connect to the SQL Server database. After that, you'll notice a pop-up that says Microsoft SQL Server at the top. It essentially asks for the server’s name, database name (which is optional), and authentication. Enter the server’s name and authentication details to sign in. Once the authentication is successful, Tableau will retrieve all of the tables and views from a SQL Server database schema. Select the required database from the drop-down menu and select the table and add the table to canvas.

Table metadata and sample records will be displayed on the canvas. Tableau can connect to other databases, such as Amazon Redshift, Oracle, Snowflake etc..,.

All these data sources are most commonly used in Tableau for data analysis and to create visualizations.