Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ETL Tools selection 10
- Thread starter ADB1
- Start date
ArtieChoke
Programmer
Right now the developers do it all. We only have one data warehouse and one area (sales) and two PowerCenter servers at the moment. Granted, if you have a lot more, it would make sense to have one person handle it - mainly from a configuration management perspective. Otherwise, things could definitely get out of sync in a hurry!
Once we get into true production mode, we will want to hand this part off to our IT staff to ensure things get done consistently (and to keep our developers out of production - including me!
)
"I think we're all Bozos on this bus!" - Firesign Theatre![[jester]](/data/assets/smilies/jester.gif "[jester] [jester]")
Once we get into true production mode, we will want to hand this part off to our IT staff to ensure things get done consistently (and to keep our developers out of production - including me!
)"I think we're all Bozos on this bus!" - Firesign Theatre
avjoshi
IS-IT--Management
- May 12, 2003
- 221
What are your thoughts on OWB (Oracle Warehouse Builder, Oracle's ETL tool). Since you are working with Oracle database and as you said, it is a basic DW implementation OWB may be a perfect fit. OWB now allows you to build your metadata repository also. I am not sure about the price but I believe it is fractional compared to the more advanced ETL tools.
Anand
Anand
I did download and install OWB, I just have to have the DBA load the repository and whatever else is required on our test server. I'll try it and post what I find.
However, my company just agreed to purchase/use Cognos as an enterprise BI tool so my manager wants me to also evaluate their ETL tool. Anyone have any experience with Cognos Decisionstream? I downloaded/installed it but can't get it to connect to our Oracle DW, keeps saying TNS error even though my other Oracle tools (and tnsping, etc) work fine.
One thing I forgot to add, since we are running Oracle 8i we lose some functionality in OWB:
Some features new in Oracle9i Warehouse Builder leverage functionality from the Oracle9i database. The following functionality is not available for use with Oracle8i and previous versions:
* Support for Slowly Changing Dimensions implementation as described in the Oracle 9i Warehouse Builder Transformation Guide.
* Snapshops are supported, but you may encounter performance issues
* Deployment of Process Flows
* Pivot Operator
* Unpivot Operator
* Full Outer Join using the Joiner Operator
* Multitable Insert
* Table Functions
* Use of CASE function in PL/SQL and SQL code
* External Tables
Some features new in Oracle9i Warehouse Builder leverage functionality from the Oracle9i database. The following functionality is not available for use with Oracle8i and previous versions:
* Support for Slowly Changing Dimensions implementation as described in the Oracle 9i Warehouse Builder Transformation Guide.
* Snapshops are supported, but you may encounter performance issues
* Deployment of Process Flows
* Pivot Operator
* Unpivot Operator
* Full Outer Join using the Joiner Operator
* Multitable Insert
* Table Functions
* Use of CASE function in PL/SQL and SQL code
* External Tables
We looked at Cognos. We found that things were never as 'easy' as promised but then all the sales guys do that to you.
We didn't go for Cog' as we wanted fast config changes and a real time control system and for what you get they seemed a little expensive and not totally suited to our needs. Our data is just a collection of flat files and a few ODBC sources (some DB, Excel etc.) but it's a continuos stream and we have to alter the delivery config quickly and on a regular basis without losing sequence. We also had a max bugdet of $25k so we didn't have a lot of options.
Overall we didn't find Cognos as flexible as some of the other packages but if your just lifting data from Oracle then it should be ok.
We didn't go for Cog' as we wanted fast config changes and a real time control system and for what you get they seemed a little expensive and not totally suited to our needs. Our data is just a collection of flat files and a few ODBC sources (some DB, Excel etc.) but it's a continuos stream and we have to alter the delivery config quickly and on a regular basis without losing sequence. We also had a max bugdet of $25k so we didn't have a lot of options.
Overall we didn't find Cognos as flexible as some of the other packages but if your just lifting data from Oracle then it should be ok.
I have finished converting a DTS series of ETL builds to Cognos DecisionStream ETL and I would have to say that Cognos tool as much better functionality for handling heirarchies and surrogates plus it handles lookups and joins through its own internal engine so you dont have to keep repetitive lookups in SQL.
They have the ability to do eveything in SQL steps same as DTS, but its the extra functionalty on top means you dont have to code as much.
Its basic main selling point is its ability to deliver whole datamarts in one king hit.
One drawback is cost of the software - but if you are using windows and unix and an assortment of different types of databases then it is great - but if you are just using SQL Server then stick with DTS.
I havent tried Informatica or Datastage so can't comment.
They have the ability to do eveything in SQL steps same as DTS, but its the extra functionalty on top means you dont have to code as much.
Its basic main selling point is its ability to deliver whole datamarts in one king hit.
One drawback is cost of the software - but if you are using windows and unix and an assortment of different types of databases then it is great - but if you are just using SQL Server then stick with DTS.
I havent tried Informatica or Datastage so can't comment.
johnherman
MIS
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
I've evaluated both through a proof-of-concept (current versions).
Both are good, both are equivalent in many respects.
DI is the easiest to use of all the major ETL tools. Easy to learn, easy to use, quick to build. Great user interface. Only vendor that can show side-by-side before and after of data going through transforms. Their data pipelining and parallelism support are good, but not as good as ASCL or INFA, so they won't scale up to the very high end as well. They include basic data profiling at no cost in all versions of the product. In all, I like the product very much.
DataStage has more pre-built features and much better enterprise-level manageability than DI. Easier to manage via an operations group, easier to manage multiple projects, easier to control developer and administrator access. It has poor debugging and data viewing features within the development environment. It is more expensive. If you buy the entire suite you get a real Metadata repository and Data Profiling and Data Quality, so they offer it all.
You must buy FirstLogic for profiling and quality, and another repository of choice for an enterprise MD repository if you use DI. But you will also pay a lot less if you do this best-of-breed approach with DI. The integration between the components in ASCL shows seams, which should not be the case in a single vendor product, but they acquired all through acquisitions.
Both offer real time. ASCL's is better if you are using the now-relabeled Mercator product. Otherwise the basic RT features are largely equivalent.
Both are good, both are equivalent in many respects.
DI is the easiest to use of all the major ETL tools. Easy to learn, easy to use, quick to build. Great user interface. Only vendor that can show side-by-side before and after of data going through transforms. Their data pipelining and parallelism support are good, but not as good as ASCL or INFA, so they won't scale up to the very high end as well. They include basic data profiling at no cost in all versions of the product. In all, I like the product very much.
DataStage has more pre-built features and much better enterprise-level manageability than DI. Easier to manage via an operations group, easier to manage multiple projects, easier to control developer and administrator access. It has poor debugging and data viewing features within the development environment. It is more expensive. If you buy the entire suite you get a real Metadata repository and Data Profiling and Data Quality, so they offer it all.
You must buy FirstLogic for profiling and quality, and another repository of choice for an enterprise MD repository if you use DI. But you will also pay a lot less if you do this best-of-breed approach with DI. The integration between the components in ASCL shows seams, which should not be the case in a single vendor product, but they acquired all through acquisitions.
Both offer real time. ASCL's is better if you are using the now-relabeled Mercator product. Otherwise the basic RT features are largely equivalent.
johnherman
MIS
Microsoft DTS is available bundled free with SQL Server, so you can buy a 5 user license of SQL Server and use the DB only for ETL.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
Re. SharmaG's question.
What are you trying to do? You may not need an ETL tool at all.
We ended up using a combo of inhouse tools and a product called Scionet DTS. Overall, I think, including our developer time the whole think cost about US$22k.
I think we could have done it without any purchased product but in the end, having live admin control (which would have taken time to code) over some of the processes was worth it.
Perhaps if you describe a little more info the developers in this forum can be more helpful.
What are you trying to do? You may not need an ETL tool at all.
We ended up using a combo of inhouse tools and a product called Scionet DTS. Overall, I think, including our developer time the whole think cost about US$22k.
I think we could have done it without any purchased product but in the end, having live admin control (which would have taken time to code) over some of the processes was worth it.
Perhaps if you describe a little more info the developers in this forum can be more helpful.
Hmm, on quick question from everyone. Is there any ETL tool available for some basic [not-so-transformation-oriented] work which can be acquired in a budget of below 20K $$
If you are using Oracle, their Warehouse Builder is $5,000 per seat base price, I believe.
If you are using Oracle, their Warehouse Builder is $5,000 per seat base price, I believe.
Hmm Thanks guys.
All I have is a bunch of data sources, some oracle, some sybase and some flat files. I need to extract information and place it in one place, on an oracle database.
I do not need any data integration. Meaning, data from different data sources is not inter-dependent.
I need some transformation and some data validation. Like I may need to produce new columns depending upon the information in the given columns. I need some data aggregation. And minor data validation like a field is less than a few millions and more than a few cents
and I may need some grouping features. Especially data in flat files is not structured properly. I need to design my own keys and structure data properly. Also probably I will need to break individual input records into columns for multiple staging tables, which means I will need some more grouping stuff to understand primary key- foreign key relations.
This whole stuff is not very heavy but developing my own java ETL is going to take real time. I will have to divert most of the focus on programming rather than making the right transformations.
I think OWB is a good choice. I however do not know whether it is good in extracting information from sybase. Other than sybase my only data sources are flat files and oracle so I should nt have any problems there.
Thanks for all your suggestions. They are really helpfull.
-Sharma
All I have is a bunch of data sources, some oracle, some sybase and some flat files. I need to extract information and place it in one place, on an oracle database.
I do not need any data integration. Meaning, data from different data sources is not inter-dependent.
I need some transformation and some data validation. Like I may need to produce new columns depending upon the information in the given columns. I need some data aggregation. And minor data validation like a field is less than a few millions and more than a few cents
and I may need some grouping features. Especially data in flat files is not structured properly. I need to design my own keys and structure data properly. Also probably I will need to break individual input records into columns for multiple staging tables, which means I will need some more grouping stuff to understand primary key- foreign key relations.
This whole stuff is not very heavy but developing my own java ETL is going to take real time. I will have to divert most of the focus on programming rather than making the right transformations.
I think OWB is a good choice. I however do not know whether it is good in extracting information from sybase. Other than sybase my only data sources are flat files and oracle so I should nt have any problems there.
Thanks for all your suggestions. They are really helpfull.
-Sharma
You could look at Data Junction, which is now owned by Pervasive. It may fit in your budget. I don't know what Pervasive has done with the pricing since acquiring the product.
The basic version of Data Integrator (Business Objects) lists for $25K, so you might be able to get a 20% discount on the product.
I don't know anything about pricing for Hummingbird or Embarcadero, but they are listed in the lower-cost category. Maybe someone else can comment on their pricing.
The basic version of Data Integrator (Business Objects) lists for $25K, so you might be able to get a 20% discount on the product.
I don't know anything about pricing for Hummingbird or Embarcadero, but they are listed in the lower-cost category. Maybe someone else can comment on their pricing.
- Status
Similar threads