SSAS: Using DMV Queries to get Cube Metadata

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.

There are some excellent posts on SSAS DMVs by Yaniv Mor and Vidas Matelis

Given below are some of the queries which I found particularly useful. A complete reference to the DMV schema rowset is available in msdn – http://msdn.microsoft.com/en-us/library/ms126079.aspx. Execute these queries from SQL Server Management Studio (SSMS) using MDX or DMX query editor.

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

 

--Calculated Measures/Members
SELECT [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE],
	[MEMBER_CAPTION] AS [CAPTION],
	[EXPRESSION]
FROM $system.MDSCHEMA_MEMBERS
WHERE CUBE_NAME ='Adventure Works'
AND [MEMBER_TYPE]=4 --MDMEMBER_TYPE_FORMULA

 

--Dimension Usage/Fact-Dimension Bus Matrix
SELECT	[MEASUREGROUP_NAME] AS [MEASUREGROUP],
		[MEASUREGROUP_CARDINALITY],
		[DIMENSION_UNIQUE_NAME] AS [DIM],
		[DIMENSION_GRANULARITY] AS [DIM_KEY],
		[DIMENSION_CARDINALITY],
		[DIMENSION_IS_VISIBLE] AS [IS_VISIBLE],
		[DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM]
FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE [CUBE_NAME] ='Adventure Works'
	AND [MEASUREGROUP_NAME] ='Internet Sales'

Benny Austin


109 comments

    • Sumanth, Measures are usually numeric and you can get the precision if numeric MDSCHEMA_MEASURES Rowset. Don’t think you can get the same info for dimensions as the key value can be different from name value.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s