Steps involved to Connect Tableau to SQL Server
If you have not started the Tableau yet, Double click on the Tableau desktop to open. Once it is open, it looks like the below screenshot.
To connect Tableau to SQL Server, first, Under the Connect to a Server section, Please select the Microsoft SQL Server Option
Once you selected the Microsoft SQL Server 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 Use Windows Authentication option. Otherwise, select Use specific Username and Password option & specify the Username and Password to access the SQL Server.
- Require SSL: Please check mark this option while connecting to SQL Server. It prevents SQL Injections.
- Read Uncommitted data: Checkmark this option to use the Read Uncommitted ACID property.
For now, we used our local SQL Server instance to connect to Tableau. Next, click on the OK button
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 with tableau
In this example, we are selecting the AdventureWorksDW2014 database that we downloaded from the Microsoft official Download Center.
Once you completed, the following screenshot will appear. Please understand the next options before you start creating a report
- AdventureWorksDW2014 is the default data source name (In fact, selected Database name) assigned by the Tableau. Please change this default name to a more meaningful, unique name.
- Server: This section will showcase the Instance name we used to connect with the SQL Server.
- Database: It displays the Database we selected.
- 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
- Drag Sheets Here: You have to Drag Table(s) from Table to this Section. Tableau will only use the Tables present in this area. It is something like a Dataset.
- It shows the data present in our Dataset
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.
TIP: Tableau 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
Once you click on the Automatically Update button, Preview region will display the data present in the DimGeography table.
Add Data from Sql Server to Tableau
The above-specified method is not the only way to add data. You can write your SQL 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 SQL query
Once you finish writing your Custom SQL, Please click on the Preview Results.. button to see the query result.
TIP: Please use the SQL Server Management Studio to build your custom query and then paste the same in this Edit Custom SQL Window
Custom SQL Query we used in the above screenshot is:
-- Custom SQL 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 INNER JOIN DimSalesTerritory ON Fact.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN DimGeography AS Geo ON DimSalesTerritory.SalesTerritoryKey = Geo.SalesTerritoryKey GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName HAVING COUNT(Fact.OrderQuantity) > 60000
NOTE: Don’t worry above the SQL 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 SQL query result
Please refer below screenshot to understand the different areas. We will use another article to discuss the Connections part.
Once you finished reviewing the data, click on Sheet 1 to design the Tableau Report
- 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.
- Dimensions: Columns with string data will place under the Dimensions section
- Measures: Columns with Numeric data or Metric values will place under the Measures section
- Here, we design our Tableau reports by dragging Measures and Dimensions