Sample Batch Macro to manage order of operations

[patrick_digan]

I created the attached workflow to demonstrate the power of batch macros. Suppose you want to pull in live data and check it against the previous data that was pulled. For example, you could schedule a job that nightly checks what changes have occurred in a database/excel file/etc. To provide a simple example, the attached just checks the time the module was run against the time it was run previously. The idea is this: The generate rows tool will generate the current time.The input tool goes out to the test.yxdb and pulls in the time of the last run. It then joins the two times and saves a results.yxdb to show the time of the current run and the previous run. Lastly, you would save the current time to the test.yxdb so that its ready to run again. The dilemma: Without the help of the batch macro, the workflow would write to the test.yxdb BEFORE it reads it. This is a big problem, as you need it read the test.yxdb first, and the last step should be overwriting the old data in the file. The solution: Adding a batch macro in between the live data (in this simple case the time from the generate rows tool) and the output to the old data, and making sure you also add the old data input as well. The way a batch macro works, it forces all the input tools to be read before it proceeds to tools after it. This means it reads in the live data and the old data since they are both inputs into the batch macro. If you open the batch macro (right click on it and open), you will see that while the old data is an input, it actually does nothing with it. The live data just inputs to and outputs from the batch macro with nothing happening. The control parameter is required to make it a batch macro.  It's important that you pass only one row of data through the control parameter so that the batch macro runs once. While this workflow/macro is simple, the concept of using a batch macros in this way is powerful. As mentioned above, we use it to compare a live database against the previous night's data to check for changes. As another example, you could use a batch macro that outputs data to an excel file and then have a run command tool after the batch macro to somehow modify that file. The possibilities are endless!

[RodL · Accepted solution]

Patrick,

Good use of a batch macro!

Are you aware of the Block Until Done tool in the Developer category? In looking at your process and reading your process issue, I'm wondering if this would also suffice. I have used this tool in a workflow to read from an Excel file and then write back to the same file. Since Excel doesn't allow you to write to an open file, this resolves that issue. I'm thinking the same thing would work for your process.

Thanks for the post.

Rod

[andrewdatakim · Accepted solution]

Hi Patrick,

I experienced a similar problem with my workflow and I came across an awesome macro call "Parellel Block until done". It allow a two different data flows through the same tool and it blocks the start of the second flow until the first one finishes. It's from CRew Macros and doesn't require you to run a batch macro. They are an excellent source of macros (http://www.chaosreignswithin.com/), an Alteryx engineer told me about them. Check it out and let me know if you have any questions.

[patrick_digan]

@RodL: Thanks for providing a simpler way to accomplish my example! You are absolutely right that a block until done could be used in this situation. As you and Andrewkim80916 have demonstrated, there are many different ways to solve the same problem.

@Andrewkim80916:Thanks for pointing out the parellel block until done! I use the CReW macros but I never seem to branch outside the handful that I use often. 

---

[SeanAdams]

Hi Patrick & Rod,

I'm using batch macros to perform multiple steps of a classic data ETL to build a reporting data set.   The first one brings in latest rows from file; the second then updates one of the dimensions to add new dimension values; the third updates the fact table where rows have been changed; the fourth deals with brand new facts; and the fifth pumps out an audit trail.

2 questions:

a) This has to run in sequence - but I couldn't find a way to run this in sequence without using batch macros (and the batch macros encapsulate the functionality of each step making testing and controlling complexity easier).   Is there a different way to do this without resorting to the CREW macros?

b) For some reason, alteryx seems to struggle with the input & outputs to the batch macros - some of the macros show as having no outputs; sometimes no inputs; even though they are all configured identically.   Over time, the controlling canvas forgets which settings were chosen and they need to be set up again, or deleted and re-added.   Any changes ot the underlying inputs / outputs on the batch macros seem to cause chaos.  

  Have you experienced the same?   I'm even resorting to text-comparison of the acutal alteryx XML files to see what's happening here, but the answer is not yet obvious - and it seems like it shoudl be relatively simple (just pop an input & output iwth a single field, and the higher-level-controlling canvas shoudl be easily able to identify these inputs and outputs and allow me to sew them togeher into a sequence)

thank you both

Sean

[patrick_digan]

@SeanAdams Great questions! @RodL may have more to add to my musings below.

a) I can usually get batch macros or the crew runner macros to work pretty well for my needs. If you wanted to avoid macros, you MAY be able to get by with run commands and dynamic input tools. The run command tool is great if you want write one data stream out (via the write source of the run command tool) and then read in the next data stream (via the read results of the run command tool) and make sure it happens sequentially. The dynamic input is great if you need to input data sequentially. Depending on your situation, these tools may/may not work well. 

b) I have experienced your pain with all of the issues you are describing with batch macros. They are powerful tools, but they are cumbersome during development.

Patrick Digan