Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
SSRS EXPERT 41
BI 36
#BI 33
brh 28
SSIS BEST PRACTICES 23
TSQL 19
MS BI STACKS 18
SSRS 17

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

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

Feb 11 2014 9:17PM by Sherry Li   

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

Tags: 


Sherry Li
14 · 12% · 3827
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]