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.
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.
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).
Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas
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.
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”
Help Doc page on supported data sources and file formats
Help Doc page on ODBC and OleDB connections
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).
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
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
Troubleshooting Database Connections
Spatial Matching Best Practices
Even though the Spatial Match tool is extremely fast and efficient, there are additional ways its speed can be further improved. This article provides suggestions to increase performance.
As defined in the Help documentation, the Spatial Match tool establishes the spatial relationship (contains, intersects, touches, etc.) between two sets of spatial objects. At least one input stream should include the Polygon type spatial objects. The other set will contain any of the other types of spatial objects, such as points or lines. But wait - which set of objects shall be used for the Universe (U) and which set for the Target (T)?
Under the hood the Spatial Match tool will put the U input into a temporary YXDB file with a spatial index . This is a highly efficient data format for spatial data. Thus, instead of indexing the geometric features of the object (first image), the objects' bounding boxes are indexed (second and third image).
This effectively means that when calculating a spatial match, only a few spatial objects inside the relevant boundary box must be considered for the spatial calculation. Next, every object from the T input is spatially matched with the relevant objects of the U input.
In one line: The Spatial Match tool can ignore most Universe records that do not match the Target record. Using this fact to your advantage can greatly speed up your workflow.
Deselect spatial objects not needed
As with many other tools, the Spatial Match tool has a built-in Select tool enabling one to deselect columns that are not needed. While discarding unnecessary columns comes in handy to make data sets more readable, it can be a real performance improvement. Therefore, unnecessary spatial objects should be removed from the workflow. Unnecessary data consumes memory and takes away otherwise available resources.
In the below example, toggling back the spatial object will increase the tool output from 7 kB to 757 kB.
Spatial tool output with unnecessary spatial object
Spatial tool output without unnecessary spatial object
Consider Using the Dynamic Input Tool
In certain circumstances, using the Dynamic Input tool is quicker to perform a Spatial Match than using the native Spatial Match tool. Note: This can be only used for the spatial relationship 'Universe contains Target'.
To perform a spatial match using the Dynamic Input tool, select the spatial data file, then choose the second option: 'Modify SQL Query'. Select the latitude and longitude fields for the Universe object, and the spatial object field for the Target object. This SQL filter will only let through data that fall within the bounding rectangle of the polygon.
Harness the Power of Calgary Data and YXDB
The YXDB (.yxdb) and Calgary DB (.cydb) data formats use spatial indexing. As explained above, this can give the workflow a major efficiency boost. Therefore, when possible it is strongly advised to import data from the above two DB types.
The second advantage is that they both enable you to leverage the spatial index. As defined in the Help documentation, for Calgary use the spatial Calgary Join tool. If specifying a Calgary file, be aware that the Calgary spatial index uses 5 decimal places of precision for compression and speed. The yxdb spatial index uses 6 decimal places. This adds an additional round-off error of up to a maximum of 1.8 feet to Calgary indexes. In other words, it is possible that a point can be 1.8 feet inside of a polygon and yet still be found as "outside."
In summary, using the YXDB and Calgary DB data formats has the advantage of the highly efficient spatial indexing.
Use Integrated Tool Input in Spatial Tools
For larger data sets, the option to Use Records from File or Database can be used for added speed. This also uses the spatial index and has the advantage that the entire dataset will not have to be read into memory for the workflow to start, as I/O is usually the biggest performance bottleneck for Alteryx.
Spatial Match tool (Help documentation)
Alteryx Designer comes with tools (based on both R and Python) to create and use predictive models without needing to write any code. But what if you've got custom models written in R or Python outside of Designer that you want to use in Designer, or vice versa?
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.
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
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.
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.
Oracle Call Interface Programmer's Guide OCIEnvCreate()
How to correct Designer Event status if it does not update
This article explains how to correct Designer event status if it does not update. In the Designer Workflow-Configuration Window, on the Events tab, there is a checkbox option: Enable Events. When selected, any items in the Events window should be enabled and run as configured. However, in Designer 2019.1.4, the status of events is not updated by the checkbox. Events may remain either enabled or disabled regardless of the checkbox setting.
Product - Alteryx Designer, known to occur in Designer version 2019.1.4 only
This issue is fixed in version 2019.1.6. As a workaround, right click the workflow .yxmd file and select the option to open in Notepad or Notepad ++.
This will open the XML view of the workflow. Find the section for Events by clicking on Ctrl + F and typing Events in the Find window. Set the Enable value to "True" or "False" to enable or disable the event.
Issue: Unable to connect to the Controller "X"
When trying to connect to your Designer's Scheduler, the following error is observed:
Unable to connect to the Controller "X". Please consult the Alteryx Scheduler FAQ in Alteryx Help for troubleshooting assistance.
You may also observe that the "My Computer" option is missing in the Schedule Workflow window:
The Alteryx Service is not running.
Start the Alteryx Service. See "Start the Alteryx Service" article.
Configure Desktop with Scheduler
How do I schedule a Workflow using Alteryx?
How To: Start the Alteryx Service
The Alteryx Service powers the scheduling functionality of Alteryx. In order for schedules to be created or executed, the Alteryx Service must be running. Follow the guide below for different ways to start the Alteryx Service
Scheduler add-on required
Procedure A - Start from the Services Window
Open the Services snap-in window using one of the below methods:
Search Services in the Start Menu
Press the Windows+R keys to open the Run dialog, type services.msc, and press Enter
Open the Control Panel click/tap on the Administrative Tools icon, and double click/tap on Services shortcut
Right click on Alteryx Service and click Start
Procedure B - Start via System Settings
Open the Alteryx System Settings (can be found in the Start Menu by search "Alteryx System Settings"
Click the Next button until you reach the end of the System Settings, then click Finish
You will then see a "Please Wait" message that indicated the Service is attempting to start
Please note that if the service fails to start for any reason, you will not receive any message to indicate the failure when starting the service this way
Procedure C - Start via Command Line
Open Command Prompt as an Administrator
Type the following command that references your installation path. The below example is the default installation path: "C:\Program Files\Alteryx\bin\AlteryxService.exe" start
Alteryx Service will not start: No suitable servers Error found in LastStartupError.txt
Gallery Service failed to start in a timely fashion
UPDATE: After reviewing the decision to deprecate the C# (.NET) SDK, we are committed to the C# (.NET) SDK working as it currently does in Alteryx Designer and Server for at least the next two years. This change is reflected in the specified time frame below.
tl;dr The C# (.NET) SDK will be deprecated in December 2021. Users currently leveraging the C# SDK for custom tools will need to convert their tools to a different backend (C++, Python, or macro) ahead of its deprecation. As part of this, the UI of the tool will also need to be reconfigured using the HTML GUI SDK, or macro if using a macro. FAQ What Is Happening? The C# (.NET) SDK will be deprecated in December 2021. See the 'Next Steps' section for more information. What is the C# SDK? A way for .NET developers to create their own custom tools for Alteryx. Documentation can be found here: [installdirectory]\Alteryx\APIs\SampleCode\DotNetCustomTools.pdf Why is this changing? Alteryx is putting more resources into improving the C++ and Python SDKs.
Who's Impacted? Anyone who is leveraging the C# (.NET) SDK. Who's Not Impacted? Anyone who is not utilizing the C# (.NET) SDK. What should we use instead? The Python SDK, C++ SDK, or macro backend (https://help.alteryx.com/developer/current/BuildCustomTools.htm). Next steps? The C# (.NET) SDK will be deprecated in December 2021. Custom tools utilizing the C# (.NET) SDK might continue to work on Windows, but not all existing functionality will be available and you should consider converting the backend of your tools to something that will be supported, such as Python, C++, or macro. The UI of the tool will also need to be reconfigured using the HTML GUI SDK, or a macro (Interface tools) if using a macro. Links to backend options documentation: Python, C++, or macro. Links to frontend options documentation: macro or HTML GUI SDK documentation. Questions?
Contact firstname.lastname@example.org Alter Everything!
You have multiple fields in your data that correspond to customer addresses. Some customers may have more than one address listed in their record. However, you want to whittle that list to one address per customer. That one address is the first, non-null address found when moving sequentially across a set of fields that contain address data.
For our example, we have three fields of data related to addresses: Address1, Address2 and Address3. The preferred field of data to use is Address1. However, if Address1 does not contain data, then we’ll use the data in Address2. If both fields of Address1 and Address2 do not have data, then we’ll use the data in Address3. The final output should contain a single list of the addresses highlighted in yellow in Figure 1.
Figure 1: The final output will contain a single list of the data highlighted in yellow.
Method 1: Write a Conditional Statement
The most common approach to this type of problem is to craft a conditional statement to select data based on a set of criteria (in this case, order). In the case of our data, it would look something like this:
IF IsNull([Address1]) AND IsNull([Address2]) THEN [Address3]
ELSEIF IsNull([Address1]) THEN [Address2]
ELSE [Address1] ENDIF
However, what if I had 20 fields of addresses instead of 3? Just getting that statement with three fields took me too long to write out! If you do have 20 fields, you might want to start typing that Formula now….
IF IsNull([Address1] AND IsNull([Address2]) AND IsNull([Address3]) AND IsNull([Address4]) AND IsNull([Address5]) AND IsNull([Address6]) AND IsNull([Address7]) AND IsNull([Address8])...
You get the idea. And now you’re thinking, “You’re going to tell me there’s a better way, right?!?” Well, yes...I am!
Method 2: Data Manipulation
An alternative method of solving this problem is to manipulate the data using the Transpose, Filter and Sample tools. I’ll share some advice from @RodL here: “If you want to make something…truly dynamic, then the ‘best practice’ when you are dealing with an unknown number of columns is to ‘verticalize’ the data”. In our case, we may know the total number of columns of address data we have; what we don’t know is which column the data we want is actually in.
Following @RodL’s suggestion, we’ll ‘verticalize’ the addresses using the Transpose tool. This stacks the addresses for each customer in order of the fields in the table (Figure 2). We’ll use the Client ID (or Record ID, if you’ve chosen to add one) as our Key Field and the fields that contain address data as our Data Fields.
Figure 2: All address fields per Record ID (or Client ID) are stacked vertically in order of the field sequence.
Since Null values are not usable records for our purposes, we’ll use a Filter to identify all the usable data (non-Null values). Now that our usable data is stacked vertically in order of field selection, we can Sample the first record from each Record ID (or Client ID) group. We’ll configure the Sample tool to identify the First N (where N = 1) Records from a group (Group by Record ID or Client ID).
Figure 3: Sample the first record from every Record ID or Client ID group.
After some data clean-up with a Select tool, we're left with a column of the selected address for each of our customers:
So we’re now downloading all the network-shared documents we want thanks to instructions posted on our Knowledge Base, and we’re on our way to mastering FTP in Alteryx. But what if we want to take it a step further? A lot of our users rely on FTP as a drop zone for datasets that are generated periodically (e.g. weekly, monthly, or quarterly datasets). We should then be able to schedule a workflow to coincide with those updates, automatically select the most recent dataset, crank out all the sweet data blending and analytics we have in our scheduled workflow, and proceed with the rest of our lives, right? Right. We can do just that, and with a little work up front, you can automate your FTP download and analysis to run while you’re enjoying the finer things in life. Here’s how in v10.1:
How do you convert a string with a $ sign into a number?
1. First remove the dollar sign and commas out of the string
2. Convert () to a negative sign "-"
3. ToNumber() function
There are many ways to do this in Alteryx, here are a few:
Formula Tool - ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))
Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))
RegEx Tool #1 - Replace \$|,|\) with Replacement Text "blank", then
RegEx Tool #2 - Replace \( with Replacement Text "-"
Select Tool - Change Type to Fixed Decimal
See attached workflow.
Is there a way to avoid the Cross Tab Tool from transferring all the input information alphabetically? Simply add a RecordID to your records and add the RecordID field as a grouping field in your Cross Tab Tool to keep the order!