14 May 2015

Alteryx: Must-Know Developer Tips for Complex Workflows

To find out more about Concentra's bespoke Analytics Services and Solutions see Services.  

In my latest work I’ve been using Alteryx with a  global investment banking client to perform data analytics. We’ve been blending data from a wide variety of heterogeneous data sources, such as relational databases (Oracle, SQL Server, PostgreSQL and Sybase), SharePoint lists, flat files and Salesforce connections. Alteryx workflows can be developed relatively quickly and quite often can become complex depending on the type of analytics performed. I wanted to share some of my favourite features of Alteryx that can help make development and maintenance easier. 



Tip #1: Use Aliases

Aliases are great way to store and maintain database connection information in one place. Any changes to the underlying source system properties, such as updating server name, database name, port number or even credentials update, can be done once in the alias repository and all workflows that refer the alias will automatically point to the new details. An alias can be created as shown in Figures 1 & 2.

Note: you need to launch Alteryx Designer as an administrator in order to create a System alias (Right Click on Alteryx Designer icon -> “Run as administrator”). 


Figure 1: Manage Alias Repository 


FIGURE 2: Alias setup 

Tip #2: Use Constant

It is quite common to hardcode values (‘constants’) in workflows using any of the following ways:

  1.  text input tool
  2.  variables within formula tool
  3.  hardcode the values within formula/any other transformation tools 

If the values are hardcoded within tools, especially in very large or complex workflows, maintenance can be difficult. The ‘constants’ property allows us to define all static variables in one place, so that they are accessible throughout the entire workflow. ‘Constants’ can be created as shown in Figure 3. 


Figure 3: Constants 

Tip #3: Use Macro Search Paths

Similar to defining aliases in  one place – as mentioned in Tip #1 - macro search paths can be defined in one place and made available as Tools in a Tool palette, as shown in Figures 4 & 5. You can even assign fancy images to macros in the Tool palette.  


Figure 4: Macro Search Patch setup


Figure 5: Alteryx Tool palette 

Tip #4: Use Truncate instead of Delete & Append

It is common for workflows to do a full refresh of tables. This can be performed using “Delete Data & Append” or “Overwrite Table (Drop)” options of the Output Data tool. The other option is to TRUNCATE the table as a part of “Pre Create SQL Statement” property and use “Append Existing” option as shown in Figure 6. This avoids heavy logging activity of the DELETE statement and saves you from losing any indexes with the Overwrite Table option. 



I hope you found these tips useful.