Major differences between ETL tools Datastage and ODI

sureshchandra · Post by **sureshchandra** » Fri Jan 16, 2009 2:05 am

Hi,
Can any one help me what are the differences between datastage and odi.Performance wise which is good.

Regards
Chandra

ray.wurlod · Post by **ray.wurlod** » Fri Jan 16, 2009 5:22 am

ODI stands for "one day international", a form of the game of cricket. Doubtless you knew that.

Based on second hand information, the Oracle offering will have you doing most of the work in sqlplus scripts, which are more difficult to produce and to maintain than the equivalent using graphical ETL tools.

Performance? Define "performance" in this context. It's as good as you can make it, irrespective of the method you use. Ultimately you are accomplishing the same result: moving data from source to target plus some transformation, whether it's ETL, ELT, TETL or some other combination.

sureshchandra · Post by **sureshchandra** » Sun Jan 18, 2009 7:48 am

Thanks Ray

vmcburney · Post by **vmcburney** » Sun Jan 18, 2009 8:41 pm

If you google ETL versus ELT you will find a lot of discussions on the topic. The main point of difference is that ELT needs the data to be in a database when it transforms it. This means either doing transforms in the source (via SQL select functions) or target (via insert and update statements) or via a staging database. All methods require extra capacity on a database - more CPU power and storage space.

ETL can run the same database transformations, you can still run a SQL select with transform functions, or an insert and update statement, you even have pushdown ELT in the next DataStage version. They also perform transformation on the ETL server with or without a database. This is handy if you have a very large transform load, there are some companies that think nothing of a 100 server DataStage grid. This processing area reduces the size of the target database. You can do data transformation that has no databases such as in a messaging architecture or XML data sharing or B2B.

ascen · Post by **ascen** » Mon Jan 19, 2009 3:46 am

In my opinion the biggest advantage of ODI over DS is a very restrictive metadata management model - you can only use source or target tables, which were previously defined in a repository. Of course some of you can tell, that it takes too much attention from developers.

Regarding performance. Over one year ago I did a POC in one of a bank. They were using a DS and they have some performance issues. During POC I've created the same ETL (ELT) logic as in DS in ODI. The performance gain was about 10 times faster in ODI.
But it really depends, which tool will be faster in such case. I would say that in the most of cases DS would be faster. And of course, DS is much more comprehensive tool.

vmcburney · Post by **vmcburney** » Mon Jan 19, 2009 6:38 pm

If you don't have much transformation then ELT/ODI will almost always be faster. You are going from source to target database without an ETL server in the middle.

vmcburney · Post by **vmcburney** » Mon Jan 19, 2009 6:39 pm

If you are looking for an ODI or OWB type tool but don't want to get locked into Oracle then have a look at Wherescape RED. It's a data warehouse/data mart builder with an ELT architecture and GUI interface.