MDSCHEMA_MEMBERS Rowset

 

Applies To: SQL Server 2016 Preview

Describes the members within a database.

The MDSCHEMA_MEMBERS rowset contains the following columns.

Column nameType indicatorLengthDescription
CATALOG_NAMEDBTYPE_WSTRThe name of the database to which this member belongs.
SCHEMA_NAMEDBTYPE_WSTRThe name of the schema to which this member belongs.
CUBE_NAMEDBTYPE_WSTRThe name of the cube to which this member belongs.
DIMENSION_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the dimension to which this member belongs.
HIERARCHY_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the hierarchy to which this member belongs.
LEVEL_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the level to which this member belongs.
LEVEL_NUMBERDBTYPE_UI4The distance of the member from the root of the hierarchy. The root level is zero (0).
MEMBER_ORDINALDBTYPE_UI4(Deprecated) Always returns 0.
MEMBER_NAMEDBTYPE_WSTRThe name of the member.
MEMBER_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the member.
MEMBER_TYPEDBTYPE_I4The type of the member:

 MDMEMBER_TYPE_UNKNOWN (0)

 MDMEMBER_TYPE_REGULAR (1)

 MDMEMBER_TYPE_ALL (2)

 MDMEMBER_TYPE_MEASURE (3)

 MDMEMBER_TYPE_FORMULA (4)

 

Note that
                    MDMEMBER_TYPE_FORMULAtakes precedence over MDMEMBER_TYPE_MEASURE. For example, if there is a formula (calculated) member on the Measures dimension, it is listed as MDMEMBER_TYPE_FORMULA.
MEMBER_GUIDDBTYPE_GUIDThe GUID of the member. NULL if no GUID exists.
MEMBER_CAPTIONDBTYPE_WSTRA label or caption associated with the member. Used primarily for display purposes. If a caption does not exist, MEMBER_NAME is returned.
CHILDREN_CARDINALITYDBTYPE_UI4The number of children that the member has. This can be an estimate, so consumers should not rely on this to be the exact count. Providers should return the best estimate possible.
PARENT_LEVELDBTYPE_UI4The distance of the member's parent from the root level of the hierarchy. The root level is zero (0).
PARENT_UNIQUE_NAMEDBTYPE_WSTRThe unique name of the member's parent. NULL is returned for any members at the root level.
PARENT_COUNTDBTYPE_UI4The number of parents that this member has.
DESCRIPTIONDBTYPE_WSTRThis column always returns a NULL value.

This column exists for backwards compatibility
EXPRESSIONDBTYPE_WSTRThe expression for calculations, if the member is of type MDMEMBER_TYPE_FORMULA.
MEMBER_KEYDBTYPE_WSTRThe value of the member's key column. Returns NULL if the member has a composite key.
IS_PLACEHOLDERMEMBERDBTYPE_BOOLA Boolean that indicates whether a member is a placeholder member for an empty position in a dimension hierarchy.

It is valid only if the MDX Compatibility property has been set to 2.
IS_DATAMEMBERDBTYPE_BOOLA Boolean that indicates whether the member is a data member.

Returns True if the member is a data member.
SCOPEDBTYPE_I4The scope of the member. The member can be a session calculated member or global calculated member. The column returns NULL for non-calculated members. This column can have one of the following values:

MDMEMBER_SCOPE_GLOBAL=1

MDMEMBER_SCOPE_SESSION=2
Zero or more additional columnsDBTYPE_UI2No properties are returned if the members could be returned from multiple levels. For example, if the Tree operator is PARENT and SELF for a non-parent child hierarchy, no member properties are returned.

This applies to ragged hierarchies where tree operators could return members from different levels (for example, if the prior level contains holes and parent on members is requested).

The rowset is sorted on CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_UNIQUE_NAME, LEVEL_NUMBER, MEMBER_ORDINAL.

The MDSCHEMA_MEMBERS rowset can be restricted on the columns listed in the following table.

Column nameType indicatorRestriction State
CATALOG_NAMEDBTYPE_WSTROptional.
SCHEMA_NAMEDBTYPE_WSTROptional.
CUBE_NAMEDBTYPE_WSTROptional.
DIMENSION_UNIQUE_NAMEDBTYPE_WSTROptional.
HIERARCHY_UNIQUE_NAMEDBTYPE_WSTROptional.
LEVEL_UNIQUE_NAMEDBTYPE_WSTROptional.
LEVEL_NUMBERDBTYPE_UI4Optional.
MEMBER_NAMEDBTYPE_WSTROptional.
MEMBER_UNIQUE_NAMEDBTYPE_WSTROptional.
MEMBER_CAPTIONDBTYPE_WSTROptional.
MEMBER_TYPEDBTYPE_I4Optional.
TREE_OPDBTYPE_I4(Optional) Only applies to a single member:

 MDTREEOP_ANCESTORS (0x20) returns all of the ancestors.

 MDTREEOP_CHILDREN (0x01) returns only the immediate children.

 MDTREEOP_SIBLINGS (0x02) returns members on the same level.

 MDTREEOP_PARENT (0x04) returns only the immediate parent.

 MDTREEOP_SELF (0x08) returns itself in the list of returned rows.

 MDTREEOP_DESCENDANTS (0x10) returns all of the descendants.
CUBE_SOURCEDBTYPE_UI2(Optional) Default restriction is a value of 1. A bitmap with one of the following valid values:

1 CUBE

2 DIMENSION

OLE DB for OLAP Schema Rowsets

Community Additions

ADD
Show: