Connecting Tableau to SQL Server

In this article, we will show you the steps involved in connecting Tableau to SQL Server. Besides, how to use the data present in SQL Tables as Tableau Data Source with an example.

Steps involved to Connect Tableau to SQL Server

If you have not started the Tableau yet, Double click on the desktop to open. Once it is open, it looks like the below screenshot.

To connect Tableau, first, Under the Connect to a Server section, Please select the Microsoft SQL Server Option

Choose Microsoft Server Option 1

Once you selected the Option, a new window will open to configure the Server Connections.

  • Server: Please specify the SQL Server instance name
  • Enter information to sign in to the server: Use this section to determine how you want to connect with SQL Server. If you’re going to use the current windows login credentials, select the Use Windows Authentication option. Otherwise, select the Use specific Username and Password option & specify the Username and Password to access the Server.
  • Require SSL: Please check mark this option while connecting to SQL Server. It prevents Injections.
  • Read Uncommitted data: Checkmark this option to use the Read Uncommitted ACID property.

For now, we used our local Server instance to connect to Tableau. Next, click on the OK button

Connecting Tableau to SQL Server 3

Once you click the OK button, the following screenshot will appear. Under the Database section, there is a drop-down menu to select the required Database available in that Instance. Here, you have to choose the database that you want to connect.

In this example, we are selecting the AdventureWorksDW2014 database that we downloaded from the Microsoft official Download Center.

Connecting Tableau to SQL Server 5

Once you completed, the following screenshot will appear. Please understand the next options before you start creating a report

  1. AdventureWorksDW2014 is the default data source name (In fact, selected Database name) assigned by it. Please change this default name to a more meaningful, unique name.
  2. Server: This section will showcase the Instance name we used to connect with the SQL Server.
  3. Database: It displays the Database we selected.
  4. Table: This section displays the available Tables, Views, and Stored Procedures in the selected Database. We have the search bar under this section, and it is handy for a large number of tables. For instance, If you have 100 or 200 tables, you can use this to search for a specific table name
  5. Drag Sheets Here: You have to Drag Table(s) from Table to this Section. It will only use the Tables present in this area. It is something like a Dataset.
  6. It shows the data present in our Dataset
Connecting Tableau to SQL Server 6

We can add the Tables to Region 5 in Multiple Ways: As the Name suggests, Either we can Drag the Table, Stored Procedure from Region 4 to a 5th region, or else double-click on the required table will automatically add.

The Desktop allows us to add multiple tables using joins. Once you add the Table, the Preview region displays two buttons, such as Update Now and Automatically Update.

  • If you click Update Now, we have to click this button every time we made changes to the Dataset
  • If you click the Automatically Update button, modifications made to the Dataset will automatically reflect the preview. In this example, we are selecting this option
Connecting Tableau to SQL Server 7

Once you click on the Automatically Update button, Preview region will display the data present in the DimGeography table.

Connecting Tableau to SQL Server 8

Add Data from Sql Server to Tableau

The above-specified method is not the only way to add data. You can write your own query to extract data from the selected database or data source. To do this, click on the New Custom SQL button at the bottom left, as we showed below.

Once you click on the New Custom SQL button, a new window called Edit Custom SQL will open to writing your own query

Connecting Tableau to SQL Server 9

Once you finish writing your Custom query, Please click on the Preview Results.. button to see the query result.

TIP: Please use the Management Studio to build your custom query and then paste the same into this Edit Custom SQL Window

Connecting Tableau to SQL Server 10

Custom Query we used in the above screenshot is:

-- Custom Query to retrieve Data from SQL Server in Tableau 
SELECT Geo.EnglishCountryRegionName AS Country, 
       Geo.StateProvinceName AS State, 
       SUM(Fact.SalesAmount) AS Sales, 
       SUM(Fact.TaxAmt) AS Tax, 
       COUNT(Fact.OrderQuantity) AS Orders
FROM FactResellerSales AS Fact
     DimSalesTerritory ON 
        Fact.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 
     DimGeography AS Geo ON 
        DimSalesTerritory.SalesTerritoryKey = Geo.SalesTerritoryKey
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName
HAVING COUNT(Fact.OrderQuantity) > 60000

NOTE: Don’t worry about the Query, understand the procedure to write the custom Query. However, If you want to understand, Please read our SQL Server Tutorial

Once you click on the Preview Results.. button, a new window called ViewData will open to see the Custom query result

View Database Table Records or rows 11

Please refer below screenshot to understand the different areas. We will use another article to discuss the Connections part.

Choose Live or Extract Connection 12

Once you finished reviewing the data, click on Sheet 1 to design the Report

  1. Data: This will display the list of currently connected data Sources. We have only one at this time. Otherwise, it shows all the available data sources.
  2. Dimensions: Columns with string data will place under the Dimensions section
  3. Measures: Columns with Numeric data or Metric values will place under the Measures section
  4. Here, we design our reports by dragging Measures and Dimensions
Connecting Tableau to SQL Server 13