Hi All,

ELT has been getting some press lately......much due to sunopsis making some noise about how great an idea it is.

My opinion?

Those who want to do ELT for any significant sized volumes of data, good luck to you because you are going to need it.

It is absolutely true that pretty much anything you can do in an ETL tool you can do in a database language, after a fashion.

It is far from true that you can do it in anything like the same amount of processing time given the same hardware platform.

It is far from true that you can do it for anything like the same amount of license fees.

For example, there are many features of ETL subsytems that I have published code for that are simply too expensive to do in the database and a far faster and cheaper to do in file processing and in memory processing.

1. Multi-level everything.

2. Attribution. Especially the use of memory maps to gain linear scalability.

3. Multi-level aggregation of only this batch of transactions.

4. Consolidation in place of multi-level aggregates.

5. Programatic detection of deltas where the source system does not provide it.

6. Programmatic correction of bad data or the multitude of date formats.

Would be to just name a few.

Some say....well we have this really fast box and so we can do ELT.

I say, if you have a really fast and expensive box, can you not think of something more useful to do with it than transformation best done in C++ on some general purpose box?

I know I can.

Today, almost no-one understands the concept of 'data derivation' and 'data conditioning' once data in in the DW.

This is where the next great level of value is...and this is what we should have our fast boxes doing....things like calculating customer value indexes, performing product catalog segmentation (PCS), determining who should be offered what next pre-emtively, RFM scores by customer by PCS, calculation of voucher values to offer customers to stimulate purchases across product segments....

We explain to our clients the only segmentation worth doing is the one that spits out the customer number to talk to and what to say to them when and how.

This is far better use of all that computing capacity that merely loading a machine and getting it to do your transformations for you.

My opinion... ;-)

Best Regards

Peter

-----Original Message-----

Sent: Wednesday, October 25, 2006 8:29 AM

To: Joe Costanzo

Subject: [dw-select] ETL vs. ELT ... what's out there?

I have been a long time user of all of the major ETL tools and am

a big fan. I agree that SSIS is far better than DTS and is

actually giving Informatica and others a run for their money

... but as data volumes grow and platforms like Netezza gain

ground, the ETL concept ... where you do the transforms in an

engine ... become more of a bottleneck. I have been researching

the ELT (Extract, Load, Transform) archtitecture and it appears

to be a potential solution ... where you push the processing to

the db engine to take advantage of the scalability of the db

engine. Both Sunopsis and Informatica with its Push Down

Optimization option appear to have a credible solution. Has

anyone had hands on experience with these? Are their others?

--------------------------------------------------------------------