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 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.
Once you select the Option, a new window will open to configure the Server Connections.
- Server: Please specify the SQL Server instance name to connect from the Tableau desktop.
- Enter information to sign in to the server: Use this section to determine how you want to connect with SQL Server. If you 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 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 must choose the Sql Server database you want to connect from Tableau.
In this example, we are selecting the AdventureWorksDW2014 database we downloaded from the Microsoft official Download Center.
Once you completed it, 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 it. Please change this default name to a more meaningful, unique name.
- Server: This section will showcase the Instance name we used in Tableau 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, which is handy for many 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 the Table to this Section. It 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.
The Desktop allows us to add multiple tables using joins. Once you add the Table, the Preview region displays two buttons: Update Now and Automatically Update.
- If you click Update Now, we have to click this button every time we make 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, the 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. In Tableau, you can write your own query to extract data from the connected SQL Server 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 query will open to write your own query
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
Custom Query we used in the above screenshot is:
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 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
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 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 reports by dragging Measures and Dimensions