/* Google Analytics ----------------------------------------------- */

Thursday, November 12, 2009

Data Management for non IT people ...

Several tools are emerging to empower user on their laptop to do data manipulation without the need of a backoffice application. I call this new trend BI on the desktop.

Ormetis - Power to the user with no programming
The first one that really impressed me was Ormetis. Ormetis enables business users to quickly analyze, combine and transform multiple data sources to produce a coherent result without the help of IT.
Such transformation process is called a Scenario in Ormetis and is automatically recorded while you manipulate your data. The advantages are twofold: you get a complete audit trail for free and you can instantly recreate new results by replaying the Scenario whenever you get new input data. Ormetis does not provide any charting solution (yet?). Another advantage of Ormetis is related to data and security governance! You can then prove than no data value was changed in a scenario and you can also show what changes were made in any scenario (that can aslo be replayed if needed).
Ormetis is positionned in a niche, but can bring lots of value if you consider the time spent by non IT people trying to merge files coming from text, excel, etc.

I also looked at QlikView personal edition and Lyza. Below, you will find a high level comparison.

Ormetis vs. QlikView
Ormetis and QlikView don’t compete with each others, they actually complement each other really well.
QlikView is primarily a reporting software (Business Intelligence – BI) while Ormetis is primarily a data transformation software (ETL). BI tools are very good a presenting data (reports, graphs, dashboards, etc.) but you need to be able to get the data properly structured in the first place.
In any business, a fraction of the data is very well structured (usually stored in datawarehouses/datamarts and managed by the IT department) while the vast majority of it (sometimes up to 90% of the data) is floating around in Excel spreadsheets, text files, etc.
This is where Ormetis really shine by enabling business users to transform multiple sets of data (with different structures) into a single, coherent data set that they can then use immediately. All of that without the help of IT.

Ormetis versus Lyza (from Lyzasoft)

Ormetis and Lyza have a similar positioning. They both promote user’s autonomy (from IT), complete audit trail, on the fly analytical capabilities and innovative user interface.
Still, there are some very fundamental differences in philosophy as well as technical architecture. Lyza tend to be a all-in-one solution ranging from data preparation to reporting and analysis while Ormetis focuses only on data transformation part (which include analytical capabilities in order to make decisions about how best to transform the data).
From a technical point of view, Lyza embed a relational database (MySQL) and relies on the fact that every single piece of data is organized in a tabular way (columns and rows). While this is certainly true of some of the data, this is clearly not the case for the majority of it (data spread across multiple files, multidimensional data, complex spreadsheets, etc.)
Compared to Ormetis, Lyza lacks OLAP capabilities (groups), automatic detection of Text/CSV files structures (Ormetis patent pending), resilience to structural changes (column order, column delimiter, etc.), complete support for Unicode (non-Latin alphabets) and regional settings (date and number formats).
Because Lyza relies on a traditional relational database (rather than doing full in-memory transformation) performances degrade rapidly (disk access rather than memory access) on a standard desktop machine. For instance, some quick benchmarks made show that Ormetis is 30 to 50 times faster than Lyza for files bigger than 100.000 rows or columns.

Conclusions
Ormetis is indeed a good investment! Millions of rows/columns is probably the point where things start to break down in Lyza while this is Ormetis’ comfort zone. Ormetis is also recommended for Audit team due to its capability to show the content of a scenario and its impact on data.
Ormetis doesn’t have reporting capabilities (beside saving in Excel or XML format and handing of the job over to Excel) but BI tools can’t do any good reporting without good data. This is why combining it with other dedicated tools, like QlikView, is ideal. Recently Paul Clayton from Microsoft also made a post on how to use Ormetis with Excel, look here.
Lyza is also a good tool, a more all in one solution but requires more install on the desktop and is not providing the audit trail I was looking for.


Let me know your thoughts.