Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
By Philipp Maier, Henan Li (@hli2007), and Valentina Radchanka (@The_Rad_Valentina)
Problem Statement
Alteryx is a great productivity tool. Its easy-to-use interface empowers “citizen developers” (i.e., someone with little to no experience in formal coding, IT, or computer science) to develop workflows and applications. Often, their detailed understanding of business requirements enables them to improve tasks and processes through Alteryx in agile and nimble ways.
Enabling citizen developers can also raise challenges. Where IT professionals adhere to established procedures to ensure the management and testing of code, citizen developers may operate without access to the same tools, professional training, or structured documentation. As citizen developers may work on processes and workflows critical for successful business operations, some of our clients have asked if Alteryx could provide ways to guide them toward a set of best practices.
In response, this community article introduces an Alteryx application we call “Risk and Audit Manager.” Its purpose is to collect information on risks inherent to a specific workflow so this information can be leveraged when promoting to production. For instance, workflows needed to complete business-critical operations that write information to a System of Record or that carry material financial or reputational risk may necessitate a review process. In contrast, workflows that meet none of these criteria may require less scrutiny before promoting to production.
Leveraging this application, we can also accomplish a secondary benefit. Often, business associates or Centers of Excellence are interested in understanding the benefits delivered by the Alteryx platform. We propose a solution that allows inputting benefit information as well, facilitating reporting for senior management and demonstrating the power of citizen developers.
Requirements and User Experience
In our mind, the solution should meet the following requirements:
- Built in Alteryx and easy to deploy, maintain, customize and understand for anyone familiar with Alteryx.
- A flexible architecture to accommodate different types of information (e.g., risk and benefits), but also extendable if, for example, regulations require additional categories.
- To keep things simple, we will not build out the full integration into any existing database structure but simply store information in a new SQL Lite database.
Our “Risk and Audit Manager” meets these requirements. It consists of several Alteryx workflows:
- One workflow called “Audit and Server Management” is running periodically on the server to check for new, changed, or deleted workflows. When a new Alteryx workflow is uploaded, the user will be directed to the Risk and Audit Manager.
- The “Risk and Audit Manager” is an Alteryx application. It will authenticate the user and ask whether (s)he wants to provide risks and benefits for a new workflow or update them for an existing workflow.
Figure 1 displays a potential process flow, illustrating how the Risk and Audit Manager could enhance the overall Alteryx user experience.
Figure 1: Process Flow Risk and Audit Manager
As we strive to limit administrative overhead, this application is designed to be filled out in under 2 minutes. We focus on 3 risk and 3 benefits categories:
- Risk #1: Does this workflow use confidential data?
- Risk #2: Could errors in this workflow lead to material financial or reputational risk?
- Risk #3: Is this workflow business-critical?
- Benefits #1: Additional revenue generated
- Benefits #2: Costs saved ("hard dollars")
- Benefits #3: Time saved ("Productivity gains")
Of course, users can easily add additional questions (we provide some ideas at the end).
Figure 2 shows the main screen of the Risk and Audit Manager: going from top to bottom, the user selects the workflow (1) and then inputs risks (2) and benefits (3). Upon clicking the RUN button, the information is then stored in a database, which can also be leveraged for reporting or audit purposes (hence the name "Risk and Audit Manager").
Figure 2: Leveraging Risk and Audit Manager to track benefits
How To Implement and Configure
While not overly complex, the Risk and Audit Manager relies on a few features casual Alteryx users may not be familiar with, notably the Server API Tools and chained apps.
- The free Server API Tools from the Alteryx Marketplace are needed to access the server to retrieve a list of workflows and check for changes.
- The use of a chained app is necessary because we dynamically populate a “List” control for the second application. Here is how this 2-step process works:
- The first application authenticates the user and presents the choice to enter information for a new workflow or update information for an existing workflow. For the authenticated user, the app then uses the Server API macro to retrieve a list of workflows. This list is passed on to the second app.
- The second application shows the interface with questions and stores the information in the “database” (in our case, an Alteryx database to keep the code simple).
Let’s look at installing and configuring the Alteryx workflows.
Step 1: Workflow “Audit and Server Management”
As we will need to access the server, first install the Server API Tool (v 1.3.4 is the latest at the time of writing) from the Alteryx Marketplace. If the tool is not installed, the server API icon in the workflow will be grayed out. We will store all temporary files in c:\temp, so please verify that the folder exists and that Alteryx has read/write permissions (we describe below how to change that).
Then, open the "Audit and Server Management" workflow. Detailed setup instructions are in the workflow (see Figure 3); at a high level, the steps are to connect the workflow to your server (the green box “Step 1” in the workflow) and create a SQL Lite database in which data can be stored (the “Step 2” box). As we explain in more detail below, the file location is determined by a user constant (user.location), which can be changed in the workflow.
Figure 3: Setting up Server and DB Connections
One complexity arises from the fact that we first need to set up a database to store the information. While we assume that most clients will use their existing database infrastructure for demo purposes, the workflow allows the setup of a database from scratch. We have added red and green containers (which can be turned on/off) to facilitate that process. Here is our recommended solution (see also Figure 4):
- On the first run, only turn on the top containers (labeled “Step 2: Set up Audit Database” and “Step 3. Enable on first run to create AuditDB”). The tool configuration in this box for the output data icon in step 3 is set to “Create new table,” which is going to set up an empty database. At least once with this configuration, the workflow needs to be run on the server to create the database.
- For all subsequent runs, turn off these two containers and instead enable the containers labeled “Step 2 Connect to existing Audit Trail Database” and “Step 3. Update existing DB.” The tool configuration for the output data icon in step 3 is set to “Overwrite table (drop).” This will update the now-existing database.
That’s it!
The workflow itself is fairly straightforward, but a special callout is that since our goal is to create an Audit trail, we do not delete any information from the database. Instead, when a workflow is replaced or deleted, we mark the prior version as “inactive” (this is why the tool configuration in Step 3 is set to “Overwrite Table (drop)”).
The location for the audit database by default is set to c:\temp; this is obviously not ideal and should be changed to match the overall data and audit strategy. The file path can be changed in the workflow settings, where we have defined a user constant (user.location); we have attached a screenshot of the workflow with the location clearly marked.
One extra feature we find quite useful is the option to send out automated emails to users who upload, modify or delete workflows. This could also be extended to provide users with instructions on the process to promote a workflow to production. If this functionality is used, setup of the email is required in “Step 4” of the workflow (see Figure 5).
Figure 5: Setting up Automatic Emails
Once the workflow is configured, upload it to the server and schedule it to run regularly (e.g., every hour). Correct implementation can be tested by uploading a new Alteryx workflow to the server and triggering a manual run. If the email functionality is enabled and setup is successful, you should immediately see a new email notification.
Step 2: Application “Risk and Audit Manager”
Once the “Audit and Server Management” workflow is up and running, installation of the “Risk and Audit Manager” chained application is required. While setup is relatively straightforward, and no changes should be required using the default file locations, a lack of experience with chained apps may pose some challenges. Figure 6 shows the first workflow: To set it up, connections to the server and database created in step 1 are needed (if you did not change the default location, no changes to the app are required). Then, we need to temporarily store information to hand off to the second workflow. The directory is clearly indicated in the workflow and will likely not be changed, but please verify that the application has read/write permissions on the server for this directory.
Because we are dealing with a chained app, we need to call a second workflow after the first workflow is completed. Again, when running the application with the default configuration, no changes are required. For those interested in the inner workings: Changing the configuration is accomplished in the properties window of the Interface designer: click on “View – Interface Designer” and go to the “Properties” tab; there, verify that the “On Success – Run Another Analytic App” box is checked and that the file path is correctly set for the second workflow (which we will configure below).
The workflow itself is short and unremarkable, though the container in the top-left corner may deserve a special callout: this is where we dynamically retrieve the user to authenticate. Specifically, we have added a text box in the Interface Designer with the name "__cloud:UserId" in the annotations tab, which will gather the information. Note also that we give users the ability to either enter risk information for a new workflow or to update an existing workflow. This is done with a radio button; the workflow then filters down existing data and stores the information gathered so far in two (temporary) files labeled “ChainedAppInput” and “ChainedAppDropdown.” This provides us with a means to hand information to the second chained app (which is called in the Interface Designer pane under “On Success Run Another App”).
Figure 6: Configuration of the chained application
The second part of the chained app looks a bit more complex (Figure 7), but the general idea is remarkably simple: The second application reads in the two files just created and accesses the server through the Server API macro to download the list of workflows specific to the user. The application then guides the user through a series of questions, which are built out at the top using repeated interface tools.
For demonstration purposes, we ask for basic risk and benefit information, but this is where options for customization exist. Ideally, the questions and risk categories are aligned to a broader risk framework and provide information useful to guide the promotion process. Setup of this last application is fairly unremarkable (and when run using our default configuration, no changes are needed): in two clearly marked containers, information about the database setup from step 1 needs to be added (circled “1” and “4” in Figure 7 below), and locations to store temporary information for the chained application needs to be verified (circled “2“ and “3” below).
Figure 7: Setting up the chained application (part 2)
As an added feature, we added the option to generate a PDF report for users, providing instant feedback. This option can be turned on by enabling the blue container (the circled “5” in Figure 7).
Once setup is complete, upload the application to a server. To ensure proper handoff of data, please make sure that the database and temporary files from the server are used. This can be accomplished by unchecking the relevant boxes in the “Save to server” dialog box. For instance, the following figure shows the correct configuration for the chained app.
Once done, make the application public for all users, and you should be ready to go!
Enhancing the Governance Process
We propose making the “Risk and Audit Manager” an integral part of the process to migrate a workflow to production. This can easily be accomplished in the following way:
- The “Audit and Server Management” workflow is scheduled to run periodically on the server (say, every hour). It will monitor the server for changes and send out emails to users who have uploaded, modified or deleted workflows.
- The “Risk and Audit Manager” is run on the server. After completing testing, users who want to migrate workflows to production will fill out the required information.
- As part of the migration process, the CoE or server admin team will review the information and determine the appropriate level of scrutiny before migrating the workflow. Depending on Enterprise policy, this could entail a review process, a manager sign-off process or enhanced testing if confidential information is used.
Managing information in a database allows us to simplify the workflow for the community, but an obvious improvement to the existing demo workflow is to leverage a database solution to store the risk and benefit information. Besides performance gains, this can enable versioning and is indispensable for an Enterprise solution.
Options to Customize, Enhance or Extend
The “Risk and Audit Manager” is a principal element in managing workflows, but this post can only scratch the surface of what is possible. We can think of several ways to customize, enhance, or extend:
- A dashboard reporting on the information, listing, e.g., which workflows use confidential data or who owns these workflows.
- Where stakes are high, annual attestation processes for business-critical workflows or workflows that use confidential data.
- Periodic reminders of the workflows for which users have not yet provided the requested information.
- Some of our clients also have restrictions in place to check that citizen developers do not build complex applications best left to IT professionals. To this end, one could imagine that citizen developers are provided with an “Assessment” or “Report Card” on their workflow, either greenlighting development or cautioning them to work with other teams to ensure their workflow meets enhanced standards.
We hope to inspire others to improve upon this demo application! If you have feedback or additional cool ideas to enhance, please make sure to post in the comments below.
Lastly, for clients who are seeking to expand this sample application into an Enterprise solution to suit their specific purposes but need help to do so, we should mention that our vast partner network or Alteryx Professional Services can be of help.
