Jan 22, 2018

What's new in version 3.7.1

With version 3.7.1 we continue expanding the automation capabilities in EasyMorph together with making data transformation more convenient. Key novelties include:

Sending emails

Sending email notifications is a cornerstone automation capability. There is a number of scenarios that have become possible due the introduction of the "Send email" action in EasyMorph:
  • Notification about failed data quality checks. A workflow can send an email before it gets halted.
  • Rule-based notifications. A Server task can be scheduled to check regularly certain conditions (e.g. existence of a file, consistency of data, or timeliness of new submissions). If a condition is not met, the task sends an according email notification.
  • Per-user data publishing. Sometimes, it's necessary to deliver each user a customized copy of a report or data extract. Sending an personalized email with an attachment could do the job.
  • Log monitoring. A scheduled project or task can parse logs and notify about possible errors or unwanted events.
The "Send email" action is highly configurable and allows specifying recipients, subject and attachment path using parameters. Parameters can also be dynamically inserted into the message body. The message text can be HTML formatted (only in Plus/Pro editions).

We're also working on making EasyMorph capable of receiving emails and saving attachments.

Triggering Tableau Server extract refreshes

This release introduced the "Tableau Server Command" action which employs the Tableau Server REST API to perform various operations (commands) on a remote Tableau Server. In version 3.7.1 the only command available is extract refreshing for a data source or a workbook. This command is available for Tableau Server version 10.3 and up.

With the expansion of the REST API new commands will become available in the action.

Easier creation of new parameters

Now it's easier to start using a parameter instead of a transformation property such as file name. For this, select <Add new parameter> in the list of parameters in the property, and a new parameter will be created automatically. The name and value of the new parameter will be defined after the transformation property it for which it was created.

For instance, when changing folder path to a parameter in the "List of files" transformation, the new parameter will automatically named "Folder path" and its value will be the folder path previously  in the transformation.

Other changes

  • The new "Metadata" transformation offers a convenient way to obtain a list of columns of a table, as well as the total number of rows and columns in it.
  • The "File Command" transformation now has a new mode for cloning existing files.
  • The "Convert Data Types" transformation has a new mode for automatically converting any data type in selected columns to text.
  • New connector "Email Server" in the Connector Manager.
  • The Start screen has a refreshed look with more choices to start a new workflow.
  • A new two-column selector is now displayed when selecting a project table, parameter, or connector. The selector allows searching and filtering that simplifies dealing with large projects.
  • Transformations Call, Iterate, and Iterate table now display parameter annotations.

Read also Release Notes v3.7.1.pdf

PS. Notice that our posts start using term action interchangeably with transformation. Eventually, in order to minimize confusion, we will reduce the use of term transformation only to the actions that actually transform data in EasyMorph.

Dec 25, 2017

EasyMorph doubles down on data-aware task automation

EasyMorph has already been offering some task automation capabilities (e.g. parameters and loops, running external programs) that are missing in less sophisticated data preparation tools. We've been witnessing large data analytics vendors entering the data preparation market after realizing that good data analysis requires good data preparation. However, we at EasyMorph understand that good data preparation almost always requires good automation. Manual data preparation is not enough anymore because it increasingly takes too much time. More and more internal and external systems get involved into data analysis every day. More and more public data APIs opened. More and more files are received, processed, sent out, or uploaded. Traditional batch scripting doesn't help much because it's not data-aware. Too frequently data-related workflows depend on data itself. For instance, before processing a file extract it may be necessary to obtain the report date from the extract contents, because it's not available in the file name or timestamp. Or, data quality has to be verified before a file is copied, or uploaded into a database automatically. Sometimes data quality verification can require querying multiple data sources. Such logic can be non-trivial to arrange with scripting.

With almost 100 transformations (as of ver.3.7), EasyMorph is already well positioned for the new reality as it combines comprehensive data transformation features with certain elements of automation. However, we're doing a step further and doubling down on the automation capabilities in EasyMorph. The goal is to provide a convenient mechanism for automating routine data-related tasks, even those that might not be classified as data transformation at all, e.g. automated rule-based alerts and file transfers.

