Block until outload complete

[dgibson]

I am developing a workflow to grab data from a variety of sources, restructure it and load it to a local SQL server followed by a call to a SQL stored procedure to perform some calculations based on the newly loaded data, then bring the results back into Alteryx for further processing. Where I'm having difficulty is in scheduling the SQL stored procedure to operate after the data has been uploaded in full.

I have experimented unsuccessfully with the Block Until Done tool, given it will not wait until the full upload has been completed before commencing the stored procedure. Similarly, I can play around with the Wait A Minute tool but would regard it as a sub-optimal solution. With the size of the data set potentially varying considerably from day-to-day it will be difficult to nominate a single time to wait without waiting unnecessarily for long periods on many days of the week. 

My reading of similar discussions is leading me down the path towards a macro involving regular status checks with the SQL server to determine if the upload has completed. Before I go down that route I'd like to explore any simpler options first. Can anyone suggest any simpler alternatives ?

Any ideas are welcome.

[willemb]

Try something like the below. You create a simple macro that's only purpose is to call the stored procedure. You then use a Block Until Done just before your Output that needs to complete first. You can probably skip the Record count that I show in the picture and just put your macro directly after the Block Until Done (output 2). The Record Count could ensure that you only submit one record to the macro though, so that in the case of a batch macro you don't call it thousands or millions of times. 

[dgibson]

Thank you for the suggestion willemb. In some cases my uploads of input data to SQL server can comprise of several millions of rows and, although I'm using a bulk upload, can take 30 seconds - 1 minute to complete. Am I running any risk by using the block until done tool that, although the data has passed through output anchor 1, it has not finished loading to SQL server before the row count is completed by output anchor 2 and the stored procedure is called?

[willemb]

I think you should be good - just make sure the Output tool is connected directly to the Block Until Done output anchor 1.

This is how I understand the block until done to work:

- firstly it blocks data from its output anchor 1 until all records have been processed upstream i.e. reached its input anchor

- then it blocks output from anchor 2 until all records have been processed by the tool connected to output anchor 1 (see message below, copied from the one tool example)

- then it blocks output from anchor 3 until all records have been processed by the tool connected to output anchor 2

Output anchor two returns this message in Alteryx's one tool example:

Message (14) This is the second message that is produced. It is connected to the second block until done output anchor and will wait for any tools connected to the first output anchor to complete.

[dgibson]

Thank you again willemb for the info. I have been experimenting with the block-until-done tool and am a little bit confused about what is happening. Connected to output anchor 1 on the block-until-done tool is an output tool to perform a bulk upload onto a local server and connected to output anchor 2 is a row-count tool as you have described. Although the data set is successfully passing into the block-until-done tool the workflow simply ends after doing so and nothing is passed out of output anchor 1. Without knowing the mechanics of any behind the scenes processing in the BUD tool, on the surface it's as though the tool is confused about whether and data will be processed downstream of output anchor 1 and is simply giving up and ending the workflow. Do you have any ideas about what might be causing the behaviour I'm seeing?

[willemb]

That sounds odd. Can you confirm a couple of things:

- your workflow settings aren't set to "Disable all tools that write output?" Your output tool will look like the picture below if that is the case. 

- you're not seeing any results in the Results pane when you select the BUD output #1 anchor i.e. you're sure it's blocked at that point and not at the Output after the BUD.

- your workflow finishes successfully

[dgibson]

Odd indeed. I confirm yes to points 1 and 2. I have even attached browse tools to both output anchors and nothing is showing up. Regarding point 3, the workflow simply stops when it hits the stop until done tool, so the subsequent steps to call the optimisation are not activated. A 'workflow finished running in XXX time' message is displayed in the results window as per normal despite multiple downstream steps being skipped.

To add some extra context, my workflow is long with approx. 1400 tools prior to the BUD tool. From my reading about BUD it will attempt to determine if up-stream workflows are complete before it will activate. Could it be getting confused by an upstream workflow and giving up?

[willemb]

"Confused and giving up" should have resulted in an error in my estimation. It sounds like unexpected behaviour to me, so perhaps best to log a call with Alteryx support. 

With so many tools in one workflow I would also consider breaking it down into its parts with macros. Not sure it will solve any problems in your specific case but I imagine it will at least make troubleshooting a little easier. 

[dgibson]

Makes sense, I will log a ticket with Alteryx support and see if they can shed any light. Thanks for your input.

[dgibson]

As it turns out, Alteryx Support have confirmed 'the BUD tool is not supported due to the AMP engine'. In any case, a colleague has suggested an alternative approach using a dynamic input tool and, by combining it with the wait-a-second tool I have been able to get the job done. Although it's a bit of a blunt approach, potentially involving longer delays than are necessary, it will be good enough for now. Should the BUD tool become available with AMP I can explore using it for a more precise solution.