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