EasyMorph as an ETL for Tableau

Tableau is a great tool. I've been watching it for a few years and I like the way it's developing -- more sophisticated computations, better performance, faster visual engine. Tableau aims at analysts without technical background -- an audience which probably is most under-served in terms of tooling than any other category of office workers. All what they usually have for their data analysis needs is Excel, love it or hate it.

At the same time I struggled to understand the conceptual breakthrough behind Tableau. While it's often considered a prominent Data Discovery platform, when it comes to data transformation it's not significantly less dependent on IT staff than traditional Business Intelligence platforms. Just as traditional BI, Tableau needs well prepared data, preferably residing in a database. And when it comes to data manipulation in databases all user-friendliness ends -- last time when SQL was user-friendly was in 1980s.

In my understanding the reason why Data Discovery started outpacing traditional BI is that it can liberate business teams from dependency on IT folks. Data Discovery tools must be self-sufficient by definition. It means that users should be able to collect, clean, transform and prepare data for analysis themselves. And this is why they still love Excel -- because it makes it possible, because it lets them control. And that's why QlikView is more self-sufficient -- because it has its own ETL. While QlikView has its own pros and cons (personally I'm not a big fan of its loading script), but in vast majority of cases it has all you need to prepare data for analysis and visualization. But what about Tableau?

I've designed EasyMorph, an easy-to-use ETL tool intended for exactly the same target audience as Tableau -- knowledge workers without a technical background. Like Tableau, EasyMorph is highly visual and it doesn't require SQL skills or programming. Like Tableau, it's fast and lightweight, and it doesn't demand weeks of training. My goal is to make EasyMorph a perfect companion for Tableau and allow Tableau to become a truly Data Discovery platform, owned by business users. Today EasyMorph made one more step towards that goal -- we've added support for Tableau Data Extracts.

If you're a Tableau user, give EasyMorph a try -- maybe this is something that you were missing in your Tableau experience. Feel free to drop me a few lines with your thoughts on it. You can find my email in the right sidebar of this blog. Thank you!

UPDATE
In 2015 EasyMorph has become a Tableau Technology partner. It has got a server edition and deeper integration with Tableau Server.

UPDATE #2
Apparently Tableau understands the lack of built-in data preparations capabilities -- in October 2017 they've unveiled Tableau Maestro, a light-weight data preparation tool. You can think of EasyMorph as "Maestro on steroids".

Meet EasyMorph

A few days ago EasyMorph, my new data transformation tool, went out from stealth mode and now Technical Preview is publicly available for downloading. It's time to tell a bit more about what this is.

EasyMorph is the result of an attempt to rethink data transformation from scratch which gained momentum after my post in May 2013. There is a stereotypical understanding of what an ETL should be and it's inherited from traditional heavy-weight ultra-complex monsters like Informatica or Ab Initio. The thing is that this stereotypical approach poorly works for people without technical background -- those who we used to call business users. They don't know anything about join types and honestly they don't have to -- their heads are busy with business problems. At the same time the need to manipulate data becomes even more urgent as data discovery tools have become more popular and analytic systems become increasingly more often owned by business departments rather than IT staff.

Excel, which is typically used for data transformations by business folks, has its own drawbacks that you're perfectly aware of:

1) Excel is poorly suited for dealing with tables. Let me put it straight -- there are no tables in Excel. Tables are actually emulated with groups of cells. From Excel's standpoint there is no difference between column header, a column value or any random cell. Even worse -- Excel has no idea where a column starts and where it ends, which cells belong to a table and which don't. No wonder such emulation is prone to errors caused for instance by inaccurate stretching a formula over a range of cells.

2) Debugging and maintaining table operations in Excel is a torture. Since table as an abstraction is missing in Excel it's hard to address table columns simply by names. You won't see something like Price = Amount * Qty in Excel when it comes to table operations. When you deal with an Excel formula you have to constantly decode what every reference actually means (e.g. column AT is Qty and column BC is Price). While formulas is a rather questionable way of defining a calculation (but that's another story) it would only be a half of the problem. Things get really bad when formulas are applied from within a Visual Basic macro (which is far not a rare situatuion) -- in this case you might not even be able to see the actual column, since it could be created or modified somewhere else earlier in the macro. So you have to decode the formula while keeping in mind program logic of the macro! Something tells me that this is not something that people enjoy doing.

3) Excel is barely capable of multi-step transformations. Again, since the concept of table is missing in Excel, there are no table operations except maybe sorting and deduplication. At the same time sequentially applied transformations is the most natural way of transforming tabular data. This is what business users are trying to emulate in Excel. Just listen how they talk about it -- "Okay, Bob, what do we do with this data? Bob: First, get rid of all transactions with missing product names -- they're synthetic and we don't need them. Then for all the transactions with missing prices use the prices from the last year file. Once you get all prices in place -- calculate amounts. Don't forget to convert non-USD amounts to USD." and so on.

