SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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

Examples how to get metadata

– List of cubes in current database
SELECT CUBE_NAME
 , BASE_CUBE_NAME — For perspective this will show source cube name
  FROM $system.MDSCHEMA_CUBES
 WHERE CUBE_SOURCE = 1 — Just cubes, =2 - dimensions.

– Show dimension in one cube or perspective
SELECT DIMENSION_ORDINAL
 , DIMENSION_NAME
 , DIMENSION_CARDINALITY
 , DEFAULT_HIERARCHY
  FROM $SYSTEM.MDSCHEMA_DIMENSIONS
 WHERE CUBE_NAME = ‘Direct Sales’ — Perspective name
ORDER BY DIMENSION_ORDINAL

– Show measuregroups in one cube or perspective
SELECT CUBE_NAME, MEASUREGROUP_NAME
  FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
 WHERE CUBE_NAME = ‘Direct Sales’

– Show dimensions related to one measure group
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’

– Show available KPIs
SELECT CUBE_NAME, MEASUREGROUP_NAME, KPI_NAME
 , KPI_DESCRIPTION
 , KPI_VALUE
– , KPI_GOAL , KPI_STATUS , KPI_TREND — etc
  FROM $SYSTEM.MDSCHEMA_KPIS
 WHERE CUBE_NAME = ‘Direct Sales’

– List of hierarchies in one dimension
SELECT [DIMENSION_UNIQUE_NAME]
 , HIERARCHY_NAME
 , [DEFAULT_MEMBER]
  FROM $SYSTEM.MDSCHEMA_HIERARCHIES
 WHERE [DIMENSION_UNIQUE_NAME] = ‘[Account]’ — Dimension unique name (should be in []’
   AND CUBE_NAME = ‘$Account’ — Dimension could be used in multiple cubes. This enforces just dimension cube
ORDER BY HIERARCHY_ORDINAL

Examples how to query data

You can also write queries to get actual dimension members from dimension or fact records from measure group. I found that performance on measure group queries was quite slow.

So more examples:

– SELECT members from Account dimension
SELECT * FROM [$Account].[$Account]

–SELECT records from [Exchange Rates] measure group in [Direct Sales] perspective
SELECT * FROM [Direct Sales].[Exchange Rates]

These examples should give you good idea what kind of metadata and data is now accessible.