SSAS Dynamic Management Views (DMV’s) are very useful to query metadata of a cube. For developers, this is quite handy to provide simple documentation for the cubes they build. Some examples :
Al cube in databaseSELECT[CATALOG_NAME]AS[DATABASE],CUBE_CAPTIONAS[CUBE/PERSPECTIVE],BASE_CUBE_NAMEFROM$system.MDSchema_CubesWHERECUBE_SOURCE=1
All dimensions in a cube
SELECT[CATALOG_NAME]as[DATABASE],CUBE_NAMEAS[CUBE],DIMENSION_CAPTIONAS[DIMENSION]FROM$system.MDSchema_DimensionsWHERECUBE_NAME ='Adventure Works'ANDDIMENSION_CAPTION <>'Measures'ORDERBYDIMENSION_CAPTION
all attributes
SELECT[CATALOG_NAME]as[DATABASE],CUBE_NAMEAS[CUBE],[DIMENSION_UNIQUE_NAME]AS[DIMENSION],HIERARCHY_DISPLAY_FOLDERAS[FOLDER],HIERARCHY_CAPTIONAS[DIMENSION ATTRIBUTE],HIERARCHY_IS_VISIBLEAS[VISIBLE]FROM$system.MDSchema_hierarchiesWHERECUBE_NAME ='Adventure Works'ANDHIERARCHY_ORIGIN=2ORDERBY[DIMENSION_UNIQUE_NAME]
All measures
SELECT[CATALOG_NAME]as[DATABASE],CUBE_NAMEAS[CUBE],[MEASUREGROUP_NAME]AS[FOLDER],[MEASURE_CAPTION]AS[MEASURE],[MEASURE_IS_VISIBLE]FROM$SYSTEM.MDSCHEMA_MEASURESWHERECUBE_NAME ='Adventure Works'ORDERBY[MEASUREGROUP_NAME]
Calculated measures
SELECT[MEMBER_UNIQUE_NAME]AS[CALCULATED_MEASURE],[MEMBER_CAPTION]AS[CAPTION],[EXPRESSION]FROM$system.MDSCHEMA_MEMBERSWHERECUBE_NAME ='Adventure Works'AND[MEMBER_TYPE]=4--MDMEMBER_TYPE_FORMULA
Annunci
