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.
Introduction Does your organization have macros that users have developed to perform common and/or repeatable tasks within Alteryx? Does your organization struggle with how best to share those macros with the Alteryx Designer users and Alteryx Workflows/Apps which have been published to the Alteryx Server? Would you like to streamline the process of updating macros and utilize them through your Alteryx Workflows? Great, now let’s take a look at how we can easily achieve this within your Alteryx environment(s). As is common with many organizations, users within the organization have likely developed macros which perform various processes in Alteryx. These macros contain everything from simple to advanced data cleansing operations, core business logic, data manipulation tasks and much more. Once a user has developed a macro, we want to make it easy for them to share that with the rest of their Alteryx community. When it comes to using macros in Alteryx Workflows and Analytic Applications published to Alteryx Server, many organizations have questions about how to deploy those Workflows that utilize these macros. More importantly, when a change is made, how do they propagate the latest version of the macro to all of the published workflows? In this article, we will discuss the best practices around sharing macros. Using the methods outlined here, users will be able to make changes to Alteryx macros that are seamlessly propagated to other Alteryx Designer users, as well as Alteryx Workflows and Analytic Applications that have been published to the Alteryx Server. What are macros? Before we dive in to sharing macros, let’s define exactly what a macro is. Macros are a series of Alteryx tools built into a single workflow, that are bundled into a single tool that can be easily inserted into another workflow to perform common, repeatable tasks. Think about it as if you were taking an entire workflow and zipping it up into one Alteryx Designer icon, which you can then put into any other workflow. A perfect example of this would be to create a standard macro out of an analytic process you have to repeatedly create on a regular basis. Having this process in a macro form, allows you to then re-use that entire process in any number of other workflows without having to recreate the analytic process (or workflow) each time. This not only helps save time, but it also makes the entire process easily repeatable and protects you from forgetting any steps in that process. One key benefit to macros is, down the road, if there is a change to be made to that whole process, you only need to make that change in one workflow (macro), rather than chase down every instance that process was replicated. Macros are identified by a .yxmc file extension. There are four main types of macros with Alteryx: Standard Macros, Batch Macros, Iterative Macros, and Location Optimizer Macros. Standard Macros These macros, as mentioned above, are designed to package a process in a workflow as a tool that can be inserted into a workflow. Batch Macros These macros run multiple times in a single workflow, creating an output after each run. The macro runs once for each record, or selected group of records, in the data. This type of macro requires a Control Parameter tool to be used as an input. For additional information, see Batch Macros. Iterative Macros These macros run in the workflow the number of times set in the configuration, or run continuously until a specified condition is met. For additional information, see Iterative Macros. Location Optimizer Macros These macros are an iterative macro that can be used in network analysis to identify an optimal location or locations. For additional information, see Location Optimizer Macros. Sharing Macros Macros can be shared with users of the Alteryx Designer and Alteryx Server using a network file share. Permissions on who has access to the macros are controlled by permissions you define on the share. You can assign share permissions to users, groups of users, and computers to expose macros to Alteryx Workflows which have been published to the Alteryx Server. Once you have placed the macro in a network share, you can add a macro repository in Alteryx Designer. After adding a macro repository in Alteryx Designer, the macros will appear in the tool palette, and can be searched by using the search toolbar in Alteryx Designer. Creating a Network Share NOTE: The following is an example of how to create a network file share. It is not intended to be used in production environments. Depending upon the permissions defined on your network, it may be necessary to work with your IT department to have a network share created or permissions granted. Assuming you have the correct permissions to create a network share, proceed as follows to create a network share for macros: Open the Computer Management console on the file server Expand the Shared Folders section Right-click on Shares and select “New Share…” Follow the prompts in the “Create a Shared Folder Wizard” to create a network share NOTE: When creating the network share, you will be prompted to set shared folder permissions. Permissions allow you to control the level of access users, groups of users, and computers have on the network share. Permission Considerations Within an Alteryx Server environment, there are various ways workflows can be run. By default, all workflows will be run by a Local System account. However, you can configure Alteryx Server to run all workflows as a specific user in the Alteryx System Settings -> Worker -> Run As settings or you can require users to enter their domain credentials to run workflows. Depending upon the configuration of your Alteryx Server environment, this can dictate how permissions need to be assigned to the network share. Example 1: If workflows are running under the default system account, in addition to assigning permissions to the share for users of the Alteryx Designer, you will need to assign computer permissions to the share for each server in your Alteryx Server environment. Example 2: If workflows are running under a service account using the Alteryx System Settings -> Worker -> Run As settings, in addition to assigning permissions to the share for users of the Alteryx Designer, you will need to grant the service account access to the network share. Example 3: If you require users to enter their credentials to run a workflow in your Alteryx Server environment, in addition to assigning permissions to the share for users of the Alteryx Designer, each user with permission to execute the workflow will need permission to the network share. User and Group Permissions on Network Shares NOTE: The following is an example of how to assign user and group permissions on a network share. It is not intended to be used in production environments. To assign User and Group permissions, ensure the Users and Groups object types is selected in the “Select Users, Computers, Service Accounts, or Groups” dialog window. If either is not shown in the object types, click the Object Types button and select the Users and Groups object types. Computer Permissions on Network Shares NOTE: The following is an example of how to assign computer permissions on a network share. It is not intended to be used in production environments. To assign computer permissions, ensure the Computers object types is selected in the “Select Users, Computers, Service Accounts, or Groups” dialog window. If Computers is not shown in the object types, click the Object Types button and select the Computers object types. Shared Macros in Alteryx Designer Now that you have created a network share for your macros, and assigned user/group/computer permissions, it is time to create a macro repository in Alteryx. Once you have added a macro repository to your Alteryx Designer, the macros will appear in the tool palette and be searchable using the search toolbar. To create a macro repository, proceed as follows: Open Alteryx Designer Go to Options > User Settings > Edit User Settings and click on the Macros tab Click to add a macro repository In Category Name, type a name. Macros will appear in the tool palette under the Category Name defined here. The default name is Macros Click to browse to the location where you save macros on your computer or a network or paste the file path to the network share containing the macros Click the OK button on the “Add Search Path for Macros” dialog window Click the OK button on the User Settings dialog window Once you have completed the steps above, the macros will appear in the Alteryx Designer tool palette under the category name specified in step 4. NOTE: Sub-folders in a search path cannot be added individually if their parent folder already exists in the Macros repository. If you want sub-folders to display in different tool categories, you will need to remove the parent folder and add each sub-folder individually. Shared Macros in Alteryx Server To make the macros available to workflows published to your Alteryx Server environment, you will need to create the macro repositories on your Alteryx Server. To ensure the path to macros in workflows match between the Alteryx Designers systems and the Alteryx Server, the macro repositories will need to match those created on your Alteryx Designer users’ systems. To create a macro repository, proceed as follows: Open Alteryx Designer as an Administrator on your Alteryx Server Go to Options > User Settings > Edit User Settings and click on the Macros tab Click to add a macro repository In Category Name, type a name. Macros will appear in the tool palette under the Category Name defined here. The default name is Macros Click to browse to the location where you save macros on your computer or a network or paste the file path to the network share containing the macros Click the OK button on the “Add Search Path for Macros” dialog window Click the OK button on the User Settings dialog window Once you have completed the steps above, the macros will appear in the Alteryx Designer Tool Palette under the category name specified in step 4. NOTE: In a multi-node server deployment, you will need to create the macro repositories on each node. After creating a macro repository on one server, you can copy and paste the newly created .ini config file (located at C:\ProgramData\Alteryx\DataProducts\AddOnData\Macros) to the same path on each server in your Alteryx Server environment. Frequently Asked Questions Q. When I publish a Workflow to my Alteryx Server environment, do I need to do anything else? A. No. When you create a macro repository, Alteryx Designer sees macros contained within the directory as a “native macro” and references the macro using relative paths. When you publish the Workflow to Alteryx Server, the macro will continue to be referenced using a relative path based off the repository path defined in your Alteryx Server environment. You can verify that the macro is not being packaged with the workflow by checking the Workflow Options -> Manage Workflow Assets in the Save As dialog window. Q. I’ve made a change to one of my macros. How do I deploy the latest macro to all the published Workflows in my Alteryx Server environment? A. When you save the macro to the network share, it will automatically be updated for every workflow that references the macro in the macro repository you created. Q. I have multiple environments, dev, staging, and production. I would like to ensure changes to macros are properly vetted before being released to production. Will this work for my needs? A. Yes! For this to work, you can create a file share for each environment and assign the appropriate permissions to each environments file share. When adding the macro repositories to each environment, you can supply the environment specific macro repository path. When users make any changes to macros, they should save the new version to the dev file share. You can then test the updates to validate the changes before moving the updated macro to the staging and then production file shares. NOTE: To ensure a seamless transition between the Alteryx Server environments, it is important to ensure the folder/file structure matches in each of the environment-specific file shares. Q. Can I use a mapped network drive to share my organizations macros with Alteryx Designer users and Alteryx Server? A. As mapped network drives are mapped on a per user basis and do not appear to batch processes, using a mapped network drive will not work for sharing macros with Alteryx Server. It will, however, work if you are only sharing macros between Alteryx Designer users within your organization.
We receive quite a few questions on how to properly chain workflows together. We have a few options that depend on what Alteryx type you are using. If you have Designer only, your options are to use the Crew Macros which are found on the Community. No link provided as the tools get updated. Please use the Community search for the latest Crew Macros. In the Crew Macros set, the tools that you will be looking to use are the Runner Tools. You can find a description of the tools here Runner Tools. Note: You will have to create another workflow using these macros to execute the workflows. If you have Designer with Scheduler, you can use the Crew Macros on the Scheduler, but they are not recommended or supported in this configuration. Using Runner Macros on an Alteryx Server or Gallery can risk over-utilization of resources and cause instability in an Alteryx Server or Gallery environment and thus Runner Macros are not supported on Alteryx Server. There are other options that will allow you to use the workflow on the Gallery or Scheduler. When you have Scheduler or Server you have access to the Alteryx Engine Command. This will allow Alteryx to run workflows through the command line to execute additional workflows or programs. In most cases, the AlteryxEngineCmd.exe will be located in C:\Program Files\Alteryx\bin\AlteryxEngineCmd.exe. Great, so what do I do with it? In your Workflow Configuration window, you have a tab called Events. In that tab you can add a Run Command event. You can choose to run the command as: In the Command: you can either browse to your AlteryxEngineCmd.exe or type in the location like so - C:\Program Files\Alteryx\bin\AlteryxEngineCmd.exe In the Command Arguments [Optional]: You will add the location of the 2nd workflow along with the name of the workflow and file type. example: "C:\temp\NHL2.YXMD" VERY IMPORTANT: Notice in the example above that we have quotations around the string. The reason for this is that the command has difficulty with spaces in the directory paths. By putting quotes around the string, this tells the command to essentially ignore the spaces. However, best practice is to not have spaces in your directory paths when using this command. Sample: Note: This method is a great method, but can be finicky. if you are running other command process or do not have admin access to the folders it can cause issues. You have another option as well! You can turn your workflows into apps for either Gallery or Scheduler use and have them run consecutively. When you drag an Interface Tool onto the canvas your workflow will automatically change to an App. If you don't want to use an Interface tool you can still change the workflow to an app to use the chained app option. Once you change the workflow to an app, you can use the Interface Designer window to chain the apps together. Note: When creating chained workflows to use in the Scheduler and Gallery, the workflows and Assets for the workflow must be placed on the Server. Also, the Server will need to have access to any database connections, file directories, and input files. Want to know more about the Interface Designer? Interface-Designer-Part-1 Want to schedule workflows to run consecutively? Check out this article - Scheduling-Workflows-Using-Event-Run-Command
Have you ever built an Alteryx App only to realize that some part of the process must remain in Excel? Instead of abandoning your Alteryx App all together, you may be able to use the Alteryx API and call your app from Excel. We’ve put together all the necessary VBA code so that it should be quick and easy for you to call an Alteryx App from Excel and get the results back. The Alteryx app for our sample, API Tester.yxzp, has been included in case you want to upload it to your private gallery and test out the Excel to Alteryx API using your Alteryx server.
SAMPLE EXCEL FILE - Input
We are going to be working with the attached “Reverse String.xlsm” to demonstrate the API. Suppose you wanted to reverse a string in excel (“Hello World” should be converted to “dlroW olleH”). You can’t quite get it in Excel, but you could quickly use the ReverseString function in Alteryx. Now the only trick is getting your data from Excel to Alteryx and back to Excel. We’ll have the user enter the string on cell E5 and then hit the Run Alteryx Button. Cell E6 will then be the results we get back from Alteryx
HOW TO SETUP THE VBA
When working with the Alteryx API, you need to setup some settings/configurations in your excel file before you can deploy it. If you open up the VBA editor (Alt + F11), there is Sub called Doer where we will have to enter a few key settings that apply to your Alteryx App. This is a one time setup for each different application that you setup.
strKey – this is your unique Alteryx Key. It is unique to all the apps in your private studio. It can be found here for the Alteryx public gallery
strSecret – this is your unique Alteryx Secret. It is unique to all the apps in your private studio. It can be found here for the Alteryx public gallery
strAppID – this is your app’s unique ID. When you look at your app in a browser, the appID is the unique ID at the end of the URL. For our sample, our AppID “589b70eceffc2a0bb0a2d530” can be found at the end of the App’s URL
AlteryxURL – this is the base URL where the app resides. For the public gallery, it is : https://gallery.alteryx.com. You would switch this your URL if you were going to use your private gallery. A private gallery would be of the format resembling: https://server.domain.com/gallery.
Data – Any data that needs to be passed from Excel to Alteryx can be saved as string variables in the VBA. For our simple app, we’re only passing one item, the string that we want reversed. In order to pass more complex data, we’ve found it easier to save the data to a .csv and then have the Alteryx App read the .csv as part of the app.
strQuery – This is the entire set of information that is being sent from Excel to the API for processing. It’s in JSON format. For each item, you need a name and value. The name corresponds to the interface tool’s name in your Alteryx App. The value corresponds to the value that want to set. From our Alteryx App, you can see that we’ve given our textbox tool the name “input_string”. For the value, we’ll be passing whatever string the user enters into excel.
To see all the questions for a given app, the API documentation is immensely helpful. After you enter your key and secret, you would go to the second GET, type in the appID, and then try it out to get a list of the name value pairs that we must send from Excel to Alteryx. Note that you only send the name and value fields from the response body.
Save – This variable is a Yes or No answer for whether or not you want to save the data coming back from Alteryx. In this case we would say Yes. For more complex apps, we may have our Alteryx workflow save the data to a network folder that our excel VBA would go grab once Alteryx is done. That post run coding would be added under the Part_Deux sub in the VBA editor. There we would have Save=”No”
Output_Name – this is the column name of the output data that we want to retrieve from Alteryx. In our sample, the column Name is “Output_String”. This only applies if Save=”Yes”
SaveLocation – this is the cell range where the output should be written. This process currently only supports writing one cell of data back to excel. Anything more complicated should be handled through custom VBA in the Part_Deux sub. This only applies if Save=”Yes”
NOTES ABOUT WHAT IS GOING ON BEHIND THE SCENES
While I won’t go through everything that is going on in the background, here are some key points:
Ensure that you have references to Microsoft XML, 6.0 if any errors pop up.
The VBA takes all the settings and adds the time and a random string before posting a request to the Alteryx Server to run the job in the RunAlteryx sub. It has to combine all these items, URL encode certain parts, create a base64 hash, and send this to the Alteryx server in just the right order.
It then makes a Get request to check the status. If the status is complete, then the VBA moves on to get the results; otherwise, the VBA creates another request to check the status in 1 second. Note that Excel will be operational during this time since it’s not during any work. This loop will continue until the status is either completed or error. Note that it will update the status on the bottom left of your screen to indicate that Excel is waiting on Alteryx to run the job.
Once the Alteryx app returns a completed status, the VBA will then make another get request for the output that you’ve requested (only if save=”Yes”). If Save=”Yes”, the VBA then saves the output to the specified cell.
If you want to write any of your own code that should execute when the code is done, place it in the part_Deux sub. For example, perhaps you have a private server setup where the Server will write an output file to a network folder that excel can read in from. The code in part_Deux might look something like the below:
Dim ActBook As Workbook
Dim FromAlteryx As String
FromAlteryx = "\\server\" + Environ$("username") + "_out.csv"
Set ActBook = ThisWorkbook
On Error GoTo 0
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Workbooks(Environ$("username") + "_out.csv").Close
SAMPLE EXCEL FILE - Output
Assuming we have everything setup correctly, our app should return the reverse of the string that is input:
Assets are an important part of your workflows in Alteryx. Assets will need to be included if you want to share, schedule, and publish your workflows to your Gallery. We run into many cases where users are able to run workflows on their Designer but they fail on Scheduler and Gallery. In a lot of cases it is due to the pieces of the workflow are not all there in the Scheduler and Server database. In this article, we will talk about when you need assets, how to package the assets, and what assets you want to make sure are a part of your workflow.
When You Need Assets:
When building and using workflows in the Alteryx Designer you don’t need to do anything with your assets as they are a part of your workflow locally, but if you want to move the workflow somewhere or to someone, you will need to package the workflow.
When sharing your workflow with another user, the workflow will need to be packaged with those assets so that the user can run the workflow without error. This would include input files as well as macros. If the workflow is connected to a database or using Alteryx Data, the user will not be able to run the workflow successfully unless they have access to those data sources on their own machine.
If you have Server or Desktop Automation, the Scheduler will be using a database separate from your Designer. If you have Server you will be using a MongoDB and if you have automation you will be using a SQLite database. This would mean that any macros that are not on the server or SQLite database would need to be added. By packaging the workflow and scheduling the package, the Scheduler will be able to access any custom macros or macros not included with Alteryx. This would include Predictive as well unless you have installed the predictive tools to your Server. If you have version 11.0 Server or later, you can send your workflows to your private gallery and schedule them on your server. When you do that, Alteryx will package the workflow the same as if you were sending an app or workflow to the Gallery as below.
The Gallery works a little different than packaging a workflow or Scheduled workflow. The difference is that the Assets will need to be on the Alteryx Server along with the workflow for the app, macro, or workflow to be used on the Gallery. Basically, the Save As option to the Gallery of your choice will start the packaging process of your workflow and Assets. The Save As a workflow window will appear with information about your workflow that you are sending to the Server Gallery. Select Workflow Option. Below the Workflow Options you will select Manage workflow Assets. This is where you can include and exclude information being sent to the Gallery.
Packaging the Assets:
Select in the tool bar Options ->Export Workflow. This will then launch a window that will show the name of your workflow, the location where the package will be stored, and the list of workflow assets.
When sending your workflow to the Gallery, you will do a Save As to and the Company’s Gallery. A window will open and you will see Workflow Options at the bottom of the page.
Select Workflow Options
You will then reach the Assets window which is similar to the packaged Assets window above.
You also have a second option when sending workflow to the gallery. In the Workflow Configuration window, select the Events tab, Select Add, Run Command. You will see a tab called Assets where you can add assets to the workflow. This is especially helpful when you are adding a chained app to the gallery. Check out Jordan’s article for the step by step: Adding-Files-to-the-Gallery.
What Assets Do I Need:
Great! I found the assets, but why are some missing, some checked, some not checked and which ones do I really need and what don’t I need?
You may see that you have Input and Output assets. When you are packaging a workflow to send to a user, you can include those assets if the user does not have those files to run the workflow. If the user does have the files or they are going to update the Input files or Output files, then you would not include them. The Input and Output assets will never include database connections only files.
The .yxmc files can be an important part of your workflow. Many of the Alteryx tools are .yxmc files and if these macros are a part of the Designer package, you won’t need to include them in the package as Alteryx knows that these tools will be in the workflow (example Report Header Tool is a macro). If macros are downloaded from the Alteryx Gallery, custom created, or sent from another user, these macros will need to be included as Assets.
Receiving the error below when attempting to schedule a module?
“An error occurred in the scheduler. Server Error: 500 Server Error GetExpectedValue: Expected “Container” but got “Sid” Incorrect type requested 1 actual 4”
Post v10.5 release, your Alteryx Server and working environment must be of the same version in order to enjoy the upgrades of the release and still be able to commit scheduled workflows correctly. When the versions of your worker and server do not match, you’ll receive the error above. While our recommendation is to be using the most up to date release, you can always upgrade or revert your designer version either at our Downloads page (current version) or the Previous Releases webpage. To check on the version you’re using, you can navigate in the Designer to the Help >> About menu.