Connecting to most of the databases on a server is straightforward. Providing the server name and authentication details is usually sufficient. There are at times, however, when either Tableau does not provide a direct connection to that database server or you want to use an ODBC connection.
The easiest way to make a connection to a database server is using an ODBC connection. To use this type of connection, we must first set up a Data Source Name (DSN). For this recipe, we will set up a DSN to connect to a database server.
In Control Panel, click on System and Security and then on Administrative Tools. Double-click on Data Source (ODBC). You'll see a Data Source Administrator window. Under User DSN, click on Add and follow the steps to create a DSN for your database. If you have a SQL Server instance installed and the Adventure Works database populated, select SQL Server Native Client and hit Finish. In the Name field, enter a name that you'll remember easily; remember that it cannot contain spaces. I chose adventureworkscnxn. Under Server, either enter the database server name or select Local. Continue with the default selections until you see a Finish button.
Once the DSN is set up, open a new worksheet in Tableau and perform the following steps to connect to a database using ODBC:
- Click on Connect to data and select Other Databases (ODBC).
- In the DSN dropdown, select the DSN that you created earlier.
- Click on Connect to test the data connection.
- Under Owner, select Person.
- Among the Table selection radio buttons, select Single Table; search for a table name by clicking on the magnifying glass icon.
- Under the Give the connection a name … textbox, enter a name and hit OK.
- If you can see the Connect live, Import all data, and Import some data options in the Data Connection page, you were able to successfully connect to the SQL Server using ODBC and DSN.
With ODBC, Tableau provides an option to connect to the data sources that otherwise do not have native support in Tableau. This option provides flexibility to connect any data source that has an ODBC drive. Although SQL Server is supported directly by Tableau, in this recipe, we saw how easy it is to create a data connection using an ODBC driver and DSN.
Microsoft's online support provides an excellent overview of ODBC connectivity at http://msdn.microsoft.com/en-us/library/windows/desktop/ms710252%28v=vs.85%29.aspx.