Inconvenience of traditional ETL tools and the drawbacks of Excel led to several cornerstone concepts behind EasyMorph:

1) Visual design. No SQL queries, no scripting or programming. All transformations are set up by only pointing-n-clicking and drag-n-dropping.

2) Everything should be easily debuggable. Result of any transformation should be just one click away. Result of any expression should be easily tracked down to actual values (not done yet).

3) Readability and  and maintainability. Business users should clearly see how this or that value is obtained. It shouldn't require decoding. It should be explained visually and automatically documented in plain English (not done yet).

4) Fit technology to business users and not vice verse. If users don't understand SQL -- get rid of SQL. If users don't understand various types of joins -- don't stick to traditional joins, find a simple way how to represent the same operation.

The screenshot below shows sample description of transformations in EasyMorph.

Another interesting observation about multi-step transformations I made when I wrote loading scripts in QlikView. As a QlikView developer I wrote possibly tens of thousands lines of QlikView script. The more I wrote the more I noticed that all my scripts tend to have the same structure (a good topic for another blog post, btw). In particular, I noticed that it's natural to split loading script into rather isolated logical blocks (usually one block is placed in one tab) where each block generates a table with meaningful result. Temporary tables and fields used for producing this table are created and dropped withing the block. These resulting tables are not necessarily final. What's important about it that they encapsulate some logic, making it more or less isolated from other parts of script. Putting it differently, for other parts of the script it's not important how exactly I produced this resulting table, they just consume the result.

This observation led to the idea of tables as transformation groups in EasyMorph which would represent such logical block. Effectively, every table in EasyMorph is a result of a group of transformations. In the picture above you can see that table "Census data" is result of a group consisting of 8 transformations. Clicking on any transformation would display intermediate output produced by this transformation (output of the 7th transformation is shown above).

Another powerful abstraction in EasyMorph is derived table. Derived table allows re-using already calculated tables. Producing a derived table is the same as loading everything from a resident table in QlikView. Apparently you can produce as many derived tables as necessary. A derived table is just a regular table so it can be transformed as well.

In the screenshot above you can see two derived tables produced from "Census data". Each of derived tables is further transformed in its own way.

With the help of derived tables you can program rather complex logic. For instance you can split rows from the originating table into three groups based on some criteria, transform every group in its own way and then append all three groups back into one table. Another example - you can perform self-joins using derived tables.

By the way, if you're familiar with functional programming, it could strike you that transformations in EasyMorph resemble function pipelining, and derived tables is the same as variable binding. To make it even more interesting, I can tell you that it is possible to introduce closures and iterations in EasyMorph which would effectively make it a functional machine.

A few technical facts about first version of EasyMorph:

• It's a 64-bit Windows desktop application with its own in-memory data processing engine.
• It doesn't require external database.
• Its first version comfortably deals with datasets up to 3-5mln rows.
• No data compression yet.
• 15 transformations that can run in parallel utilizing several CPU cores.
• Data sources include delimited text and QVD files. Support for Excel files is expected in 2-3 weeks.
• Expression syntax with 40+ functions.
• It can run from command line

Technical Preview is available at http://easymorph.com.

UPDATE
EasyMorph version 1.0 was released in February 2015 and is now actively developing. See http://easymorph.com.

Major redesign of QViewer

It's been a while since I posted last time and now I've several posts and announcements waiting to be published.

Today's post is about major QViewer redesign and update which has been due for a long time. I wrote first version of QViewer 2 years ago mainly for myself, although it was interesting to see if it can be useful for other QlikView developers. I turned out -- it can, and in the last 2 years it was downloaded more than 10'000 times (7'500 downloads of 64-bit version and 2'500 downloads of 32-bit version) with average number of downloads steadily going up.

At the same time the utility remained to great extent what it was initially -- a custom-made "home-grown" software written during afterhours and in spare time, and it had good potential for optimization.

Today, I'm releasing deeply updated and redesigned QViewer version 2.0 which is finally made comme il faut, i.e. -- as it should be. If you used QViewer previously you will notice new look and feel of the new version, however the majority of changes are under the hood. During the redesign the main focus was on performance and memory consumption that would allow using QViewer with large QVD files (up to 2GBs and more) comfortably. As the result, memory consumption was reduced in 5-7 times, and load speed has reduced in 5-10 times. For instance, load time for a sample 200MB file which I'm using for internal testing, has decreased from 17 seconds to just 1 second. Even more than that -- now load time depends only on hard drive speed, so if you have SSD -- you will enjoy even faster opening.

