SSAS: Reporting on Metadata

I had a comment recently on one of my older posts that I did about XMLA which asked about listing cubes in a database and whether the cube is processed and it's size.

I have posted about a number of different ways of doing this over the last few years, but I thought I would use this opportunity to consolidate a list all the various ways of doing this that I could think of. Under the covers all these techniques are ultimately issuing some sort of XMLA discover against the SSAS database and returning a result, it's just that they all use different levels and types abstractions.

  • Using XMLA Discover 
    The XMLA discover command returns the results we want, but being an XML result it is not the most user friendly thing to read.
  • Using VBScript
    Vidas has an example which produces nice output in it's own right, but not easy to incorporate into a report.
  • Using Powershell
    Using powerSSAS you can interactively navigate through your SSAS database and inspect objects and properties or you could write a script, but as with the VBScript example, you cannot really use a Powershell script as a data source for a report.
  • Using ASSP
    I built the Discover and DMV functions for the Analysis Services Stored Procedure project that allow you to execute discover commands and return the results as a data table
  • Using ASSP Reports
    This is really a subset of the point above, but using these stored procedures I built a sample report in Reporting Services that displays all the cubes and partitions and their processed status for a given database. I would loved to have incorporated this report into SSMS, but SSMS does not allow custom reports for Analysis Services and it does not allow add-ins like we have in BIDS (ie. BIDS Helper) so there is no supported way to add this functionality at the moment.
  • SSAS 2008 - DMVs
    In the 2008 version of SSAS it will have native DMV functionality which produces similar to the ASSP stored procedures.

 

Print | posted on Tuesday, May 6, 2008 10:08 AM

Comments on this post

# re: SSAS: Reporting on Metadata

Requesting Gravatar...
Hey Darren,

What about AMO ?
You can see example of such AMO code in here:
http://www.miky-schreiber.com/Blog/PermaLink,guid,40de294b-84e7-4d53-bc33-fd023e9a514f.aspx

Also, you CAN extend SSMS. see here:
http://aspalliance.com/1374_Extend_Functionality_in_SQL_Server_2005_Management_Studio_with_Addins.all
Left by Miky Schreiber on May 15, 2008 6:35 AM

# re: SSAS: Reporting on Metadata

Requesting Gravatar...
Hi Miky,

Yeah you could use AMO, in fact the VBScript and Powershell approaches would be going through the AMO library. But as you point out you could write a .Net based application that uses AMO. Although when I think of reporting or querying I don't tend to think in terms of writing a custom application.

Thanks for the link. I am aware of a number of articles like the one you referenced that detail ways of getting add-ins to work with SSMS. It just frustrates me that these are all basically undocumented hacks. Under the covers SSMS is a modified version of Visual Studio and they have had to deliberately disable the add-in functionality. I understand that there are meant to be good reasons for this, but it is still frustrating. :)

- Darren
Left by Darren Gosbell on May 15, 2008 1:28 PM

# re: SSAS: Reporting on Metadata

Requesting Gravatar...
Quite inspiring,

thank you for sharing this report on metadata, it has come in very useful

Thanks
Left by web development company on Oct 13, 2009 8:08 PM

# re: SSAS: Reporting on Metadata

Requesting Gravatar...
Here are this and some other articles on SSAS Metadata: http://ssas-wiki.com/w/Articles#Metadata
Left by Sam Kane on Feb 24, 2011 9:12 AM

Your comment:

 (will show your gravatar)