Following along on my recent theme of exploring the metadata rowsets and the new Dynamic Management Views (DMV) in SSAS 2008. I have added a new DMV function to the Analysis Services Stored Procedure project (www.codeplex.com/ASStoredProcedures) which is included in the v1.2 release that I put out just before Christmas.
This all started off when I decided to look at extending the existing Discover() function to provide for sorting and filtering. And I originally started off by building a wrapper function around the existing Discover() function with the following syntax:
DMV( rowset [, whereClause [, sortOrder ]] )
Which was functional, but I still had to add the option of selecting a sub-set of columns and I felt that the syntax was was not as "clean" as I would have liked. It was at this point that Mosha suggested that I could write a parser and pass in a SELECT statement as a string. So from that I have developed the function to have the following signature:
DMV( query [, restrictions] )
Which lets us do things like the following to get a list of cubes:
CALL ASSP.DMV("
SELECT CUBE_NAME , BASE_CUBE_NAME
FROM $system.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1
");
The query in the example above came from a blog post by Vidas where he was running through the new DMV functionality in SSAS 2008. You can copy and paste any of the queries in that post and run them against SSAS 2005 using the ASSP.DMV() function.
So, how does this DMV function compare to DMV's in SSAS 2008? Well, below are two lists, the first is a list of the limitations that the SSAS 2008 DMVs currently have as of the November 2007 CTP (CTP5).
- COUNT, SUM (and other aggregate functions) do not work (you cannot create your own derived columns)
- string functions like LEFT do not work
- ORDER BY <number> does not ORDER
- JOINS appear not to work
- SELECT DISTINCT does not work
- ORDER BY clause only allows one field to order by. Adding second field raises error: “Error (Data mining): Only one order expression is allowed for TOP expression at line 1, column 1″
- the LIKE operator does not work in the WHERE clause.
Rather than extending the SQL support in MDX, the SSAS team chose to exploit the SQL subset that was supported by DMX, this vastly reduced the amount of effort required to implement the DMV feature, but it did come with the above limitations.
The following list are the limitations of the SSAS 2008 DMVs (as of CTP5) that ASSP.DMV() overcomes.
- SELECT DISTINCT works, only returning distinct rows
- ORDER BY clause accepts multiple fields
- the LIKE operator works in the WHERE clause
- The $System schema is optional (the fact that you are calling the DMV function implies this anyway, so this is not such a big issue)
In addition to these points ASSP.DMV() allows you to execute queries against rowsets that have mandatory restrictions which overcomes the issue in SSAS 2008 that was reported here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2477175&SiteID=1
ASSP.DMV() has two mechanisms for doing this. Firstly it has simple restriction matching, so if you have a query with filter conditions that repeat the following pattern "<COLUMN> = <VALUE>" and are linked with logical ANDs then the DMV function can derive the restriction. Any deviation from this pattern, including the use of brackets will cause this matching to fail.
However, if you wish to use a more complicated filter you could still do so by manually passing in the restrictions XML yourself using the optional second parameter of the ASSP.DMV() function. (Note that the restrictions would be applied first and then any filtering in the WHERE clause would be applied after that)
So with ASSP.DMV() you can execute the following:
-- This call demonstrates how ASSP.DMV can map WHERE filters to the restrictions
-- of the XML/A discover call in order to support querying rowsets that have
-- required restrictions.
CALL ASSP.DMV("
SELECT *
FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT
WHERE DATABASE_NAME = 'Adventure Works DW'
AND CUBE_NAME = 'Adventure Works'
AND MEASURE_GROUP_NAME = 'Internet Sales'
AND PARTITION_NAME = 'Internet_Sales_2003'
ORDER BY DIMENSION_NAME, ATTRIBUTE_COUNT_MAX desc
");
Which produces the following output:
Or you can execute it passing in the restrictions XML explicitly (which is what ASSP.DMV infers from the WHERE clause in the call above):
-- this is the same as the call above, just with an explicit restrictions clause
-- rather than using an implied one from the WHERE conditions.
CALL ASSP.DMV("
SELECT *
FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT
ORDER BY DIMENSION_NAME, ATTRIBUTE_COUNT_MAX desc"
,"<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>
<CUBE_NAME>Adventure Works</CUBE_NAME>
<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>
<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>");
The fact that SSAS 2008 DMVs do not work with rowsets that have mandatory restrictions leads me to believe that they have been implemented in such a way that they always do an unrestricted XMLA discover call. Which means that it must be storing the entire XMLA result in memory and then processing the WHERE and ORDER BY clauses. In contrast, the ASSP.DMV() function will always attempt to do restriction matching of any filter conditions for every query (if the where clause is appropriate) which will mean that the SSAS server will often be sending back less data which should help performance and memory usage.
Because you have to pass in your query as a string with ASSP.DMV(), it obviously does not support syntax highlighting, however it does support all MDX comment types and you can use white space to lay out your query to your liking. (Below is an example where I have highlighted the comments in green to make them easier to see)
CALL ASSP.DMV("
SELECT CUBE_NAME
, BASE_CUBE_NAME
// the DMV function suppports all the MDX comment types
FROM $system.MDSCHEMA_CUBES
/* the $system schema is optional */ WHERE
CUBE_SOURCE = 1 -- Just cubes, 2 - dimensions.
");
If you wanted to get a list of all dimensions that relate to a given measure group then you could do so with a query like the following:
Call ASSP.DMV("
SELECT CUBE_NAME
, MEASUREGROUP_NAME
, [DIMENSION_UNIQUE_NAME]
, DIMENSION_IS_VISIBLE
, DIMENSION_GRANULARITY
FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE CUBE_NAME = ‘Direct Sales’
AND MEASUREGROUP_NAME = ‘Internet Customers’");
In order to demonstrate a couple of features at once, let's suppose that you want to get a list of all dimensions for any cube that contains the word "Sales" in it's name and we want to sort the list of dimensions by the dimension ordinal and then by the the cube name in descending order. This is not possible with the native DMV functionality in SSAS 2008 (as of the Nov 2007 CTP), but with ASSP.DMV you can execute the following:
Call ASSP.DMV("
SELECT DIMENSION_ORDINAL
, DIMENSION_NAME
, DIMENSION_CARDINALITY
, DEFAULT_HIERARCHY
, CUBE_NAME
FROM MDSCHEMA_DIMENSIONS
WHERE CUBE_NAME LIKE '%Sales%'
ORDER BY DIMENSION_ORDINAL, CUBE_NAME desc
");
I have highlighted the two columns in the ORDER BY clause in the following output:
In order to find out what sort of data is available, running either of the following will give you a list of all the available rowsets which you can query using ASSP.DMV()
CALL ASSP.DiscoverRowsets();
CALL ASSP.DMV("SELECT * FROM DISCOVER_SCHEMA_ROWSETS");
(with the exception of DISCOVER_XML_METADATA which returns a hierarchical results set, but you can get at that using ASSP.DiscoverXmlMetadata() function)
A Note on Querying Data
Also it should be noted that the last two examples Vidas listed in his post will actually currently work against SSAS 2005, the only trouble is that these sorts of queries can only be done by Administrators, so you can't really use them in anything that standard users will interact with. I have not double checked if this same restriction applies in 2008, but I would expect that it does.
SELECT * FROM [$Account].[$Account] ;
SELECT * FROM [Direct Sales].[Exchange Rates];
In fact you should find that you can get at most of the tables that you can see in the following rowset if you are an administrator.
CALL ASSP.DMV("SELECT * FROM DBSCHEMA_TABLES");