It is hard enough to manage your data repository, modify it to meet new business needs, and add to that: keeping documentation and metadata up to date and relevant for your consumers.

Several friends of mine who are Informatica fans let me know on a repeated basis that the data manager in the Informatica Suite helps them out here; while functional; I was never fully impressed with the feature set. I showed my colleagues the SSIS API and they were blown away with how much untapped information sat inside SSIS.

The next question – why hasn't Microsoft put any of the metadata features directly into the SSIS tool?

While I cannot answer that, I do know that Microsoft has a very active user community and has supported this exact type of development. It is, in fact, nothing new and you can get the SQL Server 2005 Metadata white paper from Microsoft off of the download center, available from December 19, 2005 forward.

The trick though is the samples – for some reason, they seem to have an issue in keeping the sample projects online, so I have made it available from my site as well so insure that it is available to the general public. (See below)

What the MSI contains:

• DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
• DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
• Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
• Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
• Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
• Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
• Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

For anyone who reads from the Kimball line, the ideas presented in this paper look familiar, particularly if you have read the Microsoft Data Warehouse Toolkit book. In chapter 13 of this book, they discuss a metadata plan for a warehouse -- the website has extra materials: sample scripts that make use of the extended properties and reporting services reports to pull out metadata repository information.

I encourage reading both the whitepaper and the book, particularly the Kimball book as the Whitepaper assumes that you know the importance of metadata in your warehouse. The Kimball Microsoft Warehouse book is great in that it takes the concepts that they have developed over the years and applies it directly to the Microsoft stack.

SQL 2005 Metadata Samples (.msi, 311 kb)

Feel free to contact me for any questions. Thanks!

A colleague of mine was in the middle of altering a database schema given some optimizations he was applying to the existing system, when he started to run into a frequent annoyance that disallowed him the easy removal of some troublesome columns: index dependencies.

In this particular instance, the database grew quickly beyond initial planned capacity and it had been tuned multiple times over by various people in order to bring performance under control. This left all the database entities with many indexes spanning across the same columns, similar sort orders, included columns, etc.

When my friend was reviewing the design from a fresh perspective, he wanted to make simple column changes for performance and to meet the new requirements.

So, I get an e-mail saying: "What is a super secret sql script I could use to drop all dependent indexes on a given field so I can drop the field?"

It is quite easy to write (and it does not drop the indexes, just lists them as simply dropping can be dangerous -- for example, some columns might be included columns and not part of the primary index requirement):

select ColumnName = c.name

        , TableName = o.name

        , IndexName = i.name

, i.type_desc

        , i.is_unique

        , i.is_primary_key

        , i.is_unique_constraint

from sys.index_columns as ic

inner join sys.indexes as i

on ic.index_id = i.index_id and ic.object_id = i.object_id

inner join sys.columns as c

on c.object_id =ic.object_id and c.column_id = ic.column_id and objectproperty(ic.object_id,'IsMSShipped') = 0

inner join sys.objects o on ic.object_id = o.object_id

order by c.name, i.name, o.name

I love it when Microsoft makes it easy to test and experiment with their technology, and this virtual machine example is no exception. This virtual machine is a pre-configured Server 2008 image with SQL Server 2005 installed. If you are using Virtual Server 2005 like I am, then it is quite simple to just mount this guy up and go.

Microsoft has been working in overdrive with the release of PerformancePoint 2007, now coming close to a year in age, and there has been a large output of material from teams within the MS castle. I came across this gem recently after having heard some rumors of its existence. It was actually published in 2006, a year before PerformancePoint, yet in the wake of SQL 2005, which completely changed the game for Microsoft in the enterprise ETL space.

What made it hard to find (for me at least) is that it was posted in CodePlex, which I normally attribute custom projects and development to, not design and architecture guides. Here is the link:

http://www.codeplex.com/BIArchAndDesignGuide

Additional notes:

Srinath Vasireddy of Microsoft was the program manager for this design guide; his personal blog can be found at: http://blogs.msdn.com/srinathv/default.aspx