Steps involved in Connecting 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 below screenshot.
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 decide how you want to connect with SQL Server. If you want to use the current windows login credentials then select Use Windows Authentication option otherwise, Please select Use specific Username and Password option and specify the credentials (User name and Password) to access the SQL Server.
- Require SSL: Please check mark this option while connecting to SQL Server. This will prevent SQL Injections.
- Read Uncommited data: Please check mark this option to use the Read Uncommited ACID property.
For now, we just used our own instance and click on OK button
Once you click OK button, below screenshot will appear.
Under the Database section, We have a drop down menu to select the required Database available in that Instance. In this example, We are selecting AdventureWorksDW2014 database. You can download this database from Microsoft official Download Center for free.
Once you are done, below screenshot will appear. Please understand the following option before you start creating report
- AdventureWorksDW2014 is the default data source name (In fact, selected Database name) assigned by the Tableau. Please change this default name to more meaningful unique name as per your requirements
- Server: This section will display the Instance name we used to connect with the SQL Server.
- Database: This section will display the Database we selected.
- Table: This section will display the available Tables, Views and Stored Procedures in selected Database. We have the search bar under this section and it is very useful for large number of tables. For instance, If you have 100 or 200 tables then you can use this to search for 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. This is something like Data set.
- This region will show the data present in our Data set
We can add the Tables to Region 5 in Multiple Ways: As the Name suggest, Either we can Drag the Table, Stored Procedure from Region 4 to 5th region or else simply double-click on the required table will automatically add.
TIP: Tableau allows us to add multiple tables using Joins
Once you added the Table, Preview region will display two buttons such as Update Now and Automatically Update as shown below.
- If you click Update Now, We have to click this button every time we made changes to the Dataset
- If you click Automatically Update button, changes made to the Dataset will automatically reflect the preview. In this example, we are selecting this option
Once you click on Automatically Update button, Preview region will display the data present in the DimGeography table.
Add Data to Tableau
Above specified method is not the only way to add the data. You can write your own SQL Query to extract data from the selected Database or data source. To do this, Please click on the New Custom SQL button at the bottom left as shown in below screenshot.
Once you click on the New Custom SQL button, a new window called Edit Custom SQL will open to write 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, Just 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 View Data will open to see the Custom SQL query result
Please refer below screenshot to understand the different areas . We will discuss the Connections part in another article.
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 will display all the data sources available.
- Dimensions: Columns with string data will be placed under the Dimensions section
- Measures: Columns with Numeric data or Metric values will be placed under the Measures section
- This is the region where we design our Tableau reports by dragging Measures and Dimensions
Thank You for Visiting Our Blog