The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

13.3. SQL Server 2005 Metadata

The good news is that the SQL Server toolset is mostly metadata driven. The relational engine has a slew of system tables that define and describe the data structures, activity monitoring, security, and other functions along with a set of stored procedures to manage it. Other components, like Analysis Services and Integration Services are based on similar metadata, but it's kept in an object-oriented structure in XML files. Much, if not all, of the property-based metadata in SQL Server 2005 can be accessed through the various object models.

The bad news is that every major component of SQL Server 2005 keeps its metadata in its own independent structures, from database tables to XML files, which have their own access methods, from SQL Management Objects (SMO) and Analysis Management Objects (AMO) to stored procedures to APIs. Not only do the tools manage their own metadata, but the metadata they use is not integrated across the tools. The flexibility you get from the programmable nature of the tools and the Visual Studio development environment makes it particularly difficult to identify which packages pulled a particular set of data and applied which transformations and loaded it into what relational database, and what cubes and what reports.

As we describe in the next section, the first step in every metadata strategy is to assess the situation. You need to conduct a detailed inventory of what metadata structures are available, which ones are actually ...