Cube Metadata MDX Query

From DataSelf Knowledge Base
Jump to navigation Jump to search

Cube Metadata MDX Query

From SQL Mgt Studio, connect to AS and then right-click on a cube database name, select New Query > MDX, then copy and paste the expressions below, run the queries. Go to the Results tab on the bottom panel, right-click Select All, right-click Copy and now you can paste results into Excel.

Lori, take a look and see if you think this can help in some cases…

— CUBES IN A DATABASE

SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1

— DIMENSIONS IN A CUBE – SPECIFY CUBE_NAME BELOW

SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION]
FROM $system.MDSchema_Dimensions
WHERE CUBE_NAME =‘Sales Cube’
ORDER BY DIMENSION_CAPTION

— MEASURES IN A CUBE – SPECIFY CUBE_NAME BELOW

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 =‘Sales Cube’
ORDER BY [MEASUREGROUP_NAME]

— DIMENSIONS & ATTRIBUTES IN A CUBE – SPECIFY CUBE_NAME BELOW

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 =‘Sales Cube’
AND HIERARCHY_ORIGIN=2
ORDER BY [DIMENSION_UNIQUE_NAME]

— DIMENSIONS & HIERARCHIES IN A CUBE – SPECIFY CUBE_NAME BELOW

SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],
LEVEL_CAPTION AS [LEVEL],[LEVEL_NUMBER] AS [LEVEL NUMBER]
FROM $system.MDSchema_levels
WHERE CUBE_NAME =‘Sales Cube’
AND level_origin=1
AND LEVEL_NAME = ‘(All)’
order by [DIMENSION_UNIQUE_NAME]

— HIERARCHIES IN A CUBE – SPECIFY CUBE_NAME BELOW

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 =‘Sales Cube’
and HIERARCHY_ORIGIN=1
ORDER BY [DIMENSION_UNIQUE_NAME]

References

http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

See also http://msdn.microsoft.com/en-us/library/ms126038.aspx