Feb 3, 2014

ETL Application Comparison, Part 6

We are having a look at an application called AutoMate 9. AutoMate is another application that, like Linx, allows the user to build a program in a procedural style. AutoMate in fact works very similar to Linx, so we will highlight the differences between these two applications.

AutoMate features a large variety of Actions that can be used. This means that the user can do a lot more with the application but this comes with the trade-off that it becomes harder to find the more common and practical Actions. A plugin-based approach might have turned out better: include only the most common components by default, and offer an easy way of browsing a large directory of more specialized components to add to the library as they are needed.

Like Linx, AutoMate also allows the use of expressions in most of the Actions, and AutoMate features a good expression editor that aids users who are not too familiar with the language. AutoMate also features a Script Action which allows the user to write a block of code to execute. The language used for expressions and statements is BASIC, whereas Linx uses C#.
AutoMate features an appealing visual editor, which highlights the code block of a control structure when that control structure is selected. The editor is very verbose and displays very descriptive texts for the actions such as
Create a variable named "inputdirectory" with an initial value of "C:\Projects\ETL Framework Comparison\data\input".
Whereas this causes a program to be very descriptive, the program quickly becomes very long. When comparing an AutoMate program next to an equivalent Linx program, the AutoMate program looks very long-winded, whereas the Linx program appears much more concise. Linx allows the programmer to give each statement a custom name, which leaves it to the programmer to assign sufficiently descriptive names to each statement in order to make the program more readable.

Implementing the data loading task in AutoMate was fairly straightforward, except for reading the input text file. It looks like AutoMate offers no easy way to read a CSV file. Thankfully, there was a sample program that showed how to do it using SQL through the “Microsoft.Jet.OLEDB.4.0” OLE DB provider, but it is tricky. Here is what the program I’ve built looks like in the visual editor:

AutoMate takes a similar approach to scheduling tasks as Linx does with a Windows Service that starts with Windows. It comes with a separate application, Task Administrator, that facilitates the scheduling and monitoring of the available tasks. Automatic task runs are set up via “Triggers”, which include Schedule, File System, and Startup, among others.

AutoMate looks like a great tool, but has one critical impediment that I could not overcome with the data loading problem that I’ve implemented. The process takes a very long time to run. It takes about 3 minutes to import the 5 000 accounts, whereas Linx takes only 10 seconds. AutoMate does not advertise itself specifically as an ETL tool, so maybe AutoMate is intended for other types of automation tasks which might explain the inferior performance.

No comments:

Post a Comment