Comparing Data Integrator to DTS, Informatica, Ascential...

dataservices
1

Several people have asked this question in these forums. Here is a summary of some of those postings:

Here are some external posts:

http://blogs.ittoolbox.com/bi/websphere/archives/forrester-etl-wave-from-2004-to-2007-16267

http://mediaproducts.gartner.com/reprints/businessobjects/151150.html

Updated Dec-07:
http://www.businessobjects.com/pdf/company/summary_forrester_etl_wave_q2_2007.pdf

Feel free to add to this thread if you have additional information.

dnewton :us: (BOB member since 2004-01-30)

2

A MS comparison from July 2008:

http://download.microsoft.com/download/6/9/d/69d1fea7-5b42-437a-b3ba-a4ad13e34ef6/SQL2008SSISComparison.docx

dnewton :us: (BOB member since 2004-01-30)

3

There is one mistake in the comparison: It says SQL Server SSIS has basic ETL capabilities - no it does not. Do you know what you have to do for a dataflow of this kind:

Source -> Query -> Table_Comparison -> Target
(or simpler Source -> Target_autocorrect_load)

?

First you need a lookup component. Then a conditional to split found records and new records. The new records go into the target table bulkloaded via inserts. The changed records need to go into a transform to compare the two inputs if anything is actually different or not. The result is sent to a OLE DB loader where you have to type “update table target set col1=?, col2=?,… where key=?”. No array operation, no performance optimization, nothing. For performance reasons you will have to load the updates into a stage table and then manually write a SQL Server 2008 MERGE-Statement.

You find tons of things like above.

I am so disappointed by what they have done. Such a nice GUI, such a bad execution.

Anyway, my opinion remains: It is a GUI for a SQL programmer.

Werner Daehn :de: (BOB member since 2004-12-17)

4

Good points.

If anyone here has spent a couple of years working with both tools, it would be interesting to hear their real-world experiences.

Remember the old maxim about Microsoft: It takes them 3 tries to get it right. So for SSIS, we’ve had 2 tries (2005 and 2008). It will be interesting to see how far they get in the next version of SQL Server.

dnewton :us: (BOB member since 2004-01-30)

5

I’ve been using SSIS for a few years and BODI for about 1.

I agree with dnewton in the standard progression of Microsoft. They are far from leaders in the Data Integration area.

To me, the actual lower level functions of moving and processing data is easier in SSIS. You get more transformation options and complex packages can be built fairly rapidly. However, packages are individual and isolated objects. They can be used in multiple jobs and invoked in many ways - and the developer has no way to see where they are used. Rapid deployment means rapid loss of control.

However the higher view, belongs to Data Integrator. The connection bewteen Data flows, work flows and jobs is very strong. As such, a developer has excellent access to Data Lineage, and the “Where used” function in DI is also powerful. SSIS does not have this.

Table Comparisons in DI are far better. The equivalent transformation in SSIS (SCD - Slowly Changing Dimension) is atrocious.

As far as execution time is concerned. I have not done an equal comparison in order to see which does X,Y and Z faster. I think that SSIS give slightly more visibility to performance settings than DI.

Ironinically, most vendors I deal with balk at the low price of SQL Server. For e.g. Would you buy a 6 bedroom house on spec for 10,000 (dollars, euro, whatever u like)?

Microsoft are throwing everything into BI at the moment. They are not visionaries, so will not be market leaders for a long time. So lets see where we are in 2 years…

Bendaberg (BOB member since 2009-07-17)

6

That’s interesting. I’ve been using SSIS and BODI for quite a while and my experience is exactly the opposite. I found that doing the easiest or simplest of things in SSIS would take quite some effort.

For instance, just to grab a table from a source system, perform some transformations and enrich the data by adding some attribute columns (populated through lookups) is literally done within a few minutes in Data Service XI. Even less if I don’t stop for coffee on the way :wink: … doing so in SSIS just takes a lot more time - mostly because of its user interface.

While SSIS can be quite powerful and can yield excellent performance in an all Microsoft environment, its user interface or the entire user experience rather is extremely underdeveloped. It seems to be that 90% of the development time went into building the SSIS platform and framework, integrating T-SQL with .NET and it wasn’t until 2 days before delivery that someone realized that the thing needed an interface.

Data Integrator has always excelled in its ease of use - it may not be the most powerful tool, but it certainly is one of the easiest ETL tools to work with. I have worked with Informatica PowerCenter and while Informatica PowerCenter is one of the fastest and most powerful ETL tools out there, it has a steep learning curve and even 8.6 isn’t free from some very annoying issues either.

I am currently part of a large scale ERP project where Data Services is deployed as a data migration and cleansing tool - utilizing an array of Data Integrator and Data Quality transformations. (It’s a very large global company so we’re talking truckloads of data that will need to be analyzed, cleaned and migrated into SAP).

Several ETL tools were evaluated, including SSIS+SSRS and Wherescape Red, both were already owned by the client - but Data Services XI won hands down. Not only did it had all the functionality required, I was able to get an evaluation environment up and running very quickly - within an hour, I was already coaching some of the business analysts to work with BODS and the Profiler … and with some days of coaching, the BA team is happily using Data Services to move data into a seperate repository, use Profiler to analyze data quality and data problems and creating Data Validation workflows to sort out bad from good data etc.

And these weren’t ETL, BI or DW people - just BA’s with some good SQL and some light VBA scripting knowledge (from using Excel). To me, that is an unquestionable statement as to how easy Data Services is to use and much attention Acta and BusinessObjects have given the user interface and overall user experience.

ErikR :new_zealand: (BOB member since 2007-01-10)

7

Hi all,

I’m in an interesting situation where Data Services has been acquired by a customer that already has IBM Data Stage heavily embedded. They are impressed with the flexible and ease of use of Data Services however of course there are DataStage developers becoming very protective of their turf and raising functionailty concerns.

This hasnt been an issue as all items have been covered off pretty easily however tnhere are 2 where I would be interested in feedback:

  1. Runtime Column Propagation: My lack of understanding on this functionality within Data Stage is making it hard for me to align with Data Services. My 1st impression is in the Data Services model why would we need it?

  2. Folders in Datastage: they are keen on the way that Datastage uses folders to help organise components within a project (note not talking about sequences etc)

Any comments or feedback greatly appreciated.

Cheers

Glenn

GlennL :australia: (BOB member since 2005-12-29)

8

Great information…

oracle dba course in nagpur

Aakanksha (BOB member since 2019-09-23)