Posted by Peter Nolan on 04/23/07 09:11

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....

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

[Back to original message]