While you are writing and testing MDX queries in Microsoft SQL Server management Studio (SSMS), how many times you find yourself need to open the Visual Studio project in SQL Server Data Tools (SSDT) just to find out the dimensionality of a measure group, or the calculation formula for a calculated measure?

Use Dynamic Management Views (DMV) to retrieve cube metadata

What I am getting at is to retrieve the cube metadata. Fortunately, Analysis Services has exposed a collection of Dynamic Management Views (DMV) as tables. We can query these tables with SELECT statement.

In our book MDX 2012 Cookbook, in Chapter 9 On the Edge, we have devoted two recipes to show how to use these DMVs to fast-document a cube, and to monitor cube activity and usage.

Unfortunately if you still have SSAS 2005 in your environment, the following SELECT (DMV) will not work.

Wrap up the SELECT (DMV) statement inside CALL ASSP.DMV()

If you are into programming, you are welcome to try to re-invent the wheel to retrieve all the metadata using the classes in the Analysis Management Objects (AMO) library.

However, there is a shortcut, that is to use the open source project Analysis Services Stored Procedure Project on CodePlex.

Once you finished installing the ASSP assembly on the SSAS server, you can simply wrap up the above SELECT (DMV) statement inside CALL ASSP.DMV().

The results from the above query will show the dimensionality of each measure group in the cube.

There is more

In the MDX 2012 Cookbook, in the same chapter, there is also a recipe that shows how to set up the Analysis Services Stored Procedure. If you don’t have the book, you can also reference my blog:

SSAS #28 – Setup to Learn SSAS Stored Procedures

If you want to know more about how to retrieve cube metadata using SSAS schema rowsets, such as the example above, MDSCHEMA_MEASUREGROUP_DIMENSIONS, check out the MSDN article:

Analysis Services Schema Rowsets