| General Notice: Upcoming Events: BI show Toulouse: Oct 18. There are 50 ASUG sponsored events in the next 45 days, see calendar for details. |
| Author |
Message |
dnewton
Forum Addict
Joined: 30 Jan 2004
 
Posts: 3193
Location: Massachusetts, USA
|
|
| Back to top |
|
|
dnewton
Forum Addict
Joined: 30 Jan 2004
Posts: 3193
Location: Massachusetts, USA
|
|
| Back to top |
|
|
wdaehn
Forum Groupie
Joined: 17 Dec 2004
 *6
Posts: 7627
Location: Germany
|
 Posted: Mon Mar 09, 2009 12:01 pm
Post subject: Re: Comparing Data Integrator to DTS, Informatica, Ascential |
|
|
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
Product Manager Data Integrator
SAP Business Objects
PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here. |
|
| Back to top |
|
|
dnewton
Forum Addict
Joined: 30 Jan 2004
Posts: 3193
Location: Massachusetts, USA
|
Posted: Mon Mar 09, 2009 12:17 pm
Post subject: Re: Comparing Data Integrator to DTS, Informatica, Ascential |
|
|
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. |
|
| Back to top |
|
|
Bendaberg
Forum Member
Joined: 17 Jul 2009
Posts: 1
|
Posted: Mon Aug 24, 2009 4:59 am
Post subject: Re: Comparing Data Integrator to DTS, Informatica, Ascential |
|
|
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... |
|
| Back to top |
|
|
ErikR
Principal Member
Joined: 10 Jan 2007
Posts: 427
Location: Wellington, NZ
|
Posted: Thu Nov 19, 2009 12:11 am
Post subject: Re: Comparing Data Integrator to DTS, Informatica, Ascential |
|
|
| Bendaberg wrote: | | 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. |
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  .. 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. |
|
| Back to top |
|
|
|
|
