Project flow and Exception Handling

[bruce.gibbins]

Hello,

I appreciate that there may be numerous and imaginative ways to do this but I was curious on how to best handle exceptions and conditional branching. For example, a transformation calls a DB Command that fails for some reason or the source data is malformed and we want to move the source file into a rejected area for human review but still keep iterating through other files. We may also want to branch off and record success or failure in a database table for traceability and auditing.

I know there is the Halt transformation but I understand this will end the project. There is also a Status change transformation but there doesn’t seem to be any linked documentation for this.

cheers

[dgudkov]

Both exception handling and workflow branching are possible through [not so elegant] workarounds. Workflow branching will get “normal” implementation in version 3.7 (it’s actually the headline feature of v3.7) through the ability to conditionally derive tables. If the condition fails then all transformations in the derived table are skipped.

Until then here are the workarounds:

Workflow branching

In a simple case, when only calculation is necessary, workflow branching can be done by simply having two derived tables that start with “Filter by condition” transformations each. The conditions should be opposite (i.e. mutually exclusive). E.g. one filter is X >= 0, and the other one is X < 0. This would make either one, or the other table empty at a time, but not simultaneously. The branches are merged back into one table using the “Either” transformation.

See this example for simple branching:
if_then.morph (3.6 KB)

However when it’s necessary to run a side effect transformation (e.g. export file, run program, or execute DB statement), then the approach is a bit more complex, and requires a workaround. After filters insert the “Keep” transformation to keep top 1 row. If the derived table is empty, then the “Keep” transformation won’t change anything. If it’s not empty, then it will have only 1 row. Then use “Iterate” or “Iterate program” to call subproject or run a command. Because Filter + Keep will make either 0 or 1 row, there will be either no iterations at all, or 1 iteration. Not very elegant, but works.

See this example:
if_then_with_helper.morph (2.3 KB)
helper.morph (1.1 KB)

In a similar fashion it is possible to implement SWITCH…CASE kind of logic – just make a separate derived table + filter for each case.

Also check out this blog post: http://blog.easymorph.com/2015/08/conditional-workflow-in-easymorph.html

In version 3.7 this workaround won’t be necessary.

Exception handling

An error, or “Halt” transformation would stop project execution. The only way to handle project errors without stopping is to run the project from another project using the “Run program” transformation (instead of “Call”) that, for instance, executes morph.exe /c can_fail.morph in the command line. The “Run program” transformation can capture console outputs – both STDIN, and STDERR. By analyzing if STDERR output is empty or not, it is possible to detect if can_fail.morph failed or not, without stopping the project. Again, not so elegant, but can do the trick.

PS. As for implementing a “normal” way of handling exceptions, the ideas are floating around a monadic-style solution (in terms borrowed from functional programming). It could be a special kind of derived table (exception catcher) in which transformations are executed only when calculation in the source table fails. This would allow having different recovery logic in different points of transformation logic. But it hasn’t been finalized yet and no roadmap for it available as of now.

[bruce.gibbins]

Thanks Dmitry as always excellent ideas and feedback

[Red_Hoar]

I would like to be able to have 2 branches

1. would write to a db if a parameter is true
2. write to a cvs file is a different parameter is true

How can I conditionally execute a branch, but keep running the other branches?

Basically, I want to call from the command line with parameters,
If debug = True,
exit
if WriteDB = True
write to db
if WriteFile = True
write to csv

Table: “Imported table 1”

Step 1: Import all columns from Excel file… Other parameters: sheet or range name is “Merged”, skip 0 lines, when a floating point number can’t be converted to a fixed point number: produce error.

Load file which path is defined by parameter {filename}.

Table: “Table 1”

Step 1: Take table “Imported table 1”

Step 2: Keep only rows where the condition below is true and remove the others:

{WriteDB} = ‘True’

Step 3: Halt project execution if table is empty.

Step 4: Export table into text file {outfile}. Parameters: separator is comma, encoding is ASCII.

Table: “Table 2”

Step 1: Take table “Imported table 1”

Step 2: Keep only rows where the condition below is true and remove the others:

{WriteFile} = ‘True’

Step 3: Halt project execution if table is empty.

Step 4: Export table into text file {txtfile}. Parameters: separator is comma, encoding is ASCII.

Red

[dgudkov]

In your case you will need two subprojects – one that exports to DB, and the other one that exports to a CSV.

In this example one subproject exports to CSV and one to XLSX depending on whether the Export parameter equals to ‘CSV’ or ‘XLS’:

if_then_with_2helpers.zip (2.4 KB)

The trick here is to use Iterate Table and give it either 0 rows (i.e. empty table) or 1 row, depending on condition.

If the current table is empty, Iterate Table will NOT run subproject, which is what we need for a failed condition. If the current table has 1 row, then Iterate Table will run subproject only once – which is exactly what we need for a successful condition.

To keep only 0 or 1 row we use the Trim transformation. When it’s set to keep 1 top row:

If the current table is empty, it will keep 0 rows. Iteration won’t happen.
If the current table is not empty, it will keep 1 row. We don’t care what data is in that row. We just need to do exactly 1 iteration.

The reason we’re using Iterate Table, but not regular Iterate is because the current table has either 0 or 1 row, but the dataset to export is in another table (Dataset).

The subprojects receive the dataset to export from the calling project using the Input transformation.

PS. The current workaround works, but not elegant. Version 3.7 planned for release in late November will have conditional derived tables that will make conditional workflows much much simpler to arrange.

[mkhaja]

Hello Dmitry,

I just wanted to check whether this is still the recommended way to implement exception handling in EasyMorph versions 4.7 or newer. In my case I have projects with many database queries, and they can often fail. I would like to be able retry the queries that failed while retaining the results from the successful queries.

Thanks.

[dgudkov]

Hi Mezan,

The current approach is to use the Call/Iterate actions in the error-capturing modes. Conditional branching is now much easier thanks to the conditional derivation and the “Skip actions on condition” action. Both techniques are described in our tutorial article “Conditional workflows”.

Here is a sample project that demonstrates error handling. It requires version 5.0 (sorry, don’t have 4.7 installed).

error-handling.morph (6.4 KB)

It works as follows:

The “Call” action in the “capture errors” mode returns a list of errors. If there are no errors, the list is empty. Two conditionally derived tables are configured to use mutually exclusive conditions (empty table or not empty) so that only one table is calculated and the other one is skipped in case of an error. And the other way around if there was no error.

In the case of the “Iterate” action (second tab), the action returns errors in a separate column. The two derived tables are configured to have mutually exclusive conditions to check whether the column has error values or not.

[mkhaja]

Thanks Dmitry, I’ll take a look at the Conditional Workflows tutorial and the example project. One issue I have is that I need to retry actions when they fail rather than capture errors and continue. Perhaps this is doable, but I haven’t looked into it yet.

[dgudkov]

For a simple retry technique that retries 2-3 times, I would just have 2-3 nested “error” branches that repeatedly called (after a delay) the same module that contains the main workflow. So the workflow would be:

With the Skip action, all the retries can be put into one table, so no derived tables are required. Something like that:

A workflow with more retries (5 or more) can be arranged with the “Repeat” action, but this would be less trivial as the action requires a bit of learning.