community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
Issue    During run time of workflow, the following error is received:    Error: Input Data (1): Error SQLExecute: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 99, HTTP Response Code: 1759505392, Error Message: Unable to connect to endpoint [Execution ID: e7fe279d-f39b-4872-b37d-8ad49d49f3f5]   Environment   Alteryx Designer Amazon Athena Environment Windows Operating System Amazon Athena ODBC Driver (found here)   Cause   In version 1.0.3 and greater of the ODBC Driver, Result Set Streaming is enabled by default which has extra requirements. More info on the requirements here.   Solution - Connection String (DSN-less) If connecting via a Connection String: Add UseResultsetStreaming=0 to your connection string Example string:  odbc:Driver={Simba Athena ODBC Driver};AwsRegion=[Region];S3OutputLocation=[S3Path];AuthenticationType=IAM Profile;AWSProfile=[YourProfileName];UseResultsetStreaming=0 Use this string with your standard tools (Input Data, Output Data) or with a Connect In-DB Tool. Solution B - DSN Connection  If you are connecting to your Amazon Athena environment with a DSN connection (via ODBC Admin): Open up ODBC Data Source Administrator  Find your Athena connection and select it Hit "Configure..." Hit "Advanced Options..." Uncheck the "Use Resultset Streaming" box Hit OK to save the Advanced Options Hit OK again to save your connection  
View full article
The guide covers SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CROSS JOIN, WHERE BETWEEN, WHEREIN, DISTINCT and we hope you find it helpful.
View full article
We are working diligently and intelligently on making connections to databases easier, more secure, and faster in Alteryx. Even when we can make the best possible experience for our users come to fruition, any user may still encounter some issues due to the use of third party drivers and/or the intricate communications between the database, driver, and Alteryx. To help to minimize those issues, Alteryx is tested with an abundance of "supported" databases and drivers that are proven to be compatible but, unfortunately, you just can't test every database and every driver out there. Our technical specifications page found here will show you those databases Alteryx supports, as well as the drivers we have tested and support with respect to each database. This does not mean Alteryx will not connect to a database or driver you do not see on this page (or links within the page), however, Alteryx can not support those databases and/or drivers if troubleshooting or setup is required.
View full article
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 guide will focus on connecting to a database via OleDB. This guide will particularly focus on setting up an OleDB connection to SQL server, however, the same general process can be followed to connect to any database via OleDB. ***If the OleDB driver for your database is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found here. 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 OleDB Connection…" The "Data Link Properties" window should appear     Select the driver for the database you wish to connect to and click “Next.” In this guide we will be selecting the “Microsoft OLE DB for SQL Server” driver     On the next screen, enter the server name by either selecting it from the dropdown, or if it does not appear, by typing it in manually Select the authentication method of the database. If your database uses “Windows Authentication” additional login information is not required. If your database uses user ID/PW authentication please enter the username and password you will be connecting with. If you are unsure of your login information please contact your IT or database admin Select the database on the server you wish to connect to by selecting it in the dropdown Click the “Test Connection” button to test that you are setup correctly   ***Step     Upon successful test you will get the following popup window: Click the “OK” button On the “Data Link Properties” window click “OK”     The “Choose Table…” Window should appear In the window select the table you wish to connect to:     After successfully connecting to a table, the connection will now be saved in the “Connect a File or Database” dropdown, both under the initial dropdown and also under “Database Connection”     Congratulations! You have successfully created and saved and OleDB connection!
View full article
Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.
View full article
How to find the relevant information and configure a Databricks connection through the Manage In-DB Connections window. The Databricks connection includes bulk loading capabilities that allow users to load large data sets faster. 
View full article
Workflow processing can take longer when a large amount of data is streamed in and out of a database. In-database processing can be used to speed up a workflow.
View full article
Alteryx has the ability to connect with MIT Kerberos based Impala implementations. With this feature, came the need to tell the Simba Impala driver that you are using Windows Kerberos and not MIT's version.
View full article
Unable to find the dll: "OCI.dll"   Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version.  This error may also appear if you do not have the drivers installed.     One way to troubleshoot this is to find out what drivers you have installed!   You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not have the correct drivers for 32 bit or 64 bit connection dependent upon which one you chose.         Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.
View full article
Issue    The Alteryx server is unable to run a workflow that runs properly on the user’s local machine where the workflow was created.    [ODBC Driver Manager] Data source name not found and no default driver specified alteryx This error can be encountered when running an app or workflow on a Gallery:   Or when uploading a workflow during validation:   Users will also receive the following error when opening a shared workflow where the Data Source Name does not exist on the user’s local machine:       Environment   Alteryx Designer Alteryx Server  Version ≥  11.0 A workflow that contains a Data Source Name (DSN) on the Input Data tool.   Diagnosis   Confirm that the data source name (DSN) specified in the workflow is pointing to an existing DSN in your machine’s ODBC Data Source Administrator:   ***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 here. On your local machine, search for ODBC Data Sources (64 bit) The DSN referenced in the input data tool within the workflow must be listed under either User DSN or System DSN tabs.   Cause   The workflow is not able to successfully run because the input data tool cannot reach the database in the following scenarios: The workflow creator sends a workflow with a database connection to a colleague who does not have the same DSN on their computer. The workflow gets sent to the alteryx server to run on a scheduled frequency where the DSN does not exist on the server machine.   Solution   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 here. Once confirmed that the appropriate driver is installed, select to add from either the user DSN or System DSN tab. Generally, the User DSN is what most users will have access to and what is commonly used. You can learn more about the difference between User and System DSNs here. In order to resolve the error at the local level or on the Alteryx Server, you will need to set up the corresponding DSN found in the app/workflow. The following Community article will provide you step by step instructions on how to set up an ODBC connection . Once you've established the DSN on your machine, you will now be able to successfully re-run the workflow! 
View full article
This article details different methods for connecting to an Oracle database from Alteryx. 
View full article
In this article, I am going to show you how to load data into snowflake using Alteryx Database Connections!
View full article
Database aliases can help users save time and allow easier sharing of data. Let's take a look at how.   The first step to creating a database alias in Designer is to establish the database connection. This can be done with the Input Data tool by selecting any of the database options in the dropdown.   Databases we can connect to with Alteryx, an overview. http://www.alteryx.com/technical-specifications   Once you’ve established the connection through the connection admin, a database connection string will appear, like the one shown below.     Next, let’s go to the Manage Database Connections window (or Manage In-DB Connections window for In-DB aliases). This will be under Options > Advanced Options > Manage Data Connections. Here we can add new aliases, and edit or delete, any existing ones.   Manage Database Connections overview: There are two types of aliases: User: Any user can add a User Alias that only they will be able to access and change. System: System Aliases can only be added/edited by a local administrator.   Available options: Sync All allows you to sync Gallery Data Connections Add Connections allows you to create a new alias (see steps below)   Additional options can be found when you hover over an existing data connection: Allows you to edit the password of the Alias.    Allows you to delete an alias         When adding a new alias, Alteryx will assist you in making the connection to both MS Sql Server, and Oracle. For all other database connections, select the 'Other' option.   There will be three things to include for 'Other' database connection types: the connection type (user vs system), an alias name and the connection string. The reason we made the connection first through the Input data tool is so that we can simply copy/paste that string into our alias manager.   When the alias has been created you can access this connection through the Input Data tool dropdown under Saved Data Connections. You’ll notice that the string now says aka:AliasName.     Advantages: Q: What happens when you have multiple workflows that all reference a connection string and your password changes?   A: If using workflows that reference an alias, the user will only have to update the password in the alias manager. If using a regular connection string, every Input Data tool will have to be updated in every workflow that uses that connection.   Q: Can I share, or export, a workflow that has database connection strings?   A: Yes! If you setup an alias on both user’s machines that are an exact match you will be able to share workflows that use those database connections.   Note:   The screenshots and steps taken to create the database connection and alias were built in Designer version 11.8.   Tony Moses Customer Support  
View full article
Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:   https://www.youtube.com/watch?v=fJjAiAHtCuc   Find more information on our visual query builder below: http://help.alteryx.com/current/index.htm#ChooseTableorSpecifyQuery.htm#Visual    
View full article
User versus System DSN   When setting up your database connection you may be unsure whether selecting a user or system DSN is the best option.     The main question you need to ask yourself is whether anyone else within your organization will need to see the same database connection? If the answer is no then a User DSN would be the correct choice, however if you are looking to share a workflow then the system DSN would be a better choice.   A User DSN is for a specific user on a workstation. For example, if I log into my machine as ‘Jordan’, set up the DSN as myself and then log back in I will be able to see that database connection. However, if my colleague ‘Tony’ logs onto my machine he will not be able to see that database connection. For Tony to see this connection I would need to set up a system DSN, so no matter who logs into my machine they will be able to see and access that database connection.   A system DSN can often be advantageous when sharing workflows within a department because if someone makes that database connection it can be shared amongst colleagues. If this creates concern for your IT department it is really easy to set up an Alias for this connection and as a result the username and password for that connection can be hidden.    The user and system DSN can be configured by using the two tabs marked below in the ODBC admin.       Best,   Jordan Barker Client Services Support Engineer
View full article
Sometimes you only need to read in specific fields from a database or input. Within Alteryx it is as easy as using a Formula tool, Dynamic Rename and Dynamic input!!   The first step is to bring in the master file and the list of field names you wish to carry through from the Master file.   Next attach a formula tool to the field names list and create a new field with the expression "Keep-" + [FieldNames].     This will rename the fields with “Keep-” appended to the field name.   The next step is to use a dynamic rename tool to “Take Field Names from Right Input Rows”. In the 'New Field Name from Column" select the field which has just been created above.   Use a dynamic select tool to ‘Select via a formula’ and use the expression below to find the field which has ‘Keep-“ appended to the left hand side.  This will then only bring through the specific fields from the Master file which you have listed in the field name input.    Using a dynamic rename you can return the fields to their original names through a substring function. This will remove the "Keep-" from the field name.       You now just have the fields you want from the Master input file!      Jordan Barker Client Service Representative
View full article
Hive ODBC can be slow when writing to tables. If you are looking for a faster option to write to Hive and want to create a new table or overwrite an existing table, use the  IN-DB tools   to output your data. 
View full article
In order to open Access databases with the .accdb extension, you will need to download the Microsoft 2010 Office System Driver: Data Connectivity Components.
View full article
For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Tool  allows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile:
View full article
How do I create an index on a field in my database table? An index for a field can be created using the Post Create SQL Statement option (option #10) found in the Configuration window of the Input tool.
View full article