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 of the cubes they build.
There are some excellent posts on SSAS DMVs by Yaniv Mor and Vidas Matelis
Pasted 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]
Benny Austin
Advertisement
Like this:
One blogger likes this post.
Finally, just what I was looking for. So much less work than having a procedure in .Net using DSO to get a cube’s metadata!
Posted by Jason | March 25, 2011, 05:54I am a technical writer and working on a data dictionary for our BI project. I was incredibly excited to read and use your blog information for helping me document our Data Dictionary’s Cube.
I have found lots of information on the web for using a Linked Server with Reporting Services in order to create reports, but I don’t understand why this is.
Any help would be greatly appreciated, which will allow me to use Report Builder 3.0 (or BIDS) to create Cube Reports.
Posted by Nicole Paul | June 11, 2011, 01:58hi Nicole, check out Alex Whittles posts to create SSRS reports from DMV – http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/
Posted by Benny Austin | June 14, 2011, 23:06i am not able to find Source column information for cube attribute with above given statements.
Posted by hema | June 28, 2011, 01:48Source column info is available in MDSchema_levels rowset in these columns [LEVEL_NAME_SQL_COLUMN_NAME], [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] and [LEVEL_KEY_SQL_COLUMN_NAME]. Have a look at the Hierarchy Levels in the post query which has since been updated.
Posted by Benny Austin | June 28, 2011, 07:11Hi! This is so great! I’m wondering if we can pull has the underlying Data Source View Table and Column name that populates the dimension attribute. In BIDS, this will be under the Properties window of the Attibute under Source ->NameColumn. Thanks!
Posted by PHSCRE | August 6, 2011, 03:02Yes you can get the underlying key and name columns from the MDSchema_levels rowset. The [LEVEL_KEY_SQL_COLUMN_NAME] & [LEVEL_NAME_SQL_COLUMN_NAME] columns give the underlying key and name columns respectively in Table.Column format. Have a look at the DMv query under “All Levels of Hierarchies (user-defined)”
Posted by Benny Austin | August 6, 2011, 22:11Useful blog post. Thanks!
Posted by Tom Huguelet | August 30, 2011, 22:25Hello!
I just started to work with SSAS and I have the following problem: I want to use the Description property for some attributes but when I run the query using $system.MDSchema_hierarchies attributes from one dimension have the Dimension property and attributes from another dimension do not have the Description property.
Do you know any reason for this behavior?
Txs!
Posted by Ramona | December 14, 2011, 02:09Hi, could you pls provide more details on the problem you are encountering. If possible send me the query
Posted by Benny Austin | December 15, 2011, 17:26Hi I want to set the WHERE CLAUSE to be set as AND BASE_CUBE_NAME IS EMPTY or NULL
How can i do that
SELECT CUBE_NAME , BASE_CUBE_NAME , *
FROM $system.MDSCHEMA_CUBES
WHERE Cube_Source = 1
AND BASE_CUBE_NAME = ” — <<——- My question is in this section
Thanks
Posted by Nik-Shahriar Nikkhah | January 25, 2012, 04:06Boolean comparison is not allowed in DMV query. Instead you could use a VBA function like LEN to get similar result.
SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME,VBA!ISNULL(BASE_CUBE_NAME) AS ISNULL
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1
AND VBA!LEN(BASE_CUBE_NAME)=0
Posted by Benny Austin | January 25, 2012, 13:19Thanks benny for the reply, I did thank you at
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/68ee34fa-f56b-406e-b255-41adad5f799f
Thanks again for tyhe help
Sincerely
Nik- Shahriar Nikkhah
Posted by Nik-Shahriar Nikkhah | January 26, 2012, 00:43