PBIDocumentation_MDX_Queries.sql

/*** MDX Queries ***/

/* All models */
SELECT 
	[CATALOG_NAME] AS SSAS_Database_Name,
	[CUBE_NAME] AS Cube_or_Perspective_Name,
	[CUBE_CAPTION] AS Cube_or_Perspective_Caption,
	[CUBE_TYPE] AS Cube_Type,
	[BASE_CUBE_NAME] AS Base_Cube
FROM 
	$SYSTEM.MDSCHEMA_CUBES
WHERE
	CUBE_SOURCE=1
ORDER BY CUBE_NAME

/* Catalog info: name, description, compatibility level, type, version */
select 
	[CATALOG_NAME], 
	[date_modified], 
	[compatibility_level], 
	[type], 
	[version]
from $SYSTEM.DBSCHEMA_CATALOGS


/* Dimensions */
SELECT 
	[CATALOG_NAME] as [DATABASE Name],
	[CUBE_NAME] AS [CUBE],
	[DIMENSION_NAME] AS Dimension_Name,
	[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,
	[DIMENSION_CAPTION] AS [DIMENSION],
	[DIMENSION_CARDINALITY] AS [Count],
	[DIMENSION_IS_VISIBLE] AS Dimension_Visible
FROM $system.MDSchema_Dimensions
WHERE 
	[CUBE_NAME] ='Model'
AND DIMENSION_CAPTION <> 'Measures'
ORDER BY DIMENSION_CAPTION


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


/* Attributes with key and 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]


/* Measures */
SELECT
	[CATALOG_NAME] AS SSAS_Database_Name,
	[CUBE_NAME] AS Cube_or_Perspective_Name,
	[MEASUREGROUP_NAME] AS MeasureGroup_Name,
	[MEASURE_NAME] AS Measure_Name,
	[MEASURE_Caption] AS Measure_Caption,
	[MEASURE_IS_VISIBLE] AS Dimension_Visible,
	[MEASURE_AGGREGATOR] AS Measure_Aggregator,
	[DEFAULT_FORMAT_STRING] AS [Format_String],
	[EXPRESSION] AS Calculated_Measure_Expression
FROM
	$SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME  ='Model'
ORDER BY
	[MEASURE_NAME]


 /* Calculated Measures */
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'

select * from $SYSTEM.DBSCHEMA_TABLES
where table_schema <> '$SYSTEM'