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

Q: Which string functions can be used in the queries on SSAS DMVs?

A: SELECT statements on SSAS DMVs accepts just a very few string functions:

• mid(<string>,<start>,<length>)
• left(<string>, <length>)
• right(<string>,<length>)
• len(<string>)

Here are examples:

1. Following query returns a list of available cubes that start with character "$". Internally SSAS treats dimensions as cubes and these "dimension cubes" starts with character "$":

SELECT CUBE_NAME
  FROM $system.MDSCHEMA_CUBES
 WHERE MID(Cube_Name,1,1) = '$'

2. Same query as above, but instead of mid function we used left function:

SELECT CUBE_NAME
  FROM $system.MDSCHEMA_CUBES
 WHERE MID(Cube_Name,1,1) = '$'

3. Following query returns a list of available cubes (not including perspectives) in the database:

SELECT CUBE_NAME, LAST_SCHEMA_UPDATE, LAST_DATA_UPDATE
, BASE_CUBE_NAME
FROM $system.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1 /* 1=Cube/Perspective, 2=Dimension */
AND LEN(BASE_CUBE_NAME) = 0

In this statement we choose to use LEN function to test if BASE_CUBE_NAME field is empty. Note: here using BASE_CUBE_NAME = '' does not work (I do not know why).