Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
MatthewO
Alteryx
Alteryx

Note: Alteryx Server received a number of enhancements with the 2021.4 release. For the 2021.4 release and beyond, please refer to this updated article on server runner macros.

 

Workflow orchestration is a topic that inevitably arises among seasoned Alteryx users. There are 3 scenarios we hear about most frequently.

 

  • Sequential execution: run workflow A, then workflow B, then workflow C
  • Simultaneous execution: run two instances of workflow A at the same time, with different inputs, to reduce overall processing time.
  • Nested execution: within workflow A, run workflow B and wait for it to complete, then continue executing workflow A.

 

Batch macros are well suited for orchestrating workflows, but some use cases may be especially unique. If you’ve exhausted your options with batch macros, you may have turned to the Runner Tool or the CReW macros. While useful in Alteryx Designer, these tools are not supported on Alteryx Server. Fortunately, there is another option. This article will help you get started building your own “runner” macro – with supported tools – and in the process demystify the Alteryx Server API.



Important

 

In a coming release, to streamline your asset management, subscriptions (studios) are going away. Accordingly, Server will also see changes to the API, including an Oauth2.0 framework. The steps below should only be referenced for versions 2021.3 and earlier. You should plan to document workflows interacting with a current version of the API as they will need to be updated as part of a Server upgrade.

 

Objective

 

This article is long and yet there is more that could be said on these subjects. With that in mind, this content is intended for users with a working understanding of RESTful APIs, JSON, building Alteryx macros, and the Alteryx Developer tools. It will not cover the basics. If you are new to these concepts, you can get started with the links under Additional Resources.

 

Example Macros

 

Most of what follows is a guide for constructing, authenticating, and submitting requests to the Alteryx Server API, rather than a step-by-step workflow tutorial. As a supplement, there are two working macro examples attached:

 

  • Alteryx Server Runner: a macro that accepts user or data inputs and submits a request to run an app on the Server. This macro will output the parsed response including the job id. The macro submits 1 API request per row of incoming data.

  • Alteryx Server Job Status: a macro that accepts user or data input and retrieves the status of a Server job. Optionally the macro can be configured to check the status intermittently until the status is “Completed”. The macro submits 1 API request per row of incoming data.

 

Feel welcome to reference these macros or modify them to suit your needs. Examples of how these macros can be used within a workflow are shown at the end of the article. You should thoroughly test these macros before using them in your production environment.

 

Alteryx Server API

 

The Alteryx Server API supports both user and administrative interactions with the Server. This article only focuses on user (subscription) endpoints, but the steps outlined are useful for either. Full documentation on the API is available at http(s)://your.companies.server/gallery/api-docs/.

 

API Access

 

Server administrators must enable API access in the Server UI. From the Admin portal, navigate to the subscription that should be granted access, toggle API Enabled to “Yes”, then save the changes.

 

NeilR_0-1641955779036.png

 

Key & Secret

 

Once API access is enabled on the subscription, users within that subscription can obtain the Key and Secret. Log into the Server UI and choose My Profile while hovering over your name in the top right corner, the Key and Secret will be located on the Keys tab. The Key and Secret values used in the subsequent examples are not real. Never share your API credentials!

 

NeilR_1-1641955779041.png

 

Run a Job on the Alteryx Server

 

With your Key and Secret in hand, you can now begin building out a request to the Server API. The following example will focus on running a workflow on the Server (creating a job), but the steps could be modified to interact with other API endpoints. In the macro examples, a Formula tool is used to create all parts of the request strings.

 

Step 1: Construct the URL

 

The components of the URL string are show in the diagram below. The base URL to your company’s Alteryx Server should be https. Documentation on all API endpoints is available at http(s)://your.companies.server/gallery/api-docs/.

 

 

NeilR_2-1641955779048.png

 

This endpoint requires that you include the ID of the app that you want to execute. To obtain the app ID, navigate to the app in the Server UI and click into it. Copy the string following the final forward slash “/” in the URL, this is the app ID. App IDs may also be retrieved programmatically by submitting a GET request to the subscription endpoint.

 

NeilR_3-1641955779052.png

 

Step 2: Construct the URL Parameters

 

The following parameters must be included in each request.

 

PARAMETER

VALUE

oauth_consumer_key

The Key obtained from your Server profile

oauth_nonce

A unique random string

oauth_signature_method

HMAC-SHA1

oauth_timestamp

Calculated as seconds since midnight on January 1, 1970

oauth_version

The only optional parameter, but if included must be 1.0

 

In the example macros, the following expressions were used to generate the parameter values below. The oauth_nonce parameter could be generated many ways; this is just one way.

 

  • oauth_nonce: IntToHex(RandInt(100000000))
  • oauth_timestamp: ToString(DateTimeDiff(DateTimeToUTC(DateTimeNow()),"1970-01-01 00:00:00","seconds"))

 

