SSAS: get Cube Metadata

funichi88 Sql server

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
WHERECUBE_SOURCE=1
All dimensions in a cube
SELECT[CATALOG_NAME] as[DATABASE],
CUBE_NAME AS[CUBE],DIMENSION_CAPTION AS[DIMENSION]
 FROM$system.MDSchema_Dimensions
WHERECUBE_NAME  ='Adventure Works'
ANDDIMENSION_CAPTION <> 'Measures'
ORDERBYDIMENSION_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
WHERECUBE_NAME  ='Adventure Works'
ANDHIERARCHY_ORIGIN=2
ORDERBY[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
 WHERECUBE_NAME  ='Adventure Works'
 ORDERBY[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

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Gravatar
Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. ( Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. ( Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. ( Chiudi sessione /  Modifica )

Annulla

Connessione a %s...