Two other changes -- more responsive user interface better suited for larger displays, and replacement of Query (simple aggregation feature) with integration with another tool which will be released soon. Also the free version now allows twice more rows -- 100'000.

Your existing license keys will keep working with version 2.0 so no replacement needed.

Don't hesitate to try out new QViewer and let me know what you think about the changes.

FastStart: tiny package to speed up creating new QlikView apps

FastStart is a tiny bundle consisting of an empty QlikView application with a couple of useful subroutines packed with empty configuration .xls file and portable QViewer for inspecting resident tables. I made it for myself for the cases when I need to quickly create a new app from scratch but you're welcome to download and use it. It was made suitable for rapid debugging and dealing with large datasets.

I'm sure there are other similar bundles, here is my approach:

Changes to default Document Settings:

• Compression -- None
• Generate Logfile -- Yes
• Default font -- Calibri, 9pt

Changes to default Load Script:

• Added Subroutines tab that contains two subroutines -- LoadVars (loads variables from external configuration file) and Inspect (calls QViewer for inspecting contents of a resident table);
• Added Partial Load tab -- calls LoadVars so that variables can be updated without full reload (by simply pressing Ctrl + Shift + R), also can hold additional script commands for execution during Partial Reload which is convenient for modifying data model;
• Default tab Main renamed to Variables  -- intended for setting up variables and runtime parameters;
• Added Main tab -- blank tab, here we start manipulating with data.

Two additional folders

• Config -- contains config.xlsx
• Tools -- contains QViewer.exe

I intentionally tried to keep it minimalist and not overload it with pre-built stuff like UI layout framework, calendar scripts, predefined color variables, etc. Although, external configuration file for storing variables and subroutine for inspecting resident tables -- these two features I use in almost every application.

A few tips for dealing with large QlikView applications

Recently, I had to develop an application with significant amount of data -- starting with about 100mln. of records with future increase up to 400mln. It was an interesting experience and below are my tips and findings that might be of use for those who have to deal with large amounts of data in QlikView.

Tip #0 -- go to Document Properties and turn off compression. It will make saving and loading application much faster. Yes, it takes more disk space now but you're not going to send such large application by mail anyway.

Mitigate long reload times
Once you've got tens of millions of records you probably don't want to perform full reload after every small change in the script. Some people use built-in debugger for limited runs, although you can have better control over limited reloads using a variable and FIRST prefix for LOAD statements that operate with large tables. Example:

LET vFirst = 1000;//00000000;

FIRST $(vFirst) LOAD ...

In this case you can do quick short runs with a limited set of data either to check correctness of script syntax after small changes or verify transformation logic. Remove semi-colon and double slash for a full load. Make sure that you have enough zeroes.

You would probably want to reduce even full reload times as much as possible. In some cases, if data model allows logical partitioning of data it's better to switch to partial reload as the main reload mode for updating the application with new information. In this case data model isn't dropped entirely, so you can append new chunks of data to it instead of building everything from scratch. Full reload can be left for emergency cases, when entire data model has to be rebuilt.

This technique is especially useful when you work with monthly (weekly, daily) snapshots and you need to join them (you might want to do this for denormalization as a way of  performance improvement discussed below). In this case instead of joining two full tables you can join monthly snapshots first, and then concatenate results month by month, using partial reloads (see picture below).

To make it more manageable, it's better to store monthly snapshots in individual QVD files -- one QVD per month.

Teammates wanted: ETL tool for non-technical people to be prototyped

I'm working on a concept of a new kind of ETL tool and I'm looking for one or two teammates (maybe future co-founders) to join our efforts and make it happen.

Background
The concept is about new data transformation tool intended for use by non-technical people. We observe the rise of Business Discovery tools like QlikView, Tableau, etc. that further simplify data analysis and visualization for people without technical background and I think there is an area where similar change is still waiting to happen -- business data manipulation and transformation. This task is traditionally "outsourced" to technical engineers who build complex ETL jobs by the means of SQL scripting or ETL-tools. However, in my opinion, this way of doing things doesn't match reality anymore:

First, there is a growing need for non-technical (a.k.a. "business-") users to manipulate data themselves. Nowadays they are increasingly more inclined to browse data and perform ad hoc analysis rather than deal with static reports. However, not being able to prepare or modify data set for analysis brings a lot of inconvenience and limitation for them. Finally, we have situation when those who prepare data -- don't analyze it, and those who analyze it -- can't prepare it. I believe this is a major factor that inhibits effectiveness of Business Intelligence in general.

