ssas从mysql获取数据库_SSAS-实用的DMV查询

以下查询，比较实用。

--All Cubes in database

SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME

FROM $system.MDSchema_Cubes

WHERE CUBE_SOURCE=1

--All dimensions in Cube

SELECT [CATALOG_NAME] as [DATABASE],

CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION]

FROM $system.MDSchema_Dimensions

WHERE CUBE_NAME ='Adventure Works'

AND DIMENSION_CAPTION 'Measures'

ORDER BY DIMENSION_CAPTION

--All Attributes

SELECT [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_hierarchies

WHERE CUBE_NAME ='Adventure Works'

AND HIERARCHY_ORIGIN=2

ORDER BY [DIMENSION_UNIQUE_NAME]

--All Attributes with key and name columns

SELECT [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_levels

WHERE CUBE_NAME ='Adventure Works'

AND level_origin=2

AND 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_hierarchies

WHERE CUBE_NAME ='Adventure Works'

and HIERARCHY_ORIGIN=1

ORDER 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_hierarchies

WHERE CUBE_NAME ='Adventure Works'

AND HIERARCHY_ORIGIN=3

ORDER 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 Measures

SELECT [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]

分享到：

2012-05-19 04:51

浏览 605

评论