Some of the new features are already available in version 3.7 released recently, but more is coming. Here is what to expect:
  • Conditional IF/THEN/ELSE workflows (already available in 3.7)
  • Deep PowerShell integration (already available in 3.7) that opens the vast .NET ecosystem as well as Windows automation for EasyMorph users. With it you can, for instance, trigger VBA macros in spreadsheets, or fetch email attachments from your Outlook folder right from EasyMorph projects.
  • In version 3.8 we will be lifting the existing restrictions on transformation order. Currently an import transformation must always come first in a table. This restriction will be removed and any transformation will be possible to insert before or after any transformation. Tables will be able to start with a workflow transformation such as Download File or Run Program, and then have an import transformation.
  • In 3.9 EasyMorph Scheduler will evolve into EasyMorph Launcher -- a handy utility residing in the system tray with a list of frequently used tasks that either run on schedule or are triggered manually. You will be able to launch in two clicks daily routines, such as transferring files between computers, fetching updates from Amazon AWS, uploading data and refreshing Tableau Server workbooks or Qlik Server dashboards, triggering EasyMorph Server tasks, sending out emails and even text messages (SMS, for those in Europe). Think of it as high-level replacement for batch scripts.
  • Triggering various actions through public APIs in external and internal systems. For instance, extract refreshing in Tableau Server (coming in v3.7.1), reloading apps in Qlik Sense Server (coming in v3.7.2), various actions with Amazon AWS, Azure, Google Apps, Slack and other cloud applications.
  • Easier iterations (loops) that won't require creating another project (v4.0).
  • Event-based task triggering (e.g on file change), automatic email retrieval and attachment processing in EasyMorph Server.
  • Error handling.
  • Transformations renamed to actions.

All these changes will be gradually rolled out during 2018 with some features coming sooner, and some later. As always, we're keeping ears open for your suggestions.

Of course, expanding into data-aware task automation doesn't mean that the "traditional" data transformation part in EasyMorph will be neglected. There are many new ETL-related features on the 2018 roadmap but that would be a topic of another post.

Discuss this article on EasyMorph Community

Dec 18, 2017

What's new in EasyMorph Server 1.3


Previously all users could access all Server tasks as well as files in the public folder. Starting from this release EasyMorph Server introduces spaces in order to offer more granularity and control in managing access to tasks and files.

Each space is independent from other spaces, and can have its own:
  • tasks
  • public web-folder
  • connector repository

Spaces are managed in a new tab called “Spaces” visible only to the Server administrator.

Server spaces

Spaces allow limiting access to tasks using one of the 4 modes:
  • Disabled – tasks are entirely disabled and not available in this particular space
  • Lock task – a user can only trigger tasks, but not create or edit them
  • Lock project – a user can edit task schedule, task parameters, but not create tasks, or change the project in a task.
  • Full access – no restrictions, the only mode where new tasks can be created.

A combination of task access mode with one of the file access modes introduced earlier allows flexibly configuring spaces for different types of users and use cases. See the table below for a few examples:

Examples of spaces with different roles

Password-protected spaces

A space can be password-protected. In this case, a user should provide correct password in order to access tasks and/or files.

Performing operations through the API with password-protected spaces also requires providing a password. The command-line client and EasyMorph Server .NET SDK have been updated accordingly.

Remote administration

By default, the Server settings and space configuration is only allowed from localhost. However, it is now possible to enable remote administration. In this case, it is possible to configure the Server from a remote computer, after providing a valid administrator password.

Start/stop batch scripts

It is now possible to execute a batch script when the Server service is starting or stopping. Modify onstart.bat or onstop.bat located in C:\Program Files\EasyMorph Server\systemscripts accordingly.

This capability can be used, for instance, to map additional network drives on server start, or to send an email notification when the Server service is shutting down or rebooting.

Dec 9, 2017

Conditional workflows in EasyMorph

A conditional workflow is a sequence of actions that changes depending on a condition. For instance: IF loaded data has no missing dates THEN export it into database ELSE send an email to Pete. If we depict this workflow graphically, it would look as below:

When no dates missing, sending an email doesn't happen.
Workflow when no dates missed

When one or more dates are missing, exporting to database doesn't happen.
Workflow when at least one date is missing.


Such conditional workflow (branching) is arranged in EasyMorph using conditionally derived tables. A regular derived table is a table which replicates the resulting state (data) of its source table, and performs certain transformations with it as well as other actions. If you're not familiar with derived tables check out this tutorial article.

A conditionally derived table does exactly the same, except it only does it when a certain condition is satisfied. If not, then no data is replicated from its source table, and no actions/transformations performed -- all actions and transformations in such table are skipped.

The example mentioned above would look slightly differently, if we use branching similar to conditionally derived tables in EasyMorph:

Branching using conditionally derivation in EasyMorph.

Here in this example we have 2 conditionally derived tables. The conditions in the tables are inverse to each other: in one table the condition is "missing a date?" while in the other it's "NOT missing a date?". Therefore, depending on whether the loaded data is incomplete or not, either one table is entirely skipped or the other.

In an EasyMorph project the example described above would look as follows:
EasyMorph project with a conditional workflow.

The two rightmost derived table are derived conditionally.  Notice that one of the tables is skipped -- you can see it has different title bar color, and a derivation icon with red "X". In this table transformations were not executed, and it has no data.

This project detects if some dates are missing or not by calculating the difference between adjacent  dates. If no dates are skipped then the difference with previous date will always be equal to 1 for all dates. If one or more dates are skipped somewhere, then for the following date the distance will be 2 or more. We calculate the max distance, and merge it into the main table as a new column using the "Peek" transformation. Later, prior to exporting, the column is removed.

