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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
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
Issue   When connecting to an OleDB Datasource using the either the Input Tool or the In-DB Tools, users can encounter the below error message :         Error opening connect string: Microsoft SQL Server Native Client 11.0: Login failed for user 'user1'.\28000 = 18456   Environment   Alteryx Designer Drivers : OleDB Providers for Microsoft SQL Server   Diagnosis   Usually, when testing the Data Connection through the OleDB "Data Link Properties" window they are able to successfully test the connection :     However the error message will persist from the canvas.   Cause   When mentioning a username/password combination in a connection string, they need to be extracted in the program calling for it. A parameter will need to be set and added to the string so windows remembers the credentials and they can be extracted in program.   Solution   Open the Driver's configuration "Data Link Properties" window Go to the "All" tab Identify the Persist Security Info parameter - at this time it should be set to False Double-click and on this parameter and set it to True Apply all the changes Run the Tool on Designer again   Additional Resources   How To: Connect to an OleDB Data Source Database Issues – Working with Alteryx Customer Support Engineers (CSEs)     As always, don't hesitate to contact us over at Customer Support if you run into any trouble.
View full article
What to check when a workflow runs in Designer and not in Gallery    There are some common reasons why the same workflow may run correctly in Designer and have errors when running in Gallery or when saving to the Gallery. The errors often include references to file not found, unable to access or connect, permission denied, or unable to translate alias. This article will review general ways to resolve these errors.   Environment   Products: Designer, Gallery Versions: all versions    Causes for file not found errors   1.The missing file was not included in the workflow dependencies when the workflow was saved to the Gallery.   Solution   When saving a workflow to the Gallery, click on Workflow Options, then Manage Workflow Assets. Make sure the files needed to run the workflow are checked. This will save a copy of each checked file along with the workflow in the Gallery's database on the Alteryx Server.   Prior to saving a workflow to the Gallery, you can check what files are required for the workflow to run by going to Options > Advanced Options > Workflow Dependencies.   When saving, the assets checked in the Manage Workflow Assets screen should include any files and macros used in the workflow and other workflows called by Run Command Events or chained applications. If multiple events or chained applications are used, all workflows that will be called after running the first workflow must be included.                                                       Note: the copies of files that are saved to the Gallery's database are not updated until a new copy of the workflow and its dependencies are saved to the Gallery. If the files are updated regularly, consider having the files on a shared drive accessible by the Alteryx Server instead. When using files on a shared drive in a workflow, leave the files unchecked in the Manage workflow assets screen. This ensures latest version of the file on the shared drive is used when the workflow runs, rather than a potentially obsolete copy saved to the database when the workflow was uploaded to the Gallery.   2. The path to the workflow or files cannot be resolved.   If a file location is shown in Designer without a file path, such as in a tool configuration screen or the Manage workflow assets screen, the location recorded is relative to the workflow.                                Similarly, in the Workflow Dependencies screen a path without the full folder directory such as .\Input.yxdb is a relative location that shows the file is in the same directory as the workflow.                                              If the workflow is saved in the Gallery’s database separate from its files on a network location, or if it moved from its original location, the relative path would no longer be valid.   Solution   Use the Edit button in the Workflow Dependencies screen and set the file locations to a UNC or Absolute path rather than a relative path to the workflow.   Causes for unable to access or connect errors and permission denied errors    1. The Run As user in the System Settings does not have permission to access a file or database.   When a workflow is run from Designer, the credentials used are the currently logged in user’s credentials.  When the workflow is run from Gallery, the credentials used come from the Run As user set in the System Settings on the Worker Run As tab. By default, the Run As user is the Local System account on the Server.  The local system account is not a domain account and may only have permissions to the local drives on the server, not files on shared drives.                               2. The Alteryx Service does not have the permissions needed to run a scheduled workflow job.    The Alteryx Service also runs as the Local System account by default.    Solution   A service account can be used for Run As user or another account with the needed permissions to shared drives and databases used in Gallery workflows. If errors occur only when scheduling, The Alteryx Service can be set to run with a service account having the permissions needed, instead of the default Local System account. In the Windows Services screen, right click on the Alteryx Service, and then go to the Log On tab.   Causes for Unable to translate alias errors   1. A User or System connection was used with the workflow that cannot be shared with the Gallery. In this case, the error would occur when trying to save to Gallery.                                                       User and System connections are stored on the local workstation where they are created. Also, the encryption used for the password in the connection string is only valid for the computer where the connection is created. It cannot be copied and used elsewhere. To check the type of connections saved on your workstation, go to Options > Advanced Options > Manage Data Connections > and look for the Type column on the right.                 There is a separate menu for In-DB connections here: Options > Advanced Options > Manage In-DB Connections. The Connection type field is right after the Data Source                     Solution   A connection can be created on the Gallery, and then shared with the users needing access. DSN-less connection strings are recommended because users are only required to install the proper database driver on their machine for the data connection to work. Afterwards, the user can access the connection from the Saved Connections menu in Designer when building a workflow.   For In-DB connections, a file type connection must be used when saving to Gallery. When saving the workflow to the Gallery, select Workflow Options, and ensure the connection file is included in the workflow dependencies that will be saved to the database used by the Gallery, along with the workflow.   2. The connection used for the workflow when it was saved to the Gallery is obsolete.   Solution   Ensure a valid connection exists to the database on Gallery and check the alias name assigned to that connection. Afterwards, in Designer go to Options > Advanced Options > Manage Data Connections and Select the Sync All button. Check to see if the validated connection is listed and use that connection in your workflow.   3. The Gallery connection was shared with a Studio or an Active Directory group instead of an individual user.   Solution   It is recommended to share Data Connections with individual users and not to share with Studios or Active Directory groups due to known issues where data connections are lost.   Additional Resources   How Workflow Credentials Work  Server Administration Part 1 Server Administration Part 2  Server Administration Part 3 
View full article
How To: Build Queries without using the Visual Query Builder   The Visual Query Builder window can take a long time to load because it always loads all column metadata for all tables for all schemas, even if a default database is selected in the database connection. This article will walk through using the In-Database tools as an alternative to using the Visual Query Builder.    Prerequisites   Alteryx A working Database connection This works best for connections supported for In-DB as noted in the Supported Data Sources and File Formats   Procedure   The Tables view loads only tables names and no column names for the default database selected and it caches the list. This allows it to load faster than the Visual Query Builder.   Make the Tables View the default view Drag an Input tool on the canvas and connect to a database (it doesn't have to be the database in question, any database will work) Click the Tables view and click the checkbox next to "Open Tables view by default" at the bottom left of the window  Create an In-DB Connection to your database as described here: How To: Create an In-Database Connection Use a Connect In-DB tool to connect to the database. To build your query: Select the table name in the Tables view. This creates a SELECT * FROM TABLE statement with no criteria to limit the query Use the In-DB tools to build the query. e.g. the Select In-DB tool to select only columns you need or the Filter In-DB to remove rows not needed Alteryx constructs a SQL query based on the tools on the canvas and sends it to the database In the example below, both workflows accomplish the same thing, one through the Visual Query Builder, one using the In-DB tools:   Additional Resources   FAQ: How Do the In-Database tools Work? How To: Create an Alteryx In-DB Connection File Database Issues – Working with Alteryx Customer Support Engineers (CSEs)
View full article
Amazon Redshift: Check 'stl_load_errors' system table for error details   When writing to Amazon Redshift, the following error appears:     Data Stream In (x): The COPY failed with error: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR: Load into table 'ayx1XXX' failed. Check 'stl_load_errors' system table for details.     Environment   Alteryx Designer In-DB connection to Amazon Redshift   Cause   Amazon Redshift uses the stl_load_errors table to track errors that happen when loading data to a Redshift table. This table contains error messages that will provide necessary detail to determine the cause for an error.    For more information on the stl_load_errors table, see Amazon's STL LOAD_ERROR documentation.      Solution   Users with appropriate permissions can access the table themselves to investigate errors: Open a new Alteryx workflow. Drag an Input Tool on the canvas. Connect to the Redshift database. Go to the SQL Editor Window and enter the following query:    Select * From stl_load_errors   Run the workflow. The table will return an error code and an error reason. Use the column "starttime" to find the right error. See Amazon's STL_LOAD_ERROR documentation for a detailed explanation of all fields on the table and their load error reference for a list of errors.    Solution B    If the user doesn't have access to the table themselves, their DBA should be able to provide the same information.      Error Message examples   String length exceeds DDL length   Input data exceeded the acceptable range for the data type, try increasing the field size in Alteryx to resolve the error.  This often happens with multi-byte characters. In Alteryx, field size relates to characters, i.e.the value ' Góðan dag' only needs a field length of 9. In Redshift, field size is in bytes, to write out 'Góðan dag', the field size has to be at least 11.  See Amazon's document on Redshift character types for more information. Multibyte character not supported for CHAR (Hint: try using VARCHAR)   The CHAR datatype in Redshift only accepts single-byte UTF-8 characters. The VARCHAR datatype accepts multi-byte characters, to a maximum of four bytes. The WString datatype in Alteryx is translated into a CHAR datatype in Redshift. To load multi-byte characters into Redshift, use the V_WString datatype in Alteryx.      Additional Resources   STL_LOAD_ERRORS Documentation from Amazon Database Issues – Working with Alteryx Customer Support Engineers (CSEs)    
View full article
How To: Change Date Type Coming Out of Date Interface Tool to Match In-DB Field Date   A field in my database has dates in the following string format: YYYYMMDD. However, the date format coming out of the Date Interface tool is YYYY-MM-DD. How can I change the date format coming out of the Date interface tool to match the date field in my database? I am pulling the data In-DB, so I don't want to change the date format of my data, which would require me to pull the data out of the database, hence, slowing things downs tremendously.   Prerequisites   Product - Designer   Procedure   In the Action Tool created between the Date interface tool and the Filter In-DB tool, choose the 'Update Value with Formula' action option. Select the 'Expression - value' in the 'Value or Attribute to Update' section. In the Formula section at the bottom of the Action tool configuration window, add the following formula: '"DateField"' + "=" + "'" + REGEX_Replace([#1], '-', '') +"'" This formula will remove the dashes in between the year and month and month and day from the date passed in by the Date interface tool in order to match the format of the field in the database. You can use other formulas, such as DateTimeFormat or DateTimeParse to modify the Date interface format to other formats as well.
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
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
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
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
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
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
Suppose you're using the In-DB tools and at some point you want to sort your data... in database.  You notice there isn't a Sort In-DB tool available.  (NOTE: you're not alone... see http://community.alteryx.com/t5/Alteryx-Product-Ideas/In-Database-Sort-Tool/idi-p/13160).  Do you need to stream your data out, sort your data and then stream your data back in to the database?  You could.  But you don't have to.  You can get the same results using the Sample In-DB tool.  (Help regarding how to configure the Sample In-DB tool is available here: http://help.alteryx.com/current/index.htm#LockInSample.htm?Highlight=in-db%20sort).   In the example below, we're connecting to a Teradata database and reading a retail customer file containing 1500 records but the process is database independent.  The Sample In-DB configuration is the important piece to the solution.  Select 'Percent' in the dropdown and sample 100%.  Check the 'Sample records based on order' checkbox and select the field(s) want to sort and what order you want the data sorted (Ascending/Descending).     If you decide to stream your data out as some point, you have the option of sorting your data in the Data Stream Out tool as well.     If you attach a Browse In-DB tool to your Sample In-DB, set the 'Browse first N records' to 0.  This will display all records in the order you specified.  At the time of this writing, any value other than 0 may not display results in the proper sort order (solution pending).        The attached workflow will need to be configured to your specific database environment and logon credentials to work properly. 
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
If you are developing with Alteryx's In-Database tools you may want to share the workflows with users who do not have as much database experience.  Below is an example workflow to create a macro that will allow your users to select which fields are brought out of the in database tools without interacting with the in database tools directly.    The premise of the process is that you can stream out a single record of your table, then leverage a List Box tool in select mode to de-select the fields your user does not want to pull. From there, you can use Field Summary and Summarize to create a select statement that will be used to update a Dynamic Input In-DB tool.    To run in your environment, update the Connect In-DB tool to a connection that exists on your machine and start with a select all query.
View full article
We are proud to introduce you to the Manage Data Connection functionality in 11.0!
View full article
Question Have you ever wondered what query was being sent to your database at a certain point in your workflow? Answer You can view this using a Dynamic Output In-DB tool! All you have to do is place a Dynamic Output tool after any tool in your In-DB process in order to see the query that is being sent to the database. Once the tool is placed in the desired spot in your workflow, make sure that you have the "Query" option selected in the Dynamic Output tool's configuration:     You can now run the workflow and since the output tab of the Dynamic Output tool is a normal connection tab, you will be able to view the query using the browse everywhere feature and the results window. If you are interested in viewing the entire query and/or copying and pasting it you will have to add a browse tool after wards so you can access the query string in the Cell Viewer:     A "fun fact" with this process is that you can take the query that is produced by the Dynamic Output tool and paste it into the SQL Editor of a normal Input tool to produce the same results:        
View full article
Alteryx has the ability to create and query Pre and Post SQL statements. Using this feature is good if you need to create a temporary table, delete or update the table before running the select. With this, you also have the ability to run stored procedures (via exec stored_procedure;) before and after the actual query as well.
View full article
To create a pseudo random sample of data using the In-Database (InDB) tools, this could be a potential solution.   (This example uses Microsoft SQL 2008)   A set of 10 records are used in the workflow and we want find a ‘random’ 50% of the records.     If we were to use the InDB Sample tool and choose to sample 50% of the records we’ll just get the first five records, not very random.   Instead, using the InDB Formula tool we can create a new field, in this case it’s being called RandomID, and use the MS SQL expression NEWID() which will generate a GUID (this expression will probably vary depending on which database is being used).     Resulting in:     Now we can feed our data to the InDB Sample tool.  Configure the InDB Sample tool to sort by the RandomID field and select the number or percentage of rows to return.     Running the workflow our results look like this, notice the BankerIDs that are returned as compared to the original data     The workflow would look like this:     (A workflow is not included with this article as it requires a specific database connection.)
View full article