Monday, November 14, 2011

Querying Cube (SSAS) Metadata Information

Internal SSAS DMV's can be used to gather MetaData information about SSAS Objects. Following are the list of DMV's which enable to query Cube, Dimension, Hierarchies, Performance information etc using SSMS.

$SYSTEM.MDSCHEMA_CUBES $SYSTEM.MDSCHEMA_DIMENSIONS $SYSTEM.MDSCHEMA_FUNCTIONS $SYSTEM.MDSCHEMA_HIERARCHIES $SYSTEM.MDSCHEMA_INPUT_DATASOURCES $SYSTEM.MDSCHEMA_KPIS $SYSTEM.MDSCHEMA_LEVELS $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS $SYSTEM.MDSCHEMA_MEASUREGROUPS $SYSTEM.MDSCHEMA_MEASURES $SYSTEM.MDSCHEMA_MEMBERS $SYSTEM.MDSCHEMA_PROPERTIES $SYSTEM.MDSCHEMA_SET

References : 

Checking Cube performance: 

http://blogs.microsoft.co.il/blogs/yanivmor/archive/2010/01/27/dmvs-for-analysis-services.aspx

Querying SSAS:

http://richardlees.blogspot.com/2010/07/querying-analysis-services-for-cube.html

http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

http://msdn.microsoft.com/en-us/library/ms126079.aspx