In SSAS 2005 to access SSAS metadata you had to use object model. In SSAS 2008 November CTP Microsoft introduced schema rowsets as an alternative way to access metadata. I did some tests last few days and here I’ll post examples of what could be done. Generally it is quite easy to write these queries. BOL already have description for most of the tables and fields you can query. What was not so easy is to get metadata on structure that is hierarchical. That is there is an easy way to get a list of hierarchies in one dimension. But to get list of levels of hierarchies of dimensions becomes not an easy task.
SELECT statements you can write on these rowsets appear to be quite limited. For example:
- SELECT DISTINCT does not return DISTINCT values
- ORDER BY clause accepts just 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″
- COUNT, SUM does not work
- WHERE clause works
- ORDER BY <number> does not ORDER, but no error
- JOINS appear not to work
- LIKE does not work
- string functions like LEFT do not work
As I did not find restriction list in documentation, list above is from my experience. It could be that I just did not do my tests properly, but I am sure sooner or latter there will be official list of what is supported.
Bellow are examples of queries that are very simple, but enough for anyone to get an idea of what it is possible. All these examples where run in Adventure Works DW database.