This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 04-14-201603:04 PM - edited on 03-11-201909:06 AM by SydneyF
Alteryx allows you to connect to many different types of data sources. One type of data source you can connect to is a database. Examples of databases are SQL Server, Oracle, Teradata, and MongoDB; amongst many others. There are several connection methods to connect to database sources including ODBC, OleDB, or natively.
This article will particularly focus on setting up an ODBC connection to SQL server, however, the same general process can be followed to connect to any database via ODBC. ***You must have the driver installed on the machine prior to using this guide. The download links to many of the drivers can be found at http://www.alteryx.com/technical-specifications. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information.
Open alteryx designer and drag and drop the “Input Data” tool onto the canvas
Click the dropdown to “Connect to a File or Database.” Then select “Database Connection”->”New ODBC Connection…” The ODBC connection window should appear
Click the “ODBC Admin” button to go to the Windows ODBC Administrator to set-up the connection
In the ODBC Data Source Administrator, click the “Add…” button to set-up the new connection (The connection can be set-up under the “User DSN” or “System DSN”. Generally, the User DSN is what most users will have access to and what is commonly used. The system DSN may be used if you have admin access and wish to set-up the connection for all users of the machine.)
You will be directed to a window to choose the driver to use for the connection. ***You must have the driver installed on the machine prior to this step. The download links to many of the drivers can be found at http://www.alteryx.com/technical-specifications. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information.
Select the driver and click “Finish”
Select a name for this connection
Click the dropdown to find the SQL server you wish to connect to or type in the server name
On the next screen, choose which type of authentication you will be using:
You can choose between ‘Integrated Windows authentication” or login ID
Choosing windows authentication requires no additional information as the user ID you are logged in to windows as will be recognized
Using login ID authentication will require you to enter the user ID and password. NOTE: the username/pw will likely be different than you windows login information. If you are unsure what your user ID/pw is, contact your IT or database admin
Once you’ve chosen your login method, click the “Next”
Click “Next” through the menu screens (Change any optional settings via the menus if you wish to do so, generally the default configuration should provide a successful connection
After clicking the “Finish” button, click the "Test Data Source..." button
If the “TESTS COMPLETED SUCCESSFULLY!” dialogue appears, you have successfully set up your connection
Click “OK” twice to exit the connection wizard
Click “OK” to exit the ODBC administrator
In the alteryx ODBC connection window, select the Data Source Name you’ve just created. Enter a username and password if required (optional) and push “OK”
The Choose Table or Specify Query window should pop up. If there are tables already in the database, choose a table and push “OK”.
Upon successful connection, the ODBC connection will now be stored in the “Input Data” tool’s initial dropdown
You have successfully set-up and saved an ODBC connection!!!