The parameter string is constructed by appending the parameter name with its value, separated by “=”. Combine all name value pairs separating each with “&”. Your parameter string will look like the diagram below.

 

NeilR_4-1641955779063.png

 

Step 3: Construct the Signature Base String

 

The signature base string is one of the inputs used to generate the authentication signature. There are 3 components to the signature:

 

  1. HTTP Method: POST in this case, but it should match the API endpoint
  2. Percent encoded URL from Step 1: the example macros copy the URL to a new field then encode it using a Find Replace tool with values from a reference table.
  3. Encoded parameter string from Step 2: the example macros encode the parameter string using the built-in function UrlEncode().

 

Below is an example of what the signature base string will look like.

 

NeilR_5-1641955779084.png

 

Step 4: Construct the Signature String

 

Append a single “&” to the end of the Secret obtained for your profile. This is the second input required for generating the authentication signature.

 

NeilR_6-1641955779088.png

 

Step 5: Generate the Oauth Signature

 

Alteryx does not have a built-in function for HMAC-SHA1 encryption. For this step, you will need to leverage a script to generate the signature. The attached Create Oauth Signature macro will execute this step for you using Python. Or you could write your own! If you are using the attached macro to generate the signature, map the following fields:

 

  • String: the signature base string from Step 3
  • Key: the secret string from Step 4

 

The macro will output a new field containing the signature. This is the final parameter needed to authenticate your request.

 

Step 6: Construct the Request String

 

You now have all the pieces needed to create the final request string.

 

  1. Append “?” to the URL string created in Step 1
  2. Append the parameter string created in Step 2
  3. Append “&oauth_signature=”
  4. Append the signature created in Step 5

 

NeilR_7-1641955779106.png

 

Step 7: Question Inputs (Optional)

 

If the app you are executing accepts inputs with Interface tools, you can pass these inputs in the body of the request. To do so, each input value must be associated with the Interface tool’s name. The name is defined in Alteryx Designer in the Configuration window, on the Annotation tab. In the example below, the Text Box displays “Enter your text”, but the tool’s name is “text_box_1”.

 

NeilR_8-1641955779108.png

 

The JSON body below would pass a value of “This is my input!” to text_box_1 when the workflow is executed.

 

NeilR_9-1641955779112.png

 

The example runner macro accepts question inputs using a List Box tool. The user can select fields to include, and they will be formatted and submitted in the request body. The field name must match the Interface tool’s name to execute correctly.

 

Step 8: Submit the Request

 

Use a Download tool to submit the request. On the Basic tab select the request field created in Step 6.

 

NeilR_10-1641955779113.png

 

On the Headers tab, specify the Content-Type as application/json.

 

NeilR_11-1641955779114.png

 

On the Payload tab, choose the correct HTTP action.

 

NeilR_12-1641955779114.png

 

Finally, if you are submitting question inputs in the body, select this option on the Payload tab and map the body field.

 

NeilR_13-1641955779114.png

 

Step 8: Parse the Response

 

Congratulations! You’ve submitted your first request to the Alteryx Server API. The Download tool outputs 2 fields:

 

  • DownloadHeaders: if your request was successful, you will see a message like HTTP/1.1 200 OK in this field. If you see a different status code, you will need to troubleshoot your request. In addition to the status code, the header field will contain a description of the error.
  • DownloadData: this field will contain the JSON response from the Server. This can be easily parsed using the JSON Parse tool. Upon completing a successful request, the response will contain an id field which is the newly created job id on the Server.

 

Orchestration Examples

 

Once you have your macros functional, you can use them in workflows to interact with other apps on the Server. Using the example macros, the three scenarios outlined at the beginning of the article are shown below.

 

Sequential Execution

 

In the example below, the runner macro is the final tool in the workflow. The Block Until Done tool is used to ensure all records are written to the database before the request to the Server API is made. The example runner macro will execute 1 API request per row of incoming data. A Sample tool is used prior to the runner macro to ensure that only 1 request is made. The runner macro creates a job on the Server for the next app in the sequence.

 

NeilR_14-1641955779119.png

 

Simultaneous Execution

 

As mentioned, the example runner macro will submit 1 request to the Server API for each row of incoming data. The goal of simultaneous execution is to run the same workflow with different inputs. Running them simultaneously reduces the overall processing time. In the example below, the column text_box_1 contains 3 rows of data, or the three inputs we want to run through the app. The runner will create a new job on the Server for each input.

 

NeilR_15-1641955779122.png

 

Nested Execution

 

When a job is created through the Server API, it does not wait until that job has completed before returning a response. It simply creates the job on the Server, then returns a response indicating if the job was successfully created. Using the example macros in tandem enables nested execution. In the example below, the runner creates a new job on the Server. The newly created job id is passed to the job status macro which checks the status each minute until the job is complete. Read the considerations before proceeding with a nested execution.

 

