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
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
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
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
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
Pull a date format switcheroo to filter records before leaving your database - speeding up your queries!
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