Meta Data Management / Dependancy Analysis

BI perspective, meta data management is quite crucial. In SQL 2000, as far as I know we didn't have a great support for meta data. Has that changed in SQL2005?
What about dependancy analysis? For example if I drop a column from a SSIS data flow what would be affected? Is it possible?

Thanks

Hi Thiru,
Re: Dependancy analysis: IDW 13 (I think) contained new functionality in the data-flow that detected when the metadata of the data-flow was not correct (e.g. If a column gets deleted from a component all downsteam componentrs will be affected) and prompts the designer with a number of options to fix. Of course, this same functionality is still there.

Re: Metadata management: What sort of metadata do you want to manage (e.g. metadata about the pipeline, execution metadata etc...?
How do you want to manage it?

-Jamie|||Jamie

We have used AbInitio EME (yes I know very very expensive product) at Reuters. Which does meta data management very well. This enables us to let the user community (via web site) to see where the data comes from and how we have transformed etc. So users can see it very clearly what calculations have been conducted etc.

At General Motors, SAS consultants used to show how SAS does dependancy analysis. I thought it was quite impressive.

I would like to see these two features inside SQL Server / even if we have to purchase it as separate product(s).

So my question, "is MS going to provide similar tools like their competitors?" I did speak to few MS speakers at TechEd, but didnt get any definite answer. Jamie, I was wondering whats your opinion on this?

Maybe Kirk or Ash can shed any light?

Thanks

Sutha|||Sutha,
Informatica too has a fantastic real-time monitoring tool called the Workflow Monitor. I asked a while back whether SSIS was going to get anything similar and was told "Not in this version".

Remember that this is still a version 1 product and even though there's a tremendous raft of features perhaps we shouldn't be expecting metadata tools such as these too early.

I'd be particularly interested to see if Donald has any thoughts on this but unfortunately he doesn't check this forum.

-Jamie|||Jamie

Infact I did speak to Donald about thing among other things. He didnt give me a definite answer and therefore I did assume it is not there. I did ask him about AbInitio data profiler and what is equivalent in SSIS. He did point that grpahs can act as a data profiler. I think MS need to make improvements on Data Profiler side too.

To compete with AbInitio, Informatica and other products, MS have to come up with a tool which can compete at same level. MS can compete very easily on price as the other products are extremly expensive.

Lets see what Kirk or Ash got to say!

Thanks

Sutha|||Hi guys, thanks for the questions. We're still just trying to get Yukon out the door. So, as you can imagine, we haven't quite transitioned to V.next discussions. We're aware that metadata management is something people are asking for. However, what that means to one person may be totally different than for another.
We're aware of what competitors provide, and will be looking at how to best fill the needs of our customers. As we get closer to planning v.next, we'll be eliciting more input like this from you our customers. We have some ideas about what's next, but we'd like to hear what you have to say.
Thanks,|||Thanks Kirk. I understand you are trying to get the product shipped in time. For next release it would be great if we could have something to cater for these 3 major areas.
Data Profiler (We can do now to a cretain extent)
Metadata Management Tool
Dependancy Analysis Tool

Sutha|||Can you describe those a little better to me? Please send me email directly and we can take this off line. Thanks,
K

Kirkha[at]Microsoftdotcom|||Ok ta. I will do that.

Thanks|||

In all of the responses I did not see where someone said if there is a way to at least generate some sort of meta data reports of the packages. Yes it is in xml, but I assume Microsoft at least has a way to retrieve meta data easilly. For examples Tasks Names, Sources, Variables and etc. Quite obviously not every one will want to open Visual Studio to find out what DTS packages we have servicing a Warehouse. Can anyone provide some insight on this?

|||Henry,
I haven't heard of anything like that but it sounds like it could be a useful little tool.
Perhaps a .Net utility that uses X-Query to get at the elements of the .dtsx file? I can really see some value in something like this. I imagine someone in the SSIS community will come up with something like that before too long!

-Jamie|||Jamie

As you say someone will come up with something soon. It is going to be a valuable tool. Clients enquire this type feature when we try to sell MS DW solution to them. Maybe Darren and Allan can come up with something? hint hint :-)

Thanks
Sutha

PS - Jamie, I haven't done that mail yet