NeilR_16-1641955779128.png

 

Considerations

 

As you can see, the Server API unlocks many powerful capabilities with the Alteryx platform. Third party applications can also interact with the API to execute Alteryx apps. That said, it is important to be mindful of your Server’s configuration so that you do not inadvertently cause issues. Partner with your server administrator and your IT team to determine who should have API access and in which contexts. Below are a few critical items to consider when using the Server API.

 

  • Do you really need a runner macro? Batch macros were mentioned at the beginning of the article as another option for orchestration. Particularly for nested execution, these will be easier to create and implement.

  • Simultaneous workflows: Alteryx Server is configured to execute a specified number of workflows simultaneously (on the Worker Configuration screen in System Settings). Once this number is reached, any scheduled or manually executed jobs will go into a queue. To understand the implications of runner macros, let’s look at a simple example.

    • Suppose you have a single Alteryx Server on a 4-core machine and the number of simultaneous workflows is configured to 2 (the recommended configuration in this case). You schedule a workflow on the Server that will use a runner macro to create 10 jobs each time it runs. On average, it takes each job 5 minutes to complete.

      10 app executions * 5 minutes / 2 simultaneous workflows = 25 minutes

      Assuming no other jobs are running or queued on the Server, 2 will immediately begin executing while the remaining 8 will go into the queue. In total, the jobs will take 25 minutes to execute. Any new jobs that arrive during this time will be added to the bottom of the queue.

  • Schedule Forecast: if you are using Alteryx Server 2021.1 or later, you may be familiar with the Schedule Forecast. Jobs created through the API are a manual run type and do not appear in the schedule forecast. Alternatively, you can monitor activity using the Server Usage Report.

  • Cancel long-running jobs: Alteryx Server can be configured to cancel jobs that run longer than a defined number of seconds (on the Worker Configuration screen in System Settings). Consider if enabling this option is useful in the context of runner macros.

  • Run Mode: if the default Server Run Mode is set to “Safe”, it may not be possible to use runner macros. The encryption step requires a Python, R, or Command Line script to generate the authentication signature. These tools are blocked in safe mode.

Additional Resources

 

Matt Orr
Solution Engineer

Matt Orr has been in the data and analytics field for over a decade holding roles in Business Intelligence, Data Science, and Data Strategy. As a Solution Engineer, he enjoys supporting customers achieve analytics transformation with the Alteryx platform.

Matt Orr has been in the data and analytics field for over a decade holding roles in Business Intelligence, Data Science, and Data Strategy. As a Solution Engineer, he enjoys supporting customers achieve analytics transformation with the Alteryx platform.

Comments
cgoodman3
14 - Magnetar
14 - Magnetar

@WillH I think you might be interested in this.

TheOC
15 - Aurora
15 - Aurora

Very Interesting! Very excited to see future API changes too, thanks for posting! 🤓

VincentL
6 - Meteoroid

Hello all,

and

@MatthewO Hello Matthew.

 

I am using 2022.1 Alteryx Server version, and I can't make this AlteryxServerRunner work on my environment.

Does this macro still working ?

 

The complete URL create by the macro is :(by using debug mode:)

http://localhost/gallery/api/v1/workflows/6246c5697f922e0f146ada1b/jobs/?oauth_consumer_key=8D9FDF6E...

 

And in the downloadHeaders return , I find:

 

HTTP/1.1 405 Method Not Allowed
Transfer-Encoding: chunked
Allow: OPTIONS, GET, HEAD
Content-Type: text/html; charset=UTF-8
Access-Control-Allow-Origin: *
Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS
Access-Control-Allow-Headers: Content-Type, Accept, Authorization
Access-Control-Max-Age: 1728000
Date: Fri, 13 May 2022 14:12:41 GMT

 

 

When I try to lunch my workflow with the API examples:

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Bearer AQAAANCMnd8BFdERjHoAwE_Cl-sBAAAAtkALVYDP0kyuyuiWeQbJywAAAAACAAAAAAAQZgAAAAEAACAAAADWnJkyYf68T-PuznWEKfQ4RP1rkiwG_S6e_8SCuYltVgAAAAAOgAAAAAIAACAAAAAUK8Viigl7tzxQ4Qpv82fdWp73MN0Y_KR2nifrrm8uCMAAAAAXRXS68CZjXJne2V4ZmRs39KibyCdk5F6QKgbuhoido29H28jyisekFKFw9WAkdaplwJu-ZqGEQRyRPa3l_2vnpe0dlBTTCEJXXYfKso8f7B__-aubWVUlXeLvX7-XY68Zb2Hdobdxls3Lf9KsvscdMpxN8GcJ4qIx103LpzH5e_xk4V8aVY8o-BCJyxibHOH8CpnbaAht6VHzGBSVDRRXdXaIITp5P1KtQPz8u_Q3Unr1BJE3lVSvW2uZCOf0KrVAAAAAJeU_Fq_kCGCMXBOmGe94JwRNXSEttzj0ZtU1NP7nRf5UYS4kktSwu2K0_ZPpvnYSyCbP2H-eQHxA1e2vfF4M5A' -d '{}' 'http://localhost/webapi/v1/workflows/6246c5697f922e0f146ada1b/jobs'

