Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Crew Macros versus API for Spreadsheet Processing

Highlighted
7 - Meteor

Dear all,

 

I have what I thought was a simple problem to which I can find no simple solution.

 

Problem:

I have a list of spread sheets that need processing. I have a map of spread sheet to macro name (each spread sheet has a unique requirement that requires a macro or workflow to be invoked – the creation of these I intend to outsource to the business support team). Hence for each spread sheet I need to invoke the appropriate macro, sounds simple.

 

The aim is for a business support team to write source to target mappings (amongst other transformations) and produce one macro per spread sheet type and deploy these in order to avoid having one single workflow that contains 600 hardcoded embedded macros.

 

There are two ways I can envisage doing this, the simple and the more complex.

 

Principles:

  • The Macros need to be as basic as possible in order for the business support team to be isolated from other non-functional concerns (finding the files, outputting to a database hence the choice of a macro)
  • Error handling needs to be included, and if one file fails, the process has to continue processing the others in the list.

 

Simpler Idea:

To invoke the Crew Macro List Runner for each macro (in this case a batch macro). For this I attempted:

  • Pass in a list of spread sheets that was relevant but you cannot pass in any parameters other than macro name so that didn’t work.
  • Considered a table structure in a database to run through to support the above (each batch macro reads it’s file names from the database), this doesn’t work however since 2 specific macros are required (breaks the first principle), and further if one of the spread sheets causes an error and the macro fails, the processing stops and the rest of the list gets ignored (thus breaking the second principle). This is the default behaviour of a batch macro.

 

Complex Idea:

Use the API to invoke the relevant macro – this requires some reading.

 

As the Crew macro seems to be so close to what I want I’ve been attempting to use it but have hit blockers. If anyone has an example of how to use the List Runner or similar macro to do this task then I’d appreciate a view.

 

Otherwise I will have to invest much more time in using the API on the Gallery.

 

Many thanks

 

Ian

Highlighted
Alteryx
Alteryx

If 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.  In your case you can set them up so if one macro errors, the process will continue.  May be a better soltion than the API.

 

Great, so how do I do it?  See the following Community posting for more information.

 

 

Highlighted
Alteryx
Alteryx

Hi @hursic,

 

I agree to check out the solution that @WayneW has mentioned. I have seen similar to this implemented in a few cases where a module checks for a file every 5 minutes (This can be more frequent or less frequent...). Depending on what file is present in the directory that is being checked, the relevant process is launched. You can use the AlteryxEngineCmd as suggested or you can add them to the scheduler to be processed as documented here. Either way, you may need to create a batch file to make the module name dynamic.

 

If you do need to go down the route of the API, then each of the macros/modules/apps would need to sit on the gallery anyhow.

 

Kane

Highlighted
7 - Meteor

 

Hi,

 

I appreciate the idea and the posting of your responses, but maybe it’s beyond Alteryx’s appropriate capabilities.

 

I want each module of work to be entirely encapsulated and include the work it needs to do, the error handling and to support the ability to effectively unit test them (a drawback I’ve now discovered when promoting Macros to the gallery – I cannot run them independently). There are 6 other workflows surrounding the spread sheet processing step, the spreadsheet processing is the only set of workflows that I need an engine to decipher which one to call.

 

Executing the Alteryx.exe with the macro/workflow as a parameter is interesting and would allow me to call the appropriate macro/workflow for each spread sheet, but how could I pass in the name of the spread sheet ? There will be 100+ spread sheets in the directory, so I guess I’ll need a command line parameter to the workflow, is there a tool for that ?

 

How do I update the command line to change the macro being called ?

 

thanks

 

Ian

Highlighted
Alteryx
Alteryx

Hi Ian,

 

There are plenty of cases of this kind of process being executed at clients (at least 3 that I know of here in Australia), but there are a couple of decisions that need to be made, in order to work out the best method.

 

  • How much instruction do you want to give to each of the business units? In order to have error checking, logging etc, I advise some element of standardisation.
  • Are these going to be Macros or Apps or Workflows?
    • For them to be Macros, they all have to be in the same stream and then the data pointed to the right macro, Note: The only possible way that I can think of to have the macro 'chosen' rather than the data just streamed to the right one, is to build a bespoke module for each run of each macro (actually using Alteryx to build the XML that makes up a module). It would be fun to build, but not the general advice.
    • If they are Workflows, then there is no variability in the filename.
    • That leaves Apps, as in the example attached
  • Ideally, having each of the processes on the gallery as apps and using the API to call them is going to give the best results, best logging, best version control etc, however, it's not the easiest or quickest solution to get something working.

 

I have attached a zip file that is an example of one possible way I could see this working. The reason that I haven't packaged it, is in case that changes some of the folder links. Keep in mind that you can make each element of this more dynamic, as I have just quickly got it to work with the filestructure that I built. This could later be transitioned to the gallery as well if it was getting unwieldy.

 

I have a Run Command at the end creating a batchfile that then kicks off each app. You could most likely put this into a List Runner Tool instead, I just don't have the time to investigate the correct syntax. However, this will help.

 

Using the API you would be able to do this with better version control and logging as all runs would be saved on the gallery, it would just require a bit of effort to get useed to the API.

 

Kane

Highlighted
7 - Meteor

 

Hi Kane,

 

Thanks for your example and input. I’ve got your workflow working on my desktop and it all makes sense and could be an initial way around this problem, very good. As you state it’s not as sustainable as using the API but at least it’s documented and more easily implementable. There are aspects that I’d have to think about such as error handling, monitoring etc. I am however having trouble deploying into the gallery, the temporary directories are causing issues.

 

When I have my local copy initially all is well. When I attempt to publish my workflows to the gallery, Alteryx hangs at validation. When I try to run it again locally the log file contains “Error - Alteryx Engine: The Feature "API" is not licensed.” And then becomes invalid locally. If I leave the desktop attempting to publish to the server it hangs indefinitely.

 

How can I debug the publish function ? the gallery log files are locked as the validation hangs, and now my collections are lost and the main gallery screen presents “an error occurred” –oops !

Highlighted
Alteryx
Alteryx

Regarding the Gallery errors in your last 2 paragraphs, that is best off contacting support so they can troubleshoot. I have never seen the publish function hang.

 

Deploying into the gallery may cause issues as the workflow directory is then a temp directory. I find that when deploying to gallery, all directories would need to be UNC.

Labels