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.
Alteryx provides GUI tools that offer similar functionality to many SQL commands. Although minimal SQL scripting may be necessary in order to properly configure tools, the amount required to complete analysis is significantly reduced.
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.
I am trying to set up a System alias, but the System option is grayed out or missing. How can I create a System alias?
Standard Connection - System connection is grayed out:
In-DB Connection - System connection is not an option:
Alteryx must be run with elevated privileges in order to be able to create a System alias connection within Alteryx. If you have certain permissions, you can run Alteryx as an administrator and then try and create it. Simply right-click on the Alteryx program (or on the Alteryx icon on your desktop) and choose 'Run as administrator' (see below). Otherwise, you will have to get administrative rights in order to add a System alias.
More information from the Help section:
Thanks for reading!
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.
To create an In-DB Connection File that can be used on your workflow, go to Options > Advanced Options > Alias Manager > In-DB Connections.
Select the Data Source from the dropdown (1), for our example we will select Oracle, then pick File for the connection type (2), this option will ask you to navigate to a folder where the INDBC file will be stored and also will ask you to 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.
Now that your connection file is created, the next step is to connect to your database using this file. Bring a Connect In-DB into your canvas, click on the dropdown for the Connection name and select Open File Connections, browse to your file and select it.
The next thing you will see the list of tables.
Select the table and/or write your query in the SQL Editor and proceed to build your workflow.
Now that you are ready to share, package your workflow 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, the password is encrypted.
Note: Workflow and all images created in this article are from Alteryx version 10.1
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).
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.)
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.
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.
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.
With Alteryx, the power to blend, clean and perform advanced analytics on disparate data is as easy as dragging and dropping tools with the click of a mouse. The new release of Alteryx Designer 10.0 expanded our predictive analytics tools to include MB Affinity, Network Analysis, In-DB Linear and In-DB Logistic regression tools.
Alteryx also has the flexibility to add additional R packages not integrated with our robust collection of predictive tools. We have had a question arise from one of our clients asking where they can find exponential (non-linear) regression. The recommended the R package for this type of analysis is the “nlstools”.
For more information on this R package please find the below link for reference:
Also, please find the link for the Alteryx Gallery app below for reference on how to install additional R packages. If you need help with the process, please let us know, and we can get our R experts in Customer Support to assist with the setup. Just email us at email@example.com.
Client Services Representative
Apart from making standard database connections, which allow for data to be pulled from the database directly into alteryx, the designer also has In-Database functionality. The In-database tools allow for the workflow created to be converted into a specific technology’s SQL language and the workflow to be executed within the database and not locally on alteryx. This will allow for subsetting of datasets by joining or filtering before the data is pulled over a network into the alteryx designer, resulting in faster workflow run times amongst other benefits
Open alteryx designer and drag and drop the “Connect In-DB” tool (Located in the “In-Database” category) 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” dropdown
Under “Connection Type,” select whether you will be setting this connection up on the user or system level. NOTE: Administrator access will probably be required for “System” level. Setting up as the default “User” connection is generally sufficient
Under “Connections,” select “New” to create a new connection, or select a previously created connection from the dropdown
Under “Connection Name,” enter a name you would like for this connection you are creating
Under “Driver,” select the method of connection you would like to set-up, ie, “ODBC”
Click the “Connection String” dropdown arrow. You can choose to use an existing connection or create a new connection by clicking the “New database connection…” option
Follow this step if you chose to create a new connection in the previous step. Please proceed to the following step if you chose an existing connection:
If setting up an ODBC connection, please see the “Connecting to an ODBC datasource” guide on how to configure this part of the ODBC connection starting at the “***Step” step.
If setting up an OleDB connection, please see the “Connecting to an OleDB datasource” guide on how to configure this part of the OleDB connection starting at the “***Step” step.
After either successfully choosing to use an existing connection or establishing a new connection, please click on the “Write” tab and select the same connection
Click “OK.” If you were able to successfully connect, the “Choose Table or Specify Query” window should appear.
Select the table you wish to connect to from the “Choose Table or Specify Query” Window
Upon successful connection, the connection will now be stored in the “Connect In-DB” tool’s “Connection Name” dropdown
You have successfully set-up and saved an In-Database connection!!!
-Ali Sayeed, Solutions Engineer
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 (Asending/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).
Attached workflow was created using Alteryx Designer v10.1. This workflow will need to be configured to your specific database environment and logon creditials to work properly.
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:
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.
Client Services Support Engineer