Apr 25, 2017

Version 3.5 is out

It's happened, finally we're releasing version 3.5! It took us 2 months longer to release it than it was originally scheduled. The visual condition builder turned out to be a more complex task than I initially assumed. Databases deal with dates, non-integer numbers and Boolean values sometimes in very different ways with lots of nuances. The fact that EasyMorph operates with decimals instead of floats, and uses so called OLE Automation dates (same as in Excel) made things even more complicated. But now all the issues have been ironed out (at least those that we're aware of currently). Here is what's new and exciting in version 3.5:

Visual Condition Builder

This is the headliner feature of this version. Now it is possible to design filtering conditions in queries visually. Just drag a field from the list on the left or the preview datagrid, onto the filtering area.

Using a custom WHERE clauses is still possible.

For those who prefer writing SQL queries manually the Query Editor helps saving time -- design a draft query visually, then switch to Custom SQL and the visual query will be automatically converted to a SQL query which you can further modify and run right in EasyMorph.

In future releases, visual conditions won't be limited to queries. We are planning to introduce them in EasyMorph transformations as well in addition to formula-based conditions, where applicable.

Delete / update database rows

In the previous release (v3.3) we introduced the possibility to export (insert) rows into database tables. With this release we're adding two more transformations that allow deleting particular database rows, thus enabling delete/update workflows:
  • "Delete database rows" transformation deletes rows specified by a filtering condition of query (column selections are ignored). Using a query in this transformation provides extra convenience as it allows previewing rows which will be actually deleted, before deleting them.
  • "Delete matching database rows" transformation deletes database rows where key fields match key fields in specified EasyMorph table. Using this transformation you can calculate a list of particular IDs and then delete only database rows with these IDs.

Transformations for large data volumes

EasyMorph keeps all datasets in memory and this provides a multitude of benefits such as the ability to instantly see results of any transformation, reactive calculation, and re-calculating only transformations affected by a change. At the same time, this approach has a downside – if a dataset doesn't fit RAM entirely it would require using one of two special techniques to process it: partitioning and pre-filtering. Partitioning means that we split the source dataset into smaller parts of the same structure, which are processed one by one (typically using iterations). Pre-filtering means that instead of loading entire dataset and then filtering it, we only load a few fields that are necessary for filtering as well as IDs (primary of foreign keys). Filtering produces a subset of IDs, and for those IDs full records are fetched from the source dataset.
  • "File splitter" transformation allows splitting (partitioning) large text files into smaller ones without loading them into EasyMorph. The smaller files can later be processed in EasyMorph one by one a usual way, typically using iterations.
  • "Select matching database rows" transformation loads only database records which ID exists in an EasyMorph table. This transformation can be used for arranging scenarios with pre-filtering described above.
These two transformations allow EasyMorph users to work with tables larger than RAM -- tens and even hundreds of gigabytes.

Other new transformations and changes to existing ones

  • New transformation: "Filter by search". Allows filtering text values by a substring, or using a regular expression.
  • "Match" transformation now has Fuzzy Matching mode. In this mode text values are matched if their edit distance (as per Damerau-Levenshtein) is within a specified limit. Fuzzy Matching helps match words with typos. For instance it would match "New York" with "New Yrok" or "New Yokr".
  • "Merge" transformation now has Full Join mode which is similar to SQL full join.
  • "Export to Excel" transformation now allows creating new sheets in existing spreadsheets.

New Filter pop-up

Starting from this release double-clicking a column header brings up the new Filter pop-up which allows viewing/searching unique values in the column as well as filtering (according transformation is created automatically). The pop-up has three modes:
  • Selection – explicitly select items that should be kept (removed). 
  • Search – only items that contain particular substring are kept (removed). 
  • Condition – use expression to filter items. Only items where the expression returns TRUE are kept. 
The Unique Values pop-up used in previous versions is discontinued.

New data sources

  • Added support for DB2 (LUW) SQL dialect in ODBC connections.
  • Added support for Vertica SQL dialect in ODBC connections.

What's next

In the future release (3.6) we will continue improving usability and closing remaining gaps in functionality. It will include importing fixed width text, loading multiple files without iterations, and regular expression matching. Version 3.6 is planned for release in June this. However, before that there will be another, no less significant event in May – we're releasing the first ever version of EasyMorph Server!