SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

NOTE - Feb 5, 2009: This post is work in progress. I am planning to add few more SSAS metadata reports and also SSAS performance reports.

New SSAS 2008 DMVs allows you to easily access Microsoft SQL Server Analysis Services (SSAS) metadata. This article contains link to SSRS solution with the list of reports that produce documentation about one SSAS database. Here is a sample Adventure Works pdf report generated by these SSRS reports.

Here is a link to download solution with SSRS reports.

Solution description 

You can execute SSAS DMV queries directly in the Analysis Services environment, but this approach has a lot of limitations - most importantly you can not do joins betweens DMVs. To go around this limitation, I created linked server from SQL Server to Analysis Services. This way I can do joins between DMVs. Here is the script that was used to create linked server:

EXEC master.dbo.sp_addlinkedserver
   @server = N'SSAS_Reporter'
 , @srvproduct=N'MSOLAP'
 , @provider=N'MSOLAP'
 , @datasrc=N'VIDAS-LENOVO'
 , @catalog=N'Adventure Works DW 2008'
 go
 
EXEC master.dbo.sp_addlinkedsrvlogin
   @rmtsrvname=N'SSAS_Reporter'
 , @useself=N'False'
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
go

For your environment you will need to make sure that linked server above point to the SSAS 2008 database that you would like to run report on.  

Attached SSRS solution contains multiple rdl files:

• Cube List.rdl - list of cubes and perspectives in one Analysis Services 2008 database.
• Cube Measures.rdl - information about measure groups and measures in each cube/perspective in one database.
• Dimension List.rdl - list of dimensions in one database.
• Dimension Details.rdl - information about dimension attributes and hierarchies in one database.
• KPIs.rdl - list of KPIs in the cubes
• Measure Group Dimensions.rdl - list of dimensions for each measure group.
• SSAS DB Structure.rdl - Main report that includes other reports as sub-reports.