1

Topic: To receive the list of all dimensions

Good afternoon.
That is possible with what means to preempt the list of all dimensions from a cube or to look in what at that a file?

2

Re: To receive the list of all dimensions

Connect to OLAP-project CLR the assembly:
http://www.codeplex.com/ASStoredProcedures
Also cause it through Action

3

Re: To receive the list of all dimensions

Screenshot

4

Re: To receive the list of all dimensions

assmsk;

-- To OPEN In SQL Mngt Studio, connection to Analysis Services and to an interesting DB
--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
--The list of cubes in multidimentional basis
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
--The list of measurements cubed - search in a cube
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
--The list of measures cubed - search in a cube
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 AND ATTRIBUTES IN A CUBE - SPECIFY CUBE_NAME BELOW
--The list of measurements and its 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 = ' CubeName'
AND HIERARCHY_ORIGIN=2
ORDER BY [DIMENSION_UNIQUE_NAME]
--DIMENSIONS AND HIERARCHIES IN A CUBE - SPECIFY CUBE_NAME BELOW
--The list of measurements and hierarchies in it
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]

5

Re: To receive the list of all dimensions

tashkafox, many thanks. That that is necessary