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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
No Lua script was found   When reading in from a database, the following message is seen:   Info: Input Data (n): No Lua script was found for corresponding ODBC driver.   Environment   Alteryx Designer 2018.4+ Windows Operating System ODBC database connection     Cause   This message indicates that you are connecting to a database, and no Lua script was found. This is expected when you are connecting to a database that does not work out of the box with Alteryx. Please note this is a message and not an error or warning; it will in no way prevent your workflows from executing successfully if you are not using a custom Lua script.     Explanation To support new ODBC drivers that Alteryx has not validated/tested, you can create a custom Lua script that will map driver and database data types to Alteryx types.  To learn more about this (and find sample Lua scripts) please see the documentation Customizable ODBC Driver Connections found in: %LOCALAPPDATA%/Alteryx/bin/RuntimeData/ODBC
View full article
"Unable to find connection 'x' " when workflow is scheduled   When running a workflow via the Scheduler (for Designer with Automation), the following error is observed:   Unable to find connection "x"   Environment   Alteryx Designer Designer with Automation Windows Operating System In-Database Connection   Diagnosis   1. Confirm that you are using Designer with Automation and not Alteryx Server. You can confirm this by opening the Alteryx System Settings from Designer under Options > Advanced Options > System Settings. On the Setup Type page you will see Designer and Scheduler Only selected.   2. Confirm the In-Database connection is setup as a User connection. You can check this from Designer by navigating to Options > Advanced Options > Manage In-DB Connections and choosing the Data Source and Connection name you are using. It will show next to the name of the Connection:     Alternatively, you can check in the drop-down for the Connection Name Connect In-DB tool or the Data Stream In tool:       Cause   The In-Database connection in use is a User connection; scheduling requires a System connection or a File connection.     Solution A - System Connection   Follow the steps in this article to set up a System connection: How To: Create an In-Database Connection This will require admin permissions. Please see the following article for more information: Unable to create System Connection/System Connection type missing   Solution B-  File Connection   Follow the steps in this article to set up a File connection: How To: Create an Alteryx In-DB Connection File The .indbc file will need to be available to the Run As User or the user running the Alteryx Service. To avoid any issues, you can package the workflow, then schedule as follows: Package the workflow by going to Options > Export Workflow, making sure you check the box to include the .indbc file Go to Options > View Schedules On the Workflows tab, click the + icon Navigate to the .yxzp file you created above and set the schedule frequency     Additional Resources   Database Connections: Creating an alias and the advantages of using an alias Database Issues – Working with Alteryx Customer Support Engineers (CSEs)
View full article
Handling Null Values in Databases In relational databases a null value is used to represent a missing or unknown value in the column. The ANSI SQL-92 specification defines the handling of the null value in databases.      SQL Ternary Logic SQL relies on a three valued logic. Additionally to true and false logical expressions can also yield unknown.  This is a consequence of SQL's support of NULL to mark either absent or unknown data. Thus, if NULL is part of a logical expression the outcome will be NULL. In more colloquial terms NULL means "it could have any value".   Therefore, in all the below cases the result will be unknown:   NULL = NULL NULL = 1 NULL <> 1 NULL > 1   Please note that the SQL standard does not specify how NULL values should be ordered in comparison with non-NULL values.   Testing for NULL From the above it emerges that a specific SQL command is needed to check whether a value is indeed NULL. SQL has the <expression> is null and <expression> is not null expressions to ascertain an expression is NULL or not.   The SQL standard also defines an expression to compare two NULL values treating them the same. However, not all SQL flavors support it and may instead have their own proprietary alternative. The SQL standard specifies <expression> is not distinct from <expression>.   Where and Having Clauses A Where clause needs to be true for the SQL query to execute.  Therefore if the condition in the Where clause will evaluate to unknown it will reject all rows. For instance   SELECT c_columns FROM t_table WHERE c_columns = NULL   will reject all columns. In order to use NULL in Where clauses WHERE c_columns is NULL should be used instead.   It is important to note that since NULL is neither true nor false, but rather unknown in SQL logic the below query will never return NULL values.   SELECT c_columns FROM t_table WHERE c_columns = 'ANY VALUE'   Additional Resource   Handling Null Values - Microsoft help documentation 
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
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
How To: Create an In-Database (In-DB) Connection   Apart from making standard database connections, which allow for data to be pulled from the database directly into Alteryx, Designer also has In-Database functionality. The In-Database tools allow for the workflow created to be converted into a query which will then be executed on the database, and not locally in Designer.     Prerequisites   Alteryx Designer Windows Operating System   Procedure   In Designer, drag and drop the Connect In-DB tool (located in the In-Database tool palette) onto the canvas Select the “Manage Connections…” option to establish a new In-DB connection The Manage In-DB Connections window should appear. To configure this window: Select the Data Source/Database type from the Data Source  drop-down. Under Connection Type, select User, System or File connection. For file connections, see this article: How To: Create an Alteryx In-DB Connection File If you do not see the System option available, see this article: Unable to create System Connection/System Connection type missing Under Connections, select New. Under Connection Name, enter a name for this connection. Under Driver, select the method of connection to use. Click the Connection String drop-down. You can choose to use an existing connection or create a new connection by clicking the New database Connection… option. You can also paste in the full connection string if you have it available. Repeat steps 5 and 6 for the Write tab. Click OK. If the connection was successful, the Choose Table or Specify Query window should appear.   The connection will now be stored in the Connection Name drop-down.     Additional Resources   Troubleshooting Database Connections FAQ: How Do the In-Database tools Work? Database Issues – Working with Alteryx Customer Support Engineers (CSEs)  
View full article
Issue   I am trying to set up a System alias, but the System option is missing.   Standard Connection - System connection is missing from Options > Advanced Options > Manage Data Connections > Add Connection    In-DB Connection - System connection is not an option:     Environment   Alteryx Designer Windows Operating System   Cause   The Designer is not running Elevated (aka running as an Administrator). Alteryx must be run with elevated privileges in order to be able to create a System Connection within Alteryx.      Solution   Right click on the Alteryx program (or on the Alteryx icon on your Desktop) and choose Run as administrator Please contact your IT department if you are unable to run Alteryx as an administrator     Additional Resources   Creating an In-Database Connection Database Connections: Creating an alias and the advantages of using an alias Database Issues – Working with Alteryx Customer Support Engineers (CSEs)
View full article
This article details different methods for connecting to an Oracle database from Alteryx. 
View full article
How To: Create an Alteryx In-DB Connection File   As users collaborate, the sharing of workflows that connect to databases can be helpful. The In-DB Connection File type allows for this. A database connection is saved as an .indbc file so it can be packaged with a workflow.   Prerequisites   Alteryx Designer   Procedure   1. In Designer, navigate to Options > Advanced Options > Alias Manager > In-DB Connections     2. Select the Data Source from the dropdown (1). For our example we will select Oracle, then pick File for the connection type (2), Click the elipses (...) button to navigate to a folder where the .indbc file will be stored. Please note that the first time you set up a connection file, you will need to name the file and click Open to create the file (4) then provide a name for this file (3).       Then proceed to set up your connection as you would normally do by selecting the appropriate driver and setting up the connection string for Read & Write.       3. Now that your connection file is created, the next step is to connect to your database using this file. Bring a Connect In-DB Tool onto your canvas, click on the dropdown for the Connection name and select Open File Connection and browse to your file     4. You can then select a Table, use the Visual Query builder, or type in SQL directly in the Choose Table or Specific Query window       5. (Optional) If you would like to share this workflow, package your workflow in Designer by going to Options > Export workflow. Notice that the .indbc file is being packaged with the workflow.       At the receiving end, the database drivers/client, data sources for that database should be installed and set up. The .indbc file looks like below, with the password encrypted.   Note: Workflow and all images created in this article are from Alteryx version 2019.2
View full article
How To: Connect to an OleDB Data Source   Alteryx can connect to databases using different methods. T his guide will 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.     Prerequisites   Product - Alteryx Designer or Server. OleDB driver for the database (if it 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).     Procedure   Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas     Before 2019.2 Click the drop-down to "Connect to a File or Database." Then select "Database Connection" > "New OleDB Connection…" The "Data Link Properties" window should appear.     2019.2 and later Click OleDB under Microsoft SQL Server. The "Data Link Properties" window should appear.   All versions   Select the driver for the database you wish to connect to and click “Next.” In this guide we will be selecting the “SQL Server Native Client 11.0” driver. This is the recommended driver for Microsoft SQL Server.     On the next screen, enter the server name by either selecting it from the drop-down, 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 drop-down. Click the “Test Connection” button to test that you are set up correctly.   Upon successful test you will get the below 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” drop-down, both under the initial drop-down and also under “Database Connection”     Additional Resources   Help Doc page on supported data sources and file formats Help Doc page on ODBC and OleDB connections
View full article
How To: Gather ODBC Logs   Gathering logs from the driver can be useful to troubleshoot errors that appear in Alteryx when attempting to connect to a database. There are two types of logs that can be gathered:     1. Data Source Name (DSN) trace logs (set up via an individual DSN) - This log uses the driver settings of the individual DSN, meaning the logs are more specific to that datasource . - This is the preferred log to gather     2. Generic ODBC tracing - This produces a standard trace file using Microsofts ODBC trace library. This will gather logs for all connections made through any driver/DSN, and is therefore less verbose and more generic. - Only gather this if your driver does not offer DSN trace logs (Procedure A).     Prerequisites   Alteryx Designer Alteryx Server Any supported database driver     Procedure A - DSN trace log   Open the ODBC Data Source Administrator window Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utilizing for the connection On the User DSN or System DSN tab, select the DSN you are using to connect in Alteryx Click the Configure button: Each driver has different configuration options for enabling logging. Generally, you will find the logging options under the Advanced tab or settings, or under Logging Options. You will also be required to set:  The level of logging - always select LOG_TRACE or the highest level, if on a scale LOG_TRACE is the preferred level                                                                                                                           Highest level on a scale of 0-6 The path to the log file Here are some examples of how to set the logging for different drivers: Amazon Redshift Simba Hive ODBC Snowflake From here, return to Alteryx Designer or Server and replicate the error. Gather the log from the location you specified. 8. Return to Alteryx and replicate the error. 9. Gather the log from the location you specified       Procedure B - Generic ODBC Trace   Open the ODBC Data Source Administrator window Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utliziing for the connection Select the Tracing tab Under the Log File Path select the Browse button and set the file to an easy-to-reach location (i.e. your Desktop) Click Start Tracing Now Return to Alteryx and replicate the error Gather the log from the location you specified   Common Issues   I am unable to find the logging options for the driver I am using. Generally, the drivers have great help documentation for how to enable logging. A Google search of "my driver name enable logging DSN" should give you detailed steps specific to that driver. Some drivers do not have logging options available,  I configured the driver logging, but no log file is created. 1. If you have run your workflow but no log appears after the run completes, this generally means you do not have the appropriate permissions level to log. Please contact your DBA to confirm and investigate. 2. If you are using Snowflake, you cannot specify a log location. The default location is C:\Users\your_username 3. If you are using PostgreSQL:      1. In the Input Tool, add the following to the end of your connection string ;commlog=1;debug=1      2. By default, it writes the log to your user directory: C:\Users\your_username    Additional Resources   Troubleshooting Database Connections
View full article
How to test if a maximum limit of connection data for an OCI driver was exceeded   An OCI error may occur when running a workflow from a network location with many OCI inputs: OCIEnvCreate Error: No error handle available to report exact OCI_ERROR.   Prerequisites   A Designer workflow with Input Data tools having OCI connections to an Oracle database.   Product - Alteryx Designer downloads.alteryx.com Product - Oracle Instant Client for validated versions see: Supported Data Sources Oracle download site: Oracle Instant Client downloads   Procedure   Decrease the total connection data that OCI driver in the Oracle Client has to process for the workflow. If the workflow is accessed from a network drive, try putting the workflow in a folder close to the root of the driver, rather than in a long folder directory, or move the workflow to your workstation instead of a network directory. If there are many OCI connections in the workflow, see if the same data could be imported with fewer connections.   The OCI driver is recording the network location of the workflow. Also, if the workflow contains multiple OCI inputs, the Oracle Client OCI driver appears to compile all of them together to create the workflow connections.    Common Issues   A maximum limit of connection data for an OCI driver can be reached In testing, we found that a maximum limit of connection data could be reached, and then the error would occur. The workflow would run fine with multiple OCI connections if the workflow was located on the workstation. However, when running the same workflow from a network drive, the length of the folder path would determine whether or not it would error out. Similarly, adding or removing more OCI inputs would have the same effect. A limit would be reached, and an error would occur beyond that limit.    Additional Resources   Oracle Call Interface Programmer's Guide OCIEnvCreate()  
View full article
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
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
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