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. Some examples :
Al cube 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 a cube
SELECT
[CATALOG_NAME]
as
[
DATABASE
],
CUBE_NAME
AS
[
CUBE
],DIMENSION_CAPTION
AS
[DIMENSION]
FROM
$system.MDSchema_Dimensions
WHERE
CUBE_NAME =
'Adventure Works'
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 =
'Adventure Works'
AND
HIERARCHY_ORIGIN=2
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 =
'Adventure Works'
ORDER
BY
[MEASUREGROUP_NAME]
Calculated measures
SELECT
[MEMBER_UNIQUE_NAME]
AS
[CALCULATED_MEASURE],
[MEMBER_CAPTION]
AS
[CAPTION],
[EXPRESSION]
FROM
$system.MDSCHEMA_MEMBERS
WHERE
CUBE_NAME =
'Adventure Works'
AND
[MEMBER_TYPE]=4
--MDMEMBER_TYPE_FORMULA