SSAS-实用的DMV查询

最新推荐文章于 2021-02-04 21:22:58 发布

incognito007

最新推荐文章于 2021-02-04 21:22:58 发布

阅读量1.6k

点赞数

分类专栏： BI SSAS 文章标签： hierarchy database sql attributes

本文链接：https://blog.csdn.net/sfrankzhang/article/details/7582126

版权

BI 同时被 2 个专栏收录

47 篇文章 0 订阅

订阅专栏

SSAS

15 篇文章 0 订阅

订阅专栏

以下查询，比较实用。

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