MetaData in the Microsoft BI stack

  • Article

Metadata is “data about data” and in the BI world this means two things:

  • Definitions of terms, particularly calculations so that business users can understand what they are looking whether on screen or on paper. 
  • Lineage to understand where and how the data in a report was derived.

There are a number of placeholder in all parts of SQL Server to support this, from extended properties in the database engine , actions in analysis services can take you to a website describing each calculation, and integration services extensive logging capabilities.

To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:

  • 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.

This is all designed to work on SQL Server 2005 and should be fine with SQL Server 2008.  My only word of caution is that report models are not being developed further and that the whitepaper only refers to Report Builder v1. 

Anyway it’s a useful (and free) set of tools and you have the source code to hack it around to get it do what you need. e.g. compliance requirements (Sarbannes Oxley, Data Protection, Basel II etc.) or simply to make your BI project more accessible to your users and easier to maintain.

Technorati Tags: SQL Server,analysis services,reporting services,integration services,business intelligence,BI,metadata