Beyond Alteryx Macros – Creating an Analytic App Installer for the Abacus AddIn

Final installer

This started out as a fun experiment to see if it was possible to replace the current PowerShell based installer for the Alteryx Abacus function library with an Analytic App, but I like the result so think this will be the new installer for it! Along the way used some GitHub REST APIs and a few tricks for building Analytic Apps in Alteryx.

The final installer is available either from DropBox or GitHub (right click on Raw and Save Link As… to download from here).

The new installer has currently only been tested on Windows 10 in v11.7 of Alteryx. It needs the Expand-Archive PowerShell cmdlet which is only available in recent versions of Windows.

Goals

Currently, the PowerShell installer does the following:

  • Find Alteryx install locations (both User and Admin installs)
  • Unblock the files for the add-in (as downloaded from the web, Windows blocks by default)
  • Copy these into the FormulaAddin folder inside the install directory
  • Promote to Administrator to get around UAC (regardless of whether there is an Admin install)

The batch file version is the same but written as a batch file. Also, there are uninstall scripts as well which remove the XML and DLL files as needed.

The goal for analytic app takes this process a little further:

  • Single Analytic App to install/uninstall any released versions
  • Obtain release list from GitHub
  • Automatic download of zip file
  • Download and view of PDF function manual
  • Only elevate to Administrator if needed
  • List of files to be uninstalled controlled from GitHub (so no new workflow if something added)

Also for compatibility reasons I want to minimize the use of PowerShell and keep as much as possible within Alteryx. The current version only uses PowerShell to elevate to be an administrator at the end and to expand the zip file. I plan to do another version which will download an executable to extract the zip, but that will be for a later iteration.

The Workflow

The next sections look through all the parts of the workflow used to install the add-ins:

Getting Version List

Workflow to download the release list

GitHub has a REST API for accessing the list of releases, which will return a list of releases in JSON format. The example below shows the structure for the Abacus add-ins:

[
  {
    "url": "https://api.github.com/repos/jdunkerley/AlteryxFormulaAddOns/releases/7310783",
    "assets_url": "https://api.github.com/repos/jdunkerley/AlteryxFormulaAddOns/releases/7310783/assets",
    "upload_url": "https://uploads.github.com/repos/jdunkerley/AlteryxFormulaAddOns/releases/7310783/assets{?name,label}",
    "html_url": "https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/tag/v1.3",
    "id": 7310783,
    "tag_name": "v1.3",
    "target_commitish": "master",
    "name": "Roman Numerals, Student T, Chi Squared and a few other bits",
    "draft": false,
    "author": {...},
    "prerelease": false,
    "created_at": "2017-08-07T21:17:54Z",
    "published_at": "2017-08-07T21:40:27Z",
    "assets": [...],
    "tarball_url": "https://api.github.com/repos/jdunkerley/AlteryxFormulaAddOns/tarball/v1.3",
    "zipball_url": "https://api.github.com/repos/jdunkerley/AlteryxFormulaAddOns/zipball/v1.3",
    "body": ...
  }, ...
]

The workflow downloads the list of releases for the Abacus addins and then parses out the name, tag_name and url out of the JSON for each release. Using a standard Alteryx macro trick, it then cross-tabs these so that the tag_name and name becomes a column header.

Versions

This can then be fed into a drop-down interface tool allowing the user to pick the version they want to install. To support uninstalling it also adds an ‘Uninstall’ column:

Version List

Alteryx Install Location

Getting Install Paths

The next section of the workflow needs to find the installation location of Alteryx. Steeling from the techniques used in the old PowerShell version, this can be read from the registry:

  • Admin Install: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\SRC\Alteryx
  • User Install: HKEY_CURRENT_USER\SOFTWARE\SRC\Alteryx

Using a formula tool, Alteryx can read the value from the registry using the ReadRegisgtryString function. Eliminating empty string determines the install locations of Alteryx on the machine, which are then presented to the users as a list box (again by converting from rows to columns) allowing them to specify which versions of Alteryx to install the add-in to.

Getting Install Paths

One small trick is done on this to allow for the user selecting not to install anywhere. An extra Temp column is added. In this case, the analytic app will download the release and just display the function manual.

Creating the Uninstall script

The first part of the process creates a list of files that it wants to uninstall and then uses this to generate a set of batch file commands.

Uninstall Script

In order, to externalise the list of files from the workflow, I chose to create a page within the Wiki section of the Abacus GitHub project – https://github.com/jdunkerley/AlteryxFormulaAddOns/wiki/FileList. This is a formatted webpage but I would like to access the raw MarkDown behind it, and of course GitHub obliges. The raw wiki page is available from https://raw.githubusercontent.com/wiki/jdunkerley/AlteryxFormulaAddOns/FileList.md. In this file is just a list of file name proceeded by a -. This can be tokenized using a Regex tool to produce:

Uninstall Files

After this, it is a case of combining with the paths to the request Alteryx install locations. There is a function FileAddPaths which you can use to combine folder and file paths into one. Then using a filter tool with the FileExists function, we can produce a list of files to remove:

FileExists(FileAddPaths([InstallDir], [FileName]))
AND
!IsNull(Mode)

