DMVs in Analysis Service -- Part 3 (MDSCHEMA_MEMBERS)

Till now we have used MDX query to get hierarchical members in a hierarchy, But after DMVs its now possible to get this values using DMV MDSCHEMA_MEMBERS
It will be a fun to use DMV to get value. Lets more explore in it.



For previous part of this article

DMVs in Analysis Service -- Part 2 (MDSCHEMA)

DMVs in Analysis Service -- Part 1 (DBSCHEMA)


SELECT * FROM $SYSTEM.MDSCHEMA_MEMBERS

SELECT TOP 10 * FROM $SYSTEM.MDSCHEMA_MEMBERS
WHERE [MEMBER_TYPE] = '3'

Type of Member_Type

1 Regular
2 ALL Member
3 Measure
4 Formula
0 Unknown

Formula has precedence over measure

Now lets look at queries to get hierarchical information

To get all ancestors of a member

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] ='ADVENTURE WORKS',
[MEMBER_UNIQUE_NAME] = '[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]',
TREE_OP = '32'
)


To get immediate children of a member

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] ='ADVENTURE WORKS',
[MEMBER_UNIQUE_NAME] = '[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]',
TREE_OP = '1'
)

To get immediate immediate parent

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] ='ADVENTURE WORKS',
[MEMBER_UNIQUE_NAME] = '[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]',
TREE_OP = '4'
)



To get self

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] ='ADVENTURE WORKS',
[MEMBER_UNIQUE_NAME] = '[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]',
TREE_OP = '8'
)

To get all descendants

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] ='ADVENTURE WORKS',
[MEMBER_UNIQUE_NAME] = '[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]',
TREE_OP = '16'
)