that works fine, respond code is 200.

 

And when I try to transform the cURL into the DownloadTool:  is also works fine :

VincentL_0-1652452322547.png

 

I can't explain why it doesn't work by the AlteryxServerRunner... 

Could you help me ?

 

patrick_digan
17 - Castor
17 - Castor

@VincentL unfortunately 2022.1 deprecated the oauth1.0a that these are using. 2022.1 now uses oauth 2.0. The api examples that work are using Oauth 2.0 (that Authorization Bearer xxxxxxxx part is an oauth 2.0 token).  

VincentL
6 - Meteoroid

Oh ! Thanks @patrick_digan for your speedy reply !

I hope someone will update those macros with the new oauth2.0 token.

 

Does anybody work on it @Alteryx ?

Perhaps only the CreateOauthSignature.yxmc has to be updated, and if it is quite simple (I am not familiar with these token), i could update with some tips...

 

 
MatthewO
Alteryx
Alteryx

@VincentL these macros will only work with versions 2021.3 and earlier. This is because, as @patrick_digan mentioned, the Oauth1.0a framework has been deprecated and the API endpoints have been updated. It is still possible to execute these requests using Oauth2.0. I recommend reading through the following articles to learn more about the API upgrades and how to modify the requests.

 

Introducing the Alteryx Server V3 API Endpoints

Alteryx Server API V3

 

This article, and the example macros, are intended to illustrate how different orchestration scenarios can be accomplished with Alteryx Server. They are not actively maintained by Alteryx. That said, my goal is to update these macros to support newer versions of Alteryx but I do not currently have a timeline on when that will be. 

JSurgeon
5 - Atom

Hello @MatthewO, and thanks for the work you've put in here. If you could help me with a curiousity, though, that would be very helpful.

 

I am having trouble using your example Gallery Server Runner macro -- my use case is Sequential Execution, where subsequent workflows are inputting and outputting data from Salesforce or to/from SQL Server. Subsequently executed workflows, IE those queud using the Gallery Server Runner macro, are unable to translate aliases for the input/output tools. I can't track down why this would be happening, as the workflows run through the server if executed by themselves without error. As I have it set up now, I have a chain of 2 workflows, with the 1st utilizing the Gallery Server Runner macro above to trigger the 2nd in the chain. When ran by themselves through the server, as mentioned, they produce the desired results -- they input and output data correctly using whichever gallery connections they happen to be using.

 

However, when I attempt to run the chain by just running the 1st in the sequence to trigger the second, the second workflow is unable to translate the connection aliases. I know this because I have an event to send me an email after each run telling me the amount of errors and their values, if they exist.

 

I've tested this further with a longer chain-- if i have a chain of 4 workflows, with workflow A triggering workflow B, and so on, only the workflow I initially begin the chain with will translate aliases appropriately. For intsance, say I test by starting with running workflow A on the server: workflow A will translate, input, and output appropriately, workflows B, C, and D will report that they were unable to translate aliases, via an email sent to me. If I test by starting with running workflow C on the server, C will work just fine, but D will still be unable to translate aliases (A and B are not triggered in this circumstance).

 

My server version is 2021.3.5.3604.

 

Again, if you or anybody else have any tips or suggestions it would be greatly appreciated!

 

Thanks in advance.

MatthewO
Alteryx
Alteryx

@JSurgeon my immediate thought is that the first workflow is running with a different user credential. The subsequent workflows are likely running under the default Server account which possibly doesn't have access to the input/output resources.

JSurgeon
5 - Atom

@MatthewO 

The emails I'm having each workflow in the chain send after their respective runs all say they are using the same User, when ran from the Gallery. I agree that this seems like some sort of credentials issue, which I'll have to speak with my server admin about, but it is odd that the supposed user of the failed workflows is a user which certainly has access to the input/output resources. 

 

I haven't ever had to deal with credential-related issues in Alteryx, so I'll have to read up on the topic. If I resolve the issue, I'll make sure to post the resolution here.

 

I appreciate your help!

MatthewO
Alteryx
Alteryx
MeganBowers
Alteryx Community Team
Alteryx Community Team

Hi all,

 

For an update to this article, Matt has posted a blog on workflow orchestration for Alteryx Server 2021.4 and beyond:  https://community.alteryx.com/t5/Engine-Works/Workflow-Orchestration-with-Alteryx-Server-2021-4-and-...

 

-Megan