DMVs in Analysis Service –Part4

For previous part of this article

DMVs in Analysis Service — Part 3 (MDSCHEMA_MEMBERS)

DMVs in Analysis Service — Part 2 (MDSCHEMA)

DMVs in Analysis Service — Part 1 (DBSCHEMA)

Information for Measures

SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’

SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’
and [MEASURE_AGGREGATOR] = ‘1’

Aggregation type

1 Sum
2 Count
3 Min
4 Max
5 Avg
6 Var
7 STDEV

You can get more information from here

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

To get information for member/cell property

SELECT * FROM $SYSTEM.MDSCHEMA_PROPERTIES

SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_PROPERTIES,
CUBE_SOURCE = ‘1’ — 1 for CUBE
— 2 for DIMENSION
)

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

For information on functions

SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS

gives information for available functions.
Parameter list provides list of parameters which we need to provide to function while using it

SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS WHERE ORIGIN = ‘1’

Origin
1 for MDX
2 for user defined”

You can get more information from here

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

For information on Actions

SELECT * FROM $SYSTEM.MDSCHEMA_ACTIONS

This one is giving error while running it.
I have created a bug here

For information on KPIS

SELECT * FROM $SYSTEM.MDSCHEMA_KPIS

You can get more information from here

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

For information on MeasureGroups

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
WHERE CUBE_NAME = ‘ADVENTURE WORKS’

You can get more information from here

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

For information on MeasureGroup Dimensions

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE CUBE_NAME = ‘ADVENTURE WORKS’

You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126192.aspx

For information on Datasources

SELECT * FROM $SYSTEM.MDSCHEMA_INPUT_DATASOURCES

Here you can get last time when data source is updated

You can get more information from here

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