Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.
The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....
I would appreciate your help!
Thank you,
Steve
Can you explain what OIM & CWM stand for and what they mean?
I wasn't aware of DTS supporting any metadata models.
-Jamie
|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model
OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).
For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?
At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||
Interesting stuff!
SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).
I can't find a CWM* file in any of my folders. Where exactly is yours?
How did SQL Server 2000 support OIM?
Sorry, I haven't been much help there have I?
-Jamie
|||Hi Jamie,
thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?
Well I'm disappointed that MS that there is no chance to import metadata into SSIS...
BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||
ssauerw wrote:
Hi Jamie, thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?
No. Not for DTS.
Well I'm disappointed that MS that there is no chance to import metadata into SSIS...BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
I'd be interested in reading that when its complete!!!
-Jamie
|||Well, you can read it but it's in German ;)
Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?
Steve|||
Why don't you qualify SSIS as a professional data integration tool?
Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.
You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.
-Jamie
P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx
|||Hi Jamie,interesting paper... I'll give it a closer look this afternoon.
Thanks!|||
OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?
-Jamie
|||Hi Jamie,
interesting paper. Thanks again.
Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...
Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...
Steve|||
Steve,
Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx
I'd be interested in reading your paper when it is complete. Do you think that would be possible?
-Jamie
|||Hi Jamie,
thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.
Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!
Steve|||
It's an interesting topic.
Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.
Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.
You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.
|||I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.
Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.
And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.
-Kory
No comments:
Post a Comment