Joining tables with Envision

Published on by Joannes Vermorel.

When it comes to supply chain optimization, it’s important to accommodate the challenges while minimizing the amount of reality distortion that get introduced in the process. The tools should embrace the challenge as it stands instead of distorting the challenge to make it fit within the tools.

Two years ago, we introduced Envision, a domain-specific language, precisely intended as a way to accommodate the incredibly diverse range of situations found in supply chain. From day 1, Envision was offering a programmatic expressiveness which was a significant step forward compared to traditional supply chain tools. However, this flexibility was still limited by the actual viewpoint taken by Envision itself on the supply chain data.

A few months ago, we have introduced a generic JOIN mechanism in Envision. Envision is no more limited by natural joins as it was initially, and offers the possibility to process with a much broader range of tabular data. In supply chain, arbitrary table joins are particularly useful to accommodate complex scenarios such as multi-sourcing, one-way compatibilities, multi-channels, etc.

For the readers who may be familiar with SQL already, joining tables feels like a rather elementary operation; however, in SQL, combining complex numeric calculation with table joins rapidly end up with source code that looks obscure and verbose. Moreover, joining large tables also raises quite a few performance issues which need to be carefully addressed either by adjusting the SQL queries themselves, or by adjusting the database itself throught the introduction of table indexes.

One of the key design goals for Envision was to give up on some of the capabilities of SQL in exchange of a much lower coding overhead when facing supply chain optimization challenges. As a result, the initial Envision was solely based on natural joins, which removed almost entirely the coding overhead associated to JOIN operations, as it is usually done in SQL.

Natural joins have their limits however, and we lifted those limits by introducing the left-by syntax within Envision. Through left-by statements, it becomes possible to join arbitrary tables within Envision. Under the hood, Envision takes care of creating optimized indexes to keep the calculations fast even when dealing with giganormous data files.

From a pure syntax perspective, the left-by is a minor addition to the Envision language, however, from a supply chain perspective, this one feature did significantly improve the capacity of Lokad to accommodate the most complex situations.

If don’t have a data scientist in-house that happens to be a supply chain expert too, we do. Lokad can provides an end-to-end service where we take care of implementing your supply chain solution.

Categories: Tags: envision technical release