In the conditionally derived tables the conditions are as follows:

[Distance, days] = 1, for the upper derived table "Export to DB".

[Distance, days] <> 1, for the lower derived table "Send an email".

You can see that conditions are inverse to each other.Therefore, either one or the other derived table is skipped, but never the both are skipped or executed simultaneously.


The "Derive table" transformation has a switch that tells it whether it should be unconditional or conditional. In the latter case,  there are three possible conditions for derivation:
  • If expression evaluates to TRUE for each row in table
  • If the source table is empty
  • If the source table is NOT empty
In the screenshot below you can see properties of the "Derive table" transformation in the conditional mode:


If after the branching execution should continue regardless of which of the derived tables was executed, then we need to join the two branched flows back into one flow. This can be done using the "Either table" transformation. The transformation compares two tables and picks one that is not empty. Since a skipped conditionally derived table is always empty (i.e. has no data), the "Either table" transformation will always replicate the resulting dataset of the table where the condition was satisfied and transformation were executed.

Conditional workflow with continuation.

In some cases, there is only one branch before continuation. In this case the continuation is derived right from the source table (see below). Make sure that "Either table" is set up to give preference to the conditionally derived table if the source table is never empty.

Workflow with one conditionally derived table and continuation.

The examples about show using conditionally derived tables for IF...THEN...ELSE type of workflows. However, in the same fashion it is possible to arrange SWITCH...CASE type of branching that produces 3 or more branches.

Dec 4, 2017

Running PowerShell commands in EasyMorph

Starting from version 3.7 EasyMorph includes the "PowerShell" transformation. The transformation allows executing arbitrary PowerShell commands and scripts with a degree of integration with EasyMorph. The integration includes:
  • Using EasyMorph parameters in PowerShell commands
  • Capturing output and error sequences of a PowerShell command back into EasyMorph
  • Sending columns values as an input sequence for the PowerShell command pipeline
For the transformation and integration to work the host computer must have PowerShell v.3 (or above) installed.

Inserting parameters

Just like in the "Run Program" transformation, in "Powershell" it's possible to insert project parameters in curly braces right into the command text. For instance in the example below the command copies a file, which is specified by project parameter {Source file}, into folder C:\test. Double quotes added just in case the file path contains spaces.

Copy-Item "{Source file}" c:\test

Inserting project parameters into PowerShell command (click to zoom)

Note that parameter values are inserted only when the text between curly braces is an existing parameter name. If no parameter with such name then the curly braces and text remain as they are and no error generated. This is convenient because PowerShell commands frequently use curly braces for various expressions, therefore you can mix EasyMorph project parameters with PowerShell expressions.

Capturing output and errors

Output sequence of a PowerShell command can be captured back into EasyMorph. However, capturing in "PowerShell" works differently than in "Run Program" where the console output is captured. In the "PowerShell" transformation, capturing means importing the output sequence of the PowerShell command pipeline, not the host console output.

In the example below a PowerShell command is used to obtain a list of running Windows services, and import it into EasyMorph.

Get-Service |
Where-Object {$_.Status -eq "Running"} |

ForEach-Object -Member DisplayName

Capturing the output collection into EasyMorph (click to zoom)

Notice that because the output sequence is not a collection of basic values (e.g. number or text) but a collection of .NET objects. Therefore ForEach-Object is used to extract a member value of each output object.

The error collection is captured as separate column, and can be used for diagnostics or arranging a failover logic.

Sending column values as input sequence for PowerShell

It is possible to send column values as an input sequence for a PowerShell command pipeline. This input sequence is available through special PowerShell variable $input. In the example below a sequence from 1 to 10 is generated in EasyMorph, then sorted using a PowerShell command, and finally captured back into EasyMorph.

$input | Sort-Object -descending

Input sequence as EasyMorph column (click to zoom).

Notice that numbers in EasyMorph become integer or float numbers in PowerShell, text values in EasyMorph become text strings in PowerShell. In the screenshot above PowerShell sorted the input sequence as numbers, not as text (otherwise 1 and 10 would've been neighbors). Similarly, boolean values in EasyMorph are converted into booleans in PowerShell, and empty values converted into nulls.

Use cases

PowerShell is a very versatile scripting language supported by the vast ecosystem of .NET framework. Having EasyMorph integrated with Powershell opens access to large number of PowerShell cmdlets which help automate various tasks, e.g. advanced file operations, sending emails, computer management and administration.

EasyMorph transformations can effectively be inserted into PowerShell command pipelines, e.g. generate a list of files in EasyMorph, filter it using EasyMorph expressions, and then send the resulting list into PowerShell.

Finally, it becomes possible to write custom cmdlets (e.g. in C#) and run them from EasyMorph with EasyMorph parameters or column data.