Friday, 24 February 2017

Get column names of measure and dimensions from sql Cube

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  ='data warehouse'AND HIERARCHY_ORIGIN=2

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  ='data warehouse'