Re: Rants. Difficulty to learn ETL tools?

From: Peter Nolan <peter_at_peternolan.com>
Date: 23 Apr 2007 02:11:33 -0700
Message-ID: <1177319493.688345.26010_at_o5g2000hsb.googlegroups.com>


Hi DBA...
your append is exactly what I have been talking about since the mid 90s as well....making ETL easier......

We have invented the future and the future of ETL is 'generated ETL from the data mapping workbook'. (www.instantbi.com)

You have to do your data mapping somehow, and excel is how most people do it, the laggards are still using word......

Since you already have to do your data mapping, and if you are sensible you do it in excel, it makes the most sense to generate the etl subsystem directly from the workbook as well as publish the workbook via the web so that authorised people can see any and all details of the ETL subsystem.

No ETL subsystem will ever be any easier to develop and deploy than what we have invented because no ETL subsystem will ever be easier to build than a direct generate from the mapping workbook.....this is the 'end game' for development of ETL subsystems.

Why use such a tool rather than PL/SQL.....well, because it is generated directly from the workbook we have 'done away with' the ETL programmer.....and that is a good thing. I have done far too much ETL programming over the years and I want to get rid of that complete waste of time....

What can it do that you can't do in PL/SQL? Well, some nice things are we can parallelise the processing of large numbers of fact records and we can put the dimension tables in memory mapped IO and access them in a shared fashion using binary search......this is 10x faster than doing the same in PL/SQL at runtime....

[Quoted] Also, we have intelligence built into it that means you can do things like add new summaries without any code changes, you can add new keys to fact tables without any code changes, you can make lookups into dimension tables to get new keys FAR more complex than possible than via normal sql statements.

In short, we have eliminated all the 'coding' effort that is required when writing you ETL subsystem no matter what the tool.....and we have done it in such a way that it is as scalable as the operating system underneath....

Another BIG feature is that the ETL subsystem is portable across databases and operating systems....something that PL/SQL and SSIS are obvioulsy not.....this means that if some better/faster database comes out we can move to it......not something that MSFT would like to her and this is their newsgroup.....but it has always been a belief of mine that the ETL subsystem should be fully portable across OS and database.....and surprisingly, this is NOT the case with any of the ETL tools that I have seen....they all require quite some effort to move them.....thereby creating a cost to adopt a faster/cheaper/better database.

If you are keenly interested, feel free to check my personal site www.peternolan.com where I have published vast amounts of code and documentation on ETL subsystems.

Best Regards

Peter
www.peternolan.com Received on Mon Apr 23 2007 - 11:11:33 CEST

Original text of this message