Reporting on Analysis Services Metadata using DMV Schema Rowsets
By Dan Meyers
Analysis Services 2008 Metadata Report Pack Download
I often get asked by clients about the best way to get metadata about the various SQL Server Analysis Services (SSAS) objects on a particular server. As usual, there are a few ways to go about getting this information. Unfortunately for DBAs and report writers, most of them are a headache to implement and often have you writing code rather than queries. In the recent versions of SSAS we have a somewhat straightforward way to accomplish this. SSAS exposes a lot of good information via built-inschema rowsets for OLAP and data mining objects. This means that all you have to do is write some queries and slap the results in a Reporting Services (SSRS) report and you are done.
If you think this sounds too good to be true, you are half correct. Although we finally have a nice simple solutions for reporting on this information, we are left wanting more. Not everything about the objects is exposed in these rowsets. If you want every detail about your objects then the best approaches are: to buy a 3rd party tool, or to put your programming hat on since you will be using AMOMD.NET to get all of the stuff that SSAS rowsets leave out.
In a previous blog post, I discussed a method where you can use a linked server to run MDX queries against your cubes. For these reports, I am going to use the same approach to execute DMX queries against SSAS 2008 using a linked server and the OLE DP provider for Analysis Services to retrieve metadata about our cubes, dimensions, measures, etc... from the rowsets.
This download provides you with a relatively comprehensive set of pre-built reports that you can easily deploy to your Reporting Services 2008 instance.
Before the reports will run, you have to create a linked server in SQL Server that points to your SSAS 2008 instance. Below is the SQL code needed to create a linked server named [SSAS_Metadata].
EXEC master.dbo.sp_addlinkedserver
@server=N'SSAS_METADATA',
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP',
@datasrc=N'localhost'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SSAS_METADATA',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
The download contains the entire reporting project pre-=configured to use the localhost as the data source for the SQL Server, Reporting Services, and Analysis Services instances.
The project contains the following 26 reports:
- Cube Details
- Cube Dimension Details
- Cube Dimension Relationships
- Cube Dimensions
- Cube KPIs
- Cube Measures
- Cube Perspectives
- Cubes
- Data Source Details
- Data Sources
- Database
- Dimension Attribute Hierarchies
- Dimension Attribute Hierarchy Details
- Dimension Details
- Dimension User Hierarchies
- Dimension User Hierarchy Details
- Dimensions
- KPI Details
- Measure Details
- Mining Model Column Details
- Mining Model Details
- Mining Models
- Mining Structure Column Details
- Mining Structure Details
- Mining Structures
- Perspective Details
The reports in the report pack were designed and modeled after the output provided by my favorite 3rd party documentation tool for SQL Server BI, BI Documenter. All of the reports contain many navigational links in the header as well as click-through navigation of the objects listed in the reports that you can use to drill into child objects for more detail.
As you can see by the names of the reports listed above, the reports touch all of the major objects and should satisfy the majority of the inquiries about them. One report that I find to be quite useful in the Cube Dimension Relationships report. It is an attempt to reproduce the grid on the Dimension Usage tab of the cube designer in BIDS that tells you at what granularity the dimensions relate to the measure groups.
Hopefully this report pack will save you some time and provide you with some good samples that you can use to create your own customized reports that might work better for you.
