Q: Which string functions can be used in the queries on SSAS DMVs?
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).