SSAS: Using DMV Queries to get Cube Metadata for Power BI Model

dax studio queries.txt

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  ='Model'
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  ='Model'
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  ='Model'
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  ='Model'
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  ='Model'
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  ='Model'
 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  ='Model'
 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  ='Model'
 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 ='Model'
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] ='Model'
    AND [MEASUREGROUP_NAME] ='Internet Sales'
    
    find more at: https://bennyaustin.com/2011/03/01/ssas-dmv-queries-cube-metadata/