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.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
As Alteryx analysts, we’re whipping up insight at blazing fast speeds. Workflow after workflow, tool after tool, we’re gleaming functional understanding from inert webs of data that empower us to make better decisions. Good insight is only as good as it is shareable, however, and to enable better sharing any Alteryx analyst can take advantage of their Workflow Dependencies to simplify input or output path dependencies in shared workflows.
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:
This is Part 1 of the Alteryx Gallery Series - The Gallery Series only refers to the Alteryx Server where you can host your Private Alteryx Gallery. This Article with refer to 'Alteryx Gallery' as a privately hosted Gallery on the Alteryx Server.
Part 2 Alteryx Gallery Series - The Lay of the Land 101: User Edition Part 3 Alteryx Gallery Series: App Publishing & Version Control 101 - Alteryx Community
Part 4 Alteryx Gallery Series: How to make the most of your Alteryx Gallery – Use Cases
The Lay of the Land 101: Gallery Admin Edition
The Gallery Admin is the Godfather of a Alteryx Gallery. The Admin is less involved with the workflow content of the site and more involved with management of users and their overall experience on the Gallery. This article will help the Gallery Admin navigate the Admin settings within the Private Alteryx Gallery.
Each section of the Admin interface is outlined and described below:
A Subscription is another name for a Studio. A Studio allows Alteryx Designer users to publish and share workflows privately within their organization.
Each user that creates a Gallery account is automatically given their own subscription which can become a Private Studio if the user permission level is designated an Artisan. As a Gallery Admin you will have to make this user an Artisan, take a look at the ‘users’ section below which details how to do this.
Each Studio includes a limited number of artisans and members – assigned by the Gallery Admin.
Once a subscription has been created, it can be deactivated but not deleted. Gallery Admins are the only ones with permissions to create subscriptions.
Creating and adding users to Private Studios
To create a studio (subscription) click on ‘subscription tab’ and click on ‘Add New Subscription’. You will now have the ability to create a studio (subscription)
This will be the subscription screen below:
Gallery Admin can create studios as well as manage the users and workflows assigned to them.
There are numerous options when creating a Subscription or Studio:
Private Studio Name: The name for the studio, which displays at the top of the page when the user clicks “Private Studio” on the left menu of the Gallery page.
Contact Name: The person who owns and manages the studio.
Free: Artisans and Members who are part of the studio can run workflows in the Public Gallery
Paid: Artisans who are part of the studio can publish and run workflows in that studio and Members can run workflows
Artist Seats: The number of Artisan seats available in the studio. Artisans can publish, run, and share workflows.
Member Passes: The number of Member seats available in the studio. Members can run workflows in studios for which they have been assigned.
Expiration Date: The date the Paid subscription expires. When a paid subscription expires it is downgraded to a Free subscription.
API Enabled: Access to the Private Server Gallery API is enabled by default. The Gallery API supports the ability to authenticate, list apps, run apps, and retrieve app results using an API Key and Secret which are accessible via Gallery Settings.
Disable user sign-ups: Limits who can sign up for your Gallery. Forces users to be “invited” to join the Gallery.
Allow anyone to run public workflows without singing in: If this option is selected, anyone with access to your Gallery URL may run workflows that are available on the Home Page.
This is how to add a user to a studio (Subscription). You need to take the subscription key from the ‘subscription’ section (above screenshot) and add this into the studio key within the User profile below.
When a user now publishes from there Alteryx Designer to the Alteryx Gallery they will see the studio name they are associated with. This is linked by the same subscription and studio key.
Users can only be part of one Studio at a time.
Gallery Admins can add users directly to the Gallery or they can send an email inviting user to log in and add themselves via the Join now form on the main page of the Gallery.
User permissions are managed in the users profile (or in “Permissions” if using Windows Auth) as well as in their assigned studio.
The new user is added to the Users page. The users name, email (for Built-In Auth), and name of their studio display in the user box as well as icons that represent their permissions.
When using Built-In Authentication, the Gallery Admin can designate the permission level of users before they sign up to Alteryx Gallery on the users tab
Built-in Permissions Screen (Users permission level for the whole Alteryx Gallery)
Windows Auth Permissions screen (Users permission level for the whole Alteryx Gallery)
Gallery Admins can manage user permissions in the Permissions page when using Windows Authentication only.
A default permission level (No Access, Viewer, Artisan, Curator) can be set for any new or existing user who joins the Gallery.
Admin Tip: Set the default permission to No Access or Viewer if Content Security is of chief concern. This will require users to be invited to run workflows on the Gallery
Once a user has been created, it can be deactivated but not deleted.
Gallery Admins can add workflows, apps, and macros to the Gallery and manage their icon, description, Run Mode settings, and exemptions.
Admins can also create “tags” and associate them to workflows so searches within the Gallery return better results.
Run Mode: Determines the level that workflows are permitted to run in the Gallery. Workflows that contain certain tools or access may need to be blocked. The default Run Mode is selected when the server is configured.
Unrestricted: Any workflow can be run.
Semi-Safe: Workflows using Run Command, Download, Email, “R”, or Events will be blocked.
Safe: Workflows using above tools or accessing/storing data on non-local drives will be blocked.
Requires Private Data: Indicates if the workflow requires licensed data to run.
Private Data Exemption: An exemption can be given to a workflow with private data so that it can be run.
Gallery Admins can create new districts and modify the district name, description, and icon image. Once the District has been created, it will be displayed below the “Home Page” menu option on the Gallery.
This tab within the Gallery Admin section is only available for Alteryx Server Version 11.0 and above
This tab will give you the option to pre-define credentials that can be sent down to designer users
This will help when managing access and Alias creation on both Designer & Server
DSN-less connections will run straight away. DSN connections will need to be set up on the location machine and server machine
Set workflow credentials which can be use when running a workflow from the server. These will also appear in the designer when uploading/scheduling a workflow
Click on 'change; and you get the following options
Use Default Credentials
This will use the server service account details to run workflows if nothing has been set in the 'Run As' section of the Server System Settings
Require User Credentials
When running the application from the Alteryx Gallery the users will have to enter in credentials
Allow users to set options for credentials option
The best practice would be to ask users for their credentials so they can only access the data they have privileges for within your internal IT environment.
The interface will look like the screenshot below. When uploading an App from your Alteryx Designer (File>>Save as>>>My Company's Gallery) you can click on 'Workflow Options' & 'Set Workflow Credentials'.
This will give the user three options
'User is not required to specify credentials' - The application/workflow will run as the default Alteryx Server user account (Run As settings in system settings on server config).
'Always run this workflow with these credentials' - The user can enter the credentials at this point, however preset credentials can be added within the Alteryx Gallery Admin section ("Workflow Credentials")
This is where you can give users permission to schedule workflows they upload to the Alteryx Gallery. This will allow all artisans to schedule workflows.
The server can send email notifications for various events. The SMTP server information is entered when the server is configured. Gallery Admins can manage the types of notifications and the message text.
Admin Tip: Disable notifications so your users don't get spammed by the Gallery.
Customize your Alteryx Gallery!
Banner Ads can be added to be displayed below the search bar on the Home page. Uploaded Files can be added to the site to which Gallery Users can access via Links.
You have the ability to change the Gallery Name, Logo and Color Scheme within the web Browser.
This is often used to house FAQs, Help & Links
Add link to your Page or URL in the Header or Footers.
If you have any outstanding questions please reach out to our Alteryx Support
Alteryx Server provides a fully scalable architecture that allows an organization to scale Alteryx to automate data analytics, tackle bigger projects, process larger datasets and put self-service data analytics into the hands of more decision makers. From scaling Worker nodes to Gallery nodes to the MongoDB persistence layer, Alteryx Server allows organizations to efficiently manage their automated and self-service data analytics needs.
Question The below question was originally asked in the Discussion boards and comes up somewhat frequently from Server users:
Where I'm left scratching my head is how to best set up Gallery, manage permissions, and manage schedules. In an ideal world, I guess I'd see it going like this:
Developers create workflows & upload to private gallery
Admin (me) updates connection strings and performs cursory review before moving it into a shared area. Developers should be prevented from doing this.
QA team reviews and gives signoff.
Admin (me) moves to a shared area (a collection?) and schedules the workflow as needed. Developers sho uld be prevented from doing this.
Is this approach feasible given the functionality of Gallery? For now, it seems somewhat all-or-nothing to me. If I make somebody an Artisan, it seems like they can publish things to the gallery, schedule workflows, etc. But I may be completely missing something here.
Also, I'm using Windows authentication and I don't see any way to add users to a Subscription. There's literally no button below the Artisans & Members boxes. How do I do this?
Answer The below answer was provided KoryC:
What you're wanting to do is very similar to what we see other customers looking to accomplish - essentially, better and more granular control over what users can do within given projects, and a promotion process of workflows. Today, our Gallery does indeed, as you mention, provide the artisan access as a sort of all or nothing type of deployment. So unfortunately, the level of access control you're looking for today is not yet available, but it is on our roadmap and something we are actively looking at for a future release - this is one of our top priorities.
So today, the best approach is indeed to make those developers artisans. Yes, this will enable them to make things public or share them even if they shouldn't, but there are still administrative capabilities, such as removal of workflows, that can help in case such accidents or activities occur.
And as for the user-to-studio management in Windows Auth mode - we're looking to get that button added for an upcoming release, and on top of that, taking a good look and building out some better and easier ways of managing users in Windows Auth mode in general, much in alignment with how we want to make user and gallery management easier in the future.
Let me know if this helps. I know it's not the ideal answer you'd want today, but we are looking to make some significant improvements here. I'd also greatly appreciate any time you may have to go over features like this and to get more direct feedback in the future too!
As far as your question regarding Windows Auth vs. Built-in - no, it's not required to use built-in for subscription artisans (though members don't make much sense in a Windows Auth environment). It is, however, trickier to manage, as you've discovered. The facilities for managing studios-to-users in Windows Auth are lacking at the moment, and it's an area we're looking to improve. Copying and editing the subscription key is indeed the only way. And yes, only one subscription per user - though this is another area we are looking at expanding upon in the future.
There is a button in v10.5 to add artisans to a studio, but not for members, which will likely ultimately go away, at least with Windows Authentication deployments.
For more information about Gallery Administration and setup, take a look at the following article. The link goes to the first of a four part article series:
Alteryx Gallery Administration
As more and more users become familiar with macros it is useful to know the potential ways of scaling and sharing these insights within your business.
Here are just a few ways to achieve this aim!
Push latest macros to users/automatically ensure that users are using the latest version
Alteryx Designer Desktop
Working with colleagues and sharing macros?
Within the Alteryx designer you can create your own macro ‘Parent Category’. When specifying a file path, you can choose either a local or a network/mapped drive. If you are building macros within a team and looking to share or update macros dynamically, having a mapped location will allow you to reflect any changes on everyone’s machines.
As a best practice when overwriting a macro it would be best to update the Meta-data tab (Within workflow properties tab) within the macro to add initials or details of what has changed. This can help you track who edited each version. Unfortunately, if you choose to use an overwrite function, you will be unable to control the version of the macro overtime.
Therefore, you may choose to add “V1” etc. to the macro when saving it to the network location. This macro parent category could then be used as a development location and then you could have another macro parent category where you publish production ready macros.
The Alteryx Server can adopt a similar outline to that of the Alteryx Designer however, it has a greater flexibility in terms of version control. When building a macro you can publish it with a description.
Then when a change is made to the macro you can press the save icon and add another description.
Within the gallery this is then reflected when you click on the macro and click on the version number. You then have the capability of running & publishing up and down versions.
Include macro pack as part of installation procedure for new users
You could zip all the macros together and get a user to extract them to their own macro folder (mentioned above).
Or to the Admin (C:\Program Files\Alteryx\bin\RuntimeData\Macros) or Non-admin (C:\Users\%USER%\AppData\Local\Alteryx\bin\RuntimeData\Macros) folders.
If you use the metadata tag when building the macro workflow it can land in that parent category as well.
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.
I have encountered a few situations recently that resulted in data loss because a backup of the Alteryx Server wasn't available. I can't stress enough the importance of regularly backing up your server. This article, the first of two-part series, will cover some options and best practices to ensure you have the necessary backups available in the event they are needed. Part II will focus on the embedded MongoDB instance provided with the Alteryx Server install. If you are utilizing a user-managed MongoDB instance, please refer to MongoDB's documentation for backup and recovery procedures at https://docs.mongodb.org/manual/administration/backup/.
Let's go over some widely accepted best practices for backing up servers and databases:
Schedule Regular Backups - Backing up consistently on a scheduled basis is key to ensure you minimize data loss and downtime. I recommend backing up nightly during non-peak hours to minimize potential impact to users and for the minimal amount of data loss in the case of a crash or another failure. If backing up nightly isn't feasible, scheduling weekly backups is also an option. The important thing is to set a regular schedule.
Keep Historic Backups for a Defined Period - Sometimes the unexpected happens and a backup fails, becomes corrupt, gets lost/deleted, or the issue isn't noticed immediately (causing the problem state to be present in the backups). Having historic backups available helps ensure you have a backup available, and allows you to choose one created before the issue began.
Store Backups on Network or SAN Storage - Storing your backups on the same server where the data resides runs the risk of those backups not being available during a failure event. Let's consider what happens when your server suffers a disk failure. If the backups are stored locally on that disk, then they are gone too, making recovery impossible. However, if your backups are stored on the network they would not be impacted by a failure event on the server.
Keep a Copy of the Backup Off-Site - This falls along the same lines as above. If the only backups are on a file server in the same data center as the Alteryx Server, and that data center suffers a disaster, you will lose both your server and your backups. Keeping an additional copy offsite will allow you to bring the server back up in the Cloud or at another data center if need be.
Validate your Backup Files - You should periodically check to ensure your backups are occurring successfully, and confirm that the backups are valid and usable. There is nothing worse than putting a backup process in place and then finding out after a failure occurs that the backups stopped working 6 months ago or that all of your backups aren't usable.
Practice your Recovery Procedures Regularly - Recovery drills allow you to become familiar with the restoration process and the amount of time needed to return to a fully functional state in the event of a disaster. Practice is also proven to reduce the occurrence of mistakes, and can save valuable time. I recommend running a recovery drill quarterly or bi-yearly.
Keep in mind that, in most cases, backing up the entire server including the OS and all data isn't necessary. In fact, it can actually significantly increase the average time to restoration. Instead, I would recommend backing up only the critical data and configuration files for the server. This is because it is significantly faster to clean install the server and necessary software, and then restore the backed up data/configuration than it is to restore the entire server. This is especially true in the case of virtual servers, as deploying a new virtual server takes minutes in most cases. These limited backups can also reduce the time it takes to complete and validate the backups and reduce the storage needs/costs involved in keeping those backups.
Part 2 - Alteryx Server Backup & Recovery Part 2: Procedures