How to query SSAS 2008 DMVs when restrictions are required

Question

To query certain DMVs in SSAS 2008 you have to specify "restrictions".

That is if I am executing code:

 

SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT

I am getting following error message:

 

Executing the query ...
XML for Analysis parser: The CUBE_NAME restriction is required but is missing from the request.

Execution complete

 

I tried adding WHERE CUBE_NAME='MyCube', but that does not work.

 

Same CUBE_NAME restriction required for $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT DMV.But for $SYSTEM.DISCOVER_INSTANCES  restriction is INSTANCE_NAME.

 

So, how do you specify this restriction?

Answer 1

I have doubts that it can be done in pure MDX. As I understand any MDX query is executed as part of <Command></Command> XMLA statement, but instead you need <Discover></Discover>.

As an alternative I can suggest you to use XMLA as in example below:

<Discover xmlns="urn: schemas-microsoft-com:xml-analysis"><!-- remove the space between "urn:" and "schemas" -->
  <RequestType>DISCOVER_PARTITION_DIMENSION_STAT</RequestType>
  <Restrictions>
    <RestrictionList>
      <CUBE_NAME>Adventure Works</CUBE_NAME>
      <DATABASE_NAME>Adventure Works DW</DATABASE_NAME>
      <MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>
      <PARTITION_NAME>Internet_Sales_2001</PARTITION_NAME>
    </RestrictionList>
  </Restrictions>
  <Properties/>
</Discover>

This is not an answer, but I hope can be used as a workaround.

Regrads,
Nicolae.

Answer 2

Nicolae,

 

Thanks for reply. There should be a way in SELECT query to specify restrictions. There are over 50 DMVs available and just some of them complain this restriction beeing a requirement. I am guessing I should be able to specify CUBE_NAME somewhere, I just need to know where. Or it is quite possible that this feature is not yet fully implemented as documentation is not awailable, but again then I would want to know that this is the case.

 

Thanks,

 

Vidas Matelis

 

Answer 3

OK, I checked it out with the team and now it is for sure!

In Katmai CTP5 there is no way to push restrictions directly from pure MDX, instead you can use ASSP (http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures) Stored Procedures to call Discover function. If you follow this path you will be able to accomplish things you desire in MDX.

Great feedback,
Nicolae

Answer 4

As a side note, ASSP is soon going to have some new sproc signatures that Darren just coded that let you do Katmai-style DMV SQL. And that will work for AS2005, too. Stay tuned to that codeplex link Nicolae mentioned.