Freigeben über

MetaData in the Microsoft BI stack

  • Artikel

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

Comments

  • Anonymous
    April 20, 2009
    Metadata (data about data) is becoming increasingly popular not just to meet ever more stringent auditing

  • Anonymous
    April 20, 2009
    Andrew, could this be a consideration for open source development?

  • Anonymous
    May 06, 2009
    I have been meaning to follow-up with you in regards to this posting.  You mentioned this statement 'word of caution is that report models are not being developed further'.  Is there any chance you could provide some additional information about this?  Is the direction MS is going to utilize the UDM layer within SSAS or create something like this to possibly replace the Report Models.  Just trying to determine where this layer will exist and what the future holds.  Thanks.  You can contact me directly if you want through my blog if you want to take this offline.

  • Anonymous
    May 07, 2009
    Dan Report models are being developed further for Gemini, but only as a frontend for relational sources.  The ability to create a model over a cube as you could in SQL Server 2005 as you could form report manager is not being further enhanced. That's about all I know at the moment. I will update this post /re post as the details of Gemini become clearer

  • Anonymous
    May 11, 2009
    Thanks for the information and that is really unfortunate to hear about the lack of support against an OLAP source.  The current model support and functionality with models against SSAS is not a good experience and seems like the ball was dropped.  Wish this would have been enhanced or developed further to provide more support and functionality.  Most people don't even know that it exists or maybe they just choose to ignore it.  I can only hope that something new will come in the future for end-users to be able to use for that layer to be able to report against OLAP sources.

  • Anonymous
    May 12, 2009
    Dan can I ask you to register your opinions on http//:connect.microsoft.com