Finally, convert the full filenames into a DEL command using a formula tool:

'DEL "' + FileAddPaths([InstallDir],[FileName]) + '"'

The output of this section looks like:

Uninstall Commands

I kept the User or Admin for each line as later will use the presence of any ‘Admin’ rows to determine if it is necessary to promote to be an administrator.

Enabling and Disabling Sections

So far all parts of the workflow will always run. The next section for installing is only needed to be run if the version selected is not ‘Uninstall’.

Conditional Sections

To make this work I needed to do this in two different ways.

  • Disabling a container
  • Detouring the workflow

In my initial testing, I hoped to just disable containers but found it caused issues with the union tool later in the process which would no longer receive any input from that section.

First step for both of the above methods is convert the output of the DropDown box from a field name to a boolean (True if equal to ‘Uninstall’). This is done using a Condition tool which will return either true or false. After this an Action tool is needed to either disable the container or detour the workflow. The configuration for both is shown below:

Action Tool Configuration

Downloading and Extracting the Files

Download

The first part of this section just downloads from the assets_url for the selected release. This contains a JSON structure of all the files associated with a release:

[
  {
    "url": "https://api.github.com/repos/jdunkerley/AlteryxFormulaAddOns/releases/assets/4523063",
    "id": 4523063,
    "name": "AlteryxAbacus.v1.3.0.0.Tests.zip",
    "label": null,
    "uploader": {
      "login": "jdunkerley",...
    },
    "content_type": "application/x-zip-compressed",
    "state": "uploaded",
    "size": 2477226,
    "download_count": 27,
    "created_at": "2017-08-07T22:00:58Z",
    "updated_at": "2017-08-07T22:01:12Z",
    "browser_download_url": "https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/download/v1.3/AlteryxAbacus.v1.3.0.0.Tests.zip"
  },
  ...

The specific fields I am interested in ar the name and the browser_download_url. The usual JSON Parse, Regex and Cross-Tab can easily transform this JSON structure into a table:

Download

If this section is detoured (due to it being in Uninstall mode), then a single record file list is produced with an empty name and URL but with the same set of columns and types.

After either route, this list of files is then fed into the installer section of the workflow:

Installer Flow

If not detoured (again controlled by the condition) then this will download the release zip and save it in the workflow’s temp directory. It will then run PowerShell to extract the zip file, and scan the files produced. The configuration of the command tool looks like:

Installer Command Tool

The Write Source is just to a temporary CSV file which is not used by the process. As the Download tool was configured to save the zip file always as Install.zip, the PowerShell commands are fixed as:

DEL Install -Force; 
GCI Install.zip | Expand-Archive -DestinationPath Install; 
ls Install | Set-Content files.txt

This is a limitation of the current version that it depends on the Expand-Archive cmdlet. I plan to use 7-zip but haven’t had time to integrate that yet.

The last command lists all the contents of the zip and writes this into a file called files.txt. The command tool can then read this in as a non-delimited CSV file. Picking just the XML and DLL files out, the process then follows the same approach as the Uninstaller converting this into a set of batch commands:

Installer Commands

Executing the Commands

So far, we have got the process to produce a set of commands which will delete existing files and install the new version. The next stage is to execute them:

Run the commands

The first task is to union the uninstall and install commands. I also add a single echo command to the top of the list to ensure there are some commands. Each command also has whether it is a User or Admin command. The second part of this section just looks to see if there are any Admin command appending this to the command and removing the Mode column.

Commands to Execute

This table is then fed into a Block Until Done tool. One of the great features of this tool is that it will execute each output in sequence sending all records to output 1 before proceeding to output 2. A word of warning on using this: when I put a Union joining another part of the flow to this I found it made it that execution of Output 2 occurred before Output 1 completed.

The first output is used to write to commands to a batch file in the workflow’s temporary directory. The second output is used to execute this batch file. It does this using a formula tool to make the command@

IIF([Admin],
'@echo off
powershell "Start-Process -FilePath ' + 
"'" + FileAddPaths([Engine.TempFilePath], "Install.bat") + "'" 
 + ' -verb RunAs -Wait"', 
'@echo off
"' + FileAddPaths([Engine.TempFilePath], "Install.bat") + '"')

If there are any Admin command, then PowerShell will be used to promote the process otherwise the batch file is just run. The command tool writes this single formula to another batch file and then executes this process.

PDF Viewer

The final part of the installer is a viewer for the function manual. This is a relatively straightforward process using similar techniques to the previous sections:

  • The whole container will be disabled if running in Uninstall mode
  • The pdf files will be downloaded to known file names
  • The filenames are converted into START commands
  • These commands are written to a batch file and then this is run to open in the system PDF viewer

Summing Up

This was an interesting experiment in what was possible within an Analytic App with a few useful tricks:

  • Putting values in columns for use in Drop Down boxes or List boxes
  • Disabling Tool Containers or Detours controlled by Conditions and Actions
  • Writing batch files before executing them in a Command tool
  • Using a Block Until Done tool to sequence execution
  • Reading values from the Windows Registry
  • Using PowerShell to extract zip files and getting a list of files

Plus along the way a few useful endpoints for working with GitHub.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s