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