-- ОТКРЫТЬ В SQL Mngt Studio, подключение к Analysis Services и к интересующей БД

--https://dataself.com/wiki/Cube_Metadata_MDX_Query
--https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/
--https://docs.microsoft.com/en-us/sql/analysis-services/schema-rowsets/ole-db-olap/mdschema-levels-rowset

--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 = 'CubeName'
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 = 'CubeName'
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 = 'CubeName'
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 ='CubeName'
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 ='CubeName'
and HIERARCHY_ORIGIN=1
ORDER BY [DIMENSION_UNIQUE_NAME]