Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

MDX+SSAS #38–CALL ASSP.DMV() to document cubes in SSAS 2005

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.

image

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().

image

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

image

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


Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...