SSAS-实用的DMV查询

以下查询，比较实用。

--All Cubes in databaseSELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAMEFROM $system.MDSchema_CubesWHERE CUBE_SOURCE=1

--All dimensions in CubeSELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION] FROM $system.MDSchema_DimensionsWHERE CUBE_NAME  ='Adventure Works'AND DIMENSION_CAPTION  'Measures'ORDER BY DIMENSION_CAPTION

--All AttributesSELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],HIERARCHY_IS_VISIBLE AS [VISIBLE] FROM $system.MDSchema_hierarchiesWHERE CUBE_NAME  ='Adventure Works'AND HIERARCHY_ORIGIN=2ORDER BY [DIMENSION_UNIQUE_NAME]

--All Attributes with key and name columnsSELECT [CATALOG_NAME] as [DATABASE],      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],      LEVEL_CAPTION AS [ATTRIBUTE],      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]FROM $system.MDSchema_levelsWHERE CUBE_NAME  ='Adventure Works'AND level_origin=2AND LEVEL_NAME <> '(All)'order by [DIMENSION_UNIQUE_NAME]

--All Hierarchies (user-defined)SELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],HIERARCHY_IS_VISIBLE AS [VISIBLE] FROM $system.MDSchema_hierarchiesWHERE CUBE_NAME  ='Adventure Works'and HIERARCHY_ORIGIN=1ORDER BY [DIMENSION_UNIQUE_NAME]

--All Hierarchies (Parent-Child)SELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],HIERARCHY_IS_VISIBLE AS [VISIBLE]FROM $system.MDSchema_hierarchiesWHERE CUBE_NAME  ='Adventure Works'AND HIERARCHY_ORIGIN=3ORDER BY [DIMENSION_UNIQUE_NAME]

--All Levels of Hierarchies (user-defined)SELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],LEVEL_CAPTION AS [LEVEL],    [LEVEL_NAME],[LEVEL_NUMBER] AS [LEVEL NUMBER],[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN] FROM $system.MDSchema_levels WHERE CUBE_NAME  ='Adventure Works' AND level_origin=1 order by [DIMENSION_UNIQUE_NAME]

--All Levels of Hierarchies (Parent-Child)SELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],LEVEL_CAPTION AS [LEVEL],    [LEVEL_NAME],[LEVEL_NUMBER] AS [LEVEL NUMBER],[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN] FROM $system.MDSchema_levels WHERE CUBE_NAME  ='Adventure Works' AND LEVEL_ORIGIN=3 order by [DIMENSION_UNIQUE_NAME]

--All MeasuresSELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],[MEASURE_IS_VISIBLE]FROM $SYSTEM.MDSCHEMA_MEASURES WHERE CUBE_NAME  ='Adventure Works' ORDER BY [MEASUREGROUP_NAME]