In this article we will show you, The steps involved in connecting Tableau to SQL Server 2014 and how to use the data present in SQL Tables as Tableau Data Source with example.
Steps involved in Connecting Tableau to SQL Server 2014
If you haven’t 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 be opened to configure the Server Connections.
- Server: Please specify the SQL Server instance name
- Enter information to sign in to the server: Please select, 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 be appeared.
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 be appeared. 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.
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 be opened 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:
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
Fact.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey
DimGeography AS Geo ON
DimSalesTerritory.SalesTerritoryKey = Geo.SalesTerritoryKey
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName
HAVING COUNT(Fact.OrderQuantity) > 60000
NOTE: Dont 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 be opened 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