Hi idelatorre,
you may feel ELT is a good approach now......
But for how long do you think your DW is going to have a single source of data from a single database from a single platform? Sooner or later data
will go the to DW that does not go to the ODS....
If this does not happen the information needs of the organisation could be
said to be very simplistic compared to other organisations...
I have seen so many projects in so much trouble based on a view of the future that people felt 'was reasonable at the time' which more experience d people would look at and say....the likelyhood of significant chan ge in the future is high and it would be less expensive to design for that change no w and expect it to happen...
The art of great system design, DW or otherwise, is to forsee the forseeable change and to design for it, and to know that unforseeable change is coming and to allow for that....
Most people who call themselves 'designers' or 'architects' do neither.
Thanks for the tip on Sunopsis being bought by Oracle....
I wonder how many ETL tools Oracle is going to have to buy?
Kind of makes the arguement so many put forward for OWB look a little weak
I would think.....as in...if OWB is so great why buy anoth er ETL tool verndor....(For those who don't remember Oracale also bought Carel ton Passport....and then could not figure out what to do with it..
..but they
got it for nothing so it hardly mattered....)
Best Regards
Peter
Original Message:
-----------------
From: idelatorre via dw-select dw-select@Groups.ITtoolbox.com
Date: Wed, 01 Nov 2006 21:55:53 GMT
To: peter@peternolan.com
Subject: RE:[dw-select] RE: ETL vs. ELT ... what's out there?
A very good example of when a ELT architecture makes more sense than a traditional ultra-powerful tool.
In my present institution we have a ODS that loads a bunch of flat files daily, this database requires continuous operation so we are planning a staging area for our next platform, the database backups are no more than 70 GB so that gives you an idea of its size.
This ODS is the single data source for our current DW, this is also around 70 GB.
ELT is from our pespective the best choice for our scenario as the data sets are reasonable in size and the DW loads from a single platform and dabase.
We were looking at Sunopsis but Oracle just bought it.
---------------Original Message---------------
>You are right, there are some great performance advantages
using
> ANY RDBMS (more so with parallel ones like Netezza, Teradata
> etc..) to perform the Transformations.
>
>In a nutshell and to assist your decision / evaluation, I know that
>you can request evaluation software from Sunopsis (go to their
>website, it's FOC) and this will allow you to test and validate the
>ELT approach with a FULL version of the product.
>Sunopsis does NOT require dedicated HW, nor does it price by
CPU,
> so it has a great TCO story when compared to the ETL players.
> You will find there are 3 main benefits of this tool:
>1) The inherent Performance advantage - It invokes the native RDBMS
>utilities with each RDBMS for great Extract and Loading; and then uses
>RDBMS SET processing for Transformations (versus row-by-row ETL
>engines... even if you buy their "parallel"
> adapters!).
>2) Productivity. Sunopsis requires you know SQL. Real simple!
> You will have people in-house capable of both development and support
>of this environment. So NO proprietary language to learn. This means
>developers are productive very quickly.
>>> There is some other really cool stuff under the hood which
is
> just magic! Its a declarative tool, so easy to design your solution!
>3) TCO - it is NOT JUST about up front costs, there is also the TCO to
>think about. Sunopsis is NOT CPU licensed, therefore as your DW scales
>up, there are no additional charges for either source or target
>growth. It is also very competitive in
upfront
> costs as there is no dedicated ETL server/storage requirement.
>As a word of advice - there is a big difference between native ELT and
>"push-down" ELT.
>Native ELT (Sunopsis for example), will allow you to create & delete
>temporary transform tables on the source / target
WITHOUT
> the overhead of seperate DBA activity.
>Push-down ELT appears to work only when the ELT does NOT
require
> temp tables and reasonably basic transforms. >>Push-down ELT makes
> for good slideware though!
>If your ELT jobs fall outside this requirement, then you will
end
> up using the ELT engine to complete the T tasks, and incur the
> performance handicap (and any additional costs associated with
> sizing your server / Software license etc...)
>hope this helps. get the eval software and see for yourself.
>---------------Original Message---------------
>>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?
>>