Second, uber-popular Excel, which partially solves the problem of self-service data manipulation, is becoming more and more an obstacle rather than a solution because of two reasons:

a) Typical data volumes went beyond Excel capabilities and continue growing exponentially. Nowadays average analyzed data set is about 10-30mln of records and of 200-500GB size. That's too much for desktop Excel.

b) Spreadsheets as technology doesn't play well with relational data sources (e.g. adding a new value to a dimension can break all references in a spreadsheet; writing back from Excel to a database is a hassle). At the same time relational databases is the main source of data for BI tools and will remain it for decades.

Concept
In my opinion, any attempts to make traditional ETL/ELT more user friendly or stuff Excel with even more features will be ineffective. I believe data transformation needs to be re-thought and re-designed from scratch in order to make it appropriate for use by non-technical people.

Here is how I envision it:

1. It's visual. Who said you can't join two tables by dragging and dropping one on another? Left/right join? Not a problem. This can be done without knowing SQL at all. Another example -- grab a field header in a table, shake it with mouse to obtain a list of unique values of this field (which is actually a new table). Table concatenation, filtering, grouping -- all this can be done in a visual way.
2. It's instant. What you see is what you get and get instantly. Result of manipulations and transformations is displayed immediately.
3. It's table-based. Cell is just a particular case of a table. Vector either. Why don't treat them as tables? We don't really need a spreadsheet full of empty cells. It should be free-form layout consisting of connected tables (connection = transformation). Click on empty space to create a new cell and start editing it.
4. It's functional/declarative. New columns and tables is result of functions applied to tables, fields or specific cell ranges. Transformation is actually a sequence of functions where resulting table of one function serves as input table for another. Business users understand concept of Excel-like functions, but don't understand SQL or imperative programming (and will never do).
5. It's semantic. Name fields meaningfully. Fields with the same name expected to contain the same kind of information (e.g. Product Description). Refer by field names in functions and expressions (e.g. =Profit/Revenue). Make own re-usable functions.
6. It's smart. Join by fields with the same names. Omit table name when referencing a field with unique name, etc.

Since business users very rarely operate with more than 50mlns of records the tool is not expected to have ultra-high performance or perform sophisticated transformations. Instead, main accent to be made on polished usability and simplicity of UI which would allow business users perform data manipulations themselves, without knowing SQL or asking data engineers for help. I believe this is achievable.

From technical standpoint -- this is in-memory data processing engine which is built either as WinForms .NET/C# application or web-application on any other stack (I've experience with .NET/C#, RoR, Javascript and now playing with Go). At some point JIT compilation of expressions will be required, so the stack should allow doing this.

Project

At this point the project is pure R&D which goal is to find out optimal product and UI concept, and build simple working prototype. There is no ready specification -- we will have to take a pen and a paper and think out, discuss and try various approaches until we find something we are strongly convinced about. There is a non-zero chance that this will never happen.

The result is working prototype that allows doing filtering, joins and concatenations and basic arithmetical operations on fields. Then there can be two options how it can evolve further -- either to build a commercial product and bootstrap as a side-project, or look for funding and turn into a company and then start building a commercial product.

Who I'm looking for

I'm looking for software development veterans who love Business Intelligence, understand its purpose and challenges, and are capable of resolving complex technical tasks which might involve extensive parallel calculations, concurrent execution and JIT compiling. Since my programming style is rather pedantic, DRY and, to some extent, perfectionist I'm looking for people with the same altitude and similar coding style. It should be mentioned that my main programming experience was 15 years ago and now I'm reinstating my coding skills with QViewer and some web-projects. I expect you to be more experienced programmer than me, however that doesn't mean I'm expecting someone to do all the coding -- we do programming together and with equal efforts.

If this project looks interesting to you and you think you have appropriate experience of software development -- give me a shout and tell a few words about yourself. My email is in the upper right block of this blog. If you know someone who might be willing to take part in this project -- please don't hesitate to share with him/her the link to this blog post.

UPDATE (Feb 2015)
The concept described above has been made real as EasyMorph. See http://easymorph.com for more details.

First look at QlikView Direct Discovery

Direct Discovery is one of the recent and long awaited features of QlikView which became available starting from version 11.2. Direct Discovery allows mixing data from external data sources and from QlikView's in-memory data engine using the same QlikView scripting language and expressions. Aim of Direct Discovery is to go beyond RAM limitations and enable real-time querying against large volumes of data outside of QlikView, while presenting the same associative experience as for regular in-memory data. Here are two good documents that describe it in more details: QlikView Direct Discovery Datasheet and QlikView Direct Discovery FAQ.

From development standpoint, Direct Discovery can be employed by two ways: using DIRECT SELECT statement in loading script and SQL( ) function in expressions.