Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

MetaData in the Microsoft BI stack

MetaData in the Microsoft BI stack

  • Comments 6
  • Likes

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 RepositoryA 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 ModelA report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
  • Integration Services SamplesA 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.

Comments
  • Metadata (data about data) is becoming increasingly popular not just to meet ever more stringent auditing

  • Andrew,

    could this be a consideration for open source development?

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

  • 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

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

  • Dan

    can I ask you to register your opinions on http//:connect.microsoft.com

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment