Automating SSAS cube docs using SSRS, DMVs and spatial data | Purple Frog Systems

 

This article outlines a method of documenting cubes with some stored procedures and Reporting Services reports.  The only flaw, which is more on the SQL side, is the lack of a way to dynamically specify the linked server name, without getting into dynamic sql.

This could be useful for managing change in cubes, and providing end-user or technical documentation.

Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes…

There are some good tools out there such as BI Documenter, but I wanted a way of having more control over the output, and also automating it further so that you don’t have to run an overnight build of the documentation.

I found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why not just build a number of SSRS reports calling these DMVs, which would then dynamically create the cube structure documentation in real time whenever the report rendered..

This post is the first in a 3 part set which will demonstrate how you can use these DMVs to automate the SSAS cube documentation and user guide.

Automating SSAS cube docs using SSRS, DMVs and spatial data | Purple Frog Systems